Dimension, CurrentMember and a hierarchy

Greetings -

I am writing some MDX for a RS report, and seem to have hit a bit of a wall. The business folks keep changing their minds on what hierarchy they want to report from, so I would like to have it as a parameter to the report. Problem is that I don't know if there is a way to get the CurrentMember.Properties('Caption') without explicitly declaring the hierarchy. My MDX:

With Set [MySet] as

StrToSet(@.HierarchyAndMember)

Set [DateSet] as

StrToSet(@.Date, Constrained)

Member [Measures].[Display] as

[Dimension].[Hierarchy Name].CurrentMember.Properties('Caption')

I would like to do StrToSet(@.HierarchyMember).CurrentMember.Properties('caption') here

Select { [measures].[created invoices], [measures].[display] } on 0,

{ [MySet] * [DateSet] } on 1

from [cube]

I have tried several different things, and this is the closest I have found. Placing the StrToSet in either it's own set or directly in the MDX doesn't affect the results. Is there any way to get the current member of the dimension without hardcoding the hierarchy? Does anyone have any creative solutions, or at least any directions to go?

Thanks in advance,

John Hennesey

It turns out that the RS designer is a pain in the sense it wants you to explicitly select a hierarchy & member so it knows what fields are returned from MDX. It doesn't check the validity of the items during runtime, it passes the values into MDX and lets AS take over. I have the ability to get done exactly what I am looking to do.

Thanks though,

John

sql