Reports

Run Query

POST https://{organisation}.clearlrs.com/api/reports/query

Runs an ad-hoc report query against the data stored in the system. The structure of this query is very flexible with the ability to target different data sources, filter and aggregate the data in numerous ways. The following documentation is not comprehensive but covers most major options.

Query Parameters

Name	Type	Description
limit	string	Limits the number of results returned
skip	string	Skips over this number of items in query results

Request Body

Name	Type	Description
expand	array	Performs a join on a secondary data source
dataSource	string	The data source to query against
filters	object	Restricts the data used in the query
values	array	An Array of values that will be used as the columns output in the report
group	array	Optional aggregation stages that describe how the output values should be combined
sort	array	An Array of fields the report output should be sorted by

200

[
	{
		"name": "User 1",
		"completed": true
	},
	{
		"name": "User 2",
		"completed": false
	}
]

Due to the powerful nature of this endpoint, it is possible to run queries across huge amounts of data which result in a very slow query. As such, it is recommended that filters are used to efficiently restrict the amount of data being queried.

Request Body

dataSource

The following table outlines available data sources in Clear LRS. See the Data Sources section below for a list of available metrics for each data source.

Data Source	Description
actors	Retrieves Users or Groups and their associated attributes
activities	Retrieves all known activities from the system, this includes top-level activities as well as child activities
status	Point-in-time completion data for users broken down by their enrolments. Data won't exist unless the user is enrolled into the activity.
completions	High-level completion data for users within a given top-level activity, broken into multiple attempts.
analytics	Fine-grained analytics data – this effectively encompasses all recorded data in the system.

values

An Array of Objects composed of the following fields:

Field	Type	Description
name	String	The output name that the value will be mapped to. This would be the object key if JSON output or the column name if CSV.
type	String	The type of value to pull. Possible values are metric, context or formula
key	String	The combination of type and key are used to identify what data point to use in the report. See the Data Sources section below for possible keys.
keyPath	Array of Strings	When the data point required belongs to a nested field, use keyPath instead of key to identify the full path to the nested field.

filters

A recursive object that can be used to build complex filter conditions to restrict the data retrieved when generating a report. The structure of a the filter object is of a single key indicating the type of comparison to be performed, and an Array of operations to perform the comparison against.

• and – All operations in the Array must be met for the filter to match.

• or – Any operation in the Array must be met for the filter to match.

• = – The metric must match any of the values in the Array.

• != – The metric must not match any of the values in the Array.

• < – The metric must be less than the second value in the Array.

• <= – The metric must be less than or equal to the second value in the Array.

• > – The metric must be greater than the second value in the Array.

• >= – The metric must be great or equal to the second value in the Array.

• >< – The metric must be between the first and second values in the Array (non-inclusive).

• <> – The metric must be less than the first value in the Array and greater than the second value in the Array (inclusive).

• exists – A value for the metric must exist.

• !exists – A value for the metric must not exist.

Example Filters Object

filters: {
	and: [
		{ "=": [ { type: "metric", key: "device" }, "mobile", "desktop" ] },
		{ or: [
			{ "!=": [ { type: "attribute", key: "State" }, "VIC" ] },
			{ "exists": [ { type: "attribute", key: "Country" } ] }
		] }
	]
}

In the above example, the filter would retrieve all records where:

• The device metric is equal to either mobile or desktop, AND;

  • The user's State attribute is not equal to VIC, OR;

  • A value for the user's Country exists

group

An Array of Objects composed of the following fields:

Field	Type	Description
fields	Array of Value Objects	The fields that this aggregation stage should be grouped by – multiple fields indicate that all unique combinations of these fields will be returned in the results.
values	Object	An object of key-value pairs that describe how each value from the values Array should be accumulated. The key must match the value's name field, and the value must be the accumulator type (see below).
filters	Object	Optional additional filters to be applied after the aggregation has been processed.
sort	Array	Optional additional sorting to be applied after the aggregation has been processed.

Accumulator Types

When grouping on a field, it's necessary to specify an accumulator for all other values so the aggregator knows how to aggregate them together. The following are all available accumulators:

• first – Picks the first value in the result set. Should be used with a sort stage.

• last – Picks the last value in the result set. Should be used with a sort stage.

• min – Picks the greatest of all values in the result set.

• max – Picks the least of all values in the result set.

• sum – Calculates the total of all values in the result set.

• avg – Calculates the average of all values in the result set.

Example Group Array

group: [ {
	fields: [
		{ type: "metric", key: "activity" },
		{ type: "attribute", key: "State" }
	],
	values: {
		completion: "sum"
	}
} ]

In the above example, the group stage will calculate the sum of all completions in the system, split by activity and State.

sort

An Array of Objects composed of the following fields:

Field	Type	Description
name	String	The name of the output field to sort on.
direction	Number	Specify 1 to sort result in ascending order, or -1 to sort in descending. Defaults to ascending.

Data Sources

status

Stability: Stable The metrics in this data source are unlikely to change and maintaining compatibility is a priority.

Type	Key / Key Path	Format
metric	"actorId"	Serialised IFI
metric	"activityId"	IRI
metric	"parentId"	IRI
metric	"nodeId"	IRI
metric	"accessed"	Number
metric	"completion"	Number
metric	"assigned"	Number
metric	"overdue"	Number
metric	"expired"	Number
metric	"unassigned"	Number
metric	"unregistered"	Number
metric	"enrolmentStatus"	String
metric	[ "enrolment", "id" ]	UID
metric	[ "enrolment", "type" ]	String
metric	[ "enrolment", "key" ]	String
metric	[ "enrolment", "tags" ]	Array
metric	[ "enrolment", "mandatory" ]	Boolean
metric	"timestamp"	Date
metric	"stored"	Date
metric	"timestampCompleted"	Date
metric	"storedCompleted"	Date

completions

Stability: Under Migration This is a legacy data source that is currently being migrated to a cleaner structure. Keys, Key Paths and the formats of some values are highly likely to change.

Type	Key / Key Path	Format
metric	"activity"	IRI xAPI Activity Id
metric	[ "agentCanonical", "_id" ]	UID Clear Agent ID
metric	[ "metrics", "enrolment" ]	String assigned overdue expired unassigned unregistered
metric	[ "metrics", "completion" ]	Number 0 – incomplete 1 – completed
metric	[ "metrics", "date_started" ]	Date
metric	[ "metrics", "date_accessed" ]	Date
metric	[ "metrics", "stored_accessed" ]	Date
metric	[ "metrics", "date_completed" ]	Date
metric	[ "metrics", "stored_completed" ]	Date

analytics

Stability: Stable The metrics documented here are unlikely to change and maintaining compatibility is a priority. However, additional undocumented metrics may exist in this data source that are prone to change.

Type	Key / Key Path	Format
metric	"id"	UUID Statement ID
metric	"timestamp"	Date
metric	"stored"	Date
metric	"actorId"	Serialised IFI
metric	"name"	String Actor's name
metric	"verb"	IRI
metric	"activity"	IRI
metric	"ancestors"	Array
metric	"parent"	Array
metric	"grouping"	Array
metric	"category"	Array
metric	"other"	Array
metric	[ "definition", "type" ]	IRI
metric	[ "definition", "interactionType" ]	IRI
metric	"duration"	Number