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:
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 - Settings - Actions. 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
whenever possible, as this will keep the token more secure in transit.
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:
✭ Tip:
- This article is showing examples based on the 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/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:
[{
"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/t1?token=xxxxx
GET
https://eu.xlreporting.com/table/t1?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/t1?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/t1?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/t1/f1?token=xxxxx
GET
https://eu.xlreporting.com/table/values/t1/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 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.
Method:
POST https://eu.xlreporting.com/table/import/t1?token=xxxxx
Request body:
"type": "replace",
"replace": {"f3": "BS"},
"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/t1?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/t1?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