Create an Excel Reporting Template

Overview

A Data Reporting Template is a metadata driven Excel Form to enable Data Providers to easily report data.

A Reporting Template definition is created in the Fusion Metadata Registry and describes the contents and layout of one Excel Workbook. Each workbook contains at least one worksheet which is backed by the Dataflow that the data collection is for. A workbook may contain multiple worksheets, enabling one Excel file to be used to submit data for multiple datasets.

The Registry provides both a web User Interface and web services to generate the Excel Workbook for a Reporting Template for a specific Data Provider. The contents of the Excel workbook are dynamically obtained based on who the Data Provider is. This enables a single data collection template to be set up for multiple data providers, where each provider's workbook contains only the worksheets, and data cells they need to report data for.

This Use Case

This example is a simple Template using the GUI and is for a scenario where the agency WB (World Bank)has a number of Data Providers (including one with the ID of GHA) who are reporting statistics against the Dataflow GCI (Global Competitive Index). The Data Provider GHA, is only permitted to report statistical Data for Ghana.

This example shows how the Registry can be used to create an Excel Template for the specific Data Provider. Once the end result is Generated, it could be issued to the Data Provider for completion. However it is important to note that the once the Excel spreadsheet has been completed by the Data Provider, Fusion Metadata Registry cannot be used to validate or publish the data as those features only exist in Fusion Registry Enterprise Edition.

Creating the Template

Step 1 - Check all the elements are in place

Data Structure

Dataflow

Data Provider and Provision Agreement

The method to restrict

In this example I am going to use the AREA code method to restrict using the code GH (the code for Ghana). If I check the Data Structure, I can see what other structures have been used. In this example I am looking at the Concept as highlighted in the image below.

Note that the Cross Reference is a link, if I click, I will be taken to the Concept Scheme, and I can see a Cross Reference to a Codelist.

Again a link. If I click the link and View the codelist, I can enter the search term Ghana, to find the code that is used. GHA,

I now have everything I need to move to the next step.

Step 2 - Create Reporting Constraint

From the Data menu, select Reporting Constraints, then use the Maintenance button to select Create New Cube Region Constraint.

The Reporting Constraint Wizard will open and Step 1 is completed with the basic details as shown below.

Step 2 allows you to select a structure type, in this example it is the Data Provider who is to be constrained to only submit data for Ghana.

You will note that the Components of the underlying Data Structure are displayed and REF-AREA has been ticked. The codelist CL_WB_CGI_AREA contains all countries. The next step of the Wizard will allow us to define which Country.

In Step 3, the Wizard allows you to define included values and in this example I have used the +Add New Value button to add the code GHA.

In Step 4, you can Exclude values but this is unnecessary in this example.

As stated above, this is a very simple example. You can read more about Reporting Templates on the main Metadata Technology Wiki in this article.