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
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'))))+']'#
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
Quote from: ctrout1 on 31 Jul 2014 04:26:30 PM
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.
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
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'))))+']'#
CognosPaul,
Perfect, it works, appreciate your insight.
Regards