Value Prompt - Current date

[cognosun]

Hi Gurus,

Is it possible to display 12 dates in the value prompt drop-down...not using any model/query item, i.e. relying completely on current_date function.

Like if today user is running the report, he should see drop-down values as...

First value should be 2012-10-18 second value should be 2012-09-18....upto 2011-11-18 ( 12th month from current date).

Please suggest.

Many thanks,
Joys

[blom0344]

So why would you disqualify a prompt query? Should be relatively using a calendertable and the right filters.  The current_date function is a Cognos SQL function, which would normally be associated WITH a query!

[cognosun]

Query will be there obviously...associated to value prompt.

And for various reasons we decided not to use or manipulate date related model items at prompt level.

My problem is...I created a query with current_date and associated this query to value prompt...and you can guess that I can able to see current_date value in the drop down...say Oct'19 2012, but our requirement is, if we want to see Sep'19 2012, Aug'19'2012-----Nov 19'2012...like that 12 dates...

I mean just navigating back, using _add_months and _add_years functions.

But I'm confused with the technique...like how shall I proceed :-)

[blom0344]

Query will be there obviously...associated to value prompt.

And for various reasons we decided not to use or manipulate date related model items at prompt level.

I do not understand the problem with manipulating prompt related queries. They are an independent part of the reports, the only formal requirement would be speed..

As Report Studio allows for unions of sets you could build the prompt query as a set of 12 subqueries (example in SQL):

select current_date from ...
union all
select _add_months(current_date,-1) from ...
union all
select _add_months(current_date,-2) from ...

etc..

Never tried whether cognos supports a set of 12, but as each subquery fetches 1 row, it will be a painless query

[cognosun]

Report studio allowed 12 unions all's :-)

Thanks blom !!!