Skip to content

Reporting Template

A Reporting Template describes an Excel workbook used to collect data. Each worksheet is built from a dataflow, with chosen dimensions on the rows and columns, and options for attributes, colours, hierarchies and display. A template can also include an instruction sheet. Reporting Templates are a Fusion extension to SDMX. Documents start ref: "ReportingTemplate=AGENCY:ID(1.0)".

Simple form

The document starts with the common header. The body holds the worksheets, each written as an object keyed by its worksheet id. There is no wrapping field such as worksheets: the worksheets sit directly in the root object. A template has at least one worksheet.

format: "fusion-yaml(1.0.0)"
ref: "ReportingTemplate=ACY:RT(1.0.0)"
name: "TEST"
WS1:
  dataflow: "ACY:DF1(1.0.0)"
  rows:
  - "REF_AREA"
  - "INDICATOR"
  cols: "TIME_PERIOD"
Source: reportingtemplate/testMinimal.yaml

  • WS1: a worksheet, keyed by worksheet id. Worksheets are written in template order.
  • dataflow: short URN of the dataflow the worksheet is built from (references).
  • rows: ids of the dimensions placed on the rows, in order.
  • cols: ids of the dimensions placed on the columns, in order.

rows and cols follow the value or array rule: one dimension is written as a string (see cols above), several as an array, in placement order.

Complete example

The example below shows every optional field except slice. It is assembled from the golden files testWithInstructionSheet, testWithDisplayOptions, testWithComponentPlacements, testWithConditionalAttributes, testWithColourAttribute and testWithHierarchies, with long lists trimmed.

format: "fusion-yaml(1.0.0)"
ref: "ReportingTemplate=ACY:RT_FULL(1.0.0)"
name: "TEST"
instructions:
  title: "How to complete this template"
  name: "Instructions"
  instruction:
  - colHeight: 2
    colWidth: 10
    content: "Complete **all** highlighted cells"
  - colHeight: 1
    colWidth: 20
    content: "Contact: reporting@acy.org"
formulaCheck: true
WS1:
  dataflow: "ACY:DF1(1.0.0)"
  rows:
  - "REF_AREA"
  - "INDICATOR"
  cols: "TIME_PERIOD"
  colMaxWidth: 40
  decimalPlaces: 3
  dimensions:
    variable: "FREQ"
  attributes:
    fixed:
      DECIMALS: "2"
      UNIT_MULT: "0"
    variable:
    - "OBS_CONF"
    - "OBS_STATUS"
    worksheet: "COMMENT"
    separate:
    - "COLLECTION"
    - "TITLE"
    conditional:
      OBS_CONF:
        output:
        - value: "C"
          condition: "ap"
          detail: "COMMENT"
      OBS_STATUS:
        output:
        - value: "A"
          condition: "dv"
        - value: "E"
          condition: "ov"
          detail: ">.100"
    colourAttribute:
      attribute: "OBS_STATUS"
      default: "#FFFFFF"
      colours:
        A: "#FFFFFF"
        E: "#C2C2C2"
        P: "#FFC000"
      seriesColours:
      - colour: "#C2C2C2"
        series:
        - "A"
        - "IND1"
        - "UK"
  implicitHierarchies: "TIME_PERIOD"
  textDisplay:
    INDICATOR: "BOTH"
    REF_AREA: "NAME"
  hierarchy:
    INDICATOR: "Hierarchy=ACY:H_INDICATOR(2.1.0)"
    REF_AREA: "Hierarchy=ACY:H_REF_AREA(1.0.0)"

Notes on the fields:

  • Instructions. instructions describes the instruction sheet: an optional title and name, and an instruction array where each entry holds colHeight and colWidth (numbers) and content.
  • Formula check. formulaCheck: true adds a formula checking summary to the workbook. It is only written when true.
  • Display options. colMaxWidth and decimalPlaces are numbers. slice (not shown) is a dimension id: when set, the worksheet is repeated for each value of that dimension.
  • Dimensions. dimensions holds variable (dimensions whose values the reporter can choose) and excluded (dimensions left out of the worksheet). Each is a sorted value or array.
  • Attributes. attributes groups the attribute placements:
    • fixed: a map from attribute id to its fixed value, sorted by attribute id.
    • variable, worksheet, separate: attribute ids reported per observation, once per worksheet, or in a separate area. Each is a sorted value or array.
    • conditional: a map keyed by attribute id, sorted by id. Each entry holds an output array of rules, in rule order, with the attribute value to output, the condition code and an optional detail. Condition codes are dv (default value), op (observation present), om (observation missing), ov (observation value), on (observation NaN), ap (attribute present), an (attribute not present) and av (attribute value).
    • colourAttribute: the attribute whose value colours the cells. attribute is the attribute id, default the default colour, colours a map from attribute value to colour (sorted by attribute value), and seriesColours an array of series that are given a fixed colour. Each entry has a colour and the series key values, sorted alphabetically as a value or array (the series is a set of key values, not a positional key). Entries are sorted by colour, then by series.
  • Hierarchies. implicitHierarchies lists dimension ids (sorted value or array). hierarchy maps a dimension id to a Hierarchy, sorted by dimension id. The map key is a dimension id, which does not name the referenced type, so the value is a canonical value, e.g. "Hierarchy=ACY:H_REF_AREA(1.0.0)".
  • Text display. textDisplay maps a component id to how it is shown: ID, NAME or BOTH. It is sorted by component id.

Field reference

Body fields, in written order:

Field Value Written when
instructions Instruction sheet object (below) The template has an instruction sheet
formulaCheck true A formula checking summary is included
<WORKSHEET_ID> Worksheet object (below) Once for each worksheet, in template order

instructions object:

Field Value Written when
title String Set
name String Set
instruction Array of objects There are instructions
instruction[].colHeight Number Set
instruction[].colWidth Number Set
instruction[].content String Set

Worksheet object:

Field Value Written when
dataflow Short URN of the dataflow Set
rows Value or array of dimension ids The worksheet has row dimensions
cols Value or array of dimension ids The worksheet has column dimensions
colMaxWidth Number Set
slice Dimension id Set
decimalPlaces Number Set
dimensions.variable Sorted value or array of dimension ids There are variable dimensions
dimensions.excluded Sorted value or array of dimension ids There are excluded dimensions
attributes.fixed Map of attribute id to value, sorted by attribute id There are fixed attributes
attributes.variable Sorted value or array of attribute ids There are variable attributes
attributes.worksheet Sorted value or array of attribute ids There are worksheet attributes
attributes.separate Sorted value or array of attribute ids There are separate attributes
attributes.conditional Map of attribute id to {output: [...]}, sorted by attribute id There are conditional attributes
attributes.conditional.<ID>.output[].value String Always
attributes.conditional.<ID>.output[].condition Condition code Always
attributes.conditional.<ID>.output[].detail String Set
attributes.colourAttribute.attribute Attribute id Always, when colourAttribute is written
attributes.colourAttribute.default Colour Set
attributes.colourAttribute.colours Map of attribute value to colour, sorted by attribute value Always (a colour map is required)
attributes.colourAttribute.seriesColours[].colour Colour Always
attributes.colourAttribute.seriesColours[].series Sorted value or array of key values Always
implicitHierarchies Sorted value or array of dimension ids Set
textDisplay Map of component id to ID, NAME or BOTH, sorted by component id Set
hierarchy Map of dimension id to Hierarchy canonical value (Hierarchy=AGENCY:ID(VERSION)), sorted by dimension id Set

dimensions is written when it has at least one entry. attributes is written when there is at least one fixed, variable, worksheet, separate or conditional attribute, or a colour attribute.

Reading notes

  • Worksheets share the root object with the header and with instructions and formulaCheck. Any other field whose value is an object is read as a worksheet, so a worksheet whose id is one of those field names cannot be read.
  • The hierarchy values are read as canonical values; the class comes from the Hierarchy= prefix.