如何解决SQL Reporting Builder中报表HDD字段值重复问题？

问题根源 #

你的查询同时关联了v_GS_PHYSICAL_MEMORY（单台机器可能有多条内存记录）和v_GS_PHYSICAL_DISK（单台机器可能有多条磁盘记录），两个表的多记录形成笛卡尔积，导致磁盘信息跟着内存条数重复输出。

解决方法 #

方法一：SQL查询层面聚合磁盘信息

将同一机器的所有磁盘信息合并成单条记录，避免重复。根据SQL Server版本选择对应方式：

方案1：SQL Server 2017及以上（使用STRING_AGG）

SELECT   
     SYS.Netbios_Name0 AS 'Netbios Name'
    ,Versions.Model0 AS 'Model'
    ,Versions.UserName0 AS 'User'
    ,FORMAT(USR.LastConsoleUse0, 'dd, MMMM yyyy') AS 'lastLogonTime'
    , NewBios.BIOSVersion0 AS 'Current Bios Version'
    , NewBios.SerialNumber0 AS 'Current Bios SerialNumber'           
    , NewProcessor.Name0 AS 'Current CPU Name'
    , CAST(CAST(NewMem.TotalPhysicalMemory0 AS bigint) / 1048576 AS numeric) AS 'Current Physical Memory (GB)'
    , NewPHMEM.DeviceLocator0 AS 'Current Device Locator'
    , NewPHMEM.Manufacturer0 AS 'Current Manufacturer mem'
    , NewPHMEM.PartNumber0 AS 'Current Part Number'
    , NewPHMEM.SerialNumber0 AS 'Current Serial Number mem'
    -- 聚合磁盘信息，用分号分隔
    , STRING_AGG(CONCAT(NewDisk.Model0, ' (', NewDisk.SerialNumber0, ', ', CAST(CAST(NewDisk.Size0 AS bigint)/1073741824 AS numeric), 'GB)'), '; ') AS 'HDD Info'
FROM 
        CM_IN1.dbo.v_R_System  SYS
        LEFT JOIN CM_IN1.dbo.v_GS_COMPUTER_SYSTEM Versions
            ON Sys.ResourceID = Versions.ResourceID 
        LEFT JOIN CM_IN1.dbo.v_GS_PC_BIOS  NewBios 
                ON SYS.ResourceID = NewBios.ResourceID
        LEFT JOIN CM_IN1.dbo.v_GS_PROCESSOR NewProcessor 
                ON SYS.ResourceID = NewProcessor.ResourceID
        LEFT JOIN CM_IN1.dbo.v_GS_PHYSICAL_MEMORY NewPHMEM
                ON Sys.ResourceID = NewPHMEM.ResourceID
        LEFT JOIN CM_IN1.dbo.v_GS_X86_PC_MEMORY NewMem
                ON Sys.ResourceID = NewMem.ResourceID
        LEFT JOIN CM_IN1.dbo.v_GS_SYSTEM_CONSOLE_USER USR
                ON USR.ResourceID = SYS.ResourceID
        LEFT JOIN CM_IN1.dbo.v_GS_PHYSICAL_DISK NewDisk
                ON Sys.ResourceID = NewDisk.ResourceID
GROUP BY 
     SYS.Netbios_Name0
    ,Versions.Model0
    ,Versions.UserName0
    ,FORMAT(USR.LastConsoleUse0, 'dd, MMMM yyyy')
    , NewBios.BIOSVersion0
    , NewBios.SerialNumber0           
    , NewProcessor.Name0
    , CAST(CAST(NewMem.TotalPhysicalMemory0 AS bigint) / 1048576 AS numeric)
    , NewPHMEM.DeviceLocator0
    , NewPHMEM.Manufacturer0
    , NewPHMEM.PartNumber0
    , NewPHMEM.SerialNumber0

方案2：SQL Server 2016及以下（使用STUFF+FOR XML PATH）

替换上述查询中的聚合部分为：

, STUFF((SELECT '; ' + CONCAT(Model0, ' (', SerialNumber0, ', ', CAST(CAST(Size0 AS bigint)/1073741824 AS numeric), 'GB)')
         FROM CM_IN1.dbo.v_GS_PHYSICAL_DISK d
         WHERE d.ResourceID = SYS.ResourceID
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'HDD Info'

方法二：报表层面隐藏重复项

无需修改SQL，直接在Reporting Builder中设置：

• 选中HDD列的单元格，右键选择「文本框属性」
• 切换到「可见性」选项卡，选择「显示或隐藏基于表达式」
• 输入表达式（根据实际字段名调整）：

  =IIF(Previous(Fields!Current_Model_Disk.Value) = Fields!Current_Model_Disk.Value AND Previous(Fields!Current_Serial_Disk.Value) = Fields!Current_Serial_Disk.Value, True, False)
  

• 确定后，同一磁盘信息仅首次显示，后续重复项自动隐藏。

额外优化建议

如果希望内存和磁盘信息分别清晰展示，可以在报表中按「计算机名称」分组，内存和磁盘各自设置子组，彻底避免交叉重复的问题。

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