dynamically create MUN for current date

[Sandy10]

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.

[MFGF]

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.

[CognosPaul]

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:

Code Select Expand

[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.

[Sandy10]

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 !!!!