DeepSee MDX Reference
ALLMEMBERS
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Returns a set of all members of the given level or hierarchy. Or returns a set of all members of the first hierarchy of a dimension. In either case, any calculated members are also returned.
Returned Type
This function returns a set of members.
Syntax and Details
level_expression.ALLMEMBERS
Or:
hierarchy_expression.ALLMEMBERS
Or:
dimension_expression.ALLMEMBERS
Where:
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
See Also