Publication Table
Contents
Publication Tables
Introduced to Fusion Registry Enterprise v11.0.0.17
Overview
A Publication Table is a curated table of data, where the values in the table rows, columns and table body cells can be individually specified. This enables a single table to pull data from multiple sources and piece the data together in a way which is not possible when dealing with standard cubes of data, whose table model is typically based on a Pivot model, with columns and rows built around the Dimension values to which the column or row is assigned. A Publication Table breaks out of this restrictive model by allowing cells of data to reference observations which do not need to share any relationship, in terms of dimensionality or even data source, with adjacent cells,.
Data in a Publication Table may be sourced from a single Dataflow or it may be sourced from multiple Dataflows enabling cross comparison of data.
Publication Tables can be static (every cell is a defined Observation) or dynamic, where a cell includes variables Dimensions in the Observation Key, which can be provided at table build time.
Time arithmetic and frequency conversion is supported, enabling a single table to pull data from different time periods and frequencies which are all derived from the base period on which the table is built.
Publication Tables support calculations, where each calculation can take input from one or more Observations over one or more Dataflows.
Variable values also play a big role in Publication Tables, and includes standard variable placeholders – as well as advanced conditional variables, whose list of variable values change based on the value of another Dimension for which the table is built.
Metadata vs Materialised
A Publication Table is stored in Fusion Registry the same way any other piece of structural metadata is stored. In this way it has an owner (Agency), and identity (ID) and a version. The change history is tracked using the same services, and the table definition can be queried using the same SDMX web services for Structural Metadata.
The Publication Table metadata contains information about which Dataflows are used by the Table, which additional variables are being imported, and a definition of the table structure, and which Observations to import by referencing them by their unique Observation Key.
A Publication Table definition can be materialised into a table of Data by from the Publication Table Web Service, which combines the information of the Table Identity, and Variable Values to rewrite the Observation Key into actual observation values. If building a web User Interface, then response from this service can be a packet of JSON, which describes the layout and content of the final Table in a way which is quick and simple to render for the User to see. The process of materialising the table is known as ‘build time’.
Additional web services are provided to enable clients to ask questions about the table such as ‘what are the earliest and latest time periods for which there is data for this table’ or ‘what possible variable values are there for this table’. This enables user interfaces to provide a user with the option to change the variables ‘on the fly’ ensuing they are able to build a table which is known to have data.
Publication Table Cell Content
Overview
A Publication Table is made up of a Table Head Rows and Table Body Rows.
The Table Head is made up of 1 or more table rows. Each Table Head cell may span 1 or more columns and 1 or more rows. The Table Head content is made up of free text and / or variable placeholders whose values are resolved when the table is built. Table Head Cells can be left empty.
The Table Body is made up of 1 or more Table Rows. The Table Rows provide the Publication Table with its data, by referencing Observations from the Dataflow.
At table build time all variables are resolved, and the corresponding data are retrieved from the data stores which hold the data.
Table Rows
Overview
A Table Row consist of a Table Row heading, this can be a mix of free text and variable text. The Table Row Heading is always the first Table Row Cell, and can not span multiple columns or rows. A Table Row can not have more then one heading. The remaining Table Row Cells are the Observation Cells, which may contain free text and variable text but will typically contain a reference to an Observation by an Obsevation Keys or a Calculation which can combine multiple Obsevation Keys.
A Table Row which results in no Observation data will not be included in materialised table at build time, unless the Table Row is a grouping row (see Row Levels for details on grouping rows).
Row Levels
The Table Row Heading can be used to define a row level. The level indicates to the client responsible for rendering the table the row's position in a hierarchy. Row levels are depicted using one or more leading chevrons ‘>‘ before the text. The number of hyphens indicates the level, with one leading hypen indicating level 1, two indicating level 2, and so on. It is possible to skip levels if required, i.e. Row 1 is level 1 with Row 2 assigned to level 3, skipping out level 2 entirely.
An example of row headings using levels is as follows:
Countries > Europe >> France >> Germany > Asia >> China
Levels can be an important way to group data. A Table Row which acts as a parent row in a hierarchy, can be used as grouping mechanism for the rows that follow. This enables Parent Rows to be included in the output table, even if the Table Row contains no Observation data. The default behaviour for a Table Row with no data is to exclude it from the built table.
Looping Rows
Instead of explicitly defining a row for each Dimension member, it is possible to define a row as a loop (to iterate over all, or a subset of Dimension members). For example, if a publication table contains a Dimension for countries with an ID of REF_AREA then a loop can be set up to loop through all REF_AREA values, or a subset of the values - for which there is data. If the values are backed by a Codelist which has a hierarchy, the hierarchy can be preserved in the final table. It is also possible to nest a loop within another loop, for example for each REF_AREA output each INDICATOR.
Simple Loop
The loop is defined by giving the table row a label which starts with a syntax of :=LOOP([args]), like in the following example
:=LOOP(REF_AREA)
This loop definition will loop through all the REF_AREA values, building a row for each value. The Observation keys for the row can refer to the loop variable using a $(DIM_ID) placeholder in the Observation Key, for example:
Employed Student :=LOOP(REF_AREA) A:$(REF_AREA):EMP A:$(REF_AREA):STU
The output table would look like the following:
Employed Student United Kingdom 20 12 France 22 11 Germany 25 15
Nested Loop
If a loop row is followed by a row (or rows) which is an extra level deep, the child row(s) will be included for each loop, for example
Employed Student > :=LOOP(REF_AREA) >> Total A:$(REF_AREA):T:EMP A:$(REF_AREA):T:STU >>> Male A:$(REF_AREA):M:EMP A:$(REF_AREA):M:STU >>> Female A:$(REF_AREA):F:EMP A:$(REF_AREA):F:STU
Because the Total row is a child row of the LOOP (denoted by the number of leading '>') and Male/Female are children of Total - for each REF_AREA there will be a Total, Male and Female, outputting a table like the following:
Employed Student
United Kingdom
Total
Male 5 1
Female 4 2
France
Total
Male 6 2
Female 7 3
It is also possible to define a subloop as a loop. The previous example can be rewritten as:
Employed Student > :=LOOP(REF_AREA) >> :=LOOP(SEX) A:$(REF_AREA):$(SEX):EMP A:$(REF_AREA):$(SEX):STU
There is no limit to the number of subloops that can be defined.
Loop Hierarchical Values
If the underlying Codelist has a hierarchy, a subsequent argument can be provided to ask for the hierarchy to be preserved
Countries > :=LOOP(REF_AREA, true)
Note: A hierarchy will keep rows with no data if they are acting as a parent node for rows that do have data (e.g. there may not be data for Europe, but the row exists because there are child rows of France and Germany with data).
Nested loops can also preserve hierarchies for example:
:=LOOP(REF_AREA, true) >:=LOOP(SEX, true)
With an example output (assuming no data exists for the hierarical parents (Continent, Europe)
Continent
Europe
France
Total (M+F)
Male
Female
Germany
Total (M+F)
Male
Female
Order Loop Rows
It is possible to order the labels by their natural order (that of the Codelist), by id or by label, using the numbers 1, 2, or 3 respectively. The following outputs the rows ordered by label.
Countries > :=LOOP(REF_AREA, true, 3)
Fixed Loop List
It is also possible to define a subset of the values to loop over. For example, if REF_AREA has data for 100 countries, but only 3 are required in the table. In this scenario, the natural order is that of the provided list, not of the underlying Codelist.
Countries > :=LOOP(REF_AREA, true, 3, UK, FR, DE)
Loop over External Variable
Instead of looping the values of the Dimension's Codelist, it is possible to loop another Codelist - the purpose for this is to support alternative labels (if the labels for the publication table need to be different from those of the Dimension members) - or a subset (as an alternative to passing the subset of values into the LOOP argument), or to define a hierarchy (if the Dimension's Codelist does not have a hierarchy, or an alternative is required).
In order to loop over an external Variable, the Publication Table must be linked to an external Codelist/Valuelist against an Alias. For example EXT=My Country Codelist with Hierarchy. The corresponding loop then loops the Codes in the external list, and stores these the corresponding Dimension ID variable, for example:
Countries > REF_AREA:=LOOP(EXT)
Noting that the loop is over the variable EXT which resolves to an external Codelist. The loop is assigned to the REF_AREA Dimension.
Anchor Rows
The default behaviour is for a row to be omitted if the row has no data and it has no child rows with data. However, if the desired result is to always output the row, then this can be achieved by using the [anchor] tag in the row header label.
Consider the following Publication Table definition:
Male Female Employed A:M:EMP A:F:EMP Student A:M:STU A:F:STU Retired A:M:RET A:F:RET
If there are no data for Student, then the materialised table would look like this
Male Female Employed 12 13 Retired 3 5
Anchor Single Row [anchor]
To anchor a single row, add the [anchor] tag at the start of the row
Male Female Employed A:M:EMP A:F:EMP [anchor]Student A:M:STU A:F:STU Retired A:M:RET A:F:RET
Then the output table would preserve the 'Student' row
Male Female Employed 12 13 Student Retired 3 5
This rule can be coupled with formatting for missing observations, then the table could be enhanced to look something like this
Male Female Employed 12 13 Student - - Retired 3 5
Anchor Child Rows [anchor+]
For a row with a hierarchy, an [anchor+] tag can be placed on a parent node to anchor the parent and all of its children, for example:
Continent
[anchor+]>Europe
>>France
>>>Total (M+F)
>>>>Male
>>>>Female
>>Germany
>>>Total (M+F)
>>>>Male
>>>>Female
The above rule would always output Europe and all of its descendants (France, Total, Male, Female, Germany).
Anchor Siblings and Child Rows [anchor*]
The [anchor*] tag can be used as a way to anchor all following siblings and child nodes, this can be a useful mechanism to anchor all rows for the whole table by placing the anchor[*] on the first row of the table.
Anchor Loops
If a loop is preceeded by an [anchor] tag then the system will have the following behaviour:
- If an external Codelist variable is being looped, all codes will be ouput regardless of what data is stored in the system
- If a Dimension is being looped, all codes in the Codelist used by the Dimension will be output regardless of what data is stored in the system
- If a Dimension is being looped and it is not coded (i.e it is free text or numerical or other) then the [anchor] has no effect, as the looped values are based on what data exists
Observation Key
Overview
The purpose of the Observation Key is to reference a Observation whose value will be resolved at table build time. The Observation Key can explicitly resolve to 1 observation value, if all parts of the Key are provided. However, it is possible to make parts of the key varaibles, allowing the values to be set at table build time.
An Observation Key is made up of 4 parts:
- Dataflow Reference
- Series Reference
- Time Period Reference
- Measure Reference
An example Observation Key with all of these parameters in place is:
DF_REG.M:UK:SUR:2008:BIRTHS
The key is broken down into the following parts:
| Observation Key Part | Name | Description | Required |
|---|---|---|---|
| DF_REG | Dataflow Alias | This identifies the Dataflow which has the data. The identification uses the alias, which is assiged to the Dataflow in the Publication Table definition. The Dataflow Alias is only required if the Publication Table references more then one Dataflow. If the Publication Table references more then one Dataflow, it can assign a Dataflow to a Default Alias, which means that it is required as part of the Observation Key. | No (if only 1 Dataflow or a default alias is set) |
| M:UK:SUR | Series Key | This identifies the Series of Data for the Dataflow, by providing a single value for each of the Dimension Members of the Dataflow, separated by a ‘:’ character. In this example the Dataflow has 3 Dimensions (excluding time), the value for the first is 'M', the second 'UK' and the last 'SUR'. As the Dataflow in this example has a Time Dimension this Series Key will uniquely identify a Series of Observations where each Observation refers to a unique Time Period | Yes (but can omit values) |
| 2008 | Time Period | This identifies the Time Period in the Series of Data identified by the previous section. The Combination of the Series and Time Period for a Time Series Dataflow will uniquely identify the Observation | No. Omit to make it variable for Time Series Data (defaults to last period). Not required if there is no Time Dimension for the DSD. |
| BIRTHS | Measure Dimension | If the Dataflow uses a Data Structure with multiple Measures, then the Measure ID must be included as the last part of the Observation Key. In this example the Dataflow has multiple measures (BIRTHS/DEATHS/MARRIAGES) so the Observation Key needs to include this value. | When there is only 1 measure it is not required, when there are multiple measures, it is required. |
Variable Dimension Members
It is possible to omit Dimension Values from the Observation Key, when this is the case that Dimension will become a variable – whose value can be set at table build time. For example, the second Dimension value of ‘UK’, from the previous example, can be omitted like this:
M::SUR:2008
The Publication Table can now be built for any Country by passing in the variable at table build time - i.e. UK, FR, DE. By passing in different variable values, the Observation Key will change, and as such it will reference a different Series in the Dataset.
When an Observation Key has a variable Dimension member, the ID of the Dimension can be referenced in text cells to create variable headings. For example, if the ID of the 2nd Dimension in the DSD is REF_AREA, the Table Column could be given a title of $(REF_AREA) which would resolve to 'United Kingdom', 'France', or 'Germany' at build time depending on which varaible value is used to build the table. #See Dimension Variables.
Variable Time Period
For Time Series dataset, the Time Dimension can form part of the Observation Key, for example M:UK:SUR:2008. The 2008 part of the key refers to the year 2008.
It is possible to omit the value for Time Period in the Observation Key. Omitting Time Period will make the Time value a variable which can be provided at table build time, defaulting to the last period for which data exists if no value is supplied.
Extending the above example to omit the Time value results in an Observation Key whose Country and Time values can be provided to the web service at table build time.
M::SUR
Note: The above key is exactly the same as using the variable Time Period value 'P' in the Time Dimension, as follows:
M::SUR:P
Additional functions can be performed on the Time Dimension, such as moving forwards or backwards from the table base period, #see Time Variables for more details.
Free Text Observation Keys
Whilst typically a cell in the body of the Publication Table would resolve to an Observation value, it is possible to use it for free text. To include free text cells, start the cell value with a leading apostrophe character, for example:
'Cell Text
Note: An Observation Key can not contain a mix of free text as well as an Observation value
Calculated Observation Cells
An Observation Cell may contain calculated values, which can consist of a mix of simple algebra (+, -, *, /) and a reference to one or more Observations which may come from multiple Dataflows.
To define a Calculated Observation the Cell the following rules apply:
- The Observation Key must start with :=
- Any Observation Keys must appear within enclosing sequare brackets '[' and ']'
- An Observation key may embed calculations on a single Dimension by using parenthesis '(' and ')' in the key part
- The same rules for variable Dimensions and Time Period apply when resolving the Obseration Keys used in the calculation
Examples are shown in the table below
| Example Syntax | Description |
|---|---|
| :=[A:UK:EMP]+10 | Add 10 to the observation value retrieved from the key A:UK:EMP |
| :=[A:(UK+FR+DE):EMP] | Add the series A:UK:EMP + A:FR:EMP + A:DE:EMP |
| :=[A:UK:EMP]+[A:FR:EMP]+[A:DE:EMP] | Add the series A:UK:EMP + A:FR:EMP + A:DE:EMP |
| :=[A:(UK+FR):EMP]/[A:(UK+FR):UMP] | Adds the series A:UK:EMP + A:FR:EMP and divides by the result of A:UK:UMP + A:FR:UMP |
| :=((100/[A:UK:EMP:(M+F):P-1])*[A:UK:EMP:(M+F)])-100 | Demonstrates the ability to use parenthesis outside of the Observation Keys to control the order of mathematical operations |
| :=[POP.M:UK:(M+F)] / [LAB.M:UK:EMP] | Demonstrates the ability to calculate data from two Dataflows (defined by alias POP and LAB) |
| :=[M:UK:EMP:M(P)] + [A:UK:EMP:A(P)] | Demonstrates the ability to calculate data where the period is converted from the base period |
Percentage Of
The PCTOF (Percentage Of) function can be used to calculate 'x' as a percentage of 'y' following the syntax:
PCTOF(x,y)
This is reolved to the following mathmatical expression: (100/y) * x
x and y must either be a numerical value, and observation key, or an expression using a combination of the two. For example
:=PCTOF([A:M:UK:P],[A:TOT:UK:P])
The Observation Key follows the same standard rules with respect to varaibles and inline calculations - the following example shows an Observation Key where Male + Female values are added with the result being used in the Percentage Of function
:=PCTOF([A:M:UK],[A:(M+F):UK])
A more complex example shows calculations around the PCTOF function:
:=(10 + PCTOF([A:M:UK:A(P)-1],[A:(M+F):UK:A(P)])) / 2
Footnotes
TODO
Formatting Cells
Overview
It is possible to provide rules for some cells on how the data are output, this is set up in the table definition by referencing mapping rules maintained in SDMX Representation Maps
Formatting Missing Observations
Observation Values can be missing for 2 reasons:
- There is no observation in the database for the given Observation Key
- There is an Observation in the database but the measure value is null or an empty string for the
In both cases it is possible to define an output value using formatting rules. For the first case, the observation does not exist, it is possible to define a default output value, for example ‘-‘. For the second case, where the observation does exist, it is possible to define a set of rules based on another Component of the Observation, for example the Observation output may be related to the OBS_STATUS Attribute.
When the formatted value is based on a Component, a set of rules must be defined which state ‘when component=x output y’, where x may be an fixed value or a pattern to match (using a regular expression). The rules for mapping component values are defined in a Representation Map, and the Publication Table simply provides the connection to the Representation Map to use. This enables reuse of the mapping rules across multiple Publication Tables. The Publication Table must define two properties in order to support Component Mapping rules:
- The Component to obtain the value from i.e OBS_STATUS
- A link to the Representation Map, which holds the mapping rules. Note, the Representation Map is expected to have 1 source mapping, this could be of any type (string, codelist, other)
An example Reprsentation Map for OBS_STATUS is shown below:
| OBS_STATUS | Is Reg Ex | Missing Value | Description |
|---|---|---|---|
| M | false | mv | When OBS_STATUS is M output mv |
| L | false | np | When OBS_STATUS is L output np |
| .* | true | - | When OBS_STATUS is anything else output - |
Frequency Formatting
Time Variables always resolve themselves to an SDMX Date Format when forming the Observation Key, this ensures the correct observation value is returned from the data store. When a time variable is used as a label, in a table heading for example, the same formatting will be applied by default but a custom format can be provided. Custom display formats for a Time Period enable a time period such as 2007-Q1 to be resolved to custom text string, for example '31 Mar 2007'.
Custom Time formats rules are defined by providing an association between an SDMX Frequency ID (#see SDMX Date Format) and the output format template. The output format template is a string that defines which components of time are output an in which order and style. For example to output a date as 'March 2001' the format template would look like this 'MMMM yyyy' to indicate a full month name (MMMM) and a 4 character year (yyyy). format rules make use of the Simple Date Format which is used by Java as such all of the documentation on Simple Date Format applies.
Frequency Format Mapping is not defined as part of a Publication Table, instead the rules are defined in a Representation Map and the Publication Table references the Representation Map as the source of its frequency conversion rules. This enables the same set of rules to be used for multiple Publication Tables. The Representation Map must map 1 source (Codelist or String) to 1 target (String), it can contain as many mapped frequencies as required. If the Representation Map contains a mapping for a value that is not a valid SDMX Frequency, then it will be ignored.
It is important to note that all time periods resolve to a single point in time, which is the end of the period, for example an Annual period of 2008 resolves to 2008-12-31:T23:59:59. The resolution of frequency to a point in time enables the frequency formatting rules to use all aspects of time, even when converting a low frequency, for example 2008 formatted using the pattern dd MMM yy would resolve to '31 Dec 2008'.
Variables Summary
| Variable Type | Description | Syntax | Example | Example Output |
|---|---|---|---|---|
| Dataflow Name | Displays the name of a Dataflow referenced by the Publication Table in the locale in which the Publication Table is built | $([dataflow alias]) | $(EDU) | World Bank, Education |
| Dataflow Description | Displays the description of a Dataflow referenced by the Publication Table in the locale in which the Publication Table is built | $([dataflow alias].desc) | $(EDU.desc) | World Bank, Education |
| Dataflow Id | Displays the ID of a Dataflow referenced by the Publication Table | $([dataflow alias].id) | $(EDU.id) | DF_EDU |
| Variable Dimension Value | Displays the name of the variable used when an Observation key has a wildcard member | $([dimension id]) | $(REF_AREA) | United Kingdom |
| Specific Component Value | Displays the name of the Component Value | $([component id].[component value]) | $(OBS_STATUS.C) | Confidential |
| Component Name | Displays the name of the DSD Component (this is the name of the Concept used by the Component) in the locale in which the Publication Table is built | $([component id].name) | $(OBS_STATUS) | Confidentiality Status |
| Enumeration Value | Displays the name of a Code in a Codelist or Value in a Valuelist | $([list alias].[list item ID] | $(SEX.M) | Male |
| Conditional Variable | Displays the variable value, which is conditional on the value of a variable Dimension | $([conditional variable id][variable id]) | $(BANKNOTES[NOTE1]) | £5 |
| Time | Displays the base time period for which the table is built, or an offset from this time period / cast to a different frequency | see Time Functions | $(P) | 2008 |
Table Variables
Overview
Variables are a very important part of Publication Tables. They can be used to make the content of the table dynamic, setting and changing the value at run time to change the table content. Variables can be used in Observation Keys, but the values of variables can also be used anywhere where there is text, for example the table heading, subheading, column or row headings, footnotes, and Observation Cells with free text.
This section describes the various variables that exist, along with the syntax of how to use them.
Dataflow Variables
Publication Tables define data cells across one or more Dataflows. Each ‘imported’ Dataflow is given an alias, which can be used to refer to the Dataflow in the Observation Key.
It is possible to refer to the Dataflow's Name, Description, and Id using the following variable syntax.
Dataflow Description (dataflow alias=EXR)
$(EXR).desc
Dataflow Name (dataflow alias=EXR)
$(EXR).name
Dataflow ID (dataflow alias=EXR)
$(EXR).id
Dimension Variables
When an Observation Key has variable Dimension Values, the table will be built by replacing this variable with a value to complete the Observation Key. The variable value is either passed explicitly to the web service, or defaulted by the system if not provided. The Dimension Variable can be referened in the table using the syntax $([dimension id]), for example:
$(INDICATOR)
This would resolve to the name of the Indicator value used in the table, for example EMP may resolve to the label Employment. The label for the Dimension Value is obtained from the Codelist or VAluelist which the Dimension uses. If the Dimension does not use a list (i.e. it is free text or numerical) then the Dimension value is simply used in place of the variable placeholder.
Dependent Dimension Variable
It is possible that the value for one Dimension is dependent on the value of another Dimension, for example CURRENCY may be dependent on the COUNTRY and COUNTRY is a variable in the Observation Key - it is possible to specify that the value to use for the CURRENCY Dimension can be derived using a lookup table. The lookup table in this instance is maintained as an SDMX Representation Map which is used to map COUNTRY to CURRENCY. The Publication Table specifies that the two Dimensions are related and provides a reference to the Representation Map to use which has the rules.
When working with a dependent variable, both the dependent variable and the Dimension on which it depends should be omitted from the Observation Key - for example consider a full key of:
A:UK:GBP:2007
Where UK is the COUNTRY and GBP is the Currency, to make the COUNTRY a variable Dimension, it should be omitted from the key, as follows:
A::GBP:2007
To then make the CURRENCY a variable it is also omitted , as follows:
A:::2007
The Publication Table is given the rule that CURRENCY depends on COUNTRY and should use the COUNTRY_2_CURRENCY Representation Map, which in this example looks like the following:
| COUNTRY | Is Reg Ex | CURRENCY |
|---|---|---|
| UK | false | GBP |
| US | false | USD |
| .* | true | EUR |
When the Publication Table is materialised the web service only requires the COUNTRY information, and it will complete the Observation key using the representation map lookup table.
Component Values
If a fixed Dimension value is used in the table, or if there is a Series/Observation Attribute whose value is required in the table, then these can be explicitly referenced using the following syntax $([component id].[component value]), for example:
$(OBS_STATUS.M) resolves to Missing
The Component Name can be referenced using the following syntax $([component id].name), for example:
$(OBS_STATUS.name) resolves to Observation Status
Coded Variables
It is possible to import additional Codelists and/or Valuelists into the Publication Table, for the purpose of using the Names or enumerated items as variables. When a Codelist/Valuelist is imported, it is given an alias, and the combination of the Alias and Item ID resolves to the Name of the Item. The syntax is $([codelist alias].[item id]) for example:
$(CURRENCY.$)
Resolving to the text Dollar in English and דוֹלָר if the Publication Table were built in Hebrew (and a Hebrew name for the corresponding Value List item).
Conditional Variables
A Conditional Variable is one whose value is conditional on the value of a Dimension Variable.
An example use case is Banknotes, where the values for NOTE1, NOTE2, or NOTE3 is dependant on the Country Dimension. A Conditional Variable could be used to set a values for each variable (NOTE1, NOTE2, NOTE3) based on the value used for the current Country Dimension i.e. US NOTE1=$5, UK NOTE1=£5.
The syntax of referring to a Conditional Variable is $([conditional variable id][variable id]), for example
$(BANKNOTE[NOTE1])
Time Variables
When the data is time series (i.e. the DSD has a Time Dimension), the Observation Key can include the time period, for example:
A:UK:EMP:2008
Where 2008 is the Observation Time Period to use. The format of the Time Period must always be the same format as the frequency for which the data are reported. For example if the data is annual, and an observation is reported for 2008, then the observation key must use 2008, not another frequency such as 2008-12-31. It is typical in a DSD for the Frequency of the Series to be the first Dimension, in all the examples on this page this convention is applied, and as such the above key A:UK:EMP refers to Annual data, depicted by the initial dimension value 'A'.
If the key omits the time period, then the time period becomes a variable which can be set at table build time, if not set it defaults to the last period for which there is data.
A:UK:EMP
When a Publication Table has a variable time period, the resolved variable time value for this varaible is known as the base period. There can only be 1 base period for a Publication Table, however other Observation Keys (and corresponding headings) can perform time arithmetic from the base period for which the table is built.
The supported time functions include walking forwards and backwards in increments of the given frequency, converting to another frequency of data, and a combination of the two (convert and move, move and convert, move convert and move again). Ths supported functions are shown in the table below.
| Syntax | Description | Example Observation Key | Example Resolved Key |
|---|---|---|---|
| P | The base period (default if not provided) | A:UK:EMP:P | A:UK:EMP:2008 (base period=2008) |
| P-n | Base Period minus 'n' Periods where n is a positive Integer | A:UK:EMP:P-1 | A:UK:EMP:2007 (base period=2008) |
| P+n | Base Period plus 'n' Periods where n is a positive Integer | A:UK:EMP:P+1 | A:UK:EMP:2008-Q2 (base period=2008-Q1) |
| A(P) | Base Period converted to Annual | A:UK:EMP:A(P) | A:UK:EMP:2008 (base period=2008-Q2) |
| S(P) | Base Period converted to Bi-Annual (Semester) | S:UK:EMP:S(P) | S:UK:EMP:2008-S2 (base period=2008-Q3) |
| T(P) | Base Period converted to Tri-Annual | T:UK:EMP:T(P) | T:UK:EMP:2008-T3 (base period=2008) |
| Q(P) | Base Period converted to Quarterly | Q:UK:EMP:Q(P) | Q:UK:EMP:2008-Q4 (base period=2008) |
| M(P) | Base Period converted to Monthly | M:UK:EMP:M(P) | M:UK:EMP:2008-03 (base period=2008-Q1) |
| W(P) | Base Period converted to Weekly | W:UK:EMP:W(P) | W:UK:EMP:2008-04 (base period=2008-01-01) |
| D(P) | Base Period converted to Daily | D:UK:EMP:D(P) | D:UK:EMP:2008-12-31 (base period=2008) |
| H(P) | Base Period converted to Hourly | H:UK:EMP:D(P) | H:UK:EMP:2008-12-31T12 (base period=2008-02-02T12:00:03) |
| A(P-n) | Base Period minus (or plus) 'n' periods, converted to another frequency 'A' | A:UK:EMP:A(P-4) | A:UK:EMP:2007 (base period=2008-Q4) |
| A(P)-n | Base Period, converted to another frequency 'A' minus (or plus) n' periods | A:UK:EMP:A(P)-4 | A:UK:EMP:2004 (base period=2008-Q4) |
| A(P-n1)-n2 | Base Period, minus (or plus) 'n1' periods, converted to another frequency 'A' minus (or plus) 'n2' periods | A:UK:EMP:A(P-4)-4 | A:UK:EMP:2003 (base period=2008-Q4) |
| FA(P) | Subtracts 1 Period from the Converted Period IF the resolved Convert From end of period does not match the same point in time as the Convert To period.
Examples:
This works for ALL frequencies where the convert to frequency is lower then the base Period P, for example Quarter converted to Annual. |
A:UK:EMP:FA(P) | A:UK:EMP:2007 (base period=2008-Q3) |
Notes on Time Period
When converting from a lower frequency to a higher frequency (Weekly to Daily for example), the cast date always resolves to the end of the Period.
It is also important to note that as the Observation Keys in these examples all have Frequency as the first Dimension, when converting to another frequency (Quarterly to Annual for example) the Observation Key changes the value for the Frequency Dimension, ensuring the correct series of data is retrieved.
Time Period variable in free text
When refering to the Time Period as a variable in free text, for example in the table heading or a column heading, the variable is placed inside the variable syntax $(). For example $(P) would resolve to base period, and $(A(P)) would resolve to 'base period converted to annual frequency'. An example usage is shown below:
Table 1A showing data for $(P) to $(P+3)
