Mdx Using a member property as an argument for a strtomember

Question

I'm working on a cube that has two date dimensions, with the same structure, but measuring different behaviours: one measures the stay date, and the other one the reservation date. So I need to create a set that will isolate all reservations made for a specific date, until 1 year prior that specific date. I need the set to be dynamic, so it changes when the stay date changes...

Now, I am blowing my mind off trying to undertand why this works fine:

SET [D365] AS {NULL: [Creation Date].[Calendar].[Day].&[20131023].lag(365)}

But this does not:

SET [D365] AS {null:strtomember("[Creation Date].[Calendar].[Day].&["+ [Business Date].[Calendar].currentmember.properties("Key")+"]").lag(365)}

when

MEMBER [Measures].[Arg] as [Business Date].[Calendar].currentmember.properties("Key")

returns : 20131023

and

MEMBER [Measures].[Arg2] as "[Creation Date].[Calendar].[Day].&["+[Business Date].[Calendar].currentmember.properties("Key")+"].lag(365)"

returns [Creation Date].[Calendar].[Day].&[20131023].lag(365)

I've also tried to use cstr and format ("yyyyMMdd") before the argument, but to no avail...

Really, I am running out of ideas... :)

Thanks a lot for your help.

Why do you use StrToMember at all? SET [D365] AS {NULL: [Creation Date].[Calendar].CurrentMember.lag(365)} should work fine. — FrankPl
Thanks so much for your answer, but I need the STRTOMEMBER because I'm mixing two time dimensions. I am using the Key of Creation Date, as an argument to Business Date... :/ — CSF

FrankPl FrankPl · Accepted Answer · 2014-07-09T12:10:52

The problem is that sets in the WITH clause are evaluated after the evaluation of the WHERE clause but before the rows and columns of the query, while members definitions in the WITH clause take the context (current row/column position) into account, as their definition is evaluated each time they are found to appear on one of the axes. This means that [Business Date].[Calendar].currentmember probably is the all member when the set is defined (at least if you put the [Business Date].[Calendar] hierarchy on the rows or columns and not in the WHERE clause). Thus, you can use your set definition as a sub expression of a member definition or as a sub expression of the rows or columns set, but it is not useful to use it in a named set definition.

BTW: If you need a reference form one date dimension to the other date dimension, and these have the same structure, which they should have if they are role playing dimensions based on the same dimension object, you can use LinkMember and avoid StrToMember.

Mdx Using a member property as an argument for a strtomember

1 Answers