Skip to main content

HIERARCHIZE (MDX)

Given a set, returns a set that is in hierarchical order (the order specified by the hierarchy).

Returned Type

This function returns a set of members.

Syntax and Details

HIERARCHIZE(set_expression)

Or:

HIERARCHIZE(set_expression, POST)

Where:

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:

generated description: hierarchy example

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

See Also

FeedbackOpens in a new tab