SSRS Highlight Duplicate Values in Column Across Entire Report

Question

I have an SSRS report that looks at products used in a bill of materials. It lists the items involved, the bill of materials (BOM) they belong to and other various information. I would like to highlight ANY duplicates in the item column without having to sort by item.

Example data:

Level	Item No.	BOM No.	Quantity Per
1	001468	017998	4
1	001850	017998	6
1	017663	017998	2
1	017792	017998	2
2	001468	017663	3

So in the above data, the item # "001468" should get highlighted even though the information in the other columns is different per row.

• I am aware of the "Previous" function but I don't want to re-order the item number column so I don't think using that is an option

• The report sorts by level so that it first lists all the items in the top level (level 1) followed by the lower levels (2, 3, 4 etc...)

• There can be multiple duplicates (not just 2), so any solution will have to factor that in

• Would like the duplicate(s) too be highlighted in Bold Red

If more information is needed please let me know.

Edit: More Details added below 08/05/2022

Here is the code I am working with which begins with a recursive CTE:

WITH CTERBOM AS  
(
    SELECT PBL.[Production BOM No_], PBL.[Version Code], PBL.[No_], PBL.[Quantity per], 1 AS LVL
    FROM [DataBaseName$Production BOM Line] PBL
    LEFT JOIN [DataBaseName$Production BOM Header Extra] PBHEX ON PBHEX.[No_] = PBL.[Production BOM No_]
    WHERE PBL.[Production BOM No_] = '008722' AND PBHEX.[Active Version No_] = PBL.[Version Code]

    UNION ALL

    SELECT PBL2.[Production BOM No_], PBL2.[Version Code], PBL2.[No_], PBL2.[Quantity per], LVL + 1
    FROM [DataBaseName$Production BOM Line] PBL2
    INNER JOIN CTERBOM ON CTERBOM.[No_] = PBL2.[Production BOM No_]
)

SELECT
CTERBOM.[LVL]                           AS [Level],
CTERBOM.[No_]                           AS [Item No.],
CTERBOM.[Production BOM No_]                    AS [Production BOM No.],
CTERBOM.[Quantity per]                      AS [Qty. Per]



FROM CTERBOM
LEFT JOIN [DataBaseName$Production BOM Header Extra] PBHEX ON PBHEX.[No_] = CTERBOM.[Production BOM No_]

GROUP BY
CTERBOM.[LVL],
CTERBOM.[Production BOM No_],
CTERBOM.[No_],
CTERBOM.[Quantity per],

ORDER BY
CTERBOM.[LVL],
CTERBOM.[Production BOM No_],
CTERBOM.[No_]

Answer 1

One way to do this would be to do add a new column to the query, as duplicates = count(*) over (partition by [Item No.]) (insert your actual column name).

Then use that to conditionally highlight the Item No field in the report if the value of the duplicates column is > 1. You would do this by going to the Item No field in the report > properties > fill > fill color > press the expression button and enter something like:

=IIf(Fields!duplicates.Value > 1,"Yellow","Default")

Add this column to your final select:

-- APPROACH 1:

SELECT
CTERBOM.[LVL]                              AS [Level],
CTERBOM.[No_]                              AS [Item No.],
count(*) over (partition by CTERBOM.[No_]) as duplicates,
/* ... */

/* APPROACH 2:
if you can't use approach 1 because of other code not currently 
shown in the question, you can wrap the entire select in 
another select, which will always work:
*/

WITH CTERBOM AS  
(
    SELECT PBL.[Production BOM No_], PBL.[Version Code], PBL.[No_], PBL.[Quantity per], 1 AS LVL
    FROM [DataBaseName$Production BOM Line] PBL
    LEFT JOIN [DataBaseName$Production BOM Header Extra] PBHEX ON PBHEX.[No_] = PBL.[Production BOM No_]
    WHERE PBL.[Production BOM No_] = '008722' AND PBHEX.[Active Version No_] = PBL.[Version Code]

    UNION ALL

    SELECT PBL2.[Production BOM No_], PBL2.[Version Code], PBL2.[No_], PBL2.[Quantity per], LVL + 1
    FROM [DataBaseName$Production BOM Line] PBL2
    INNER JOIN CTERBOM ON CTERBOM.[No_] = PBL2.[Production BOM No_]
)

select  level,
        [Item No.],
        count(*) over (partition by [Item No.]) as duplicates,
        [Production BOM No.],
        [Qty. Per]
from    (
           SELECT
           CTERBOM.[LVL]                      AS [Level],
           CTERBOM.[No_]                      AS [Item No.],
           CTERBOM.[Production BOM No_]       AS [Production BOM No.],
           CTERBOM.[Quantity per]             AS [Qty. Per]
           /* rest of existing query ... */
        ) t;