如何在SSRS报表中无需排序整列高亮重复物料编号？

问题背景 #

现有一款查看物料清单（BOM）的SSRS报表，展示涉及的物料、所属BOM及其他相关信息。需求如下：

• 无需按物料编号列排序，需高亮该列中所有重复值（即使其他列信息不同，只要物料编号重复就需高亮，例如物料编号"001468"）
• 报表必须按Level层级排序（先显示所有顶层物料，再依次显示下层物料），因此无法使用SSRS的Previous函数
• 重复项需设置为加粗红色显示，支持多个重复项的场景

现有递归CTE查询代码 #

2022年5月8日补充的当前使用的SQL查询：

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_]

解决方案 #

方法一：在SQL查询中添加重复标记字段 #

修改现有查询，新增统计物料编号出现次数的子查询，并添加标记字段，后续在SSRS中直接基于该字段设置格式：

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_]
),
-- 统计每个物料编号的出现次数
ItemOccurrences AS (
    SELECT [No_], COUNT(*) AS Count
    FROM CTERBOM
    GROUP BY [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],
-- 标记是否为重复项
CASE WHEN ItemOccurrences.Count > 1 THEN 1 ELSE 0 END AS IsDuplicate

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

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

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

SSRS格式设置

选中报表中[Item No.]对应的文本框，分别设置以下表达式：

• 字体颜色：=IIF(Fields!IsDuplicate.Value = 1, "Red", "Black")
• 字体粗细：=IIF(Fields!IsDuplicate.Value = 1, "Bold", "Normal")

方法二：直接在SSRS中使用聚合函数判断 #

无需修改SQL查询，直接在SSRS文本框的格式表达式中，通过聚合函数统计当前物料编号在整个数据集内的出现次数：
选中[Item No.]文本框，设置：

• 字体颜色：=IIF(Count(Fields!Item_No.Value, "你的数据集名称") > 1, "Red", "Black")
• 字体粗细：=IIF(Count(Fields!Item_No.Value, "你的数据集名称") > 1, "Bold", "Normal")

注意：将表达式中的"你的数据集名称"替换为报表实际使用的数据集名称，该方式不受报表排序规则影响，能准确识别所有重复的物料编号。

内容的提问来源于stack exchange，提问作者adhocEY