Reference axis(2) members in MDX

Hello everybody.

I've got a little problem: I want to reference axis(2) members in a calculated member, so I can use multiple selection.

I've tried:

with member [Measures].[Test] as 'settostr(axis(2))'

select {[Measures].[Test]} on 0,

{[Dim Sales Territory].[Sales Territory Region].members} on 1

from [Adventure Works DW]

where ([Dim Geography].[Country Region Code].&[US],[Dim Geography].[Country Region Code].&[CA])

However, I get an error.

Is there any other way?

Thanks a lot,

Santi

The Axis function cannot be used on the filter axis.The Axis function cannot be used on the filter axis.

http://msdn2.microsoft.com/en-us/library/ms145531.aspx

your shuldn't use Axis function if you whant to use multiple filter

|||

Thanks Vladimir.

Then, assuming I have several members on the filter axis, how can I find out what those members are?

Is it at all possible?

The thing is, I'm trying to enable multiple selection in a dimension, and the selected members go directly to the where clause of the query. Since I have some calculated measures, I can′t reference those members using currentmember.

Thanks again for your answer.

|||What the client do you use? Do you have a full control on it.|||

You could use the Existing function:

>>

with member [Measures].[Test] as

settostr(Existing [Geography].[Country].[Country].Members)

select {[Measures].[Test]} on 0

from [Adventure Works]

where {[Geography].[Country].&[United States],

[Geography].[Country].&[Canada]}

-

Test
{[Geography].[Country].&[Canada],[Geography].[Country].&[United States]}

>>

This technique is discussed in detail here:

http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx

>>

Writing multiselect friendly MDX calculations

>>

|||

Thanks a lot Deepak!

Didn't know about Exist and Existing at all.

I've also read Mosha's blog entry, and it explains exactly the internals of what I'm trying to acomplish.