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.
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])
I think the conditional style expression wants a Boolean true or false result. You might try something like this:
(
[Query1].[Quantity_B] is null
and
[Query1].[Quantity_A] > 0
)
or
(
[Query1].[Quantity_B] is not null
and
[Query1].[Quantity_A] > [Query1].[Quantity_B]
)