Unit 1: Introduction to FMR Reporting Templates for Data Collection

This unit provides a detailed view of what Fusion Metadate Registry (FMR) Reporting Templates are and their use in the statistical production chain.

What is a Reporting Template?

To begin, let’s take a moment to consider the purpose of a Reporting Template and the benefits for both data providers and collectors.

Select each question to see the answer.

A Reporting Template is an SDMX structural model or more specifically, a dataflow, being represented in a tabular format for collecting data.

Fundamentally, it is a metadata driven MS Excel form to enable data providers to easily report data and to allow data collectors to easily validate these data and integrate them into their production workflows.

Reporting Templates create and use MS Excel documents in XLSX format. However, there is no requirement to use MS Excel when creating the Reporting Templates or in the production workflows, other XLSX compatible tools may be used instead.

For example, the data collection agency may use MS Excel or Google Sheets or Open Office and the same is true for data reporting agencies, they may use any XLSX-compatible tool.

Data providers can use everyday tools (MS Excel, Google Sheets, Open Office) to enter their data into the table and submit it back to the collection agency. The data submission, being model based, is easy to validate and import into data production processes. This approach may be an efficiency saving as well as an improvement in data quality. Furthermore, as most organisations are very familiar with providing data via spreadsheets, there are few, if any, challenges or learning curves to successfully adapting to and using reporting templates.

Reporting Templates enable data collectors to design and run efficient data collection processes using MS Excel XLSX documents. This helps improve data quality and allows data providers to report data in a simple and easy manner.

XLSX reports can be processed in an automated way and automatically validated according to nine standard validation tests, leading to process efficiency and data quality gains.

Furthermore, as Reporting Templates are model-based and supported by SDMX FMR web services, implementing Reporting Templates lays the foundation for web-service-based data reporting which is a further leap forward along the data collection maturity curve.

How Reporting Templates work

The Reporting Template form is metadata driven with a definition created in the FMR that describes the contents and layout of one XLSX workbook. The Template also enables all incoming data to be automatically subjected to nine quality-control checks.

Select each feature to learn more.

Metadata

Metadata
A Reporting Template form is metadata driven as the definition of each worksheet takes into account:

Dataset (dataflow).
Which dimensions are placed in the rows.
Which dimensions are placed in the columns.
Whether to output the headings and values as Ids or Names (e.g. FREQ/A or Frequency/Annual).
Whether to exclude fixed dimension values, or place them in the header.
How to report attribute values such as: Observation Confidentiality, Status or Pre-Break values.
Constraints are used to determine the universe of data. Each form can contain a different universe of data depending on which data provider the form is generated for.
Mathematical validation rules can be used to inject formula and checking tables into the worksheet.

XLSX workbook

XLSX workbook
A Reporting Template definition is created in the FMR and describes the contents and layout of one XLSX 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 XLSX file to be used to submit data for multiple datasets.

The FMR provides both a web user interface (UI) and web services to generate the XLSX workbook for a Reporting Template for a specific data provider. The contents of the XLSX 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.

Quality control

Quality control
Reporting Templates enable all incoming data to be automatically subjected to nine quality-control checks before the scarcest and most critical resource, employees, start working with the data:

Syntactically compliant – the XML, JSON, CSV, or XLS is well formed.
Duplicate observations – only one observation value is reported for each time period (Uniqueness).
Mandatory attributes – all mandatory attributes are reported.
Obs Status – OBS_STATUS is consistent with the observation value.
Time period format – consistent with frequency (e.g. Freq=M, TIME_PERIOD format=YYYY-MMM).
Valid calculations – balance equalities defined using Validation Schemes (observation consistency).
Valid constraint – the data is within the universe defined by Data Constraints.
Valid representation – each component complies with the representation defined in the DSD.
Valid structure – the dimensions and attributes are consistent with the DSD.

Reporting Templates tools

Reporting Templates do not use any VBA coding or require any plug-ins, they are simple native XLSX files and do not place any burden on the data respondent other than the provision of data.

Reporting Templates are fully compatible with the following tools (list not exhaustive):

MS Excel – compatible with both Win and MacOS versions.
Open Office – It is possible to use Apache Open Office to complete a data set using the same XLSX spreadsheet generated by FMR.
Google Sheets – to use Google Sheets, simply import the generated XLSX spreadsheet into Google Sheets, complete as required and then download a copy in XLSX format. This can then be uploaded into FMR and converted as normal.

Internationalisation (i15n)

Every label in the XLSX workbook is derived from the metadata in the FMR, this includes concept name, code names, and the Reporting Template name.

As the FMR supports multilingual labels for all metadata, the labels will be written in the language specified when the report is built. If built from the web UI, this will default to the locale of the web browser, or the language chosen from the FMR UI. If built from the web service, the locale can be passed as a parameter, or the standard HTTP Accept-Language header can be used.

When the XLSX workbook is opened, the locale configurations will be used as set by the user. When the workbook is read back in by the FMR, numerical values will be resolve in as follows:

numbers with exponents e.g. 1.2E2 will be resolved as 1200
numbers with decimal separators will always be converted to use the period separator ‘.’ as the decimal point.

XLSX workbook security

It is recommended to protect worksheets to prevent users from making changes to the workbook which prevent it being read back in by the FMR.

The FMR supports per-Agency passwords. For example Agency ‘WB’ can set a password of 1234 for its workbooks, and ESTAT can set a password of ‘abcd’. Passwords can be set in the Admin section of the FMR, or via the FMR web service.

Once protected, a workbook will only allow the user to fill in observation and attribute cells, and the report period on the main worksheet if the workbook is created for a single period.

Web services

As an alternative to the FMR web UI, the Data Reporting Template Web Service can be used to build a Reporting Template.

The Data Validation Web Service can be used to validate the reported values in a Reporting Template XLSX file according to nine quality-control checks.

Design workflow

As Reporting Templates are metadata-driven, the first steps in the design workflow are to Describe the data domain and Define data providers. These activities of creating structural models and capturing them in the FMR have been covered in other training modules and are not repeated here.

The focus of this module is on the following tasks.

Restricting the reporting universe
Defining ancillary metadata

Select the design workflow to enlarge.

What do you know?

Now that you’ve completed our introduction, try this.

Which of the following best describe the characteristics of FMR Reporting Templates?

Select all that apply and then select Submit.

To create a Reporting Template, you must first install MS Excel.

All the data that you enter into a Reporting Template is automatically quality checked.

A Reporting Template may contain multiple XLSX worksheets so that it can be used to submit data for multiple dataflows.

The Reporting Template form is metadata driven using a definition created in the FMR that describes the contents and layout of one XLSX workbook.

It is recommended that you share Reporting Template worksheets so that other users can easily change and update cells and metadata.

That's right.

There is no requirement to use MS Excel when creating the reporting templates or in the production workflows, other XLSX compatible tools may be used instead.

The Reporting Template form is metadata driven with a definition created in the FMR that describes the contents and layout of one XLSX workbook.

It also enables all incoming data to be automatically subjected to nine quality-control checks.

A Reporting Template definition is created in the FMR and describes the contents and layout of one XLSX workbook. A workbook may contain multiple worksheets, enabling one XLSX file to be used to submit data for multiple dataflows.

That's incorrect. The correct answers are options 2, 3 and 4.

There is no requirement to use MS Excel when creating the reporting templates or in the production workflows, other XLSX compatible tools may be used instead.

The Reporting Template form is metadata driven with a definition created in the FMR that describes the contents and layout of one XLSX workbook.

It also enables all incoming data to be automatically subjected to nine quality-control checks.

Not quite. The correct answers are options 2, 3 and 4.

There is no requirement to use MS Excel when creating the reporting templates or in the production workflows, other XLSX compatible tools may be used instead.

The Reporting Template form is metadata driven with a definition created in the FMR that describes the contents and layout of one XLSX workbook.

It also enables all incoming data to be automatically subjected to nine quality-control checks.

Coming next …

In the next unit you’ll learn about the components, or building blocks, of a collection template.