SSRS报表如何基于日期参数展示历史销售汇总数据？

一、SQL查询调整（获取汇总数据） #

你的现有SQL用于提取指定日期范围的销售明细，我们可以通过新增汇总数据集或扩展现有查询来获取三个核心指标：

方案1：独立汇总数据集（推荐） #

创建名为ds_SalesSummary的数据集，复用@ItemNo、@StartDate参数，SQL如下：

WITH CTE_SalesHeaderData (DocN, NoSeries, PostingDate)       
AS (
    SELECT [No_], [No_ Series], [Posting Date] FROM [Sales Invoice Header]
    UNION ALL           
    SELECT [No_], [No_ Series], [Posting Date] FROM [Sales Cr_Memo Header]
    UNION ALL           
    SELECT [No_], [No_ Series], [Posting Date] FROM [Service Invoice Header]
    UNION ALL           
    SELECT [No_], [No_ Series], [Posting Date] FROM [Service Cr_Memo Header]
),
CTE_SalesLinesData (DocNo2, ItemNo, Qty)  
AS (
    SELECT [Document No_], [No_], [Quantity] FROM [Sales Invoice Line] WHERE [Type] <> '1'
    UNION ALL
    SELECT [Document No_], [No_], [Quantity] FROM [Sales Cr_Memo Line] WHERE [Type] <> '1'
    UNION ALL
    SELECT [Document No_], [No_], [Quantity] FROM [Service Invoice Line] WHERE [Type] IN ('1','2')
    UNION ALL
    SELECT [Document No_], [No_], [Quantity] FROM [Service Cr_Memo Line] WHERE [Type] IN ('1','2')
),
CTE_AllSales AS (
    SELECT
        SLD.ItemNo,
        SHD.PostingDate,
        CASE
            WHEN SHD.NoSeries IN ('SI','PSI') THEN SLD.Qty
            WHEN SHD.NoSeries IN ('SC','PSC') THEN SLD.Qty * (-1)
            ELSE 0
        END AS AdjustedQty
    FROM CTE_SalesHeaderData SHD
    LEFT JOIN CTE_SalesLinesData SLD ON SLD.DocNo2 = SHD.DocN
    WHERE SLD.ItemNo = @ItemNo AND SLD.Qty > 0
)
SELECT
    SUM(CASE WHEN PostingDate BETWEEN DATEADD(MONTH, -6, @StartDate) AND @StartDate THEN AdjustedQty ELSE 0 END) AS QtyLast6Months,
    SUM(CASE WHEN YEAR(PostingDate) = YEAR(@StartDate) THEN AdjustedQty ELSE 0 END) AS QtyThisYear,
    SUM(CASE WHEN YEAR(PostingDate) = YEAR(@StartDate) - 1 THEN AdjustedQty ELSE 0 END) AS QtyLastYear,
    YEAR(@StartDate) - 1 AS LastYear
FROM CTE_AllSales

方案2：扩展现有明细查询 #

若不想新增数据集，可在现有SELECT中加入窗口函数，每条明细会重复汇总值（适合小数据量）：

-- 在现有SELECT语句末尾添加
SUM(CASE WHEN SHD.PostingDate BETWEEN DATEADD(MONTH, -6, @StartDate) AND @StartDate THEN [Qty.] ELSE 0 END) OVER () AS QtyLast6Months,
SUM(CASE WHEN YEAR(SHD.PostingDate) = YEAR(@StartDate) THEN [Qty.] ELSE 0 END) OVER () AS QtyThisYear,
SUM(CASE WHEN YEAR(SHD.PostingDate) = YEAR(@StartDate) - 1 THEN [Qty.] ELSE 0 END) OVER () AS QtyLastYear,
YEAR(@StartDate) - 1 AS LastYear

二、SSRS报表配置 #

1. 动态上年销量标签 #

选中上年销量的标签文本框，设置表达式：

="Qty. Sold in " & First(Fields!LastYear.Value, "ds_SalesSummary")

（使用方案2时，替换数据集名为你的明细数据集名称）

2. 汇总值文本框配置 #

三个汇总文本框的值表达式分别为：

• 近6个月销量：=First(Fields!QtyLast6Months.Value, "ds_SalesSummary")
• 本年销量：=First(Fields!QtyThisYear.Value, "ds_SalesSummary")
• 上年销量：=First(Fields!QtyLastYear.Value, "ds_SalesSummary")

3. 关键注意点 #

• 确保@StartDate参数类型为Date/Time，避免年份计算错误
• 如需更精准的年份范围，可替换YEAR()逻辑为DATEFROMPARTS，例如本年范围：PostingDate BETWEEN DATEFROMPARTS(YEAR(@StartDate),1,1) AND DATEFROMPARTS(YEAR(@StartDate),12,31)
• 验证信用备忘录的负数量调整逻辑是否匹配业务规则，确保销量统计准确

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