Calendar Prompts with OLAP data source - Can dynamic default date be set?

[ctrout1]

Gurus,

I am using a calendar prompt to populate the date field in multiple cubes, and it works great as long as the user executes the report and answers the prompt.  My challenge today is now the user would like to schedule the report and have the date default to yesterday's date.  I thought this would be as simple as populating the default field in my prompt macro, however, I can't seem to get it to work.

Here is my data item that works great when the calendar prompt is utilized;
#'[Test Cube].[Dates].[Dates].[Day]->:[PC].[@MEMBER].['+substitute('-','',substitute('-','',prompt('Selected Day - Calendar','date')))+']'#

I was hoping I could simply add a default to my data item like this;
#'[Test Cube].[Dates].[Dates].[Day]->:[PC].[@MEMBER].['+substitute('-','',substitute('-','',prompt('Selected Day - Calendar','date','_add_days(current_date, -1)')))+']'#

...but it just adds the string of text to my MUN and then kicks out of error that I have an invalid MUN.

Any ideas on how to schedule a report with a date prompt and have it default to yesterday's date against an OLAP data source?

Thanks

[CognosPaul]

current_date is a database function, the macro equivalent is $current_timestamp.

Try

#'[Test Cube].[Dates].[Dates].[Day]->:[PC].[@MEMBER].['+substitute('-','',substitute('-','',prompt('Selected Day - Calendar','date',timestampMask($current_timestamp,'yyyymmdd'))))+']'#

[ctrout1]

CognosPaul,

Thank you much, it works.

Follow-up question, can I adjust it to be server date minus 1 (yesterday)?  To function like.... _add_Days(current_date, -1)

Regards

[MFGF]

CognosPaul,

Thank you much, it works.

Follow-up question, can I adjust it to be server date minus 1 (yesterday)?  To function like.... _add_Days(current_date, -1)

Regards

Hi,

How about just using the prevMember function in the expression, ie:

#'prevMember([Test Cube].[Dates].[Dates].[Day]->:[PC].[@MEMBER].['+substitute('-','',substitute('-','',prompt('Selected Day - Calendar','date',timestampMask($current_timestamp,'yyyymmdd'))))+'])'#

Cheers!

MF.

[ctrout1]

MF,

Thank you.  To clarify a bit.  I would only like the default to be server date minus 1, so if the report is scheduled it will default to yesterday.  However, if the report is run and the user selects a date from the calendar prompt, the date selected in the calendar prompt is utilized (not selected date minus 1).

Thanks

[CognosPaul]

There are a number of macro date functions. In your case, you can simply use the _add_days() function.

#'[Test Cube].[Dates].[Dates].[Day]->:[PC].[@MEMBER].['+substitute('-','',substitute('-','',prompt('Selected Day - Calendar','date',timestampMask(_add_days($current_timestamp,-1),'yyyymmdd'))))+']'#

[ctrout1]

CognosPaul,

Perfect, it works, appreciate your insight.

Regards