How to write case when statement

[mp3909]

Cognos Version 11

I have created a crosstab in my report which is based on an underlying query that contains 2 data items - PRODUCT DESCRIPTION and VALUE.

For the VALUE field in my cross tab report, I am creating an Expression for it such that I have written this:

CASE WHEN [Business View].[ETP File].[PRODUCT_DESCRIPTION] contains 'COMMENT' THEN [Business View].[ETP File].[VALUE]

But it is not liking it.
Is there anything wrong with it?

Thank You

[BigChris]

Even if you don't put an ELSE clause in you're still going to need to END the case statement

CASE
  WHEN [Business View].[ETP File].[PRODUCT_DESCRIPTION] contains 'COMMENT' THEN [Business View].[ETP File].[VALUE]
  else NULL
END

[mp3909]

Thank you BigChris.

[MFGF]

A handy tip when creating expressions is to look for the (f) button - usually on the left at the bottom in the expressions dialog. This gives you access to see (and drag in) a very useful set of expressions for functions, summaries, constructs, constants etc.

If you expand the Constructs section and drag in a 'Search case' construct, this is what it puts into the expression editor:

CASE  WHEN <condition> THEN <expression> . . . [ ELSE <expression> ] END

You can then replace the relevant parts with your own items and choose to use or discard the optional parts (in square brackets)

Doing things this way - especially while you are learning the product - can help you avoid issues like this and also educate you on how to code your expressions successfully.

Cheers!

MF.