Here are some common questions and scenarios you might encounter.
Why am I unable to read data from OData feeds?
There are several reasons why data is not returned from OData.
- The OData key you are using belongs to an admin account that has expired or been cancelled. For security reasons the key is blocked. Please contact the Aptem Support Team.
- Please bear this in mind if you have built reports using a specific admin's API Key, and their account has now been cancelled, as the reports will no longer work until the API Key used for the reports has been updated.
- The OData key has been mistyped in your query and therefore is rejected.
- The group access settings on the admin account assigned to the OData key do not have permission to view certain groups of learners, thus their data is not returned.
- Your OData query has not specified the column names for the required data. By default, on large tables only data for the first ten columns are returned.
- Where column names have been defined the query can be very long, this usually occurs with custom fields and skill radar fields in the Users table. For security reasons, the request URL cannot exceed 8000 characters. If it does then the query is blocked. The solution is to split the required columns into groups so that each query is less than 8000 characters.
What are the fields used in OData to retrieve information on usage?
The Users table from the OData feed includes some information on the user logins:
- UserSummary_LoginsNumberThisAcademicYear
- UserSummary_LoginsNumberMonth
- UserSummary_LoginsNumber7Days
- UserSummary_LoginsNumber
- UserSummary_LastLoginDate
- UserSummary_FirstLoginDate
There are additional fields providing summary information on employability related activity such as ClickedJobLinks, ApplicationsMade, Placements logged hours and use of the Advice centre.
The Users table also provides summary information on a user’s Learning Plan, Reviews and Tasks activity.
For more information on available data field, please see the OData dictionary.
How do I report on hours uploaded to a specific learning plan component?
The LearningPlanComponents table in OData provides information on every learning plan component for every learner and programme.
The columns “PlannedHours” and “ActualHours” provide this information for each component.
“PlannedHours” – these are the hours recorded against the component under ‘Planned hours’ field. Returned value is displayed in hours (integer).
“ActualHours” – these are the sum of Off-The-Job Hours Spent across all evidence of the selected component where evidence status is either Accepted or QA Verified. Returned value is displayed in hours (decimal): for instance, ‘2.05’ refers to ‘2 hours and 3 minutes’.
The OData dictionary provides more information on the available data fields.
In OData, how do I see the sub-programmes we have as well as the learners linked?
The Episodes table in OData provides information on all programmes and subprogrammes assigned to a learner – this includes current and historic Aptem programmes.
Filtering the Episodes table using: ProgramType=SubProgramme provides a list of all subprogrammes assigned to at least one learner.
To identify which programmes and subprogrammes a learner is currently assigned to involves the following steps:
- Use a lookup for the LearnerId against the Id on the Users table to add the UserProgram_ProgramId field (the learner’s current programme) to the Episodes table.
- Filter the Episodes table where either the Episodes[ProgramId] or the Episodes[ParentProgramId] matches the current program id from the Users table.
What date does the ‘Last feedback date’ field display in OData?
The ‘Last feedback date’ field displays the latest Assessment/Feedback date for any piece of Evidence from all Learning Plan components for a Learner. Effectively, this is the last date any evidence was marked in the Learning Plan.
Is it possible to report on the ‘Total completed hours’ using OData?
The “UserLearningPlanSummary_CompletedTime” field corresponds to a Learner’s Total Completed Hours metric, returned as the sum of minutes. Please apply manual conversion into hours, if needed.
For example, the Odata returns ‘"UserLearningPlanSummary_CompletedTime": 1584’: dividing 1584 by 60 [minutes], you get 26.4 hours, which should be interpreted as ‘26 hours and 24 minutes’.
Note that “UserLearningPlanSummary_CompletedTime” field returns the same as the ‘Completed’ hours value you can see from both Learning Plan Overview page (in Classic) and Off-The-Job Hours widget in Learner Dashboard.
How is the AgeAtStart field calculated?
ILR page - Age on 31 Aug of the year programme started
- This age is based on the learner’s age as of 31st August of the funding year in which their Learning Start Date falls.
- The Learning Start Date comes from the ILR aims (specifically, from the most recent programme aim, or, if not available, the most recent non-programme aim).
- This calculation is not directly available in OData and must be calculated manually (please see details below)
OData - PersonalInformation_AgeAtStart [Users table]
- This is a simplified calculation of the learner’s age based on the current programme's start date (from the Programme Details page) and the Date of Birth.
- It does not factor in the 31st August cut-off used in ILR calculations.
These age values can be calculated using power query in Excel or Power BI:
- ILR Age can be derived using the fields UserPersonalDetails_Dob and UserILRSummary_ProgrammeStartDate from the OData Users table.
- Age at Start is already available in the Users table as the field PersonalInformation_AgeAtStart, which is calculated using UserPersonalDetails_Dob and UserProgram_StartDate.
What are the equivalent OData fields for 'Complete by' date and 'Due date’ in the learning plan?
The LearningPlanComponents table in OData provides information on every learning plan component for every learner and programme (including historic episodes).
The corresponding OData field that reflects the deadline for completing the component is ‘DueDate’ – which is showing as ‘Complete By’ field in the Learning Plan page.
For those components with associated Tasks, additional field will be showing in the Learning Plan to reflect the date the Task is scheduled on:
‘Due Date’ (Meeting, Assignment (task), Online training – external, End-Point Assessment, Mentoring, Shadowing components)
‘Dates’ (Offline learning (placement/workshop), Scheduled online event components)
Part of review meeting summary (Review components)
Since these fields are of Tasks origin, these are not available from LearningPlanComponents OData table.
However, to retrieve the component’s Task date, you can use the Tasks OData table instead, filtering the feed by the ‘LearningPlanComponentId’ field and selecting ‘Start’ field.
Example: ~/odata/1.0/Tasks?$filter=LearningPlanComponentId eq '1068'&$select=Id,LearningPlanComponentId,Start
In OData, how is the EmploymentEndDate field calculated?
The LearnerEmployment table in OData contains a field named “EmploymentEndDate”.
This date is calculated as the day before the start of the next chronological employment status record, if one exists. Otherwise, the date will be null.
The start date of the employment status record refers to ‘Date status applies’ ILR field (DateEmpStatApp).
In the below example, the EmploymentEndDate for each of the employment status records will equal to:
- (for the 1st): 17/03/2025
- (for the 2nd): 04/04/2025
- (for the 3rd): null
Is there an OData field for the 'Target' and 'Current' progress for the learner's Apprenticeship Standard progress?
The Current and Target % that are shown in the below screenshot are not available in the OData feed at present.
Where can I find the corresponding program type to the number in the ILRAims OData entity 'ProgType'?
The IlrAims table in OData lists all ILR episodes for learners. The field ‘ProgType’ displays the ILR ProgType value for each aim.
The ProgType corresponds to the attribute code provided by the ILR specification.
Please note that Aptem supports all ProgType codes from both the current and archive specifications mentioned in the following table:
Code |
Description |
2 |
Advanced Level Apprenticeship |
3 |
Intermediate Level Apprenticeship |
20 |
Higher Apprenticeship - level 4 |
21 |
Higher Apprenticeship - level 5 |
22 |
Higher Apprenticeship - level 6 |
23 |
Higher Apprenticeship - level 7+ |
24 |
Traineeship |
25 |
Apprenticeship standard |
30 |
T-level foundation year |
31 |
T-level programme |
32 |
Skills Bootcamps |
33 |
Combined Authorities |
You can also find a list of supported ProgType codes from the ILR page. To do so, navigate to a live ILR. Go to the 'Aims' section and click the edit button.
You will then see the list of Programme types, and the corresponding “ProgType” number.
A learner is added to multiple groups at the same levels (eg. group level 0, 1, 2). Can these multiple groups be reported on in OData?
Yes, this is possible from the expandable 'Groups' table found within the Users table.
Each group selected for the particular learner will show in the data feed as its own row.
Can I report on the Learner Reference Number(LRN) using OData?
Yes, it is possible to report on the LRN. The field is "UserILRSummary_LRN" in the Users table.
- The Users table in OData contains ILR reference numbers related to a learner.
- The LRN for a learner can be found in the field "UserILRSummary_LRN"
- The ULN for a learner can be found in the field "UserILRSummary_ULN"
Please refer to the OData dictionary for a full list of data fields.
How are the TasksDetails_ReviewMeetingOverdue and TasksDetails_ReviewMeetingCompleted OData fields calculated?
TasksDetails_ReviewMeetingOverdue shows the number of scheduled reviews that are overdue and incomplete. It excludes reviews with a Not Scheduled status and includes only those that are In Progress or Not Started and past their due date.
TasksDetails_ReviewMeetingCompleted shows the total number of completed reviews. These are all the reviews listed under the Finished tab on the Reviews page.
In the Episodes table in OData, a learner is showing to have been enrolled onto multiple programmes, although on the User profile, I can only see one additional programme. Where are these other programmes being pulled from?
The Episodes table within OData lists all programmes and sub-programmes that have ever been assigned to a learner. This list includes the programmes that may have been assigned to a learner but later removed – for example where the sub-programme was added in error.
As opposed to the Episodes OData table, the “Programme” section in the User profile page (Classic interface) lists only:
- Current main programme (Delivery/Onboarding)
- Active sub-programmes linked to the current main programme
- The ‘IsActive’ field is TRUE for these programmes in the Episodes OData table
- Previous (stopped) main programmes (Delivery/Onboarding)
- Subprogrammes were active when their main programme was stopped
- The ‘IsActive’ field is TRUE for these programmes in the Episodes OData table
And does not list:
- Sub-programmes, assigned and stopped within the associated main programme
- The ‘IsActive’ field is FALSE for these programmes in the Episodes OData table
Please note that in Console, in Programme Details page, only current programme and its active sub-programmes are visible.
This explains why the Episodes OData table returns larger scope of details, comparing to what the Administrator can see from the web page.
However, from the Episodes OData table, there is no option to determine whether the listed programme is the current one for the Learner.
For that purpose, you can use Users OData table instead, requesting the following fields:
- UserProgram_ProgramId
- The current main programme ID
- UserProgram_CurrentProgramme
- The current main programme name
- UserProgram_SubPrograms
- The list of active sub-programmes linked to the current main programme
In OData, how is the EmploymentStartDate field calculated?
The LearnerEmployment table in OData contains a field named “EmploymentStartDate”. This field is calculated based on the start date of the employment status record from the Learner’s ILR and varies depending on the Status field:
If the status is “In paid employment”, the date is taken from the Start date with Employer field
-
For all other statuses, it reflects the Date status applies field (corresponding to the DateEmpStatApp field of ILR schema).
Why does my Power BI report fail to load columns from the "Onboarding_ADET_GRModel" fields in the Users table?
This typically occurs when the OData query URL exceeds the maximum supported length of 8,060 characters. Tenants with a large number of custom onboarding fields are most likely to encounter this, as combining all field names in a single query can push the URL over the limit.
There are two options:
Option 1 -- Split the query
Divide the Users table query into multiple smaller queries, each staying under the URL length limit. Include the Id field in every split query. Combine the results in Power BI using a merge or join on Id.
Option 2 -- Reduce the fields selected
Select only the specific fields you need in your report rather than all available columns.
For guidance on building OData queries for Power BI, see: Connecting your organisation's live data to Power BI via an API
Why is the last submission date in the Power BI Caseload by Assessor/Occupancy report not showing the most recent evidence submitted by a learner?
The last submission date may not be updating if the relevant learning plan component does not have a due date defined.
This is intended behaviour. The Power BI Caseload report uses the same logic as the Users OData feed: the expected number of components completed is calculated based on component due dates. Components without a due date - or with a due date set in the future - are excluded from this calculation. As a result, only evidence from components that have a defined, non-future due date is reflected in the last submission date field.
To ensure a learner's latest evidence is captured in this report, check that the relevant learning plan components have a due date set.