HIERARCHIZE
Returned Type
Syntax and Details
HIERARCHIZE(set_expression)
Or:
HIERARCHIZE(set_expression, POST)
Where:
set_expression is an expression that evaluates to a set of members.
If POST is specified, child members precede their parents. This is called post-natural order.
If the set members are in different hierarchies, the order of the hierarchies themselves is indeterminate. That is, if some members are from hierarchy A and the others are from hierarchy B, the A members will be listed consecutively in hierarchical order and the B members will be listed consecutively in hierarchical order, but there is no rule governing whether the A members or the B members are first overall.
Example
Within a hierarchy, the hierarchical order is determined as follows:
The All member of the dimension, if present, is first.
The next member is the first member of the highest level of that hierarchy.
The next member is the first child of that member.
And so on. For example, consider the following hierarchy:
To see the overall hierarchical order of these members, we use the following query, which uses a set consisting of all members of dimension to which these members belong:
SELECT MEASURES.[%COUNT] ON 0, HIERARCHIZE(homed.members) ON 1 FROM patients
Patient Count
1 32006 2,272
2 Juniper 1,155
3 Spruce 1,117
4 32007 1,111
5 Redwood 1,111
6 34577 3,399
7 Cypress 1,150
8 Magnolia 1,111
9 Pine 1,138
10 36711 1,069
11 Centerville 1,069
12 38928 2,149
13 Cedar Falls 1,045
14 Elm Heights 1,104
The following example creates a set of several members of the Home City and Home ZIP levels and then uses the HIERARCHIZE function to place these members into hierarchical order:
SELECT MEASURES.[%COUNT] ON 0,
HIERARCHIZE({homed.36711, homed.38928, homed.[elm heights], homed.Spruce}) ON 1
FROM patients
Patient Count
1 36711 1,069
2 Spruce 1,117
3 38928 2,149
4 Elm Heights 1,104
In contrast, the next example uses the POST keyword:
SELECT MEASURES.[%COUNT] ON 0,
HIERARCHIZE({homed.36711, homed.38928, homed.[elm heights], homed.Spruce}, POST) ON 1
FROM patients
Patient Count
1 36711 1,069
2 Spruce 1,117
3 Elm Heights 1,104
4 38928 2,149