13 months back from the Chosen Month

[kalyanswarna]

Hi, I am using SAP BW Query as Source for my Universe and i dont have any BI Variables in the Query.

My Month and Year Objects is [0CALMONTH] it will display like JAN 2009.

here the requirement is i am using this dimension as FILTER in my Query Panel. i have to get 13 months back from the chosen month and year.

Suppose the User has chose JAN2009 in Prompt. the report should disaplay the months from JAN 2009 to Jan2008.

Can anyone please Tell me how to construct my Prompt in the Universe to get 13 months.

i know its very easy in Relational world but here the problem is i am using SAP query as backend.

So Need MDX solution for this.

Thanks,

Kalyan

[Former Member · Accepted solution]

Hi,

It is possible to define such a filter using MDX expression.

Thi is a little bit tricky because calculated members defined in MDX with SAP is limited to numeric expressions and supports very few string expressions.

Moreover the result of any calculated expression MUST be numeric.

So, in order to build the filter we have to use

[0CALMONTH]

display attributes:

  [20CALYEAR]
  [20CALMONTH2]

For instance you can build a calculated expression that will return the YEAR and the MONTH of the characteristic using the following format: YYYY.MM:

<EXPRESSION>([0CALMONTH].[20CALYEAR] + ( [0CALMONTH].[20CALMONTH2] /100))</EXPRESSION>

Now you can define a filter taht includes a promppt where the user can enter manually (in free mode) a YEAR / MONTH like this: YYYY.MM

Here is the code:

<FILTER EXPRESSION=" IIF ( ([0CALMONTH].[20CALYEAR] + ( [0CALMONTH].[20CALMONTH2] /100))  < @Prompt('Year/Month (YYYY.MM)','N',,mono,free,persistent)-1, 0, IIF ( ([0CALMONTH].[20CALYEAR] + ( [0CALMONTH].[20CALMONTH2] /100))  <= @Prompt('Year/Month (YYYY.MM)','N',,mono,free,persistent),1,0)) "><CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"/></CONDITION></FILTER>

This filter returns "1" when the 0CALMONTH value is between the date entered by the user minus 13 months, otherwise the filter returns "0".

Didier.