Zoho Reports
Internationally we see a lot of people using Zoho Reports as an alternative for Microsoft Power BI.
This guide explains how to real-time download data from Exact Online into Zoho Reports.
Exact Online into Zoho Reports
Base of this integration is the use of the real-time online variant of Invantive SQL. Invantive SQL provides a super-set of most commercially used SQL dialects, but relies for data storage on standard database platforms and cloud applications accessible through a API.
The real-time online variant of Invantive SQL is Invantive Data Access Point, located at https://data-access-point.com. It is free for incidental and non-commercial use. Dedicated instances with additional security features are available for commercial and/or regular use.
As with all Invantive SQL variants, it supports cross-company queries as well as cross-country queries on Exact Online.
Steps to Load Data
Execute the following steps to import your data into Zoho Reports:
- Make sure your Exact Online user account allows access to the needed data through the screen.
- Open Zoho Reports.
- For this sample, we will import the Profit & Loss and Balance per period.
- Click on ‘Files & Feeds’:
- You can also use a cloud database in combination with Invantive Data Replicator.
- Specify the database name as ‘Exact Online - Financial Statement’.
- Zoho Reports is very flexible in the file types supported. I recommend JSON for the most accurate representation of data types.
- Specify the expected file type as JSON.
- Specify the data location ‘web’ and ‘URL with Authentication’.
- Use the URL https://data-access-point.com/eol/stable/dap/Results.
- You can use either Post or Get; both will work.
- Specify the Data Access Point output file format by giving the parameter ‘format’ a value of ‘json’.
- Specify the user and password as parameters ‘user’ and ‘password’ for simplicity.
- There are also various options to use the OAuth Code Grant Flow in combination with Invantive Data Access Point. This will in general require preauthentication using cloud.invantive.com.
- There are also various options to restrict the IP-address ranges from which data may be retrieved. In this case I would recommend listing the Zoho Reports IP-addresses as well as your own static IP-address for better information security.
- In this sample, the credentials are not secured. Remember to secure the credentials using one of the possibilities such as IP-address authentication, pre-encrypted passwords and tokens for pre-authentication generated for a specific client ID.
- Specify the query as ‘query’:
- Zoho Reports requires you to escape the parameter values manually, by replacing spaces by %20 and for instance the '' character as %5C.
- For some unclear reason, the preview includes only the first four rows.
- The preview consists of both metadata from Invantive SQL (such as ‘Nullable’ on the left) and actual data (such as ‘PERIODS…’). In general the actual data is not visible at this phase, but the metadata takes several rows of data.
- I recommend to specify ‘Retain Column Names’ as ‘Yes’:
- And then click ‘Create’:
- The scheduling features enable you to regularly download current facts. As a rule of thumb, reduce the frequency as much as possible for instance daily or weekly.
- The resulting table requires some work like renaming columns:
Invantive and Exact Online Documentation
To more easily develop queries, you might want to use the Online SQL Editor for Exact Online or Invantive Query Tool.
The full Invantive SQL syntax is available as described in the grammar and concepts.
A graphical representation of the Exact Online data model as made available in Invantive SQL using XML and REST APIs is also available.
Also, all tables and views of the Exact Online data model are available on documentation.invantive.com.