ALLMEMBERS (MDX)
Returned Type
Syntax and Details
level_expression.ALLMEMBERS
Or:
hierarchy_expression.ALLMEMBERS
Or:
dimension_expression.ALLMEMBERS
Where:
-
level_expression is an expression that returns a level. For example:
[dimension_name].[hierarchy_name].[level_name] -
hierarchy_expression is an expression that returns a hierarchy. For example:
[dimension_name].[hierarchy_name] -
dimension_expression is a dimension name, included within square brackets if needed (see Identifiers). For example:
[dimension_name]The system interprets this as a reference to the first visible hierarchy within the given dimension.
Given a level expression, this function returns a set that consists of the members of that level. The members are in the order specified in the level definition in the cube.
Given a hierarchy expression, this function returns a set that consists of the members of all levels in that hierarchy, including the All member, if defined. The members are returned in hierarchical order.
Given a dimension name, this function returns a set that consists of the members of all levels in the first visible hierarchy of that dimension.
In any case, any calculated members are also returned (in contrast to the MEMBERS function).
For information on hierarchical order, see the HIERARCHIZE function.
Example
The following query displays all members of the Home Zip level as rows:
SELECT MEASURES.[%COUNT] ON 0, homed.zip.ALLMEMBERS ON 1 FROM patients
Patient Count
1 32006 2,272
2 32007 1,111
3 34577 3,399
4 36711 1,069
5 38928 2,149
The following query displays all members of all levels in the Home.H1 hierarchy as rows:
SELECT MEASURES.[%COUNT] ON 0, homed.h1.ALLMEMBERS 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 query shows all measures, each aggregated across the cube:
SELECT MEASURES.ALLMEMBERS ON 1, gend.gender.MEMBERS on 0 FROM patients
Female Male
1 Patient Count 5,067 4,933
2 Age 187,139 170,117
3 Avg Age 36.93 34.49
4 Allergy Count 3,067 3,131
5 Avg Allergy Count 1.02 1.04
6 Encounter Count 169,164 158,183
7 Avg Encounter Cou 33.39 32.07
8 Test Score 302,267 298,818
9 Avg Test Score 74.78 74.46
