MEMBERS (MDX)

Returned Type

This function returns a set of members.

Syntax and Details

level_expression.MEMBERS

Or:

hierarchy_expression.MEMBERS

Or:

dimension_expression.MEMBERS

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 name, this function returns a set that consists of the members of that level, not including any calculated members. The members are in the default order specified in the level definition in the cube. This default order is as follows:

• For non-date levels, members are sorted in increasing order alphabetically by name, unless the cube specifies a different sort order. The system provides flexible options for the default sort order of each level.

• For date levels, members are sorted chronologically.

Given a hierarchy name, 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. For information on hierarchical order, see the HIERARCHIZE function.

Given a dimension name, this function returns a set that consists of the members of all levels of the first visible hierarchy in this dimension.

Example

The following query displays all members of the Home Zip dimension as rows:

SELECT MEASURES.[%COUNT] ON 0, homed.zip.MEMBERS 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 homed.h1 hierarchy as rows:

 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

See Also

• ALLMEMBERS