SSRS中基于条件日计数计算总超额容量的技术问题

在SSRS报表中，行组为Location、列组为date，需在总计行内计算每日超额容量的总和。现有表达式在date列分组下可正常计算单日超额容量（当VisitsCount>7时，结果为VisitsCount-7，否则为0），但在总计行时，会先对全周期VisitsCount求和再减7，而非累加每日已计算的超额值。

数据集查询 #

SELECT 
    L.Location
  , D.[date]
  , COUNT(DISTINCT CAST(V.VisitID AS VARCHAR(10))+V.Location+V.[Room-Bed]) AS VisitsCount
FROM dbo.DateTable(@StartDate, @EndDate) AS D  
     CROSS JOIN (
    SELECT DISTINCT Location FROM dbo.vHIMOverFlowBedReport2) 
    AS L LEFT JOIN  dbo.vHIMOverFlowBedReport2 AS V ON D.[date]      BETWEEN 
    V.EffectiveDate AND ISNULL(V.ServiceEndDate, @EndDate)                      AND V.Location = L.Location
WHERE V.EffectiveDate IS NULL OR
      (V.EffectiveDate <= @EndDate OR
      V.ServiceEndDate >= @StartDate OR
      V.ServiceEndDate IS NULL)
GROUP BY 
      L.Location
      , D.[date]
ORDER BY L.Location, D.[date]

超额容量（OverCap）新增列测试 #

新增OverCap列的数据集查询 #

SELECT 
      x.Location
     , x.[date]
     , x.VisitsCount
    , OverCap = IIF(VisitsCount-7 <0 ,0, VisitsCount-7)
    FROM (
            SELECT 
            L.Location
            , D.[date]
            , COUNT(DISTINCT CAST(V.VisitID AS VARCHAR(10))+V.Location+V.[Room-Bed]) AS VisitsCount
            FROM dbo.DateTable(@StartDate, @EndDate) AS D  
            CROSS JOIN (
            SELECT DISTINCT Location FROM dbo.vHIMOverFlowBedReport2) 
            AS L LEFT JOIN  dbo.vHIMOverFlowBedReport2 AS V ON D.[date]      BETWEEN 
            V.EffectiveDate AND ISNULL(V.ServiceEndDate, @EndDate)                      AND V.Location = L.Location
            WHERE V.EffectiveDate IS NULL OR
                  (V.EffectiveDate <= @EndDate OR
                  V.ServiceEndDate >= @StartDate OR
                  V.ServiceEndDate IS NULL)
            GROUP BY 
                  L.Location
                  , D.[date]
            ) x
      ORDER BY x.Location, x.[date]

报表按Location（行）和date（列）分组，Location组外有合并位置的总计行。

测试结果说明 #

• 使用OverCap = IIF(VisitsCount-7 <0 ,0, VisitsCount-7)时，LOC3-4的结果为129
• 使用OverCap = IIF(Location = 'LOC3' OR Location = 'LOC4', VisitsCount,0)时，LOC3-4的结果为34
• 使用OverCap = IIF((Location = 'LOC3' OR Location = 'LOC4') AND VisitsCount > 7, VisitsCount,0)时，LOC3-4的结果为24

已成功使用的表达式 #

分组位置的表达式 #

• 每日访客数（DailyVisitorCount）及总访客数：

=Sum(Fields!VisitsCount.Value)

• 每日超额容量（DailyOverCapacityCount）及分组总超额容量：

=SWITCH(
Fields!Location.Value = "LOC1" AND Fields!VisitsCount.Value > 24, SUM(Fields!VisitsCount.Value - 24), 
Fields!Location.Value = "LOC2" AND Fields!VisitsCount.Value > 16, SUM(Fields!VisitsCount.Value - 16), 
Fields!Location.Value = "LOC3" AND Fields!VisitsCount.Value > 7, SUM(Fields!VisitsCount.Value - 7), 
Fields!Location.Value = "LOC4" AND Fields!VisitsCount.Value > 7, SUM(Fields!VisitsCount.Value - 7),
Fields!Location.Value = "LOC5" AND Fields!VisitsCount.Value > 11, SUM(Fields!VisitsCount.Value - 11),  
   True, 0)

• 平均值计算：在上述表达式后追加/CountDistinct(Fields!date.Value)

合并位置（Location组外）的表达式 #

• 每日访客数及总访客数：

=IIF(Fields!Location.Value = "LOC3" OR Fields!Location.Value = "LOC4", Sum(Fields!VisitsCount.Value), 0)

• 每日总超额容量（仅单日有效，全周期总计无效）：

=IIF(SUM(IIF(Fields!Location.Value = "LOC3" OR Fields!Location.Value = "LOC4",Fields!VisitsCount.Value,0)) > 7,
SUM(SUM(IIF(Fields!Location.Value = "LOC3" OR Fields!Location.Value = "LOC4",Fields!VisitsCount.Value,0))-7),
0)

所需表达式解决方案 #

1. 全周期总超额容量（累加每日超额容量总计） #

通过指定date列组作为计算作用域，先计算单日LOC3+LOC4的超额值，再累加所有日期的结果：

=SUM(
    IIF(
        SUM(IIF(Fields!Location.Value = "LOC3" OR Fields!Location.Value = "LOC4", Fields!VisitsCount.Value, 0)) > 7,
        SUM(IIF(Fields!Location.Value = "LOC3" OR Fields!Location.Value = "LOC4", Fields!VisitsCount.Value, 0)) - 7,
        0
    ),
    "date"  -- 替换为报表中实际的date列组名称
)

2. 全周期日均超额容量（每日超额容量总计的平均值） #

基于全周期总超额容量，除以周期内的日期总数即可：

=SUM(
    IIF(
        SUM(IIF(Fields!Location.Value = "LOC3" OR Fields!Location.Value = "LOC4", Fields!VisitsCount.Value, 0)) > 7,
        SUM(IIF(Fields!Location.Value = "LOC3" OR Fields!Location.Value = "LOC4", Fields!VisitsCount.Value, 0)) - 7,
        0
    ),
    "date"
) / COUNT(DISTINCT Fields!date.Value)

或直接取每日超额值的平均值：

=AVG(
    IIF(
        SUM(IIF(Fields!Location.Value = "LOC3" OR Fields!Location.Value = "LOC4", Fields!VisitsCount.Value, 0)) > 7,
        SUM(IIF(Fields!Location.Value = "LOC3" OR Fields!Location.Value = "LOC4", Fields!VisitsCount.Value, 0)) - 7,
        0
    ),
    "date"
)

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