All - I have a report which 'requires' a parameter value to filter. I placed a value prompt at the top of the report page, rather than creating a prompt page because I want the user to be able to choose other values and regenerate the report all from one page. Because this parameter is required, Cognos is generating its own prompt page before displaying the report page.
What could prevent this from happening? Conditional formatting, setting a default value, etc?
Any ideas would be great
If I understand the requirements, you want to have an interactive report with the prompt inside the report itself.
If there is a prompt inside the page, the query will wait until the page is loaded, check the prompts for any default or set values, then attempt to run. If it can't retrieve a value from prompts on the page, it will create the prompt page. Knowing this, the first thing to try would be to give the prompt a default value. The filter in the query would be something like:
[Year]=?Par_Year?
and you'd put 2009 in the default value for the prompt.
If for some reason you can't put a default value into the prompt itself, you can use a prompt macro in the query, and put the default value there.
For example:
[Year]=#prompt('Par_Year','integer','extract(year,current_date)')#
Thanks for your response as my next question was going to be how do I dynamically create the current year as the default value. :)
I haven't worked with Macros very much, is there a way to use the current fiscal year? I'm guessing this will be more difficult as companies define their fiscal years all differently. Ours runs from July 1 - June 30.
If it helps, when writing SQL to calculate the current fiscal year, I use a case statement similar to:
case
when extract (month from sysdate) >= 7 then extract (year from sysdate) +1
when extract (month from sysdate) < 7 then extract (year from sysdate)
end
I'm not at a cognos install that I can play with, but I can think of two things to try.
First, place the case statement inside the default parameter of the prompt macro. So:
[Year]=#prompt('Par_Year','integer','case when extract (month from sysdate) >= 7 then extract (year from sysdate) +1 else extract (year from sysdate) end')#
The other thing to try would be to save the case statement inside another data item, and reference that:
[Year]=#prompt('Par_Year','integer','[DynamicFiscalYear]')#
If that doesn't work, try
[Year]=#prompt('Par_Year','token','[DynamicFiscalYear]')#
You could also create your own prompt page that has the same look as the report, where the headings are the same, the prompt is in the same spot, etc. Then change the prompt on your report page to auto-submit (if it isn't already). The 1st time thru the prompt page will be used, but then whenever the user changes the value on the report page that will be used. If the user would hit the "Run" button/triangle they will get the prompt page again, but if they just change the value it will auto-submit without generating the prompt page again.