Mun value to dynamic macro month expression

[cognos05]

Hi All,
My month mun value looks like this
[Sales].[Time Period].[Time Period].[Month]->:[PC].[@MEMBER].[20130701-20130731]    for July 2013.

I have to select the last completed month value when user runs the report. say if its aug 2014 now , i should show july 2014

#'[Sales].[Time Period].[Time Period].[Month]->:[PC].[@MEMBER].[timestampMask($current_timestamp,'yyyymmdd')
how will i know whether that month has 1 to 31 days or 30  days and how do i get the previous month instead


Thanks,
Nithya

[bdbits]

_add_months ( date_expression, integer_expression )
Returns the date or datetime, depending on the format of "date_expression", that results from the addition of "integer_expression" months to "date_expression".

Example: _add_months ( 2002-04-30 , 1 )
Result: 2002-05-30

Example: _add_months ( 2002-04-30 12:10:10.000, 1 )
Result: 2002-05-30 12:10:10.000

[MFGF]

Hi All,
My month mun value looks like this
[Sales].[Time Period].[Time Period].[Month]->:[PC].[@MEMBER].[20130701-20130731]    for July 2013.

I have to select the last completed month value when user runs the report. say if its aug 2014 now , i should show july 2014

#'[Sales].[Time Period].[Time Period].[Month]->:[PC].[@MEMBER].[timestampMask($current_timestamp,'yyyymmdd')
how will i know whether that month has 1 to 31 days or 30  days and how do i get the previous month instead


Thanks,
Nithya

Hi,

You can get the current month using this expression:

#'[Sales].[Time Period].[Time Period].[Month]->:[PC].[@MEMBER].['+timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'#

To get the month prior, you could use the prevMember() function

ie

#'prevMember([Sales].[Time Period].[Time Period].[Month]->:[PC].[@MEMBER].['+timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + '])'#

Cheers!

MF.

[cognos05]

Hi MFGF,

Thanks for the reply. The macro you posted would work perfectly.
But I am using cognos 8.1 and I believe there is no time stamp function allowed in 8.1.
I am getting the below error while running the report.
Unknown function 'timestampMask' in macro expression.

Is there any other alternative statements available.

I appreciate your help.

Thanks,
Nithya.

[MFGF]

Hi MFGF,

Thanks for the reply. The macro you posted would work perfectly.
But I am using cognos 8.1 and I believe there is no time stamp function allowed in 8.1.
I am getting the below error while running the report.
Unknown function 'timestampMask' in macro expression.

Is there any other alternative statements available.

I appreciate your help.

Thanks,
Nithya.

If you are using Cognos 8.1, why is your question posted in the Cognos 10 Report Studio forum and not the Cognos 8 Report Studio forum? If you're expecting people to spend their own time and skills to give you solutions, it seems selfish of you to post in the wrong forum and not to specify you are using a different version. People could feel aggrieved that they had wasted their valuable time giving you solutions appropriate to Cognos 10 that are not relevant to your situation. If they give you a fully working solution for Cognos 10 and then you point out afterwards that the solution they worked on for you is inappropriate because it's for a different version, how do you imagine that would make the person helping you feel?

MF.

[cognos05]

Sorry about that. I will be using in both 8 and cognos 10 as well. we have few users restricted to cognos 10.I think this solution will be helpful for me to implement in 10 as well. Thanks for your help. I will post it in cognos 8 forum as well.

Thanks,
Nithya