Trying to determine current member of the row axis to use in a calculated measure

I have a pretty simple cube in which I am trying to determine the current member of the set on the row axis that is in use in an IIF function. There is no hierarchy in this dimension but if I am at the [All] member in the iteration through the set, I want to arrive at the total differently then if I am at one of the child members.

[347 byte] By [PeterThurston] at [2008-1-10]

«« DataGridView strange cell wrapping effect
»» Named instance connectivity error - only works when port number is specified?

# 1

Have you tried the MDX Axis() function, which returns the sets on query axes? Assuming that you want the current member of the 1st (or only) dimension on rows, something like: Axis(1).Item(0).Item(0).Hierarchy.CurrentMember

SQL Server 2005 Books Online

Axis (MDX)

Updated: 17 July 2006

Returns the set of tuples on a specified axis.

Syntax

Axis(Axis_Number)

Arguments

Axis_Number: A valid numeric expression that specifies the axis number.

Remarks

The Axis function uses the zero-based position of an axis to return the set of tuples on an axis. For example, Axis(0) returns the COLUMNS axis, Axis(1) returns the ROWS axis, and so on. The Axis function cannot be used on the filter axis.

DeepakPuri at 2007-10-3 >

top of Msdn Tech,SQL Server,SQL Server Analysis Services...

# 2

I had tried the axis function but I was missing the use of the hierarchy function. What exactly do the Item(0) specifications provide? Thanks, again as this has given me the pieces that I need to complete my calculated measure.

PeterThurston at 2007-10-3 >

# 3

Item(), as applied to a set, returns a tuple from that set - accounting for the first occurrence:

Item (Tuple) (MDX)

Updated: 17 July 2006

Returns a tuple from a set.

...

Item(), as applied to a tuple, returns a member from that tuple - accounting for the second occurrence:

Item (Member) (MDX)

Updated: 17 July 2006

Returns a member from a specified tuple.

...