Reports 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.skydesk.jp as the API request URL.

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

  • The list of Workspaces in your account
  • Types of views available in SkyDesk Reports
  • Datatypes supported in SkyDesk Reports
  • The list of views in your workspace

Getting Metadata Using JDBC Driver

SkyDesk Reports has JDBC Driver and it can be used to fetch SkyDesk Reports' metadata.

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 SkyDesk Reports' workspaces for a specified user
    • ZOHO_CATALOG_INFO : To fetch the SkyDesk Reports' information, including the tables and views present in the workspace.
    • ZOHO_DATATYPES : To get the data types supported by SkyDesk Reports
    • ZOHO_TABLETYPES: Various view types available in SkyDesk 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.

  • ZOHO_CATALOG_LIST : To list the SkyDesk Reports' workspaces for the specified user
  • ZOHO_CATALOG_INFO : To fetch the SkyDesk Reports workspace information, including the tables and views
  • ZOHO_DATATYPES : To get the data types supported by SkyDesk Reports
  • ZOHO_TABLETYPES : Various view types available in SkyDesk Reports

Getting Workspaces (Catalog) List

Sample request for fetching the workspaces (catalogs) created by a SkyDeskloginemailaddress.

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.skydesk.jp/api/SkyDeskloginemailaddress?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_CATALOG_LIST
&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML
&authtoken=g38sl4j4856guvncrywox8251sssds&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/SkyDeskloginemailaddress" 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\/SkyDeskloginemailaddress",
"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 Workspace (Catalog Information)

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

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.skydesk.jp/api/SkyDeskloginemailaddress/SalesDB?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_CATALOG_INFO
&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML
&authtoken=g38sl4j4856guvncrywox8251sssds&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/SkyDeskloginemailaddress/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\/SkyDeskloginemailaddress\/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 SkyDesk 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.skydesk.jp/api/SkyDeskloginemailaddress?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_DATATYPES
&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML
&authtoken=g38sl4j4856guvncrywox8251sssds&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/SkyDeskloginemailaddress" 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"></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"></ZDATATYPE>
</ZDATATYPES>
</RESPONSE>

JSON Format

{
"response":
{
"uri": "\/api\/SkyDeskloginemailaddress",
"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,
},
     .
.
.

{
"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,
}
]
}
}

Table Types

Sample request for obtaining the view types (table & reports) supporeted in SkyDesk 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

TABLE VIEW VIEW
Tables Query Tables Charts 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.skydesk.jp/api/SkyDeskloginemailaddress?ZOHO_ACTION=DATABASEMETADATA&ZOHO_METADATA=ZOHO_TABLETYPES
&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML
&authtoken=g38sl4j4856guvncrywox8251sssds&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/SkyDeskloginemailaddress" ACTION="ZOHO_TABLETYPES">

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

JSON Format

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

Response Attributes

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

Attribute/Property Description
IS_DEFAULT/isDefault Can be used to set defult workspace for third party tools that loads SkyDesk Reports Workspaces using JDBC Driver
TABLE_CAT/tableCat SkyDesk Reports workspace name (ex: SalesDB)
COLUMN_NAME/columnName Name of the column in table type views (ex: Region)
LITERAL_PREFIX Prefix character to be used when literal values found (ex: '45')
NULLABLE whether the column takes null vale or not (ex: false)
MAXSIZE Maximum size of the column (ex: 20)
TYPE_NAME SkyDesk 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

Top