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"
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.
instructionsdescribes the instruction sheet: an optionaltitleandname, and aninstructionarray where each entry holdscolHeightandcolWidth(numbers) andcontent. - Formula check.
formulaCheck: trueadds a formula checking summary to the workbook. It is only written when true. - Display options.
colMaxWidthanddecimalPlacesare numbers.slice(not shown) is a dimension id: when set, the worksheet is repeated for each value of that dimension. - Dimensions.
dimensionsholdsvariable(dimensions whose values the reporter can choose) andexcluded(dimensions left out of the worksheet). Each is a sorted value or array. - Attributes.
attributesgroups 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 anoutputarray of rules, in rule order, with the attributevalueto output, theconditioncode and an optionaldetail. Condition codes aredv(default value),op(observation present),om(observation missing),ov(observation value),on(observation NaN),ap(attribute present),an(attribute not present) andav(attribute value).colourAttribute: the attribute whose value colours the cells.attributeis the attribute id,defaultthe default colour,coloursa map from attribute value to colour (sorted by attribute value), andseriesColoursan array of series that are given a fixed colour. Each entry has acolourand theserieskey 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.
implicitHierarchieslists dimension ids (sorted value or array).hierarchymaps 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.
textDisplaymaps a component id to how it is shown:ID,NAMEorBOTH. 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
instructionsandformulaCheck. 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
hierarchyvalues are read as canonical values; the class comes from theHierarchy=prefix.