Skip to main content

ALLMEMBERS (MDX)

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:

  • 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

See Also

FeedbackOpens in a new tab