Syntax and Details
level_expression is an expression that returns a level. For example:
[dimension_name].[hierarchy_name].[level_name]Copy code to clipboard
hierarchy_expression is an expression that returns a hierarchy. For example:
[dimension_name].[hierarchy_name]Copy code to clipboard
dimension_expression is a dimension name, included within square brackets if needed (see Identifiers). For example:
[dimension_name]Copy code to clipboard
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.
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