如何在SQL或Report Builder中按国家分组并汇总数据？

我在使用Report Builder制作报表时，想要将同一国家下的月度结果求和后显示在单个单元格中。比如截图里country1下有两个数值1，希望合并成一行，总和显示为2。尝试按Country分组，但视图没变化，请问怎么实现？可以通过Report Builder或SQL查询完成吗？

注：我在Report Builder中使用以下表达式获取星期字段 #

=iif(Fields!Date_WEEKDAY__NUMBER_.Value=1,"Sunday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=2,"Monday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=3,"Tuesday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=4,"Wednesday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=5,"Thrusday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=6,"Friday",
iif(Fields!Date_WEEKDAY__NUMBER_.Value=7,"Saturday","Null")))))))

我的SQL主查询 #

SELECT 
    COUNT([Patient ID]) AS ID, 
    Activity,Date, [Interaction Type], [Adverse Event Occurred], 
    [Product Complaint Occurred], [Cancellation Reason], Created, 
    Status, [Interaction Reason], Country, Drug, 
    [Registered Nurse], [Created by], 
    YEAR(Date) AS [Date Year], 
    DATENAME(MONTH, Date) AS [Date Month], 
    DATENAME(WEEK, Date) AS [Date WEEK], 
    DATENAME(WEEKDAY, Date) AS [Date WEEKDAY],
    DATEPART(WEEKDAY, Date) AS [Date WEEKDAY (NUMBER)]
FROM
    MNZL_Patient_Activities
GROUP BY 
    ID, Activity, Date, [Interaction Type], [Adverse Event Occurred], 
    [Product Complaint Occurred], [Cancellation Reason], 
    Created, Status, [Interaction Reason], Country, Drug, 
    [Registered Nurse], [Created by], YEAR(Date), 
    DATENAME(MONTH, Date), DATENAME(WEEK, Date), 
    DATENAME(WEEKDAY, Date)
HAVING 
    ([Interaction Reason] = N'Medication Administration')
ORDER BY
    DATENAME(WEEK, Date)

解决方法 #

方法一：在Report Builder中实现分组求和 #

• 调整分组维度：不要只按Country分组，要同时按Country+Date Year+Date Month分组（避免跨年同月份数据混淆）。操作：在报表的「行组」面板，右键现有组，选择添加组 > 父组，分组依据选Country，再添加子组以Date Year和Date Month为依据。
• 修改求和表达式：把显示数值的单元格表达式从直接引用Fields!ID.Value改成=Sum(Fields!ID.Value)，这样会自动计算当前分组内的总和。
• 隐藏明细行：如果不需要显示细分的明细数据，右键明细行号，选择行可见性，设置为隐藏，只保留分组汇总行。

方法二：在SQL查询中直接生成汇总数据 #

你的当前SQL因为GROUP BY包含了Date、Date WEEK等细分字段，导致同一国家同月份的数据被拆分成多行。可以修改SQL直接按国家+年月分组求和：

SELECT 
    COUNT([Patient ID]) AS TotalID,
    Country,
    YEAR(Date) AS [Date Year], 
    DATENAME(MONTH, Date) AS [Date Month]
FROM
    MNZL_Patient_Activities
WHERE 
    [Interaction Reason] = N'Medication Administration'
GROUP BY 
    Country, YEAR(Date), DATENAME(MONTH, Date)
ORDER BY
    YEAR(Date), DATENAME(MONTH, Date), Country

如果需要保留其他字段，必须确保这些字段要么在GROUP BY中，要么用聚合函数（比如MAX(Activity)）处理。

额外优化：简化星期字段表达式 #

你当前的嵌套IIF可以换成Switch函数，可读性更强：

=Switch(
    Fields!Date_WEEKDAY__NUMBER_.Value=1, "Sunday",
    Fields!Date_WEEKDAY__NUMBER_.Value=2, "Monday",
    Fields!Date_WEEKDAY__NUMBER_.Value=3, "Tuesday",
    Fields!Date_WEEKDAY__NUMBER_.Value=4, "Wednesday",
    Fields!Date_WEEKDAY__NUMBER_.Value=5, "Thursday",
    Fields!Date_WEEKDAY__NUMBER_.Value=6, "Friday",
    Fields!Date_WEEKDAY__NUMBER_.Value=7, "Saturday",
    True, "Null"
)

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