Data Sources¶

Easy Report can make reports on tables (resources in service's terminology) loaded from data sources. Below is a list of supported data sources.

Table requirements¶

All attributes lie in denormalized/text form (see Division, Supplier, Article columns);
Hierarchies are laid out in flat format; The hierarchy Department (e.g., IT Department) → Vendor (e.g., Kings and Unicorn Ltd) is laid out in the table view as follows:

Department Vendor

IT Department Kings

IT Department Unicorn Ltd

File storages¶

Dropbox
Google Drive
Local Files

To connect, you need to provide permission to read your files and folders. The service will not have access to modify or delete your files.

Supported resource types¶

Resources in file storages are files in .xlsx and .csv formats that meet the following parameters:

xlsx
- First line on the sheet must contain headers.
- The column with dates must be in excel-format "Date".
csv
- You must use comma as a delimiter.
- Acceptable formats for dates are: D.MM.YYYY, D.MM.YY, YYYYY.MM.DD, YY.MM.DD (delimiters: .-/`).

Databases¶

Postgres

To connect you must specify the following parameters:
- host
- port
- database name
- database schema
- username
- user password
MySQL

To connect you must specify the following parameters:
- host
- port
- database name
- username
- user password
MSSQL

To connect, you must specify the following parameters:
- host
- port
- database name
- database schema
- username
- user password

Supported resource types¶

Tables and views (view, materialized view). It is necessary to give the user rights to read these resources on the database side.

Tip

Using views (materialized view) is useful if your database consists of several tables.

Supported data types¶

Integer (int32, int64, etc.);
Floating point numbers (float32, float64, double, etc.);
Text (text, string, etc.);
Date (date, datetime, etc.).

The full list of supported data types depends on the user's initial system and is negotiated separately.

API¶

JSON API

To connect, you need to specify the following parameters:
- main API URL
  
  For example, https://api.github.com or https://analyticsreporting.googleapis.com.
- timeout
  
  Maximum wait time of response from data source (by default, 30 seconds).
- headers (optional)

Supported resource types¶

For each API you can create any number of resources by specifying different addresses (path) when creating them. That's why the main URL of the API should be as general as possible (e.g., the host of your data source).

The JSON object is of the following format:

[
    {
        key1: value1,
        key2: value2,
        ...
    },
    {
        key1: value3,
        key2: value4,
        ...
    },
    ...
]

valueN are primitive types (string, number, logical, null),
the structure of Objects {key1, ..., keyN} is always the same.

To load complex (nested) JSON objects you need to transform them to a supported form using JSON parser - jq.

Example of jq-transformation¶

For example, the initial JSON has complex structure:

[
  {
    "sha": "d25341478381063d1c76e81b3a52e0592a7c997f",
    "commit": {
      "author": {
        "name": "Stephen Dolan",
        "email": "mu@netsoc.tcd.ie",
        "date": "2013-06-22T16:30:59Z"
      },
      "committer": {
        "name": "Stephen Dolan",
        "email": "mu@netsoc.tcd.ie",
        "date": "2013-06-22T16:30:59Z"
      },
      "message": "Merge pull request #162 from stedolan/utf8-fixes\n\nUtf8 fixes. Closes #161",
      "tree": {
        "sha": "6ab697a8dfb5a96e124666bf6d6213822599fb40",
        "url": "https://api.github.com/repos/stedolan/jq/git/trees/6ab697a8dfb5a96e124666bf6d6213822599fb40"
      },
      "url": "https://api.github.com/repos/stedolan/jq/git/commits/d25341478381063d1c76e81b3a52e0592a7c997f",
      "comment_count": 0
    },
    "url": "https://api.github.com/repos/stedolan/jq/commits/d25341478381063d1c76e81b3a52e0592a7c997f",
    "html_url": "https://github.com/stedolan/jq/commit/d25341478381063d1c76e81b3a52e0592a7c997f",
    "comments_url": "https://api.github.com/repos/stedolan/jq/commits/d25341478381063d1c76e81b3a52e0592a7c997f/comments",
    "author": {
      "login": "stedolan",
...

If you want to get an array of objects of this type:

[
  {
    "message": "Merge pull request #163 from stedolan/utf8-fixes\n\nUtf8 fixes. Closes #161",
    "name": "Stephen Dolan"
  },
  {
    "message": "Reject all overlong UTF8 sequences.",
    "name": "Stephen Dolan"
  },
  {
    "message": "Fix various UTF8 parsing bugs.\n\nIn particular, parse bad UTF8 by replacing the broken bits with U+FFFD\nand resychronise correctly after broken sequences.",
    "name": "Stephen Dolan"
  },
  ...
]

The jq-transformation will look as follows:

[.[] | {message: .commit.message, name: .commit.committer.name}]

You can find a more detailed description of the features of jq at this link - jq Tutorial.

Push-Integration via Easy Report API¶

Sometimes the user is unable/unwilling to provide access to their source systems, or it is much more difficult than sending the data to us from their end.

Easy Report provides this capability by sending data in files.

Supported data format:

csv

Restrictions:
1. Date format - "YYYY-MM-DDT" (2006-01-02T) or "YYYY-MM-DDTHH:mm:ss.SSSSSSSSSX" (2006-01-02T15:04:05.999999999Z07:00)

Resource creating¶

Step 1. Get a special URL to load the file

POST https://demo.easyreportbot.com/api/endpoints/connections/bucket/:id/upload-url/

Headers:

Attribute	Type	Required	Description
`Authorization`	string	yes	Provide credentials that authenticate a user. eg. "Token 35e1f..."

Parameters:

Attribute	Type	Required	Description
`id`	integer	yes	The ID of Bucket connection

Body:

Attribute	Type	Required	Description
`file_name`	string	yes	The name of file that you will upload. This file_name can be used later to upload new version of the file

Response example:

{
    presigned_url: "https://demo.easyreportbot.com/bucket-7158517e3a834a6ea2b632f0b0fb1ed8/example.csv?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=easyreport%2F20220428%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20220428T170712Z&X-Amz-Expires=7200&X-Amz-SignedHeaders=host&X-Amz-Signature=0f53e73d43bdcdc60a1a4c0634caa0edc8badcbabfb16e2f050f80489485be5c"
}

Step 2. Using the received presigned_url load the file:

PUT https://demo.easyreportbot.com/bucket-XXX/example.csv?...

Headers:

Attribute	Type	Required	Description
`Content-Type`	string	no	`text/csv`

Body:

Attribute	Type	Required	Description
`file`	Blob	yes	Attached file-descriptor to this request. Implementation of body depends of what client you use to send request

Step 3. After uploading the file, create a resource in the Easy Report admin panel by selecting the appropriate Bucket and file name.

Resource update¶

Full-update¶

If you are using a full data update strategy, then you need to update the file called file_name that you uploaded earlier.

To get a link to load an updated file, use the same API, specifying the file_name that you used earlier.

POST https://demo.easyreportbot.com/api/endpoints/connections/bucket/:id/upload-url/

Headers:

Attribute	Type	Required	Description
`Authorization`	string	yes	Provide credentials that authenticate a user. eg. "Token 35e1f..."

Parameters:

Attribute	Type	Required	Description
`id`	integer	yes	The ID of Bucket connection

Body:

Attribute	Type	Required	Description
`file_name`	string	yes	The name of file that you will upload. This file_name can be used later to upload new version of the file

Response example:

{
    presigned_url: "https://demo.easyreportbot.com/bucket-7158517e3a834a6ea2b632f0b0fb1ed8/example.csv?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=easyreport%2F20220428%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20220428T170712Z&X-Amz-Expires=7200&X-Amz-SignedHeaders=host&X-Amz-Signature=0f53e73d43bdcdc60a1a4c0634caa0edc8badcbabfb16e2f050f80489485be5c"
}

Similarly, use the link received in the response to send a new file. After the file has been uploaded to the Easy Report server, the resource must be updated. You can do this in 3 ways:

Wait for scheduled updates
In the admin panel on the resource page, click the "Update" button
Trigger update via Easy Report API

POST https://demo.easyreportbot.com/api/endpoints/resource/:id/load/full/

Headers:

Attribute	Type	Required	Description
`Authorization`	string	yes	Provide credentials that authenticate a user. eg. "Token 35e1f..."

Parameters:

Attribute	Type	Required	Description
`id`	integer	yes	The ID of Resource that you want to update

Body:

Empty

Response example:

200 OK

Incremental-update¶

If you use the incremental update strategy, then to update you need to load a file with an incremental data set, which will be merged into the original data file on the Easy Report side.

Step 1. Use a special URL to load the patch-file

POST https://demo.easyreportbot.com/api/endpoints/connections/bucket/:id/upload-patch/

Headers:

Attribute	Type	Required	Description
`Content-Type`	string	yes	Set value `multipart/form-data`
`Authorization`	string	yes	Provide credentials that authenticate a user. eg. "Token 35e1f..."

Parameters:

Attribute	Type	Required	Description
`id`	integer	yes	The ID of Bucket connection

Body:

Attribute	Type	Required	Description
`file`	file	yes	File with patch data to be uploaded
`file_name`	string	yes	Original file name which will be updated
`field_delimiter`	string	no	Field delimiter that used in patch csv file. Default: `,`
`record_delimiter`	string	no	Record delimiter that used in patch csv filed. Default: `\n`
`date_column`	string	yes	Date column name which will be used to filter updated data
`datetime`*	string	no	Filter data from specific datetime
`date`*	string	no	Filter data from specific date at 00:00 time
`last_n_days`*	string	no	Filter data within last N days
* You can use only one of parameters: `datetime`, `date`, `last_n_days`.

Response example:

200 OK

Example

Today is May 15th, 2022. You loaded csv file named Sales.csv with data for two last years. And on May 16th you want to add data for the new day and rewrite data for previous two days. You need to prepare a csv file with the same structure which contains only three last days and send it to Easy Report using API described above with parameters such as:

file=<my_delta_file>.csv

file_name='Sales.csv'

date_column='Date'

last_n_days=3

You also can use parameter date and set it to 2022-05-14 or datetime='2022-05-14T00:00:00Z'.

Easy Report will internally replace data for May, 14-16th in original csv with the new data from csv patch-file.

Step 2. Update the resource using one of the available methods:

Wait for scheduled updates
In the admin panel on the resource page, click the "Update incrementally" button
Trigger update via Easy Report API

POST https://demo.easyreportbot.com/api/endpoints/resource/:id/load/partial/

Headers:

Attribute	Type	Required	Description
`Authorization`	string	yes	Provide credentials that authenticate a user. eg. "Token 35e1f..."

Parameters:

Attribute	Type	Required	Description
`id`	integer	yes	The ID of Resource that you want to update

Body:

Empty

Response example:

200 OK

Department	Vendor
IT Department	Kings
IT Department	Unicorn Ltd