Example of relative periods
A typical report will list the profit and loss
statement for one company but for different periods. For example,
a table report might have accounts as rows and different periods as
columns. The period columns are all based on one current period which
the report consumer will be prompted for. For example, the columns
could be "current period", "previous period", "last year's closing
period" and "last year's corresponding period". In the Report Generator
in IBM® Cognos® Controller, the three periods would
be expressed as "-1", "-N112" and "-12"; all relative to the prompted
period ([currentperiod]).
In the target, these relative period functions are not supported, but the report author can instead use the query functions available in Report Studio and other reporting products.
| Source expression | Report Studio query function (MDX) |
|---|---|
| -1 | lag([currentperiod],1) |
| -12 | lag([currentperiod],12) |
| +1 | lead([currentperiod],1) |
| +12 | lead([currentperiod],12) |
| P001 | firstSibling([currentperiod]) |
| P012 | lastSibling([currentperiod]) |
| N101 | firstChild (lag(parent([currentperiod]),1)) |
| N112 | lastChild (lag(parent([currentperiod]),1)) |
Note: Note that the BI syntax uses navigation in the period hierarchy.