Cognos Dimensional Reporting

[cognos05]

I have a Dates Dimension which has values from 1 to 31 . I always wanted my report to be filtered on the last completed date.

Say today If I run the report in the morning it is  12 th Jan. My report should be filtered for the value 11 in my Dates Dim.

How should my slicer filter be

Thanks,
Nithya

[MFGF]

I have a Dates Dimension which has values from 1 to 31 . I always wanted my report to be filtered on the last completed date.

Say today If I run the report in the morning it is  12 th Jan. My report should be filtered for the value 11 in my Dates Dim.

How should my slicer filter be

Thanks,
Nithya

You need to tell us what the MUN (Member Unique Name) of a date member looks like for us to be able to answer your question.

MF.

[cognos05]

Yes the MUN would look like
[Daily Sales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[11]

SO today is 12 the cognos will have members in Day dimension till 11 and I need to pick the last completed day.(Todays date -1)

Thanks,
Nithya

[schrotty]

I have a Dates Dimension which has values from 1 to 31

[...] cognos will have members in Day dimension till 11 [...]

Do you have a Dimension with 31 members or a Dimension with 11 members ?  :o

If there exist only 11 members (for all completed days) simple use the tail - function to get a set of the last members from a level. Embed it into the item function to get only one member:

Code Select Expand

item ( tail ( [Daily Sales].[Day].[Day] , 1 ) , 0 )

If your dimension contains 31 members the solution will look different ( filter function or macro ..)

Schrotty

[cognos05]

Yes the cube builds daily and I will have date only till the previous day. But still I would like to filter based on the day.
[Daily Sales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[11]

Thanks,
Nithya

[schrotty]

Macro Function to generate the MUN based with the previous day:

Code Select Expand


#'[Daily Sales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[' +timestampMask( toUTC ( _add_days ( $current_timestamp , -1 ) ) , 'dd' ) + ']' #


Schrotty

[cognos05]

Thanks !!

[cognos05]

Schrotty,

I just wanted to hardcode the value for currenttimestamp and check so I used below

#'[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[' +timestampMask(  _add_days ('2016-01-07 10:24:15.290000000',-1 )  , 'dd' ) + ']' #

but it throws an error saying
[DailySales].[Day].[Day].[Day1]->:[PC].[@MEMBER].[00]" does not exist.

WHy is it giving 00 instead of 6 .

Is this _add_days ('2016-01-07 10:24:15.290000000',-1 ) not recognoizable should I cast it to time stamp ?

Thanks,
Nithya

[schrotty]

Hi,

if you want to Hardcode the Timestamp try the following Format:
'2005-11-01 12:00:00.000-05:00'

Link with Examples:
http://www-01.ibm.com/support/knowledgecenter/SSWGNW_10.1.0/com.ibm.swg.im.cognos.ug_fm.10.1.0.doc/ug_fm_id23945macro_functions.html

Schrotty