If you are unable to create a new account, please email support@bspsoftware.com

 

Issues creating static prompts for dynamic date range (last 12 months, YTD,

Started by chape, 30 Jan 2019 07:37:50 AM

Previous topic - Next topic

chape

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!

CognosPaul

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')#