Metadata API

Tags:  

Metadata APIs


     

Introduction


Zoho Reports provides APIs for fetching the meta information about the reporting databases created in Zoho Reports and the reports created within the database. You can use this metadata APIs to fetch information about databases and use the same in your application to show the databases and reports as you like. In this section, we have described the syntax and query parameters for the below APIs


Getting Started


To get started with the below documentation, make sure that you already gone through the Prerequisites and API Specifications documentations.


Database Metadata API


Using the Database Metadata API, you can get the following meta information.

  • List of Reporting Databases in your account
  • List of Views and View Information in your Reporting Database
  • Available Type of views (reports, tables, dashboards,...) available in Zoho Reports
  • Available Datatypes in Zoho Reports

Sample Request

The sample request URL is below.

https://reportsapi.zoho.com/api/abc@zoho.com?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=<METADATA_INFO_PARAMETER> &ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML& authtoken=g38sl4j4856guvncrywox8251sssds&ZOHO_API_VERSION=1.0

Query String Parameters

In the query string of the URL include the following parameters.

  • ZOHO_ACTION parameter should be DATABASEMETADATA
  • ZOHO_OUTPUT_FORMAT should be the required output format. The supported formats are
    • XML
    • JSON
  • ZOHO_METADATA specifies the information to be fetched. The supported values are: 
    • ZOHO_CATALOG_LIST: To list the Zoho Reports databases for the specified user
    • ZOHO_CATALOG_INFO: To fetch information about the tables & reports (view) present in the given reporting database in Zoho Reports. 
    • ZOHO_DATATYPES: To get the list of datatypes supported by Zoho Reports
    • ZOHO_TABLETYPES: Various view types available in Zoho Reports

For explanation about other mandatory query string parameters such as ZOHO_ERROR_FORMAT, refer to this link.

Getting Databases (Catalog) List

Sample request for fetching the reporting databases (catalogs) present in the user account.

In the query string of the URL, the ZOHO_ACTION parameter should be DATABASEMETADATA and ZOHO_METADATA parameter should be ZOHO_CATALOG_LIST.

For other mandatory query string parameters such as ZOHO_OUTPUT_FORMAT, Refer to the Common parameters document for details.

 

https://reportsapi.zoho.com/api/abc@zoho.com?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_CATALOG_LIST &ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML& authtoken=g38sl4j4856guvncrywox8251sssds&ZOHO_API_VERSION=1.0

Sample Responses

The following code snippets provides the response in JSON (ZOHO_OUTPUT_FORMAT=JSON) and XML (ZOHO_OUTPUT_FORMAT=XML)formats for the sample referred above.

XML Format

<RESPONSE URI="/api/abc@zoho.com" ACTION="ZOHO_CATALOG_LIST">
<ZCATALOGS>
<ZCATALOG IS_DEFAULT="1" TABLE_CAT="SalesDB"></ZCATALOG>
<ZCATALOG IS_DEFAULT="0" TABLE_CAT="Super Store Sales"></ZCATALOG>
<ZCATALOG IS_DEFAULT="0" TABLE_CAT="Project Manager"></ZCATALOG>
</ZCATALOGS>
</RESPONSE>

JSON Format

{
"response":
{
"uri": "\/api\/abc@zoho.com",
"action": "ZOHO_CATALOG_LIST",
"result":
[
{
"isDefault": true,
"tableCat": "SalesDB"
},
{
"isDefault": false,
"tableCat": "Super Store Sales"
},
{
"isDefault": false,
"tableCat": "Project Manager"
} 
]
}
}


Getting Tables and Reports in a Database (Catalog Information)

Sample request for fetching information about the tables, reports and dashboards available in a reporting database, SalesDB.

In the query string of the URL, the ZOHO_ACTION parameter should be DATABASEMETADATA and ZOHO_METADATA parameter should be ZOHO_CATALOG_INFO.

For other mandatory query string parameters such as ZOHO_OUTPUT_FORMAT, Refer to the Common parameters for more details.

Sample URL

https://reportsapi.zoho.com/api/abc@zoho.com/SalesDB?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_CATALOG_INFO &ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML&authtoken=g38sl4j4856guvncrywox8251sssds&ZOHO_API_VERSION=1.0

Sample Responses

The following code snippets provides the response in XML (ZOHO_OUTPUT_FORMAT=XML) and JSON  (ZOHO_OUTPUT_FORMAT=JSON) formats for the sample referred above.

XML Format

<RESPONSE URI="/api/abc@zoho.com/SalesDB" ACTION="ZOHO_CATALOG_INFO">
<ZCATALOG TABLE_CAT="SalesDB">
<ZVIEW REMARKS="\N" TABLE_NAME="SalesTable" TABLE_TYPE="TABLE">
<ZCOLUMNS>
<ZCOLUMN
COLUMN_NAME="REGION" PKCOLUMN_NAME="\N" NULLABLE="true" COLUMN_SIZE="100"
PKTABLE_NAME
="\N" DATA_TYPE="12" REMARKS="\N" TYPE_NAME="Plain Text"
DECIMAL_DIGITS
="-1" ORDINAL_POSITION="1"></ZCOLUMN>
 .
.

.
<ZCOLUMN COLUMN_NAME="Order Date" PKCOLUMN_NAME="\N" NULLABLE="true" COLUMN_SIZE="19"
PKTABLE_NAME="\N" DATA_TYPE="93" REMARKS="\N" TYPE_NAME="Date"
DECIMAL_DIGITS
="-1" ORDINAL_POSITION="6"></ZCOLUMN>
</ZCOLUMNS></ZVIEW>
<ZVIEW REMARKS="\N" TABLE_NAME="Region" TABLE_TYPE="REPORT"></ZVIEW>
<ZVIEW REMARKS="\N" TABLE_NAME="SalesTabular" TABLE_TYPE="REPORT">
<ZVIEW REMARKS="\N" TABLE_NAME="SalesQuery" TABLE_TYPE="VIEW">
<ZCOLUMNS>
<ZCOLUMN COLUMN_NAME="Market Type" PKCOLUMN_NAME="\N" NULLABLE="false" COLUMN_SIZE="100"
PKTABLE_NAME
="\N" DATA_TYPE="12" REMARKS="\N" TYPE_NAME="Plain Text"
DECIMAL_DIGITS="-1" ORDINAL_POSITION="1"></ZCOLUMN>
.
.
.
<ZCOLUMN COLUMN_NAME="Order Date" PKCOLUMN_NAME="\N" NULLABLE="false" COLUMN_SIZE="19"
PKTABLE_NAME
="\N" DATA_TYPE="93" REMARKS="\N" TYPE_NAME="Date"
DECIMAL_DIGITS
="-1" ORDINAL_POSITION="2"></ZCOLUMN>
</ZCOLUMNS></ZVIEW>
<ZVIEW REMARKS="\N" TABLE_NAME="SalesPivot" TABLE_TYPE="REPORT"></ZVIEW>
<ZVIEW REMARKS="\N" TABLE_NAME="SalesSummary" TABLE_TYPE="REPORT"></ZVIEW>
</RESPONSE>

JSON Format

{
"response":
{
"uri": "\/api\/abc@zoho.com\/SalesDB",
"action": "ZOHO_CATALOG_INFO",
"result":
{
"views":
[

{
"remarks": null,
"tableName": "SalesTable",
"tableType": "TABLE",
"columns":
[
{
"columnName": "REGION",
"pkcolumnName": null,
"nullable": true,
"columnSize": 100,
"pktableName": null,
"dataType": "12",
"remarks": null,
"typeName": "Plain Text",
"decimalDigits": -1,
"ordinalPosition": 1
},
.
.
.
{
"columnName": "Order Date",
"pkcolumnName": null,
"nullable": true,
"columnSize": 19,
"pktableName": null,
"dataType": "93",
"remarks": null,
"typeName": "Date",
"decimalDigits": -1,
"ordinalPosition": 6
}
]
},

{
"remarks": "Region wise chart for the year 2009",
"tableName": "Region",
"tableType": "REPORT",
"columns":
[
]
},

{
"remarks": null,
"tableName": "SalesTabular",
"tableType": "REPORT",
"columns":
[
]
},

{
"remarks": null,
"tableName": "SalesQuery",
"tableType": "VIEW",
"columns":
[
{
"columnName": "Market Type",
"pkcolumnName": null,
"nullable": false,
"columnSize": 100,
"pktableName": null,
"dataType": "12",
"remarks": null,
"typeName": "Plain Text",
"decimalDigits": -1,
"ordinalPosition": 1
},

.
.
.
{
"columnName": "Order Date",
"pkcolumnName": null,
"nullable": false,
"columnSize": 19,
"pktableName": null,
"dataType": "93",
"remarks": null,
"typeName": "Date",
"decimalDigits": -1,
"ordinalPosition": 2
}
]
},

{
"remarks": null,
"tableName": "SalesPivot",
"tableType": "REPORT",
"columns":
[
]
},

{
"remarks": null,
"tableName": "SalesSummary",
"tableType": "REPORT",
"columns":
[
]
}
],
"tableCat": "SalesDB"
}
}
}

The Data Type (DATA_TYPE in XML response and dataType in JSON response) attribute found in the above response will refer the data type of the column. The details about the data type numbers can be found in this section.


Getting Datatype Information

The following table lists the common DATA TYPE numbers and its corresponding TYPE NAMES.

Data Type Number Type Name
12
Plain Text
Multi Line Text
Email
URL
-7 Yes / No Decision
8 Percent
Currency
Decimal Number
-5 Number
Auto Number
Positive Number
93 Date

Sample request for obtaining information about the different datatypes supported in Zoho Reports.

In the query string of the URL, the ZOHO_ACTION parameter should be DATABASEMETADATA and ZOHO_METADATA parameter should be ZOHO_DATATYPES.

For other mandatory query string parameters such as ZOHO_OUTPUT_FORMAT, Refer to the Common parameters document to know more about this parameters.

Sample URL

https://reportsapi.zoho.com/api/abc@zoho.com?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_DATATYPES &ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML&authtoken=g38sl4j4856guvncrywox8251sssds&ZOHO_API_VERSION=1.0

Sample Responses

The following code snippets provides the response in JSON (ZOHO_OUTPUT_FORMAT=JSON) and XML (ZOHO_OUTPUT_FORMAT=XML)formats for the sample referred above.

XML Format

<RESPONSE URI="/api/abc@zoho.com" ACTION="ZOHO_DATATYPES">
<ZDATATYPES>
<ZDATATYPE LITERAL_PREFIX="&apos;" MAXSIZE="19" MAXIMUM_SCALE="\N" NULLABLE="1"
TYPE_NAME
="Positive Number" DATA_TYPE="-5" MINIMUM_SCALE="\N" SQL_DATA_TYPE="-5"
FIXED_PREC_SCALE="false" LOCAL_TYPE_NAME="\N" AUTO_INCREMENT="false" ...
SQL_DATETIME_SUB="0" PRECISION="-1" UNSIGNED_ATTRIBUTE="true" ID="5"></ZDATATYPE>
 .
.
.
<ZDATATYPE LITERAL_PREFIX="&apos;" MAXSIZE="1" MAXIMUM_SCALE="\N" NULLABLE="1"
TYPE_NAME
="Yes/No Decision" DATA_TYPE="-7" MINIMUM_SCALE="\N" SQL_DATA_TYPE="-7"
FIXED_PREC_SCALE="false" LOCAL_TYPE_NAME="\N" AUTO_INCREMENT="false" ...
SQL_DATETIME_SUB="0" PRECISION="-1" UNSIGNED_ATTRIBUTE="false" ID="10"></ZDATATYPE>
</ZDATATYPES>
</RESPONSE>

JSON Format

{ "response": 
{
"uri": "\/api\/abc@zoho.com",
"action": "ZOHO_DATATYPES",
"result":
[

{
"literalPrefix": "\'",
"maxsize": 19,
"maximumScale": null,
"nullable": true,
"dataType": -5,
"minimumScale": null,
"sqlDataType": -5,
"fixedPrecScale": false,
"localTypeName": null,
"autoIncrement": false,
"searchable": false,
"literalSuffix": "\'",
"caseSensitive": true,
"numPrecRadix": "10",
"typeName": "Positive Number",
"createParams": "500",
"sqlDatetimeSub": "0",
"precision": -1,
"unsignedAttribute": true,
"id": "5"
},
 .
.
.

{
"literalPrefix": "\'",
"maxsize": 1,
"maximumScale": null,
"nullable": true,
"dataType": -7,
"minimumScale": null,
"sqlDataType": -7,
"fixedPrecScale": false,
"localTypeName": null,
"autoIncrement": false,
"searchable": false,
"literalSuffix": "\'",
"caseSensitive": true,
"numPrecRadix": "10",
"typeName": "Yes\/No Decision",
"createParams": "500",
"sqlDatetimeSub": "0",
"precision": -1,
"unsignedAttribute": false,
"id": "10"
}
]
}
}


Getting Table Types

The following table lists the common TABLETYPES.

Actual View
TABLETYPE Value
Tables
TABLE
Query Tables VIEW
Charts
Pivots
Tabular Views
Summary Views
Dashboards
REPORT

For other mandatory query string parameters such as ZOHO_OUTPUT_FORMAT, Refer to the Common parameters document for more details.

Sample URL

https://reportsapi.zoho.com/api/abc@zoho.com?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_TABLETYPES &ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML&authtoken=g38sl4j4856guvncrywox8251sssds&ZOHO_API_VERSION=1.0

Sample Responses

The following code snippets provides the response in XML (ZOHO_OUTPUT_FORMAT=XML) and  JSON (ZOHO_OUTPUT_FORMAT=JSON) formats for the sample referred above.

XML Format

<RESPONSE URI="/api/abc@zoho.com" ACTION="ZOHO_TABLETYPES">
<ZVIEWTYPES >

<ZVIEWTYPE TYPE="TABLE"></ZVIEWTYPE>
<ZVIEWTYPE TYPE="VIEW"></ZVIEWTYPE>
<ZVIEWTYPE TYPE="REPORT"></ZVIEWTYPE>
</ZVIEWTYPES>
</RESPONSE> 

JSON Format

{"response": 
{
"uri": "\/api\/abc@zoho.com",
"action": "ZOHO_TABLETYPES",
"result":
[
"TABLE",
"VIEW",
"REPORT"
]
}
}


Response Attributes

In this section, we have described the detailed explanation of the attribute / property we used in DATABASEMETADATA APIs.

 
Attribute/Property Description
IS_DEFAULT/isDefault Indicates whether a Zoho Reports database is set as the default.  Can be used to set default database for third party tools that loads Zoho Reports Databases using JDBC Driver. 
TABLE_CAT/tableCat Zoho Reports database name (ex: SalesDB)
COLUMN_NAME/columnName Name of the column in table type views (ex: Region)
LITERAL_PREFIX Prefix character used when literal values found (ex: '45')
NULLABLE
Will be true if the column can contain null value, false otherwise. 
MAXSIZE
Maximum size of the column (ex: 20)
TYPE_NAME Zoho Reports name for the data type (ex: Positive Number)

Note:\N in XML response refer to the NULL value 

More information can be found in the following Java DatabaseMetadata documentation: getTables, getTableTypes, getColumns, getTypeInfo, getCatalogs


Getting Metadata Using JDBC Driver

In case you are using a Java application then Zoho Reports offers a JDBC Driver and which can be used to fetch all the Zoho Reports metadata information. Refer to the page JDBC Driver for details.

Get Database Name API


This API returns the name of a database in Zoho Reports given its Database ID as input. Database ID's are unique identifiers that is associated with each reporting database that is created in Zoho Reports.

Request

The sample request URL is below.

https://reportsapi.zoho.com/api/abc@zoho.com?ZOHO_ACTION=GETDATABASENAME&DBID=999999999999999
&ZOHO_OUTPUT_FORMAT=XML&ZOHO_API_VERSION=1.0&authtoken=8b30a900d73sjc7392n8923dkf983k

Query String Parameters
Parameter Name Description Possible Values
DBID The DBID of the Zoho Reports Online Reporting Database which can be taken from database URL
A Long number like 9999999999999

Sample Response:

XML Format:

<response uri="/api/abc@zoho.com" action="GETDATABASENAME">
<dbname>Employee</dbname>
</response>

JSON Format:

{
"response":
{
"uri": "\/api\/abc@zoho.com",
"action": "GETDATABASENAME",
"result":
{
"dbname": "Employee"
}
}
}

Get View Name API

This API returns the name of a view in Zoho Reports given its View ID as input. View ID's are unique identifiers that is associated with each view (tables, reports and dashboards) that is created in Zoho Reports.

Request

The Sample Request URL is below.

https://reportsapi.zoho.com/api/abc@zoho.com?ZOHO_ACTION=GETVIEWNAME&OBJID=999999999999999
&ZOHO_OUTPUT_FORMAT=XML&ZOHO_API_VERSION=1.0&authtoken=8b30a900d73sjc7392n8923dkf983k

Query String Parameters:

Parameter Name Description Possible Values
OBJID The ID of the view which can be taken from the corresponding view URL. 
A Long number like 9999999999999


Sample Response:

XML Format:

<response uri="/api/abc@zoho.com" action="GETVIEWNAME">
<viewname>Employee Details</viewname>
</response>

JSON Format:

{
"response":
{
"uri": "\/api\/abc@zoho.com",
"action": "GETVIEWNAME",
"result":
{
"viewname": "Employee Details"
}
}
}

Get Info API

This API returns the Database ID (DBID) and View ID (OBJID) of the corresponding Database and View Name provided as input for the API.

Request

The sample request URL is below.

https://reportsapi.zoho.com/api/abc@zoho.com/Employee/EmployeeDetails?ZOHO_ACTION=GETINFO
&ZOHO_OUTPUT_FORMAT=XML&ZOHO_API_VERSION=1.0&authtoken=8b30a900d73sjc7392n8923dkf983k
Sample Response:
<response uri="/api/abc@zoho.com/Employee/EmployeeDetails" action="GETINFO">
<objid>99999999999999</objid> <dbid>88888888888888</dbid>
</response>

JSON Format:

{
"response":
{
"uri": "\/api\/abc@zoho.com\/Employee\/EmployeeDetails",
"action": "GETINFO",
"result":
{
"objid": "99999999999999", "dbid": "88888888888888"
}
}
}

Get View URL API


This API returns the URL to access the mentioned view. You need to provide the database name and the view name as input for the API.



Request
The sample request URL is below.

https://reportsapi.zoho.com/api/abc@zoho.com/Employee/EmployeeDetails?ZOHO_ACTION=GETVIEWURL
&ZOHO_OUTPUT_FORMAT=XML&ZOHO_API_VERSION=1.0&authtoken=8b30a900d73sjc7392n8923dkf983k

Sample Response

 
XML Format:
<response uri="/api/abc@zoho.com/Employee/EmployeeDetails" action="GETVIEWURL">
<result>  <viewurl> https://reports.zoho.com/ZDBDataSheetView.cc?&OBJID=9999999999999&STANDALONE=true&REMTOOLBAR=true&ZDB_THEME_NAME=blue </viewurl> </result>
</response>

JSON Format:

{
"response"
:
{
"uri
": "\/api\/abc@zoho.com\/Employee\/EmployeeDetails",
"action"
: "GETVIEWURL",
"result"
:
{
"viewurl"
: "https://reports.zoho.com/ZDBDataSheetView.cc?&OBJID=9999999999999&STANDALONE=true&REMTOOLBAR=true&ZDB_THEME_NAME=blue",
}
}
}

Error Handling


This section explains about the error response sent from the Zoho Reports server on failure of Metadata APIs. The sample error response is below.

XML Format:

<response uri="/api/abc@zoho.com/Employee" action="DATABASEMETADATA">
<error> <code>7103</code>
<message>Database not found! Please check whether the database exists</message>
</error>
</response>

JSON Format:

{
"response":
{
"uri": "\/api\/abc@zoho.com\/Employee",
"action": "DATABASEMETADATA",
"error":
{ "code":7103,
"message": "Database not found! Please check whether the database exists"
}
}
}

 

Error Codes

The below table provides the list of error codes that could be returned while invoking the Metadata API requests.

Error Code Reason Solution
7102
The database name specified in the API request URL is empty. 
Check the API Request URL and provide the Database Name
7103
The database name specified in the API request URL does not exist
Check the database name in the request URL and provide a valid database name
7104
The view name specified in the API request URL does not exist. Check the view name in the request URL and provide a valid view name

In case you encounter any other errors, please mail the API request URL parameters and error response details to support@zohoreports.com. We will get back to you shortly with the best possible solution.


    Post a comment

    Your Name or E-mail ID (mandatory)

    Note: Your comment will be published after approval of the owner.




     RSS of this page