Apologies if the method is messy. I'm new to cognos. I want to make a 12 month report based on ITIL metrics, where the user can run the report and choose the type of report they want to run (last 12 months, YTD, PYTD). I want these to be static choices, so they don't have to pick the start and end date themselves. One simple option.
I originally thought this would work be designing a filter that uses one parameter for start and end date. Note that the expression is date and time. I don't have one available that's just date.
[Incident open time] between _first_of_month(?p_open_time?) and _last_of_month(_add_months(?p_open_time?),+11))
Then I made static choices for the parameter inside of a value prompt
12 Months ending this month _add_months(sysdate(),-12)
Previous 12 Months _add_months(sysdate(),-11)
YTD _make_timestamp(extract(year,current_date), 1, 1)
Last YTD _make_timestamp((extract(year,current_date)-1), 1, 1)
2 years Previous YTD _make_timestamp((extract(year,current_date)-2), 1, 1)
If I plug these individually into the filter, instead of using a parameter, they work perfectly. However, when I put them in as a parameter in my value prompt I get an error when I go to choose at the prompt.
QE-DEF-0381 Invalid format for prompt 'p_open_time'. Expected format for date: CCYY-MM-DD.
Not sure how to resolve the issue and make it work. Is there a way to pass these choices into the parameter with it ignoring the format? As I said individually these all work if I plug them into the filter manually.
Thanks in advance for your help!
When you use a prompt alias Cognos will add some validation to the parameter. In this case Cognos understands that any value passed to the parameter must be a date because it's inside a date function. The simple solution is to replace the prompt alias with a prompt macro.
Try replacing ?p_open_time? with
#prompt('p_open_time','token')#