Order of Members in a Level
Within a cube definition, a level definition determines the members in that level, as well as their default order, which is as follows:
-
For non-date levels, members are sorted in increasing order alphabetically by name, unless the cube specifies a different sort order.
-
For date levels, members are sorted chronologically, in ascending order or descending order, depending on the definitions in the cube.
The MEMBERS function returns the members in their default order, as defined by the level. For example:
SELECT gend.gender.MEMBERS ON 0,homed.city.MEMBERS ON 1 FROM demomdx
Female Male
1 Cedar Falls 58 52
2 Centerville 41 58
3 Cypress 51 61
4 Elm Heights 53 65
5 Juniper 58 64
6 Magnolia 58 56
7 Pine 64 57
8 Redwood 58 53
9 Spruce 47 46
If you have a subset of the members of a level and want to return them to the default order, use the HIERARCHIZE function as in the following example:
SELECT MEASURES.[%COUNT] ON 0, HIERARCHIZE({allerd.eggs,allerd.soy,allerd.mold}) ON 1 FROM demomdx
%COUNT
1 eggs 32
2 soy 36
3 mold 51
For a more thorough introduction to this function, see Working with Sets.
