It partially worked. Now the prompt displays the value for yesterday = 18 Feb 2024 for today. However, when scheduling the report, I have to select the value in the drop down list. Not sure how I can pass "Yesterday" as the value in the drop down list and it does the work? As of now, in the scheduling tab, in prompts, it opens the prompt and I have to manually select the date that it shows.
Original Message:
Sent: Mon February 19, 2024 07:16 AM
From: Chris Turner
Subject: Set Yesterday as default date for Date Prompt to enable scheduled reports
I wrote this blog post a lifetime ago, but I think it will still do the job for you. When you schedule the report you just need to select Yesterday
Original Message:
Sent: 2/19/2024 7:11:00 AM
From: mia mi
Subject: RE: Set Yesterday as default date for Date Prompt to enable scheduled reports
Hi Marc,
Yes, we need to find a way to store a dynamic value that represents yesterday for the prompt, to enable scheduling reports and auto-selection (as it is a mandatory prompt).
I tried adding this to the queries : if (?p_date? = 2099-01-01) then ( _add_days (current_date, -1 ) )
else ( ?p_date? ) and adding this in the report tab > schedule as a default value and make the prompt optional, but it didn't work unfortunately.
Best solution would be to have the prompt = yesterday and have it run without prompting the user to select a date, as it will be already selected by default. I'm not really sure how we can achieve this.
As explained, the date item [Report Date] is in one query for the prompt, and in the general queries, it is set as cast([Report Date]) = ?datePromptValue?. I've tried setting it as [Report Date] = _add_days(current_date(),-1) , didn't work. Then tried with setting a data item with 1 value = yesterday, and selecting by default 1 in the prompt values, didn't work. Also tried Patrick solution, but it didn't work either... Most logical solution would be [Report Date] = _add_days(current_date(),-1) but it doesn't work at all...
------------------------------
mia mi
Original Message:
Sent: Mon February 19, 2024 03:48 AM
From: Marc Reed
Subject: Set Yesterday as default date for Date Prompt to enable scheduled reports
I think this question pops up more than any other question here!
You state that you need a solution that works with scheduling. This means that we need to be able to store a prompt answer that represents yesterday.
This sounds obvious, but it's worth pointing out. So whilst you could make the report open up and a date prompt show '18 Feb 2024' (yesterday at the time of writing). The prompt would remember '18 Feb 2024'. So when the report is run today, tomorrow, 2 months time, it would show '18 Feb 2024' and not yesterday. I think this is also a limitation of the C12 new reporting prompts (can someone confirm?)
If you are using a date prompt then you could pick some date that would never be used to represent yesterday. For example, you could tell your users that if they picked the date 01 Jan 1900 then that will always be interpreted to be yesterday.
The filter you would add to your report would be something like:
if (?p_date? = 1900-01-01) then ( _add_days (current_date, -1 ) )
else ( ?p_date? )
= [C].[C_Great_outdoors_data_module].[page_6].[Date_]
To make this better for the user, you could then set the prompts default date to 1900-01-01 and add some text to the prompt page telling users to pick 1900-01-01 for yesterday.
I think this is the simplest way of achieving this.
There are numerous other ways.
- Add another prompt which gives the user 2 options. The first being the text 'yesterday' and the second being 'value in the date prompt'. Your report logic looks at this additional prompt, and if its yesterday then it does a yesterday filter, otherwise it uses the date prompt filter. the logic would be something like...
if (?p_YesterdayOrDate? = 'Yesterday')
then ( [C].[C_Great_outdoors_data_module].[page_6].[Date_] = _add_days (current_date, -1 ) )
else ( [C].[C_Great_outdoors_data_module].[page_6].[Date_] = ?p_Date? )
- Not use a time prompt at al;, but build a value prompt list based around a calendar.
You could then add Yesterday to this.
The user can either pick a date, or yesterday,
Again the report logic can do something like the filter in (1)/
------------------------------
Marc Reed
Original Message:
Sent: Thu February 15, 2024 03:31 PM
From: mia mi
Subject: Set Yesterday as default date for Date Prompt to enable scheduled reports
Hi,
I am trying to set the day before the current day as a default value in Cognos, in order to enable scheduling reports. The reports are timebased partitioning, hence the requirement to select a date in the date prompt.
The date item [Report Date] is in one query for the prompt, and in the general queries, it is set as cast([Report Date]) = ?datePromptValue?.
I tried using this filter in the query with no success : [Report Date] = (#prompt('datePromptValue','date', '(_add_days (current_date,-1))')#) and encountered an error that ?datePromptValue? was used for different types : date, smallint.
I also tried adding 'Yesterday' as a default value for the prompt, and add the filter : cast([date1], date) = (if (?Date1? = 'Yesterday') then (
_add_days(current_date, -1 )) else (?Date1?)) with no luck and encountered the same kind of error.
Can anyone help with a solution please? I'm running out of ideas here...
Thanks,
------------------------------
mia mi
------------------------------