Building a checkpoint assessments report using OData in Excel

This guide walks you through building an Excel report that shows checkpoint assessment results enriched with learner details. The report covers scores, completion status, time spent, and checkpoint deadlines, joined to learner name, programme name, and case owner from the Users v1 feed.

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.uk

• Your 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

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 and returns the checkpoint data including LearnerId, which you will use in Step 2 to join to the Users feed.

1. In Excel, select Data > Get Data > From Other Sources > Blank Query.

2. In the Power Query Editor, select Advanced Editor from the toolbar.

3. Delete any existing text and paste the following code, replacing yourtenant with your tenant name and YOUR_API_KEY with your API key:

4. Select Done.

5. In the Query Settings pane on the right, rename the query to Checkpoints.

6. Do not close the Power Query Editor yet — continue to Step 2.

Step 2: create the Users query

This query connects to the Users v1 feed and returns learner name, programme name, and case owner. You will merge this with the Checkpoints query in Step 3.

1. In the Power Query Editor, select Home > New Source > Blank Query.

2. Select Advanced Editor and paste the following code:

3. Select Done.

4. Rename the query to Users.

Step 3: merge checkpoints with learner details

This step joins the two queries on LearnerId (Checkpoints) to Id (Users), bringing learner name, programme name, and case owner into the checkpoint data.

1. In the Power Query Editor, select the Checkpoints query in the left pane.

2. Select Home > Merge Queries.

3. In the Merge dialog, select the LearnerId column in the Checkpoints table (click the column header).

4. In the second dropdown, select the Users query, then select the Id column.

5. Leave the join kind as Left Outer (all rows from Checkpoints, matching rows from Users).

6. Select OK.

7. A new Users column appears in the Checkpoints table. Select the expand icon (double arrow) on the column header.

8. Tick UserProfile_FirstName, UserProfile_LastName, UserProgram_ProgramName, and UserProgram_CaseOwnerName. Untick Use original column name as prefix.

9. Select OK.

If a checkpoint row has no matching learner in the Users feed, those columns will be blank. This can happen if a learner has been removed from the system.

Step 4: load the report

Select Home > Close & Load To > Table and choose a worksheet location. The report loads with checkpoint data alongside learner name, programme name, and case owner.

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 Checkpoints query above returns all checkpoint assessments. To reduce the volume of data, you can add a $filter to the URL in Step 1. For example, to return only completed checkpoints:

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, ProgrammeName, or CaseOwner

• Use conditional formatting on the Score column to highlight low scores

Troubleshooting

Authentication errors: check that your API key is correct and has not expired. Replace YOUR_API_KEY in both queries 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 and a large Users table. Add a $filter to the Checkpoints URL to reduce the data volume (see Optional: adding a filter above).

Blank learner details: if name and programme columns are blank for some rows, the learner may have been removed from the system. The checkpoint data is still valid — the Users join simply has no match for that learner ID.

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