Querying Report Data via REST API

Introduction

This article provides a basic description of how a REST API works and its mechanisms, followed by an overview of the implementations available in the LMS for querying report data and the necessary help and authentication interfaces, including examples. Report data can be queried via API for all on-screen reports, but not for standard reports or reports created using Report Designer.

The following chapters provide a basic introduction to the architecture of REST APIs. If you are already familiar with these topics, we recommend that you skip directly to the section REST API of the LMS to start with the information that is relevant to you.

About REST API

A Representational State Transfer (REST) API is an architectural style for designing
network applications. It is based on established World Wide Web standards and uses the HTTP protocol for communication. The core idea behind REST is interaction with resources, whereby each resource can be identified by a unique URI (Uniform Resource Identifier). Communication between client and server is stateless, which means that each request contains all the necessary information and the server does not need to store any client context between requests. JSON (JavaScript Object Notation) is predominantly used as the data format for exchange, as it is lightweight and easy to read for both humans and machines. The consistent use of standard HTTP methods such as
GET, POST, PUT and DELETE makes REST APIs intuitive, flexible and scalable, making them the preferred choice for modern web services.

Get

The GET method is one of the most basic and frequently used HTTP request methods within the framework of a REST API. Their primary purpose is to retrieve data from a specified resource. By definition, a GET request is safe and idempotent, which means that it must not change the state of the resource on the server and that multiple identical requests lead to the same result as a single request. Parameters for filtering, sorting or paginating the retrieved data are usually appended to the URI as query parameters. If the request is successful, the server typically responds with the HTTP status code 200 OK and delivers the requested data in the body of the response, usually in JSON format.

Authorization Header

The Authorization Header is a standardised HTTP header used to authenticate a client to a server. It enables the client to prove its identity or authorisation in order to access protected resources. The header contains the login information (credentials) in an extensible schema. The structure consists of the type of authentication method (e.g. Basic, Bearer) followed by the actual credentials. The correct implementation of the Authorization header is crucial for the security of an API, as it ensures that only authorised parties can access sensitive data and functionalities.

Basic Authentication

Basic Authentication is a simple authentication scheme defined in the HTTP standard. In this method, the client sends the username and password in a single Base64-encoded string in the Authorization Header. The format is Authorization: Basic <base64-credentials>, where <base64-credentials> is the Base64 encoding of username:password. Although this scheme is widely used and easy to implement, it does not offer a high level of security, as Base64 can be easily decoded. For this reason, Basic Authentication may only be used in conjunction with an encrypted connection such as HTTPS/TLS to prevent third parties from intercepting login information.

Bearer Authentication

Bearer Authentication (also known as Token Authentication) is a modern and secure authentication scheme that is often used in conjunction with OAuth2 and OIDC. Instead of sending a username and password with every request, the client authenticates itself once with an authorisation server and receives an access token (Bearer Token) in return. This token is then sent with every request to the protected API in the Authorisation Header with the Bearer scheme. The name ‘Bearer’ implies that anyone in possession of the token will be granted access. It is therefore essential to handle these tokens securely and only transfer them via encrypted connections.

Bearer Token

A Bearer Token is a cryptographic string that serves as proof of authorisation in Bearer Authentication. This token is issued by an authorisation server after a client has successfully authenticated itself. It represents the authorisation to access certain resources on behalf of a user. Bearer tokens are usually short-lived in order to minimise the security risk in the event of theft. These are often JSON Web Tokens (JWT), which, in addition to access authorisation, may also contain information (claims) about the user and the validity period. The client must store the token for the duration of its validity and send it in the Authorisation Header with every request to a protected endpoint.

Path Parameter

Path Parameters are variable components of the URL path and are used to uniquely identify a specific resource within a collection. They are embedded directly into the URI structure of the endpoint, typically marked by curly brackets, such as /users/{userId}. To access a specific resource, the client replaces this placeholder with an actual value, for example /users/123. Path Parameters are usually mandatory, as they are an essential part of determining the requested resource. They are ideal for accessing a single, unique data element.

Query Parameter

Query Parameters are key-value pairs that are appended to the end of a URL to modify the amount of data returned by an endpoint. They are separated from the path by a question mark (?) and separated from each other by an ampersand (&), e.g. /articles?status=published&sort=desc. Unlike Path Parameters, which identify a resource, Query Parameters are typically used to filter, sort or paginate a collection of resources. They are usually optional and offer the client a flexible way to tailor the server's response to its specific requirements without having to define a new endpoint.

POST

The POST method is a central HTTP request method that is mainly used to send data to a server in order to create a new resource. Unlike the GET method, which passes parameters in the URL, a POST request transports the data to be transmitted (the so-called Payload) in the body of the request. An essential attribute of POST is that the method is neither secure nor idempotent. This means that it changes the state on the server, and executing the same POST request multiple times usually results in the creation of multiple new resources. If creation is successful, the server typically responds with HTTP status code 201 Created. In addition, the response often contains a Location Header that specifies the URI of the newly created resource and can return a representation of the new object in the body.

url-form-encoded

application/x-www-form-urlencoded is a media type (MIME type) used to encode data sent from a client to a server, typically in the body of a POST request. This format mimics the way an HTML form transmits its data. The data is structured as a string of key-value pairs, with each pair separated by an equal sign (=) and the individual pairs connected by an ampersand (&). Special characters within keys and values are percent-encoded to ensure correct transmission and interpretation. To use this data type, the client must set the Content-Type header of the HTTP request to application/x-www-form-urlencoded. Although JSON (application/json) is often preferred for transferring complex data in modern REST APIs, url-form-encoded remains a common standard, especially for simple form submissions and in OAuth2 flows, such as when requesting tokens.

REST API of the LMS

The following sections describe the endpoints for querying report data. They also explain the procedure for correct authentication, filtering, sorting, pagination and language selection. The placeholder <baseUrl> is often used in the explanations. The URL for the LMS must be specified up to the top-level domain (.com/.de) in this placeholder.

A Postman collection with practical sample queries is available for download for all interfaces mentioned in the following sections: Report API examples.postman_collection.json

Report Interfaces

The LMS offers several interfaces in the context of reports.

The response can be sent in both XML and JSON; the selection is made via the Accept header:

Authentication

One way to authenticate at such an interface is Basic Authentication. However, the User Name and Password must always be provided. The User Name and Password must be separated by a colon and written in a header in Base64 encoding as follows:

Since Basic Authentication always requires the transmission of User Names and Passwords, authentication using Bearer Tokens is recommended for a secure workflow. To obtain such a token, a POST request must first be sent to the IDM (Identity Management) of the LMS. The interface for this is:

The Payload must be of the media type x-www-form-urlencoded.

Accordingly, the following Header is still necessary:

If the request is successful, a response with status 201 Created is returned, which then contains the Bearer Token (access_token) that can be used to authenticate the other interfaces. The expiry (expires_in) specifies how many seconds a token is valid from the time of the request.

{
  "refresh_token": “eyJra… ",
  "access_token": "eyJra…”,
  "token_type": "Bearer",
  "expires_in": 86400
}

Basic Query of Report Data

The following interface can be used to obtain the data records of a report via REST API:

In addition to the placeholder <baseUrl>, there is now also the placeholder <reportId>. At this point, enter the respective ID of the report as a path parameter whose data you want to retrieve as an authenticated user.

The reportID can be determined in several ways. The easiest way is to read it from the Report Manager in the system, where all reports are listed. Other options include reading it at code and database level, which can be done by our Consulting Team.

If the query is successful, the result contains a count with the number of records contained. It also contains a header object containing the individual column names with ID, name and data type, as well as the sort columns and the sort order. Finally, there is a rows object containing the report records nested as a combination of columnID and contained value.

{
  "count": 77,
  "header": {…},
  "rows": {…}
}

This interface forms the basis for querying report data, but it is not always useful to query all data. The next section therefore presents options for parameterisation.

Parameterisation of Report Data

The result set can be adjusted using various parameters. The following sections describe the procedures for filtering, sorting, column selection, language selection and pagination.

Filter

The results can be restricted with the help of filters. The filtering options are limited to the filters already existing in the report. Filters can be specified using query parameters:

A report data query with multiple filters therefore has the following format:

An example with filtering in a course progress report:

Report number 125 is the report Course progress (administrator). By filtering for courseBookingStatus=8, we only get entries in the result set that have the status Enrolled, and with time_mode=0, we only get courses that are date-dependent.

To filter by multiple values, the values can be separated by commas:

The filterID can be obtained by using the metadata API call – only the reportID is required for this:

The response always contains the filterID, name, type, and flags for visibility and multiple selection.

{
  "id": "courseBookingStatus",  
  "name": "Course enrollment status",  
  "type": "SELECT",
  "hidden": false,
  "multiple": true  
}

Important notes on the use of filters:

For filters from a user list, the attribute name must be used instead of a filterID.

Filters from a meta tag list must be specified as follows:

In some cases, a filter may be defined for numbers, but the database supports text. Accordingly, no error message is displayed if the filter value is incorrectly not a number.

Filtering by date or time period must always follow a specific format:

The part ,userdefined is strictly necessary and must always be added after the second date, otherwise the filter will not work. In addition, many filters do not have a time selection, in which case the time part of the date is simply ignored. The %20 stands for a whitespace.

Sorting

Sorting is only possible for columns that are displayed in a report by default. The metadata interface can also be used for this purpose. Example of sorting:

Example of sorting by surname in descending order:

Column Selection

The fields parameter can be used to restrict or expand the columns in the response sent. The column names can be read from the report's XML file or queried via a metadata API call:

Example of a selection of first and last names:

Language Selection

The preferred language can be set via the Accept-Language header:

It is also possible to specify a weighting for the language selection. Here is an example:

The supported language with the highest weighting is then returned, in this case DE. However, if DE is not supported, EN-GB is returned.

Output Limits / Pagination

It is also possible to set a limit (limit) on the query or to list the results from a certain starting point (startindex). In combination, report data can be processed in batches. The maximum number of entries returned per call is 50,000. This may vary depending on the system configuration. If there are more entries, this is noted in the count, but only the maximum number is returned.

Important notes on the use of limit and startindex:

• startindex without limit has no effect

• limit=0 is not a valid parameter value

• startindex>=count does not return an error, only an empty response

• the first entry has the index 0, and accordingly, the first 10 entries have the indexes 0 to 9

• startindex=0 is not a valid parameter value

• startindex=1 cuts off the first entry

Here are a few examples: