Zoho Reports

Zoho Reports is an online reporting and business intelligence service.

Zoho Reports supports JDBC, enabling it to import data from databases for which a JDBC driver is available. The Easysoft JDBC-ODBC Bridge extends the number of databases that are available to Zoho Reports by bridging between JDBC and ODBC. You can then also work with data from backends for which an ODBC driver is available. For example, you can connect Zoho Reports to Microsoft Access by using the Easysoft JDBC-ODBC Bridge and the ODBC Driver for Microsoft Access:

Note Currently, the Easysoft JDBC-ODBC Bridge supports 32-bit ODBC drivers only. On Windows, all recent Easysoft ODBC drivers install both a 32-bit and a 64-bit ODBC driver.

These steps show how to import some ODBC data from Zoho Reports on Windows:

  1. Configure a System ODBC data source for the database that you want to connect to in Zoho Reports.

    To do this, use the 32-bit version of ODBC Data Source Administrator on your Zoho Reports machine. On some versions of Windows, this is located in Control Panel > Administrative Tools. On some version of Windows, you need to search for ODBC in the taskbar search box. The 32-bit version of ODBC Data Source Administrator should be clearly labelled. If in doubt, in the Windows Run dialog box, type:

    %windir%\syswow64\odbcad32.exe
  2. Download the Easysoft JDBC-ODBC Bridge. (Registration required.)
  3. Install and license the Easysoft JDBC-ODBC Bridge on the machine where you created the data source.

    For installation instructions, see the Easysoft JDBC-ODBC Bridge documentation.

  4. Install the Zoho Reports Upload Tool on this machine.
  5. Copy the Easysoft JDBC-ODBC Bridge JAR file, EJOB.jar, to the Zoho Reports Upload Tool lib subfolder, for example, C:\ZohoReportsUploadTool_Windows\ZohoReports\UploadTool\lib.

    EJOB.jar is installed in the following location on the machine where you installed the Easysoft JDBC-ODBC Bridge:

    <easysoft_install>\Jars

    On 64-bit Windows, the default location for <easysoft_install> is <drive>:\Program Files (x86)\Easysoft Limited\Easysoft JDBC-ODBC Bridge.

  6. Use one of the methods that Zoho Reports provides to generate an auth token for its data API.
  7. Open common_params.conf in a text editor.

    common_params.conf is located in the conf subfolder. For example, C:\ZohoReportsUploadTool_Windows\ZohoReports\UploadTool\conf.

  8. Set the following properties in this file to these values:
    Property Value
    REPORT_SERVER_URL https://reportsapi.zoho.eu

    –Or–

    https://reportsapi.zoho.com

    If the domain name of your Zoho Reports server contains eu, use the first URL. If the domain name of your Zoho Reports server contains com, use the last URL.
    IAM_SERVER_URL https://accounts.zoho.eu

    –Or–

    https://accounts.zoho.com
    USER_EMAIL_ADDRESS The email address that you use to log into Zoho Reports.
    AUTHTOKEN The Zoho Reports data api auth token that you generated earlier.
  9. Open database_connection_params.conf in a text editor.

    database_connection_params.conf is located in the conf subfolder. For example, C:\ZohoReportsUploadTool_Windows\ZohoReports\UploadTool\conf.

  10. Set the following properties in this file to these values:
    Property Value
    DBTYPE Easysoft
    DRIVERCLASSNAME easysoft.sql.jobDriver
    CONNECTIONURL jdbc:easysoft://localhost/my_odbc_data_source:logonuser=my_windows_user:logonpassword=my_windows_password
  11. Open database_sql_queries.sql in a text editor. Enter the Zoho Reports database and the table you want to import the data into. Add the SQL Query that retrieves the relevant data from your ODBC data source. For example: 
    <?xml version="1.0" encoding="UTF-8" ?>
<Queries>
  <Query id="" dbname="Super Store Sales" tablename="Sales" importtype="APPEND" matchingcols="" selectcols="" skiptop="" batchsize="" queuesize="">
          select SaleDate, Region, ProductCategory, Product, CustomerName, Sales, Cost, Profit from Sales
  </Query>
</Queries>
  12. Run the UploadFromDB batch script from a command prompt to import the data. For example: 
    cd C:\ZohoReportsUploadTool_Windows\ZohoReports\UploadTool\bin
UploadFromDB.bat