SSRS Tablix筛选重复EmpID时仅保留最大HID行的实现方法

针对同一EmpID对应多条记录、仅需保留自增ID（RecordID/HID）最大的最新记录的需求，提供两种可落地的实现方式：

方案1：数据集SQL层修改（优先推荐） #

直接在查询阶段过滤重复数据，无需改动报表Tablix现有样式、绑定逻辑，性能更高，后续统计不会出现隐藏行计入聚合的问题。
核心逻辑是用ROW_NUMBER()窗口函数按EmpID分区，按HID倒序排序，每个分区仅保留序号为1的行（即HID最大的最新记录），修改后的完整查询代码如下：

WITH FilteredAbsences AS (
    SELECT        
        Absences.Type, 
        humres.fullname, 
        humres.res_id, 
        Absences.StartDate, 
        DATEADD(dd, - (DATEPART(dw, Absences.StartDate) - 2), Absences.StartDate) AS FirstDayOfWeek, 
        Absences.FreeTextField_01 AS Monday,
        Absences.FreeTextField_02 AS Tuesday, 
        Absences.FreeTextField_03 AS Wednesday, 
        Absences.FreeTextField_04 AS Thursday, 
        Absences.FreeTextField_05 AS Friday, 
        Absences.HID,
        CASE 
            WHEN Absences.FreeTextField_01 <> 'Office' AND DATENAME(weekday, GETDATE()) = 'Monday' THEN Absences.FreeTextField_01 
            WHEN Absences.FreeTextField_02 <> 'Office' AND DATENAME(weekday, GETDATE()) = 'Tuesday' THEN Absences.FreeTextField_02 
            WHEN Absences.FreeTextField_03 <> 'Office' AND DATENAME(weekday, GETDATE()) = 'Wednesday' THEN Absences.FreeTextField_03 
            WHEN Absences.FreeTextField_04 <> 'Office' AND DATENAME(weekday, GETDATE()) = 'Thursday' THEN Absences.FreeTextField_04 
            WHEN Absences.FreeTextField_05 <> 'Office' AND DATENAME(weekday, GETDATE()) = 'Friday' THEN Absences.FreeTextField_05 
            ELSE '' 
        END AS Status,
        ROW_NUMBER() OVER (PARTITION BY humres.res_id ORDER BY Absences.HID DESC) AS RowRank
    FROM humres 
    LEFT OUTER JOIN Absences ON humres.res_id = Absences.EmpID
    WHERE        
        (humres.emp_stat = 'A') 
        AND (humres.freefield16 = 0) 
        AND (Absences.StartDate BETWEEN DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) AND DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 4)) 
        AND (NOT (humres.res_id IN (1, 248)))
        AND (humres.emp_type = 'E') 
        AND (humres.blocked = 0) 
        AND (Absences.Type = 161) 
        AND (
            CASE 
                WHEN Absences.FreeTextField_01 <> 'Office' AND DATENAME(weekday, GETDATE()) = 'Monday' THEN Absences.FreeTextField_01 
                WHEN Absences.FreeTextField_02 <> 'Office' AND DATENAME(weekday, GETDATE()) = 'Tuesday' THEN Absences.FreeTextField_02 
                WHEN Absences.FreeTextField_03 <> 'Office' AND DATENAME(weekday, GETDATE()) = 'Wednesday' THEN Absences.FreeTextField_03 
                WHEN Absences.FreeTextField_04 <> 'Office' AND DATENAME(weekday, GETDATE()) = 'Thursday' THEN Absences.FreeTextField_04 
                WHEN Absences.FreeTextField_05 <> 'Office' AND DATENAME(weekday, GETDATE()) = 'Friday' THEN Absences.FreeTextField_05 
                ELSE '' 
            END <> ''
        )
)
SELECT Type, fullname, res_id, StartDate, FirstDayOfWeek, Monday, Tuesday, Wednesday, Thursday, Friday, HID, Status
FROM FilteredAbsences
WHERE RowRank = 1
ORDER BY FirstDayOfWeek, HID DESC

针对给出的示例数据，执行后EmpID=102将仅保留RecordID=10004的OUT状态行，完全匹配需求。

方案2：Tablix层筛选（无需修改查询） #

如果不想改动现有数据集查询，可以直接在Tablix行组上设置规则实现效果，操作步骤如下：

• 选中目标Tablix，在设计界面下方的「行组」面板找到默认的(详细信息)组，右键打开「组属性」
• 在「常规」选项卡下添加分组条件，分组字段选择EmpID（员工ID字段）
• 切换到「排序」选项卡，添加排序规则：排序字段选择HID（自增ID字段，对应示例中的RecordID），排序顺序设为降序
• 切换到「可见性」选项卡，选择「按组实例显示/隐藏」，设置显示值为1，即仅展示组内排序后的第一行（HID最大的记录）
• 保存设置后预览即可得到预期效果

注意：该方案是在报表层隐藏重复行，若Tablix存在计数、求和等聚合计算，需要手动调整聚合范围排除隐藏行，否则统计结果会包含被隐藏的重复数据。

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