column value comparison and fill color change based on the expression

Question

I am comparing 2 similar tables from different data warehouses and writing a report to highlight the discrepancies based on the differences. I would like to highlight the column values of the 2 fields if they are different. So, I am trying to write the Fill COLOR Expression which can change the color based on the value in the column.

I have tried writing some expressions.

Below are the examples:

I tried :

=IIF(Fields!DB1.Value=”NULL”,”Red” ,”White”) Or IIF(Fields!Db2.Value=”NULL”,”Red” ,”White”) Or IIF(Fields!DB1.Value=Fields!DB2.Value,"NO Color","Red")

--Not Working

=IIF(Fields!DB1_Number.Value<>Fields!DB2_Number.Value,"NO Color","Red")

-- Not Handling NULL

There are no error. It is just that the code is not behaving as per the intention.

Isnothing is equivalent to checking for NULL.. test =isnothing(Fields!DB1.Value) to see if it returns a 1 for null values.. if it does.. then it is working. — Harry
It's not clear what you're intending to get. The first example you gave has the OR statements outside the IIf functions which means you are just comparing strings and the result would be a boolean, not a color. You'd want to put all the conditions inside one IIf statement. — StevenWhite
an alternative approach would be to derive the color in your dataset by doing all the comparisons there (case statements ??) and assign a color according. Then it is only a matter of using the derived column color as your background color on the report! — Harry

Strawberryshrub Strawberryshrub · Accepted Answer · 2019-05-22T04:41:00

Just use the second expression and check for Null before:

=IIF(IsNothing(Fields!DB1_Number.Value) And IsNothing(Fields!DB2_Number.Value), "Red", 
     IIF(Fields!DB1_Number.Value <> Fields!DB2_Number.Value,
         "White",
         "Red")
     )

column value comparison and fill color change based on the expression

1 Answers