COGNOiSe.com - The IBM Cognos Community

Good day guys,

Stuck with an date prompt issue.

User wants a report to fecth previous day data and wants a date picker on the report page for reprompt.

I couldn't figure out the filter. When i run the report, it choose the current date as default date and displays nothing. Reprompt is working fine on the report page.

Please assist me guys

Thanks guys

Hi - you might be able to do something with this:
http://cognostips.weebly.com/blog/date-prompts (http://cognostips.weebly.com/blog/date-prompts)

Thanks for the reply bigChris.

Sorry,i didn't explain the issue clearly.

When user run the report,

1, User want to see the report page directly with the yesterday's data.
2, User also wants a date prompt in the report page for reprompting with different date.

Thanks

Yep, so you could build that prompt into the report page and default the selection

I created a prompt page in the report page. I couldn't pass the yesterday date to the date prompt.

I used a java script, but it can only change the date prompt to highlight the yesterdays date.

Hi,

To submit report directly for yesterday's date you can follow below solution.

1. Create one query "Query1"
2. Add data item "yesterday" with expression _add_days (current_date,-1)
3.Create prompt page
4. Add value prompt (name = startdate) on the prompt page with parameter name "p_date"
5. Assign "Query1" to value prompt and set  "yesterday" data item to use value.
6. Set visible property of Value prompt to "No"
7. Add JavaScript after value prompt with below expression

----------
<div id="msgDiv" style="color:chocolate; font-size:18"></div>
<script>
// Attach functions to an arbitrarily named object to mimic a namespace to ensure the name uniqueness
var zxcv = {};

// Get the required prompt.
var oCR = cognos.Report.getReport("_THIS_");
var oP = oCR.prompt.getControlByName("startdate");

// Get the values and isolate the first value.
var allValues = oP.getValues(true);
var firstValue = allValues[2];

// Set the prompt selection to the first value.
oValues = [firstValue];
oP.setValues(oValues);



setTimeout('oCVRS.promptAction(\'finish\')', 100);

// Display a debug message to indicate that this script has been run.
</script>
----------------------

8. Now you can use p_date parameter in your query. Report will default run for yesterday's date.
9.Now to have user to select date , add date prompt on  report page with same parameter "p_date"

I have tried this solution and it is working fine for me.

please let me know if you face any issues.

Thanks R Ambre.

I tried it and it doesn't filter on the yesterday date. I got all the data from DB.Page also refresh every second and page goes blank.

Hi
I tried this solution at my end and it is working fine.

Are you putting javascript on Prompt page ?
If it is on prompt page then page will not refresh again and again.

Are you applying p_date filter in your main query..if yes then it should show data only for selected date not for all dates from DB.

Please find attached screenshot of my report .

Forgot to mention.

Please use
setTimeout('promptAction(\'finish\')', 100); instead of
setTimeout('oCVRS.promptAction(\'finish\')', 100);

Hi,

Try using prompt macro in query filter

[Date Field] = #prompt('Promptparameter', 'date', '_add_days({sysdate}, -1)')#


The macro will return currentdate -1 when the report is first run.

Thanks,
Gopinath.M

Thanks Guys.

I don't know where am i going wrong . Its no working on my end.

R Ambre : Could you please send me the XML of your sample report?

Hi,

Please check attached XML

All the suggestions here are great. I personally would try to avoid the javascript one - you'll have to maintain it, and there's a way without it. The macro solution is good but you may experience formatting issues. They can be solved, of course. Have you tried the macro solution? What was the outcome?

Thanks R Ambre. It's working now.

I haven't  tried the macro solution. Will try and update .