I have put this in the Data Filter expression and it causes error when running the report. What I'm trying to achieve here is that when the Force Expired column has a value of Force then the action on the two other columns such as ([Policy Expiration Date] and [Policy Effective Date] should use the logic used against the current_date.
case when [Force Expired] = 'Inforce' then ([Policy Expiration Date] >= current_date and [Policy Effective Date] <= current_date) else end 2 Answers
Cognos is finicky about the exact syntax of conditionals in filters. I generally avoid CASE statements in filters for this reason. You can convert your filter to one of these two syntaxes instead:
IF ([Force Expired] = 'Inforce') THEN ([Policy Expiration Date] >= current_date and [Policy Effective Date] <= current_date) ELSE (1=1) ([Force Expired] = 'Inforce' AND [Policy Expiration Date] >= current_date AND [Policy Effective Date] <= current_date) OR [Force Expired] <> 'Inforce' If you insist on using a CASE statement then I believe you can put parentheses around your WHEN clause and provide a result for ELSE and it should work:
CASE WHEN ([Force Expired] = 'Inforce') THEN ([Policy Expiration Date] >= current_date AND [Policy Effective Date] <= current_date) ELSE (1=1) END For some reason, Cognos insists on an ELSE clause.
when you use case you have to specify what to do when the requirement is met - not to give another condition. something like: case when [bla] = "foo" then "return_value" else "nothing" end remember -whatever you write in a calculated vlue in a data item is translated to sql, and that's precisely how it also goes in sql...
1
