If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Mun value to dynamic macro month expression

Started by cognos05, 11 Aug 2014 01:21:28 PM

Previous topic - Next topic

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


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') + '])'#


Hi,

I am not able to use TimestampMask in cognos 8.1..is there any alternative to achieve the above.

Thanks,
Nithya

MFGF

Quote from: nithya1224 on 11 Aug 2014 01:21:28 PM
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


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') + '])'#


Hi,

I am not able to use TimestampMask in cognos 8.1..is there any alternative to achieve the above.

Thanks,
Nithya

Well, I just managed to dig out an ancient virtual machine running Cognos 8.4.1. It's the oldest version of Cognos software I have. This expression works fine in 8.4.1. Are you sure the timestampMask() macro function isn't supported in your version?

If not, you should seriously consider moving off 8.1. If your development effort doubles because of it, then it's not a financially viable proposition to remain on that version, surely?

In Cognos 8, you can only see the macro functions displayed when you are in FM setting up calculations, although the syntax still works in Report Studio. You should fire up FM and see if the timestampMask() macro function is there.

MF.
Meep!