This guide walks you through building an Excel report that shows checkpoint assessment results across all learners and programmes. The report covers scores, completion status, time spent, and checkpoint deadlines.
The checkpoint assessments feed currently uses LearnerUniqueId (a GUID) rather than the numeric learner ID used in other feeds. This means the report cannot yet be enriched with learner names, programme details, or case owner information from the Users feed. A follow-up release will add a numeric LearnerId field. Once available, an updated version of this report will include learner details. See Checkpoint assessments feed reference for the latest on this.
What you need before you start
Microsoft Excel (desktop version with Power Query, included in Excel 2016 and later)
Your Aptem tenant URL, for example
https://yourtenant.aptem.co.ukYour OData API key (contact your Aptem administrator if you do not have one)
Access to the OData v2 feeds on your tenant
What this report includes
Column |
Description |
|---|---|
Checkpoint name |
The name of the checkpoint |
Learner unique ID |
The learner's GUID identifier (a numeric learner ID will be added in a future release) |
Programme ID |
The programme the checkpoint belongs to |
Checkpoint type |
How the checkpoint was triggered (for example PeriodicGeneration) |
Status |
Current status of the checkpoint (for example Completed) |
Questions count |
Total number of questions in the checkpoint |
Score |
The learner's score |
Correct answers |
Number of questions answered correctly |
Created date |
When the checkpoint was created |
Completed date |
When the checkpoint was completed (blank if not yet complete) |
Checkpoint end date |
The deadline by which the checkpoint must be completed |
Time spent |
Time the learner spent on the checkpoint, in seconds |
Understanding Power Query
Power Query is a data connection tool built into Excel. It lets you pull data from external sources (such as the Aptem OData feeds), combine tables together, and load the results into a worksheet. You access it from the Data tab in Excel.
You do not need to write code to use Power Query. However, this guide includes the query code (called M code) for each step, which you paste into the Advanced Editor. This is the most reliable way to set up OData connections.
Step 1: create the Checkpoints query
This query connects to the OData v2 checkpoint assessments feed. The v2 feed uses a direct URL rather than a table reference.
In Excel, select Data > Get Data > From Other Sources > Blank Query.
In the Power Query Editor, select Advanced Editor from the toolbar.
Delete any existing text and paste the following code, replacing
yourtenantwith your tenant name andYOUR_API_KEYwith your API key:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/CheckpointAssessments?$select=CheckpointName,LearnerUniqueId,ProgrammeId,CheckpointType,Status,QuestionsCount,Score,CorrectAnswersCount,CreatedDate,CompletedDate,CheckpointEndDate,TimeSpent", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
Select Done.
In the Query Settings pane on the right, rename the query to
Checkpoints.Select Close & Load To > Table and choose a worksheet location.
The report loads into your worksheet. You can refresh it at any time by selecting Data > Refresh All or right-clicking the table and selecting Refresh.
Optional: adding a filter
The query above returns all checkpoint assessments. To reduce the volume of data, you can add a $filter to the URL. For example, to return only completed checkpoints:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/CheckpointAssessments?$select=CheckpointName,LearnerUniqueId,ProgrammeId,CheckpointType,Status,QuestionsCount,Score,CorrectAnswersCount,CreatedDate,CompletedDate,CheckpointEndDate,TimeSpent&$filter=Status eq 'Completed'&$orderby=CompletedDate desc", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
For more filter examples, see Checkpoint assessments query examples.
Useful ways to use this report
Once the data is loaded into Excel, you can use the built-in table filters, conditional formatting, and pivot tables to analyse the results:
Filter by Status to see only completed or pending checkpoints
Sort by Score ascending to find learners who may need additional support
Filter CheckpointEndDate to before today to find expired or overdue checkpoints
Use a pivot table to summarise average scores by CheckpointName or ProgrammeId
Use conditional formatting on the Score column to highlight low scores
Coming soon: enriched report with learner details
A future release will add a numeric LearnerId field to the checkpoint assessments feed. This will enable a richer version of this report that joins to the Users v1 feed to include:
Learner name
Programme name
Programme start and end dates
Case owner
Groups and cohorts
This page will be updated with the enriched report once the field is available.
Troubleshooting
Authentication errors: check that your API key is correct and has not expired. Replace YOUR_API_KEY in the query with a valid key.
Empty results: confirm that the checkpoint assessments feed is enabled on your tenant and that checkpoint data exists. Contact your Aptem administrator or raise a support ticket if you are unsure.
Slow loading: large tenants may have many checkpoint records. Add a $filter to the URL to reduce the data volume (see Optional: adding a filter above).
Getting help
If you have questions about building this report:
Contact your Implementation Consultant if you are still in implementation
Contact your Customer Success Manager if you are a live customer
Raise a support ticket