创建按时间展示未结工单数量的交叉表/矩阵KPI报表

我之前刚好处理过几乎一模一样的需求，给你分享两种可行的方案，都能帮你生成适合交叉表的数据源，不用单独定义12个数据集。

方法一：用CTE生成日期列表（推荐，维护更简单） #

这种方法先自动生成过去12个月的月末日期，再关联工单表统计每个日期的未结数量，不用手动写12次重复逻辑。

直接用这个SQL作为报表数据源：

WITH MonthEndDates AS (
    -- 生成过去12个月的月末日期
    SELECT 
        DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - n + 1, 0)) AS MonthEndDate
    FROM (
        -- 生成1到12的数字，对应过去12个月
        VALUES (1), (2), (3), (4), (5), (6), 
               (7), (8), (9), (10), (11), (12)
    ) AS Numbers(n)
)
SELECT 
    MED.MonthEndDate,
    -- 提取月份名称，用于交叉表列头
    DATENAME(MONTH, MED.MonthEndDate) AS MonthName,
    -- 提取年份，避免跨年时月份重复
    YEAR(MED.MonthEndDate) AS Year,
    -- 统计未结工单数量：创建日期<=月末，且完成日期>月末或未完成
    COUNT(W.WorkOrderID) AS OpenWorkOrders
FROM MonthEndDates MED
LEFT JOIN WorkOrders W 
    ON W.CreationDate <= MED.MonthEndDate
    AND (W.CompletionDate > MED.MonthEndDate OR W.CompletionDate IS NULL)
GROUP BY MED.MonthEndDate, DATENAME(MONTH, MED.MonthEndDate), YEAR(MED.MonthEndDate)
ORDER BY MED.MonthEndDate

交叉表设置步骤： #

1. 在Report Builder里新建交叉表
2. 把Year拖到列组（如果需要区分跨年的月份，比如2023年12月和2024年12月）
3. 把MonthName拖到列组（放在Year的下方，作为子列组）
4. 把OpenWorkOrders拖到数据区域
5. 行组可以留空，或者添加一个固定的行标签（比如“未结工单总数”），这样报表就会呈现出每月数值横向排列的交叉表样式。

方法二：用UNION合并多日期查询（适合快速验证） #

如果你更倾向于用UNION的思路，可以把每个月末的统计查询用UNION ALL合并起来，生成统一的数据集：

-- 第一个月：当前月的上月末
SELECT 
    DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS MonthEndDate,
    DATENAME(MONTH, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) AS MonthName,
    YEAR(DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) AS Year,
    COUNT(WorkOrderID) AS OpenWorkOrders
FROM WorkOrders
WHERE CreationDate <= DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
  AND (CompletionDate > DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) OR CompletionDate IS NULL)

UNION ALL

-- 第二个月：往前推2个月的月末
SELECT 
    DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0)) AS MonthEndDate,
    DATENAME(MONTH, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0))) AS MonthName,
    YEAR(DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0))) AS Year,
    COUNT(WorkOrderID) AS OpenWorkOrders
FROM WorkOrders
WHERE CreationDate <= DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0))
  AND (CompletionDate > DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0)) OR CompletionDate IS NULL)

-- 继续复制上面的查询，把-1改成-2、-3...直到-11，覆盖过去12个月
ORDER BY MonthEndDate

不过这种方法需要手动复制12次查询，维护起来比较麻烦，所以更推荐第一种CTE的方案。

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