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 all data - returns the data content of the data set.
• 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.
• Import data - import data into the data set.
• Clear data rows - clear selected data from this data set.
• Clear cached data - clear all report caches that refer to this 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. 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 assigned to users, and you can request a token through Account - Users. Once issued, tokens are valid for 360 days, but you can revoke tokens, renew tokens, and issue new tokens, at any time.

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 use the custom HTTP header in POST calls 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 customer. They can be created, changed, and deleted at any time. The columns within a data set are also configurable. Nothing is set in stone and data sets are likely to be different for every customer.

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 need to refer to the internal ID's of objects.

Therefore, before you can retrieve data from -or import data into- 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 - View 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/t4?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 all 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.

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.

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 can any 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 indicate which column(s) will trigger the replacement. 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 indicate which column(s) will trigger the deletion. Read more here.
• replace: If you select type replace or delete, you must provide an object literal with the columns and values to specify which existing rows need to be deleted, before new data is imported (e.g. {"f1": "Jan-2017"}.

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

Clear data rows

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 cached data

To clear all report caches that refer to this data set, you need to pass the data set ID in the request. No request body is needed.

Need any help?

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