'data type error' in conditional style trying to set null to behave like zero

[abcuser]

Hi,
I have Cognos 10.2.1 fixpack 6 Report Studio using DQM package accessing Cognos TM1 10.1.1 fixpack 2 cube.

I would like to colour the background of cell with red if Quantity_A is higher then Quantity_B.
I created Conditional Style with expression:
[Query1].[Quantity_A]>[Query1].[Quantity_B]
and set this expression to red color.
Note: This is simple sample. My sample has several conditions separated with AND statement.

Above expression is working fine if Quantity_B is not null. But in my case Quantity_B can also be null. But in this case I need to set null to behave like 0.
I tried this:
[Query1].[Quantity_A]>if([Query1].[Quantity_B] is null) then (0) else ([Query1].[Quantity_B])
but I am getting error:

RSV-VAL-0002 Invalid expression [Query1].[Quantity_A]>if([Query1].[Quantity_B] is null) then (0) else ([Query1].[Quantity_B])
CRX-API-0003 A data type error occurred at or near ...
The operand types ('integer,integer,double') for operator 'Conditional Expression' are not compatible

Just for test I replaced column name with "1":
[Query1].[Quantity_A]>if([Query1].[Quantity_B] is null) then (0) else (1)
and command runs without an error. So "if" statement is correct, just data types are incorrect.

Any idea how to fix this?
Thanks.

[bdbits]

The expression is not quite right. I think you need something like this:

if ([Query1].[Quantity_B] is null) then ([Query1].[Quantity_A]>0) else ([Query1].[Quantity_A]>[Query1].[Quantity_B])

[Lynn]

I think the conditional style expression wants a Boolean true or false result. You might try something like this:

Code Select Expand


(
  [Query1].[Quantity_B] is null
  and
  [Query1].[Quantity_A] > 0
)
or
( 
  [Query1].[Quantity_B] is not null
  and
  [Query1].[Quantity_A] > [Query1].[Quantity_B]
)