Create an Excel Reporting Template

Overview

An Excel Reporting Template (also sometimes called 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. Once the Excel spreadsheet has been completed by the Data Provider, Fusion Metadata Registry can be used to validate the data via the Convert data page.

Creating the Template

Step 1 - Check all the elements are in place

Data Structure

Dataflow

Data Provider and Provision Agreement

The means 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.

Creating the Reporting Template

Once all the relevant steps have been completed, the actual Reporting Template can be created using the Wizard.

Step 1 - High Level Details

Step 2 - Excel Worksheets

Follow the instructions shown in the image below to add the first worksheet which will be used to report the observations.

In this example, an Instruction worksheet is created as shown in the image below

As this is a simple example, Steps 3 and 4 can be skipped so click Finish to save.

Generate the Template

Only authenticated users are able to Generate a Template so this example assumes that the Template is to be generated and emailed to the person responsible for completing it.

TIP: If the button is inactive, check that the Provision Agreement is in place.

You will note that the Data Provider GHA has been selected, this means that the Constraint attached to the Provision Agreement will be used. The Frequency selected is Annual, so to to report for the whole of 2019, that year is placed on both the From and To dates. Click Generate when done.

The resulting Workbook can be opened from the downloaded file.

In this example, the recipient would read the Instruction worksheet (which has been named READ ME) fill in the observational data and then save and return the completed spreadsheet to the sender.

As stated above, this is a very simple example. You can read more about Reporting Templates in this article.