Hi, When I was trying to respond to this post, somehow it got deleted.
I wanted to answer the 2 folks who had replied to me
1. Using sq gave me the same error as below, except for "IF ('09' in('07','08','09')) THEN (Consolidated Sales"
2. I used the paramater map to find the quarters, and it worked.
Here's my code to get the current month and the previous month.
set (
[Consolidated Sales].[Time].[Time].[Month]->:[TM].[Time].[Time].[@MEMBER].#sb(timestampMask(_add_months ($current_timestamp,-1),'yyyy') + '-Q' + $Quarter_Lookup{timestampMask(_add_months($current_timestamp,-1),'mm')} + '^' + timestampMask(_add_months($current_timestamp,-1),'yyyy') + timestampMask(_add_months($current_timestamp,-1),'mm'))#,
[Consolidated Sales].[Time].[Time].[Month]->:[TM].[Time].[Time].[@MEMBER].#sb(timestampMask($current_timestamp,'yyyy') + '-Q' + $Quarter_Lookup{timestampMask($current_timestamp,'mm')} + '^' + timestampMask($current_timestamp,'yyyy') + timestampMask($current_timestamp,'mm'))# )
Thank you both very much !!!!
PREVIOUS POST THAT WAS DELETED
I have a Report Studio Report with a Crosstab using TM1 as a data source.
The Time Dimension is one of the axis.
I want to schedule this report and have it run for this month and 1 month prior.
I want the month MUNs dynamically created for these two time periods.
Our MUN names for each month are as follows, using Sept. 2011 as an example.
[Consolidated Sales].[Time].[Time].[Month]->:[TM].[Time].[Time].[@MEMBER].[2011-Q3^201109]
I can get the report to run for September when I hard code Time set to:
[Consolidated Sales].[Time].[Time].[Month]->:[TM].[Time].[Time].[@MEMBER].#sb(timestampMask($current_timestamp,'yyyy') + '-Q3^' + timestampMask($current_timestamp,'yyyy') + timestampMask($current_timestamp,'mm'))#
But, I have to test which Quarter I'm in to build the MUN.
I was testing just with Q2 and Q3, which I created as Data Items.
My Time Data Item Expression is as follows:
IF (#timestampMask($current_timestamp,'mm')# in('04','05','06'))
THEN ([Q2])
ELSE ([Q3])
I get the following Runtime error:
At least one invalid member reference was encountered in the query.'[Consolidated Sales].[Time].[Time].[Month]->:[TM].[Time].[Time].[@MEMBER].[2011-Q2^201109]'
Another issue when I run the above with just Q3, it works, but it says 'Time' for my Column Header, not the CaptionWhen I change the Time Data Expression to
IF (#timestampMask($current_timestamp,'mm')# in('07','08','09'))
THEN
[Consolidated Sales].[Time].[Time].[Month]->:[TM].[Time].[Time].[@MEMBER].#sb(timestampMask($current_timestamp,'yyyy') + '-Q3^' + timestampMask($current_timestamp,'yyyy') + timestampMask($current_timestamp,'mm'))#
ELSE
[Consolidated Sales].[Time].[Time].[Month]->:[TM].[Time].[Time].[@MEMBER].#sb(timestampMask($current_timestamp,'yyyy') + '-Q3^' + timestampMask($current_timestamp,'yyyy') + timestampMask($current_timestamp,'mm'))#
I get this Error.
Parsing error before or near position: 53 of: "IF (09 in('07','08','09')) THEN (Consolidated Sales"
These are great techniques, however they are development intensive and dependent on the structure of the MUN not changing.
It would be a better practice to identify requirements for relative time and build relative time members (ie: current month, prior month) in the cube.