How to delay a date prompt when running a report ???

[kyleboerner]

I created a dashboard.

One of the components of my dashboard is a list of Daily Receipts.

I inserted a Date Prompt (calendar icon) below the list so a user can review yesterday's Daily Receipts, and afterwards, select other past dates and rerun the report.

I'm 99% done; however I have run into an obstacle hopefully one of you COGNOS gurus could solve.

I was provided the following filter:
(?pSelectedDate? is null
And
[Date of Last Receipt] = _add_days(current_date,-1))
Or
([Date of Last Receipt] = ?pSelectedDate?)

When I run the report with the aforementioned filter, a date prompt screen appears (I guess null isn't the right item to use?).  I do not want the date prompt screen to appear, hence the "?pSelectedDate? is null" part of the filter.  I need the report to run with yesterday's Daily Receipts, and then offer the user the ability to select a date in the date prompt calendar icon and rerun the report.

Any ideas????????????   Plus, I'm a marketing guy with zero programming knowledge, so please bear with me.

Thanks, Kyle

[leahmarie]

Do you have the prompt set as required? Because if you do then the prompt will automatically run, even with the null. Check there and change it to optional.  Be sure to change it to optional not only inside the prompt properties but also in the detail or summary filters as well.

[kyleboerner]

I switched the prompt filter to "Optional", and the prompt properties to "Not Required", and instead of yesterday's Daily Receipts, the list populated with all receipts on record, though the date prompt calendar worked. 

Any thoughts?

[leahmarie]

You probably had the filter for yesterday's receipts based on the filter that was required.  Since you had the is null part, even when you didn't select anything, yesterday's receipts showed.  I wish I knew more and could help more, but I am not good with code.  I would start by adding a filter to the report filtering it for yesterday's receipts, possibly using a case statement?

Case [Receipts]
when [date] is null then 'yesterday code'
when [date] is not null then ?dateparameter?
end

something like that should work, like I said I'm really a beginner as well, I usually just mess with things until I force them to do what I want ;) hopefully that helps though.

[Lynn]

Making the prompt optional will prevent the preliminary generated prompt, but then the result on initial display won't be filtered because you have the prompt control on the report itself and not in a preliminary prompt screen. Essentially you haven't specified a date yet and the report doesn't require you to, so it doesn't do anything with the parameter until you select a date and re-submit.

Anyway, date prompts pose extra problems because you can't default them to a dynamic value and they aren't ever really null.

I have an idea that might work for you. The premise is that you can't have your cake and eat it too, so you need to fake things out a little behind the scenes.

1) Create one query and one list that shows the default situation which is yesterday's receipts. This has a required filter:  [Date of Last Receipt] = _add_days(current_date,-1)

2) Then create a second query and second list that shows receipts for a selected day. This one also has a required filter, but uses the parameter: [Date of Last Receipt] = ?pSelectedDate?

Now the magic part.

3) Create a boolean variable that will control rendering the default list for yesterday's receipts. Name it variable 'renderDefault' and set the expression to: ParamCount ('pSelectedDate') = 0

4) Repeat step 3 but name the variable 'renderSelected' and make the expression <> 0

5) Navigate to the list properties for the default list that will show yesterday's receipts and set the render property to 'renderDefault'.

6) Repeat step 5 for the selected date list, setting the render property to 'renderSelected'.

The reason you won't get the generated date prompt for the selected list even though it is set as required is because the render variable steps in ahead of things and then cognos knows not to even bother with the query. Once a date is selected from the control on the page it kicks into effect.

There are other approaches, but all the ones I can think of  have one form of compromise or another. I'd love to see other people's ideas on the question.

[kyleboerner]

Lynn,

Your solution is very much appreciated.  I sat down with a programmer and applied all the steps you detailed in your message.  Once everything was complete, we ran the report.  The report presented yesterday's Receipts in the list, yet, when we selected a past date in the Date Prompt Calendar Icon, and reran the report, the list came back with the same Receipts from yesterday and not the date selected.

Any ideas on what went wrong, or where we went wrong, if once we reprompt the report for a different date we still receive yesterday's receipts?

We were toying with the list's properties, the boolean variable formulas, but we couldn't find a way to solve the problem.

Quick Question:  Our programmer chose to Copy and Paste the original Query (two queries Joined to create one query) and the original list to create the second query and list.  We changed the filter of course, but could this be causing a problem?

Thanks,
Kyle

Thanks,
Kyle

[Lynn]

When you copied the list did you re-point it to the copied query? Maybe both lists are pointing to the same query?

[kyleboerner]

Lynn,

Thanks to your help - we have finally solved the date prompt!!!

We created two lists; the second a copy of the original (we also copied the original join of queries).

The difference in the queries was in the filters in the first of the two queries in each join.
The first join had the following filter = [Date of Last Receipt]=_add_days(current_date, -1)
The copied join had the following filter = [Date of Last Receipt]=?DatePrompt?

The later filter was set to 'Optional'.

Created two variables:
'renderDefault' = ParamCount (ParamDisplayValue('DatePrompt')) is missing
'renderSelected' = ParamCount (ParamDisplayValue('DatePrompt'))=0

We set the first list's Render Variable to 'renderDefault' and the Query Data Reference to the first joined query.  We set the second list's Render Variable to 'renderSelected' and the Query Data Reference to the second joined query.

When the dashboard/report runs, it populates with yesterday's receipts and allows the user to select a previous date in the date prompt and rerun the report.

Thanks for all your help.
Kyle

[Lynn]

Glad it worked out!