Zoho Reports has implemented the Zoho CloudSQL technology as an extension to its HTTP Web API. Using the HTTP API, users can query Zoho Reports database by providing the SQL queries.

Currently Zoho Reports supports only SQL SELECT Queries. Other SQL statements like INSERT, UPDATE and DELETE will be supported very soon.

Using SQL Select statements developers can fetch data from a single Table/Query Table or joining one or more tables in Zoho Reports. The data can be fetched in different response formats, which includes CSV, PDF, HTML, JSON and XML

SQL SELECT Query Request Format:

Zoho Reports uses the EXPORT HTTP API action request to execute any SQL SELECT query given. The following parameters are to be provided as part of the Export API request to execute an Select query:

Query String Parameters:

The following parameters are to be passed in the Query String

ZOHO_ACTION parameter should be 'EXPORT'
ZOHO_OUTPUT_FORMAT that defines the output format for the API request could be any of the following:
- CSV
- XML
- JSON
- HTML
- PDF

SQL Query as POST parameter:

The exact SQL Select query has to be passed as a post parameter to the API request. The query should be passed as a value to the parameter ZOHO_SQLQUERY. The exact SQL Select query string should be URL encoded.

Sample:

The sample Select Query will fetch all the employees in the 'finance' department along with their details in CSV Format.

Entities Involved:

Database Name: EmployeeDB
Tables Involved: Employee, EmpDetails

Request URL:

http://reports.zoho.com/api/demouser/EmployeeDB?ZOHO_ACTION=EXPORT
&ZOHO_OUTPUT_FORMAT=CSV&ZOHO_ERROR_FORMAT=XML
&ZOHO_API_KEY=hewfdrbgs&ticket=gsssds&ZOHO_API_VERSION=1.0

Select Query in POST Parameter: (Note the query should be URL Encoded)

ZOHO_SQLQUERY=select empdet.Name Name,empdet.DOB Date_Of_Birth,empdet.Address Address,emp.BasicSal BasicPay,round(emp.BasicSal + emp.Allowance,2) Salary from Employee emp inner join EmpDetails empdet on emp.ID = empdet.ID where emp.Dept = 'Finance'

URL Encoded Select Query in POST Parameter:

&ZOHO_SQLQUERY=select%20%20empdet.Name%20Name%2Cempdet.DOB%20Date_Of_Birth%2C
empdet.Address%20Address%2Cemp.BasicSal%20BasicPay%2Cround(emp.BasicSal%20%2B%20emp.Allowance%2C2)%20
Salary%20from%20Employee%20emp%20inner%20join%20EmpDetails%20empdet%20
on%20emp.ID%20%3D%20empdet.ID%20where%20emp.Dept%20%3D%20'Finance'

Sample Success Response:

The sample response for the above query in CSV format is given below. The first row of the CSV response will contain the column names:

Name,Date_Of_Birth,Address,BasicPay,Salary
Kumar,"10 Dec, 1979 00:00:00",Chennai,10000,10500
....

To know more about the EXPORT API action and the parameters (like ZOHO_ERROR_FORMAT etc.,) involved in the request, refer to this link.

Next: Using Zoho Reports JDBC Driver

2 Comments Show recent to old

jkurgan10, 221 - days ago

how do i find my database name? I have a DBID that I pasted in, but that is giving me an error message

saminathan, 220 - days ago

Hi jkurgan,

You need to provide only the database name within the API URL and not the DBID.

You can find the database name by following steps:

1. Login to reports.zoho.com with your zoho account
2. This will take you to the home page of Zoho Reports. Here you can see the list of databases under the tab named 'My Databases'
3. The names which are listed under the tab 'My Databases' are the database names

Thanks,
Saminathan