Accessing the Current Member within an Iteration
In a typical query, you iterate through a set of members, perhaps displaying each as a row. Sometimes you want to do something specific with each member in turn. To do so, you use the CURRENTMEMBER function, which accesses the member used in the current context.
For example, consider the following query:
SELECT MEASURES.[%COUNT] ON 0, homed.city.MEMBERS ON 1 FROM demomdx
%COUNT
1 Cedar Falls 110
2 Centerville 99
3 Cypress 112
4 Elm Heights 118
5 Juniper 122
6 Magnolia 114
7 Pine 121
8 Redwood 111
9 Spruce 93
This query has one row for each city. The data shown is the %COUNT measure. Suppose that instead we would like to show the city’s population, which we access via the PROPERTIES function. This function requires a reference to the member used in the row; for that, we use the CURRENTMEMBER function, which we can call as follows:
[dimension_name].[hierarchy_name].CURRENTMEMBER
With this function, we can create the following variation of our query:
SELECT homed.h1.CURRENTMEMBER.PROPERTIES("Population") ON 0, homed.city.MEMBERS ON 1 FROM demomdx
H1
1 Cedar Falls 90,000
2 Centerville 49,000
3 Cypress 3,000
4 Elm Heights 33,194
5 Juniper 10,333
6 Magnolia 4,503
7 Pine 15,060
8 Redwood 29,192
9 Spruce 5,900
For another example, the following query shows the internal keys for the members of Doctor:
SELECT docd.h1.CURRENTMEMBER.PROPERTIES("KEY") ON 0, docd.[doctor].MEMBERS ON 1 FROM demomdx
KEY
1 None <null>
2 Ahmed, Thelma 34
3 Alton, Chad 35
4 Black, Ashley 4
..
