CODEWITHSUNDEEP
cognoscognos-8
jiwon chang
jiwon chang

Reputation: 21

Cognos Report Studio: CASE and IF Statements

I'm very new in using Cognos report studio and trying to filter some of the values and replace them into others.

I currently have values that are coming out as blanks and want to replace them as string "Property Claims"

what i'm trying to use in my main query is

CASE WHEN [Portfolio] is null then 'Property Claims' ELSE [Portfolio]

which is giving me an error. Also have a different filter i want to put in to replace windscreen flags to a string value rather than a number. For example if the flag is 1 i want to place it as 'Windscreen Claims'.

if [Claim Windscreen Flag] = 1 then ('Windscreen') Else [Claim Windscreen Flag]

None of this works with the same error....can someone give me a hand?

Upvotes: 1

Views: 2779

Answers (2)

dougp
dougp

Reputation: 3087

Your first CASE statement is missing the END. The error message should be pretty clear. But there is a simpler way to do that:

coalesce([Portfolio], 'Property Claims')

The second problem is similar: Your IF...THEN...ELSE statement is missing a bunch of parentheses. But after correcting that you may have problems with incompatible data types. You may need to cast the numbers to strings:

case
  when [Claim Windscreen Flag] = 1 then ('Windscreen')
  else cast([Claim Windscreen Flag], varchar(50))
end

In future, please include the error messages.

Upvotes: 1

VAI Jason
VAI Jason

Reputation: 544

it might be syntax

  • IS NULL (instead of = null)
  • NULL is not blank. You might also want = ' '
  • case might need an else and END at the bottom
  • referring to a data type as something else can cause errors. For example a numeric like [Sales] = 'Jane Doe'

For example (assuming the result is a string and data item 2 is also a string),

case
when([data item 1] IS NULL)Then('X')
when([data item 1] = ' ')Then('X')
else([data item 2])
end

Also, if you want to show a data item as a different type, you can use CAST

Upvotes: 0

Related Questions