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

[
	{
		"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

PreviousActorsNextLeaderboards

Last updated