Analytics-Plus Help

Metadata

Important Note:

  1. It is mandatory to use HTTPS in all API requests instead of HTTP request. HTTP is not supported.
  2. Always use https://<reports-hostname>:<port>/api/<reports-loginname>/<databasename>/<tablename> or<reportsname>
     as the API request URL.

Using the APIs provided in this section you could get information about the databases and the reports created within it, in your ManageEngine Reports account. With the APIs provided you could get the following.

  • The list of Reporting databases in your account
  • Types of views available in ManageEngine Reports
  • Datatypes supported in ManageEngine Reports
  • The list of views in your database

Getting Metadata Using JDBC Driver

ManageEngine Reports has JDBC Driver and it can be used to fetch Zoho Reports' metadata. Refer to the page JDBC Driver for details.

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 a specified user
    • ZOHO_CATALOG_INFO : To fetch the Zoho Reports' information, including the tables and views present in the database.
    • ZOHO_DATATYPES : To get the data types 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.

Samples

The following listing shows samples for various METADATA request.

Getting Databases (Catalog) List

Sample request for fetching the databases (catalogs) created by a demouser.

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.

Sample URL

https://<reports-hostname>:<port>/api/demouser?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_CATALOG_LIST

&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML&ZOHO_API_KEY=hewfdrbgs&
ticket=4564832c0e0ea23321df427d599d2d17&ZOHO_API_VERSION=1.0 

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/demouser" 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\/demouser",
"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 the Database (Catalog Information)

Sample request for fetching the database information which includes the views present in the database, SalesDB created by a demouser.

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://<reports-hostname>:<port>/api/demouser/SalesDB?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_CATALOG_INFO

&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML&ZOHO_API_KEY=hewfdrbgs&
ticket=4564832c0e0ea23321df427d599d2d17&ZOHO_API_VERSION=1.0

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/demouser7/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\/demouser\/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"
}
}
}

Getting Datatype Information

Sample request for obtaining the information about the datatypes supporeted in ManageEngine 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://<reports-hostname>:<port>/api/demouser?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_DATATYPES

&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML&ZOHO_API_KEY=hewfdrbgs&
ticket=4564832c0e0ea23321df427d599d2d17&ZOHO_API_VERSION=1.0

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/demouser" 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\/demouser",
"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"
}
]
}
}

Table Types

Sample request for obtaining the view types (table & reports) supporeted in ManageEngine Reports.

In the query string of the URL, the ZOHO_ACTION parameter should be DATABASEMETADATA and ZOHO_METADATA parameter should be ZOHO_TABLETYPES. The following tables lists the common TABLETYPES

TABLEVIEWVIEW
TablesQuery TablesCharts Pivots
Tabular Reports
Summary Views

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

Sample URL

https://<reports-hostname>:<port>/api/demouser?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_TABLETYPES

&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML&ZOHO_API_KEY=hewfdrbgs&
ticket=4564832c0e0ea23321df427d599d2d17&ZOHO_API_VERSION=1.0

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/demouser" ACTION="ZOHO_TABLETYPES">

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

JSON Format

{
"response":
{
"uri": "\/api\/demouser",
"action": "ZOHO_TABLETYPES",
"result":
[
"TABLE",
"VIEW",
"REPORT"
 ]
}
}

Response Attributes

The following listing describes the common attributes/properties mentioned in the response.

Attribute/PropertyDescription
IS_DEFAULT/isDefaultCan be used to set defult database for third party tools that loads Zoho Reports Databases using JDBC Driver
TABLE_CAT/tableCatManageEngine Reports database name (ex: SalesDB)
COLUMN_NAME/columnNameName of the column in table type views (ex: Region)
LITERAL_PREFIXPrefix character to be used when literal values found (ex: '45')
NULLABLEwhether the column takes null vale or not (ex: false)
MAXSIZEMaximum size of the column (ex: 20)
TYPE_NAMEManageEngine 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

Share this post : FacebookTwitter