Unit 2: Web Services and Reporting Templates

This unit explores how the FMR REST API assists in generating, managing, and securing reporting templates. It covers how templates can be created, modified, and retrieved programmatically, as well as how access rights and worksheet protection are managed.

You'll also learn about security considerations, including role-based access control and password protection, and how FMR ensures data integrity while preventing unauthorised modifications.

Introduction to web services for reporting templates

FMR offers a set of RESTful web services that enable users to create, list, and manage reporting templates programmatically. These services support:

• List Reporting Templates
• Generating an XLSX workbook from a Reporting Template, without any data
• Optionally, populating an XLSX workbook with existing data
• Validating a completed XLSX workbook against FMRs 9 validation rules
• Converting an XLSX workbook to another SDMX format
• Retrieving and updating XLSX worksheet passwords

Security considerations: access rights

By default, FMR restricts the generation of XLSX workbooks from reporting templates. Access is controlled based on user roles.

Select each role type for more details.

Admin

Admin

Admin can download workbooks for any Data Provider.

Agency

Agency

Agency can download workbooks for Data Providers maintained by the agency.

Data Provider

Data Provider

Data Provider can download workbooks relevant to them.

Data Consumer & Anonymous

Data Consumer & Anonymous

Data Consumer and Anonymous cannot download any workbooks.

Modifying Settings

Modifying Settings

To modify these default settings, you can enable or disable security restrictions but note that this applies globally. Additionally, it's recommended to set passwords for each agency to ensure that the generated Excel workbooks are protected and to prevent unauthorised modifications. It is via the configured directory service that users are assigned these roles in FMR. Fine-grained assignment of, for example, a specific Data Provider to specific workbooks occurs within FMR.

FMR reporting template security: Directory services and role assignment

Security considerations: worksheet passwords

If your templates include password-protected worksheets, you can retrieve and update these passwords using the following FMR REST API:

• Retrieve Passwords:
  GET ws/secure/settings/reporttemplate/retrievePasswords
• Update Passwords:
  POST ws/secure/settings/reporttemplate/updatePasswords

The request body should be a JSON object with agency IDs as keys and their corresponding passwords as values.

Only authorised users are allowed to change these values.

FMR reporting template security: worksheet passwords

List reporting templates

It is possible to retrieve a list of all reporting templates available to the authenticated user. This is achieved using the following web services endpoint:

GET ws/public/reporttemplate/provider/templates.

Knowledge check

Which of the following actions can be performed using FMR REST API for reporting templates?

Select all that apply and then select Submit.

Generating an XLSX workbook

Once the reporting template is defined and configured, data collectors or authorised Data Providers can generate an XLSX Workbook using a web service.

The XLSX Workbook is tailored to the specific Data Provider, containing only the worksheets and data cells relevant to them. Therefore, it is necessary to specify the Data Provider as part of the request to create the XLSX Workbook.

To create an XLSX Workbook via the FMR REST API, use the following endpoint:

GET
ws/public/reporttemplate/provider/create/​{agencyId}/​{templateId}/​{version}/​{providerAgencyId}/​{providerId}

Replace {agencyId}, {templateId}, {version}, {providerAgencyId}, and {providerId} with the appropriate identifiers.

How to generate an XLSX workbook

Populating an XLSX workbook with data

Now, let’s explore the process of populating the XLSX workbook with data and address some common questions.

Select each question to reveal the answer.

Validating and transforming an XLSX workbook

Once an XLSX workbook has been updated with data, the file should be validated against FMR's nine validation rules and potentially converted from XLSX to another SDMX format (XML, JSON, or CSV) for processing.

These steps could be completed by the data reporter, the requesting agency, or both, depending on the specific use case.

Please recall that the Accept Header is used by FMR REST API to specify the input format (XLSX) and the desired output format (XML, JSON, or CSV).

The sdmx.io course Validating and Transforming Data fully addresses this process step.

Reading and processing a workbook

The Registry can read any XLSX workbook that it has created from a Report Template.

The registry is able to read the XLSX workbook because the SDMX structural metadata relating the XLSX workbook to SDMX artefacts in the registry is embedded and hidden within the XLSX file.

Even if the report template definition has been changed or deleted since the workbook's initial creation, the XLSX workbook contains the necessary metadata structures to be read back in.

For a workbook to be read back into the Registry, the following conditions must be met:

• It must contain the same Provision Agreement and Dataflow that the worksheet references.
• The DSD must not have any changes to the number or order of Dimensions.

Knowledge check

Which of the following conditions must be met for the Registry to successfully read and process an XLSX workbook?

Select all that apply and then select Submit.