API integration

You can retrieve data from -and import data into- data sets through our REST API. For example, 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.

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.

API token

You need to provide a valid API access token with all requests. Tokens are linked to users, and you can request a token through My account - Users. Once issued, tokens are valid for 360 days, but you can revoke tokens, and issue new tokens, at any time.

You can either pass the token as a URL parameter (token=xxx) or as a custom HTTP Header (x-access-token: xxx).

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 - Define API integration within a given data set:

screenshot define datasets api

✭ Tip:
  • This article is showing examples based on the Chart of Accounts data set from our demo data.

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:

[{
    "id": "f1",
    "name": "Account code",
    "type": "key"
},
{
    "id": "f2",
    "name": "Account name",
    "type": "text"
},
{
    "id": "f3",
    "name": "Account type",
    "type": "text"
},
{
    "id": "f4",
    "name": "Account category",
    "type": "text"
},
{
    "id": "f5",
    "name": "Account subcategory",
    "type": "text"
}]

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.

Method:
GET https://eu.xlreporting.com/table/t4?token=xxxxx
GET https://eu.xlreporting.com/table/t4?filter=f3:BS&token=xxxxx

Result:
"success": true,
"data": [{
    "f1": "10000",
    "f2": "Buildings",
    "f3": "BS",
    "f4": "10. Fixed Assets",
    "f5": "100. Fixed Assets (cost value)"
},
{
    "f1": "10010",
    "f2": "Furniture & Fittings",
    "f3": "BS",
    "f4": "10. Fixed Assets",
    "f5": "100. Fixed Assets (cost value)"
},
{
    "f1": "10020",
    "f2": "IT & Equipment",
    "f3": "BS",
    "f4": "10. Fixed Assets",
    "f5": "100. Fixed Assets (cost value)"
}]

Get row count

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

Method:
GET https://eu.xlreporting.com/table/stats/t4?token=xxxxx

Result:
"success": true,
"data": 54

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.

Method:
GET https://eu.xlreporting.com/table/fields/t4?token=xxxxx

Result:
"success": true,
"data": [{
    "id": "f1",
    "name": "Account code",
    "type": "key"
},
{
    "id": "f2",
    "name": "Account name",
    "type": "text"
},
{
    "id": "f3",
    "name": "Account type",
    "type": "text"
},
{
    "id": "f4",
    "name": "Account category",
    "type": "text"
},
{
    "id": "f5",
    "name": "Account subcategory",
    "type": "text"
}]

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.

Method:
GET https://eu.xlreporting.com/table/values/t4/f1?token=xxxxx
GET https://eu.xlreporting.com/table/values/t4/f1?filter=f3:BS&token=xxxxx

Result:
"success": true,
"data": [{
    "10000",
    "10010",
    "10020",
    "10100",
    "10110"
}]

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.

Method:
POST https://eu.xlreporting.com/table/import/t4?token=xxxxx

Request body:
"type": "add",
"data": [{
    "f1": "10000",
    "f2": "Buildings",
    "f3": "BS",
    "f4": "10. Fixed Assets",
    "f5": "100. Fixed Assets (cost value)"
},
{
    "f1": "10010",
    "f2": "Furniture & Fittings",
    "f3": "BS",
    "f4": "10. Fixed Assets",
    "f5": "100. Fixed Assets (cost value)"
},
{
    "f1": "10020",
    "f2": "IT & Equipment",
    "f3": "BS",
    "f4": "10. Fixed Assets",
    "f5": "100. Fixed Assets (cost value)"
}]

Result:
"success": true,
"data": {}

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.
Method:
POST https://eu.xlreporting.com/table/clear/t4?token=xxxxx

Request body:
"filter": "f1:2016-01"

Result:
"success": true,
"data": {}

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.

Method:
POST https://eu.xlreporting.com/table/cache/t4?token=xxxxx

Result:
"success": true,
"data": {}

Need any help?

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

Recommended reading:
Back to top | Data sets