The element references can be filtered by appending a list of filter names to the element range.
For example: ‘sum(d-1, d1:0 (visible, siblings), m1)’ gives the accumulated sum of the first measure of the last column, but includes only the visible elements, and only the elements that are siblings to the current row.
As a shorthand, the abbreviation letter in the table below can be supplied instead of the entire name.
|
Modifier Type |
Short |
Description |
|
Visibility Modifier |
|
|
|
all |
a |
Both visible and hidden elements are included. This is the default visibility scope modifier. |
|
visible |
v |
Only visible elements are included. |
|
hidden |
h |
Only hidden elements are included. |
|
Hierarchy Modifier |
|
|
|
level |
l |
Only elements on the same hierarchy levels in the dimensions as the current element are included. This is the default hierarchy scope modifier. If an integer >= 0 immediately follows this filter name, e.g. ‘l2’, then only the values in level 2 are included, with level zero being the grand total. As an example sum(d1, d-1(l(1)), m1) references the first column (d1) in the last row (d-1) and the last row is to be found in the second level in the hierarchy l(1). Referencing the grand total should be done this way: Note: With two dimensions on the X-axis, the two parameters to the level modifier may be added e.g. sum(d1, d-1(l(1,1)), m1) references the first column (d1) in the last row (d-1) and the last row is to be found in the second level in the first hierarchy and the second level in the second hierarchy l(1,1).
|
|
siblings |
s |
Only elements with the same hierarchy parents in the dimensions as the current element are included. |
|
children |
c |
Only elements that are nested inside the current element are included. Please note that only data references can be used with this filter. Example: This filter can be used with an if-then-else operator for making subtotals that only include the visible elements, when some elements have been hidden by a Visibility Agent: if allcount(0,d1:d-1(c),m1) > 0 then sum(0,d1:d-1(v,c),m1) else sum(0,0,m1) |
|
ragged |
r |
This modifier is useful e.g. for making an accumulation which follows the current expansion of single elements. Like with the ‘level’ filter, an integer >= 0 can follow this filter name, e.g. ‘r0’. In ragged filter, however, this value is somewhat more complicated to explain: Level zero are the leaf elements (i.e. the elements that have no children). Level one are their parents. Level two are the elements that have level 1 children, etc. |
|
leaves |
rl |
Only elements with no children are included. This is the exact same as ‘r0’. |
|
Order Modifier |
|
|
|
sorted |
o |
Relative and data references are indexed according to the current sorting of the grid. This is the default order scope modifier. |
|
unsorted |
u |
Relative and data references are indexed according to the order of the dimension values in the cube. |
|
uncollated |
n |
Relative and data references are indexed according to the uncollated order of the dimension values in the grid. This is useful, e.g. when the top 10 products must be highlighted by a Color Agent in a collated grid. |