Using a Tuple and Prompt inside a CASE Construct

[dstump01]

Hello,

I haven't done many BI reports and apparently need some help.

I have a TM1 cube that has a dimension called Time_Month_Day  which at Level 0, has a member called Total Year. Within Total Year, there's a member called '3rd Qtr' and then within that, there's a member called 'Jul MTD'. Also within this same dimension, there's a different Level 0 member called YTD_12-31 which consists of members called 12-31 and YTD_12-30. YTD_12-30 then expands to 12-30 and YTD_12-29. YTD_12-29 expands to 12-29 and YTD_12-28 etc... all the way back until you get to 01-01.

What I'm trying to do is based on a prompted month called ?Current Month?. I want a column of my crosstab report to give me the 'Total Amount' that corresponds to the appropriate YTD_mm-dd member based on ?Current Month?.

The following is what I did to correctly get the 'Jul MTD' Total Amount into my report (Note that I'm also prompting for a current year):
tuple ([Alternate Securities].[SEC_Time_Month_Day].[SEC_Time_Month_Day]->?Current Month?,[Alternate Securities].[SEC_Time_Year].[SEC_Time_Year]->?Current Year?,[Total Amount]) 

Based on this prompted month and year, I'd like to set up a CASE statement or an IF statement (if I can get that to work) so that when July is selected on my prompt screen, I get data for YTD_07-31 and when June is selected, I get data for YTD_06-30  etc...

CASE
   WHEN [Alternate Securities].[SEC_Time_Month_Day].[SEC_Time_Month_Day]->?Current Month? = [Jul MTD]

THEN(tuple ([YTD_07-31],[Alternate Securities].[SEC_Time_Year].[SEC_Time_Year]->?Current Year?,[Total Amount]))

ELSE (tuple ([YTD_06-30],[Alternate Securities].[SEC_Time_Year].[SEC_Time_Year]->?Current Year?,[Total Amount]))
END

I'd appreciate any help you could give me.

Don

[dstump01]

Can anyone help me with the syntax of the CASE statement I originally posted?

Thanks

[blom0344]

Code Select Expand


WHEN [Alternate Securities].[SEC_Time_Month_Day].[SEC_Time_Month_Day]->?Current Month? = [Jul MTD]


According to the MDX syntax rules the WHEN part should resolve to a scalar value:

http://msdn.microsoft.com/en-us/library/ms144841.aspx

Do you get an error message with the expression, or does it parse but not function properly?