SSRS evaluate column in fill expression

Question

I have a table in SSRS, Grouped on customer number. I need to evaluate the same column within the fill expressions.

The evaluation should be - where if the ABTAX for the two different customers is the same and the AIMAXO are also the same then fill red, else leave column white.

I've tried the below expression but it just fills the whole column red.

=iif(Fields!ABTAX.Value = Fields!ABTAX.Value and (Fields!AIMAXO.Value = Fields!AIMAXO.Value), "RED","WHITE")

The Data:

CustomerNo     ABTAX                AIMAXO
--------------------------------------------
999991       00592235 COF           235000
999992       00592235 COF           235000

You are currently only checking the two values against themselves, not against any other data (effectively `if (1=1 and 2=2)`). Can you share your dataset and report layout with us? — Jonnus, Dec 08 '15 at 10:11
Are you able to edit the underlying SQL? This would be much easier to calculate in SQL than in SSRS — Jonnus, Dec 08 '15 at 10:38

score 0 · Accepted Answer · answered Dec 08 '15 at 10:54

Assuming you can alter the underlying SQL I would create a flag to show the duplicate. A query like this would raise the correct flags*

SELECT 
    A.CustomerNo, 
    ABTAX, 
    AIMAXO, 
    CASE WHEN B.CustomerNo = A.CustomerNo THEN 1 ELSE 0 END AS Duplicate
FROM @Temp A
LEFT JOIN (
    SELECT CustomerNo FROM @Temp
    WHERE ABTAX = (
        SELECT ABTAX
        FROM @Temp
        GROUP BY ABTAX,AIMAXO
        HAVING COUNT(*) > 1)
    AND AIMAXO =(
        SELECT AIMAXO
        FROM @Temp
        GROUP BY ABTAX,AIMAXO
        HAVING COUNT(*) > 1)
ON A.CustomerNo = B.CustomerNo

Using this source dataset

CustomerNo  ABTAX         AIMAXO
999991      00592235 COF  235000
999992      00592235 COF  235000
999993      00592236 COF  235000
999994      00592235 COF  235001

Will return the following dataset with flags

CustomerNo  ABTAX         AIMAXO  Duplicate
999991      00592235 COF  235000  1
999992      00592235 COF  235000  1
999993      00592236 COF  235000  0
999994      00592235 COF  235001  0

You can then use this duplicate flag to colour the cells appropraitely

*(I'm willing to admit there may be a more efficient way of coding it)

SSRS evaluate column in fill expression

1 Answers1