Integration

You can retrieve data from -and import data into- data sets through our REST API. You can send data directly from another system or database into your data sets without having to go through the web pages. You can also retrieve data from your data sets to send it straight into another system or even directly into an Excel workbook.

This page describes inbound API integration. For outbound API connections, please refer to import connectors.

XLReporting provides the following API methods:

• Get schema - returns all active data sets.
• Get row count - returns the number of data rows in the data set.
• Get column info - returns the defined columns, and the properties of all columns.
• Get column values - returns the list of unique values from the data in a given column.
• Get data - returns the data content of the data set.
• Clear data - clear selected data from this data set.
• Clear cache - clear all caches that refer to this data set.
• Import data - import data into the data set.
• Get report data - returns the data content of a report.
• Power query - creates a file that you can open in Excel or PowerBI to establish a direct and realtime connection to the data set.

All requests need to be made using GET or POST methods over HTTPS, and responses and results will be returned as JSON objects. The response codes that can be returned are standard 200, 304, 400, 401, 403, and 404. All responses include a success property which indicates (true/false) whether the request was succesful.

Please note that this option is only accessible if your user role has Access API permission, and only for data sets that have Access API permission.

API token

You need to provide a valid API access token with all requests. Tokens are issued by users through Manage - Profile - Actions, and are personal to every user. Users can revoke or renew tokens at any time. At every API request, XLReporting verifies the token against the user who issued it, and that user must still exist, be active, have the right token, and have the relevant permissions for the API request to be processed.

You can pass the token either as a URL parameter (token=xxx) or as a custom HTTP Header (x-access-token: xxx). For security reasons, we recommend you pass the token as a custom HTTP header whenever possible, as this will keep the token more secure in transit.

Formats

All data is transmitted and returned as JSON objects. Dates need to be passed as per ISO 8601 (e.g. yyyy-mm-dd), numbers need to be passed with decimal points without thousands separators (e.g. 1600.45).

Data sets

It is important to understand that data sets are configurable objects, and unique for every tenant. They can be created, changed, and deleted at any time. The columns within a data set are also configurable. So data sets and columns are likely to be different for every tenant.

The names of data sets and columns are simply visual labels in the web application, which can be renamed on the fly. In API requests you can only refer to the internal ID's of objects. So when you make API requests to a data set, you need to know its metadata: its internal ID, and its collection of column ID's.

You can view that metadata by clicking on Actions - Get API access within a given data set:

Select the API method in the dropdown, and you will see the appropriate URL for your data set, for example: https://eu.xlreporting.com/table/t1?token=xxxxx (you should replace xxxxx with your real API token). The left panel will show a small sample of the data in your data set, and the right panel will show the metadata of its columns, for example:

In all API requests, you need to work with these column ID's, never with the column names.

Get schema data

Returns all active data sets.

Get row count

Returns the number of data rows in the data set. You need to pass the data set ID in your request.

Get column info

Returns the defined columns of the data set, and the properties of all columns. You need to pass the data set ID in your request.

Get column values

Returns the list of values from data in a given column. You need to pass the data set ID and the column ID in the request.

Optionally, you can provide additonal query parameters:

• filter: set one or more filter expressions, separated by a comma. Each expression must have the format [field:value]. For example: filter=f1:10000
• limit: set a number (greater than 0) to limit the numbers of rows that will be returned.

Get data

Returns the data content of the data set. You need to pass the data set ID in your request.

Optionally, you can provide additonal query parameters:

• filter: set one or more filter expressions, separated by a comma. Each expression must have the format [field:value]. For example: filter=f1:10000
• limit: set a number (greater than 0) to limit the numbers of rows that will be returned.

Clear data

To clear data from this data set, you need to pass the data set ID in the request, as well as a JSON-encoded request body with the following properties:

• filter: set one or more filter expressions, separated by a comma. Each expression must have the format [field:value]. For example: filter=f1:10000
  
  If you omit this parameter, all data rows will be cleared.

Clear cache

To clear all caches that refer to this data set, you need to pass the data set ID in the request. No request body is needed. Please note that caches are automatically purged whenever data is imported into a data set, so you don't need to do this manually.

Import data

To import data into the data set, you need to pass the data set ID in the request, as well as a JSON-encoded request body with the following properties:

• data: collection of rows, each row containing an object literal with column values.
• type: this must be one of the following options:
  • add - add new data to the existing content of the data set.
  • overwrite - overwrite the entire existing content of the data set.
  • replace - selectively replace existing content of the data set. For example, anytime you import a certain financial period, you might want to overwrite the previous content for that same period. If you select this option, you also need to define the column(s) and column values that determine which existing rows need to be replaced. Read more here.
  • update - update the given columns in existing rows in the data set. This option will only update rows that already exist in the data set, and ignore any other data in the import source. The target data set must have a defined Key column, because that is used to determine whether a given row already exists or not.
  • upsert - update the given columns in existing rows in the data set, and insert rows that don't yet exist in the data set. The target data set must have a defined Key column, because that is used to determine whether a given row already exists or not.
  • delete - selectively delete existing content from the data set. Data that matches your data source will be deleted. No new data will be imported. If you select this option, you also need to define the column(s) and column values that determine which existing rows need to be deleted. Read more here.
• replace: If you select type replace or delete, you must provide an object literal with the columns and column values that determine which existing rows need to be deleted, before new data is imported (e.g. {"f3": "BS"}.

For more detail on the replace option, please refer to Define imports.

Get report data

Returns the data content of a report. You need to pass the full report query string into your request, including lookups, filters and fields to be shown. You can explore this by using the web application.

As part of the query string, you can provide additonal query parameters:

• filter: set one or more filter expressions, separated by a comma. Each expression must have the format [field:value]. For example: filter=f1:10000
• lookup: set relationships between data sets to lookup linked data, separated by a comma. Each expression must have the format [table:field:key]. For example lookup=f1:t2:t3
• show: set one or more fields to be included in the response, separated by a comma. Each expression must have the format [id:table:field]. For example: show=f1:t2:f1,f2:t1:f3

Power query

In addition to calling the API to retrieve data, you can also access data sets and reports directly from Excel or PowerBI using Power queries. This enables you to design your own reports in Excel or PowerBI using realtime data from XLReporting. The data will be automatically refreshed at any time without you having to login to XLReporting.

XLReporting creates a small file for you specific to each data set, which you can open in Excel via Data - Get external data.

You can choose to store your API token and selected values in the file, or leave them as runtime parameters.

Need any help?

We have several tools, connectors, and examples available for integration, and we provide free consulting, so please contact us anytime.