COGNOiSe.com - The IBM Cognos Community

Hi guys,

Long time reader, first time poster, I would like to thank you all for your great help. I found many answers to my problems in the past, unfortunately not to this one, hope you could help.

I built a DMR model using FM on a SQL database.
I have a report developed with RS with a tree prompt based on the Date hierarchy, asking to select a Month.
Right now, I have to select the month manually and get the prompt parameter in a data item (Month_Selected) as is : [Package].[Date].[Date].[Month]->?p_date?

But this report needs to be automated so I need to be able to get this parameter automatically. The parameter needs to be the previous month.

I tried to build the MUN of the previous month :
MUN ex : [Package].[Date].[Date].[Month]->[all].[2016].[201601].[201603]
               Year > Quarter > Month

Now this is what I did :
'[all].['
+
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))#
+ '].[' +
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))# +
CASE
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 1 AND 3 THEN '01'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 4 AND 6 THEN '02'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 7 AND 9 THEN '03'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 10 AND 12 THEN '04'
END
+ '].[' +
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))# +
#sq(timestampMask(_add_months($current_timestamp,-1),'mm'))#
+']'

It works fine but when I tried to assign this value to the data item Month_Selected it doesn't :(

#'[Package].[Date].[Date].[Month] ->
[all].['
+
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))#
+ '].[' +
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))# +
CASE
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 1 AND 3 THEN '01'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 4 AND 6 THEN '02'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 7 AND 9 THEN '03'
WHEN #sq(timestampMask(_add_months($current_timestamp,-1),'mm'))# BETWEEN 10 AND 12 THEN '04'
END
+ '].[' +
#sq(timestampMask(_add_months($current_timestamp,-1),'yyyy'))# +
#sq(timestampMask(_add_months($current_timestamp,-1),'mm'))#
+']'
#

Any idea why ?

If you think about another way to do it (#prompt ?) don't hesitate.

Thanks for your help.
Fabien

I would probably look head() and tail() functions.

Something like: head(tail(members([Package].[Date].[Date].[Month]), 2))

You might need to flip head() and tail() around, depending on how your time dimension is set up.

Thanks for your help bdbits.
Unfortunately my problem right now is to get automatically the "Previous Month" member, based on the current date.

I am sorry, I made a rather poor assumption that your time dim had dates up through 'today' but nothing future.

How are you testing "it works fine"? What do you mean by "assign it to a data item" - as in an expression on a data item? Or something else? If you are getting an error message, what is it?

I managed to do it  8)

I created a parameter map to get the quarter based on the month instead of doing a case when (which didn't work) and here is the final calculation :

#'[Package].[Date].[Date].[Month]->[all].['+
timestampMask(_add_months($current_timestamp,-1),'yyyy')
+'].['+
timestampMask(_add_months($current_timestamp,-1),'yyyy')
+
$pm_quarter{timestampMask(_add_months($current_timestamp,-1),'mm')}
+'].['+
timestampMask(_add_months($current_timestamp,-1),'yyyy')
+
timestampMask(_add_months($current_timestamp,-1),'mm')
+']'#

Thanks bdbits for trying to help me out :)