Skip to main content

Accessing the Members of a Hierarchy

Accessing the Members of a Hierarchy

To access the members of a hierarchy (that is, all the members of all its levels), you use the MEMBERS function. In this case, the syntax is as follows:

[dimension_name].[hierarchy_name].MEMBERS

In InterSystems MDX, if you omit the hierarchy name, the system assumes that you are referring to the first visible hierarchy in the given dimension.

For example, in the DemoMDX cube, the homed dimension has only one hierarchy. The following query shows the members of that hierarchy:

SELECT MEASURES.[%COUNT] ON 0, homed.MEMBERS ON 1 FROM demomdx
 
                                    %COUNT
 1 32006                                215
 2 Juniper                              122
 3 Spruce                                93
 4 32007                                111
 5 Redwood                              111
 6 34577                                347
 7 Cypress                              112
 8 Magnolia                             114
 9 Pine                                 121
10 36711                                 99
11 Centerville                           99
12 38928                                228
13 Cedar Falls                          110
14 Elm Heights                          118

When you use the MEMBERS function with a hierarchy, it returns the set of members in hierarchical order. The first member is the All member, if present. After that, each member is one of the following:

  • The first child of the previous member.

  • The next sibling of the previous member.

For another example, the following query shows all the measures (apart from %COUNT):

SELECT gend.gender.MEMBERS ON 0, MEASURES.MEMBERS ON 1 FROM demomdx

                              Female                 Male
1 Age                          18,413               17,491
2 Avg Age                       37.73                34.16
3 Allergy Count                   326                  332
4 Avg Allergy Count              1.08                 1.07
5 Test Score                   29,542               31,108
6 Avg Test Score                73.49                74.42

FeedbackOpens in a new tab