Display only last 3 years in the prompt

[Jeka]

Hello,
Using relational package to build reports.

Im building a prompt, and I would only want the user to select from last 3 years.
I'm using [Year](YYYY)for prompt from FM package.
Currently [Year] is from 1950 to 2050. At the prompt user sees all the years.

The user must be able to select only from last 3 years (YY). 08, 07, 06.

Thank-you!

[bloggerman]

Try putting a filter in the query associated with the prompt.

[JGirl]

Include the following filter in your prompt query:
[Year] between extract(year,current_date)-2 and extract(year,current_date)

This assumes your [Year] column is an int.  If its a varchar col you may also need to add some casts into the filter definition.

Hope this helps.
J

[blom0344]

Be aware that if you modify an existing prompt and caching is enabled then Cognos will keep returning the unfiltered set. It takes some doing to force Cognos to execute the query as a new one instead of returning a cached set of values..

[Sunchaser]

Hi,

For exemple, for the prompt, I'll use a SQL like:

Code Select Expand


SELECT TO_CHAR (ADD_MONTHS (SYSDATE, (a.nums * -12)), 'YY') display_year
  FROM table_numbers a
WHERE a.nums BETWEEN 0 AND 2

where "table_numbers" is a small table with only one column ("nums"), filled with numbers from 0 to 10 (for example).

Hope it could help.
vinc.