Excel Reporting Template Data Format

see Data Formats

This article describes the format for reporting data using Fusion Registry Excel templates.

Overview

The Reporting Template Data format is not an official SDMX format. It was developed by Metadata Technology initially as a format for data collection, simplifying the data reporting process by building an Excel workbook which prebuilds all the observation cells the Data Provider is expected to report. This design means the data reporter only has to concern themselves with filling in the observation values, and not deciding what classification ids they need to report for each Dimension (like the Fusion-Excel data format).

However, due to the flexability of the format, which allows each Agency to design the layout of each worksheet tailored for each Dataflow, the use cases were extended to support Reporting Templates as a data dissemination format. So from Fusion Registry 10.2.x and higher, it is possible to query for a data in this format.

The Reporting Template Data format differs from all the other CSV and Excel style data formats as there is no set layout. The layout (rows and columns) and inclusion / exclusion of information, such as Attributes, is designed in the Fusion Registry by creating a Reporting Template Definition. The output dataset is built into a table in Excel, with the table populated with the observation values, and optionally observation attributes (if it has been designed to include this information).

For more information on this format, click here.

Formatting Using Query Parameters

The following URL parameters can be used in a RESTful query for to get data in Excel format.

• format=reporttemplate
• partial=true

The partial parameter is used to indicate that the output excel file should build the output table based on the data in the response or the universe of data defined by the report template definition. This partial parameter should be set to true for data dissemination as it is not possible to generate a non-partial template without passing extra information about who the Data Provider is. If the partial parameter is false (which is the default if not provided), then the data query must include the Data Provider identifier as part of the path. The report template content will then be built in the context of what the Data Provider has been configured to report, and will therefore include all the cells that the Data Provider can report for. This is in contrast to the partial=true output which only includes cells which are part of the data reponse.

Examples of this behviour is shown in the next section.

Example

The following example shows how the report template can be used as a data dissemination format, using the partial parameter set to true.

https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA+MYS+NZL.GCI+STARTBUSDAYS..?format=reporttemplate&partial=true

If the partial parameter is not set, then the data provider must be included in the path (/GHA at the end of the path, specifies the data provider to be Ghana)

https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI../GHA?format=reporttemplate

The above query will build the full reporting universe for Ghana, but only populate the numbers that match the above query (as opposed to the partial parameter which only shows the data from the query response).

If the query is expanded, then more of the cells will be populated as the following example shows.

https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.../GHA?format=reporttemplate

The reporting universe for Ghana is derived from all the possible combinations of dimension values for the Global Competative Index Dataflow, overlaid with the restrictions imposed by reporting constraints. https://demo.metadatatechnology.com/FusionRegistry/data/constraints.html

In this example, the Global Competative Index has been restriced to Annual data (for all data providers), in addition Ghana has been restricted to only report data for Country Ghana, and finally the Provision Agreement for Ghana defines an explicit list of series that they can report data for.

Note: The above URL only works if Ghana has actually reported the data, as the query is only obtaining the data as reported by the Data Provider GHA, it is not based on the Reference Area Dimenison which happens to have the same Code Id as the Data Provider Id.

Note: The same formatting can be applied using HTTP Accept Headers as opposed to query parameters.

Note: The workbooks are locked, the only editable parts are the observation cells. It is possible to edit the observation values and load the data back into the Fusion Registry.