Hi -
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]'
Obviously, there's no 201109 member under Q2.
Another issue when I run the above with just Q3, it works, but it says 'Time' for my Column Header, not the Caption
When 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"
Any help is greatly appreciated. Thank you.
For the last error, it looks like you are comparing a numeric month (09) with character month values in your expression. You could try using the sq() macro function:
IF (#sq(timestampMask($current_timestamp,'mm'))# in('07','08','09'))
This may also fix your Quarter calculation too?
I can't test this wild theory just now, so I may be completely wrong, but it's worth a shot!
Regards,
MF.
Do you have access to the model which created the package? Try creating a static parameter map called quarterLookup
With keys and values:
KEY | Value
-----+--------
01 | 1
02 | 1
03 | 1
04 | 2
and so on.
Then you could reference that lookup in the macro expression:
[Consolidated Sales].[Time].[Time].[Month]->:[TM].[Time].[Time].[@MEMBER].#sb(timestampMask($current_timestamp,'yyyy') + '-Q'+$quarterLookup(timestampMask($current_timestamp,'mm'))+'^' + timestampMask($current_timestamp,'yyyy') + timestampMask($current_timestamp,'mm'))#
I haven't tried it, but I don't see any reason why it wouldn't work.
Hi, Thank you both for replying.
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 !!!!