Concur / Cognos Report Studio: Filter with options for timeframe always using timepropmt values?

Question

I am working with Concurs IBM Cognos integrated solution. The data i am working with is travel expense report data. My goal for this report is to have a prompt page that allows the user to select the timeframe from which the report should pull the data. Since this report may also be scheduled to run on a regular basis, it should not only include a daterange prompt (or in this case two date prompts), but also options for relative timeframes like "Last year", "Last 6 months" and "Year to date".

So far i have set up a prompt page using two dateprompts ("TravelDateIntervallStart" and "TravelDateIntervallEnd") and two value prompts. Only one of these value prompts is relevant for the timeframe options though. The idea is to have the dateprompts in place for the user to run ad hoc reports for a specific timeframe. The value prompt ("RelativeTimePrompt") should then provide the options to use a relative timeframe depending on when the report is scheduled to run, in addition to the "SelectTimeFrame" option where the date prompts are used. I have set up the options accordingly within the value prompt and built a filter as follows:

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] >= _add_years (current_date, -1) AND [Departure Date/Time] >= _add_years (current_date, -1))

WHEN 'Last6m' THEN ([Arrival Date/Time] >= _add_months (current_date, -6) AND [Departure Date/Time] >= _add_months (current_date, -6))

WHEN 'Year2Date' THEN (_year([Arrival Date/Time]) = _year (current_date) AND _year([Departure Date/Time]) = _year (current_date))

END

When "Year to date" ("Year2Date") is selected in the value prompt, then it throws an error:

"RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'. CAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:"[some date and number]

Ofc i googled the error, but wasnt able to find anything applicable in my case. Is there anything obvious that might be causing an error with the "Year to date" filter?

The error basically means you need to look into the back end log to find the real error. I guess you don't have access to the back end log. What you need to do is just build this expression up bit by bit to work out the issue. Don't code the entire thing and expect it to work. In the end you may need to use a token prompt to generate the filter you require — Nick.McDermaid
You are right, i have no access to the back end log. Thanks for the tip with building up the expression bit by bit though - the year to date part really is the culprit, and more specifically the function to get the year from the Arrival or Departure Date/Time field. Both the "_year([Arrival Date/Time])" or "year([Arrival Date/Time])" and the "extract(year, [Arrival Date/Time])" function through an error as soon as they are used in the filter. I do not know why though. — Torric
I think the 'year' function isn't prefaced with an underscore like some of the other functions. Try removing the underscore. — Johnsonium
Yes - you again need to break it down further an learn the correct syntax by trying expressions as columns and see what they return. — Nick.McDermaid

Daniel Wagemann Daniel Wagemann · Accepted Answer · 2019-05-10T14:11:54

In a new report, bring in the date fields and take the filter expression out of the case. So just _year([Arrival Date/Time]) = _year (current_date) AND _year([Departure Date/Time]) = _year (current_date) to see if that still fails.

If it does you could try changing it to:

extract(year,[Arrival Date/Time]) = extract(year, current_date) AND extract(year,[Departure Date/Time]) = extract(year,current_date)

or

extract(year,[Arrival Date/Time]) = #
timeStampMask($current_timestamp,'YYYY')
# AND extract(year,[Departure Date/Time]) = #
timeStampMask($current_timestamp,'YYYY')
#

Concur / Cognos Report Studio: Filter with options for timeframe always using timepropmt values?

1 Answers