HIERARCHIZE (MDX)
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