HIERARCHIZE - InterSystems MDX Reference - InterSystems IRIS Data Platform 2019.2

docs.intersystems.com

InterSystems IRIS Data Platform 2019.2 / InterSystems MDX Reference / MDX Functions

InterSystems MDX Reference

HIERARCHIZE

InterSystems: The power behind what matters

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:

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