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
Can anyone help me with the syntax of the CASE statement I originally posted?
Thanks
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?