Accessing Multiple Members of a Level
You can access multiple members of a level in several different ways.
First, you can use the MEMBERS function. In this case, the syntax is as follows:
[dimension_name].[hierarchy_name].[level_name].MEMBERS
For example:
SELECT MEASURES.[%COUNT] ON 0, allerd.allergies.MEMBERS ON 1 FROM demomdx
%COUNT
1 No Data Available 390
2 additive/coloring agen 46
3 animal dander 34
4 ant bites 47
5 bee stings 36
6 dairy products 30
7 dust mites 35
8 eggs 32
9 fish 45
10 mold 51
11 nil known allergies 140
12 peanuts 58
13 pollen 57
14 shellfish 54
15 soy 36
16 tree nuts 45
17 wheat 52
You can also specify a range that selects adjacent members of a level, as follows:
member1:membern
For example:
SELECT MEASURES.[%COUNT] ON 0, {birthd.1942:birthd.1947} ON 1 FROM demomdx
%COUNT
1 1942 6
2 1943 7
3 1944 6
4 1945 11
5 1946 12
6 1947 9
In this case, you can omit the dimension, hierarchy, and level identifiers for the member that you use for the end of the range. For example:
SELECT MEASURES.[%COUNT] ON 0, {birthd.1942:1947} ON 1 FROM demomdx
You can select multiple, nonadjacent members. To do so, refer to them directly, and place them in a comma-separated list surrounded by curly braces:
SELECT MEASURES.[%COUNT] ON 0, {allerd.eggs,allerd.soy,allerd.mold} ON 1 FROM demomdx
%COUNT
1 eggs 32
2 soy 36
3 mold 51