I am building the filter for a report in cognos report studio that uses parameters from a prompt page. The idea is to give the user who will be running the report the option to specify a start date and an end date (and select only the business trips within that timeframe) and also have the options of selecting relative timeframes in place in case the report will be scheduled.
I had a few syntax errors, which i... adressed, or at least i thought so. When verifying the filter, it even opens the prompt page, but after i select a value it spits out errors like:
UDA-QOS-0006 Error UDA-SQL-0219 The function "getdate" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported
QE-DEF-0459 CCLException RQP-DEF-0177 An error occured while perfomring the operation 'sqlPrepareWithOptions' status='-126'. UDA-SQL-0219 The function "getdate" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported
RSV-VAL-0004 Unable to find query information for the item Total Hours Abroad for Report.
The last one apparently is repeated for each and every data item in the report. The Filter expression i am using is below:
CASE ?RelativeTimePrompt?
WHEN 'SelectTimeframe' THEN
([Arrival Date/Time] between ?TravelDateIntervallStart? and ?TravelDateIntervallEnd?
AND
[Departure Date/Time] between ?TravelDateIntervallStart? and ?TravelDateIntervallEnd?)
WHEN 'Last12m' THEN ( [Arrival Date/Time] between _add_days(getdate (),-365) and (getdate ()) AND [Departure Date/Time] between _add_days(getdate (),-365) and (getdate ()))
WHEN 'Last6m' THEN ( [Arrival Date/Time] between _add_days(getdate (),-183) and (getdate ()) AND [Departure Date/Time] between _add_days(getdate (),-183) and (getdate ()))
WHEN 'YTD' THEN ( [Arrival Date/Time] between _add_days(getdate (),((_day_of_year(getdate ())-
1)*-1)) and (getdate ()) AND [Departure Date/Time] between _add_days(getdate (),((_day_of_year(getdate ())-
1)*-1)) and (getdate ()))
END
"When verifying the filter" -- you're actually running the "validate" function on the entire report. Although the button is in the Filter Expression dialog, it is not related to the filter expression you are currently viewing.
Take the errors in the order they are listed. Fixing the first error will often make all of the other errors disappear.
Total Hours Abroad for Report.Also, have you considered simplifying your case statement?
WHEN 'Last12m' THEN [Arrival Date/Time] >= _add_years (current_date, -1)
WHEN 'Last6m' THEN [Arrival Date/Time] >= _add_months (current_date, -6)
WHEN 'YTD' THEN _year([Arrival Date/Time]) = _year (current_date)