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:
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