Matt Matt - 5 months ago 24
SQL Question

Highlight Row Differences in SSRS Report

I have an SSRS report that compares duplicate values. I want to compare rows with the same ID value and highlight the columns with differences. Here is an example of my desired report.

enter image description here

What is the best way to achieve this in SSRS?

Here is a simplified example of the query used in my dataset

Select Diffs.ID
, Diffs.Name
, Diffs.Hobby
FROM
(
SELECT a.ID
, a.Name
, a.Hobby
FROM Table1 as a
INNER JOIN Table2 as b
ON a.ID = b.ID
WHERE a.Name <> b.Name
OR a.Hobby <> b.Hobby

UNION

SELECT b.ID
, b.Name
, b.Hobby
FROM Table1 as a
INNER JOIN Table2 as b
ON a.ID = b.ID
WHERE a.Name <> b.Name
OR a.Hobby <> b.Hobby

) AS Diffs
ORDER BY Diffs.ID

Answer

you could create a group on id column (just suppress it, so it does't affect your report) then in background create a formula like

For Name column.

      =iif(CountDistinct(iif(isnothing(Fields!Name.Value) or 
Fields!Name.Value ="","x",Fields!Name.Value),"ID")<> 1 ,"Yellow","Transparent")