在 SSRS 中，如何根据日期参数显示过去的销售汇总结果？

In SSRS, How to display past sales summarized results based on date parameter?

提问人：adhocEY 提问时间：8/17/2023 最后编辑：adhocEY 更新时间：8/19/2023 访问量：54

问：

我正在构建一个 SSRS 报表，显示已售商品的基本销售历史记录。用户输入项目 #、开始日期和结束日期作为参数。主表逐行显示输入物料的销售信息以及开始/结束日期之间的销售日期。

作为报告的一部分，我想在文本框中包含一些关键的汇总数据，这些数据将显示：

  • 过去 6 个月售出数量
  • 今年售出数量
  • 去年售出数量

如果我能将它们标记为“2022 年售出的数量”（去年），并根据输入的“开始日期”参数进行更新，那就更好了。因此，如果用户输入的开始日期是 2021 年的某个时间，它将显示为“2020 年售出的数量”。

我正在努力知道如何汇总数量并根据开始日期和结束日期更改正在查看的日期范围。

示例数据：

参数：开始日期=2023年1月1日 结束日期=2023年2月28日 商品编号=009833

刊登日期 订货号 客户 商品编号 数量。
一月/01/2023 25467 乔·史密斯 009833 4
2月/06/2023 26802 彼得 009833 27
2月 20/2023 26980 桑德拉· 009833 11

已编辑以添加代码 8/18/2023：

WITH        
    
----This CTE Gathers Sales Invoice / Cr. Memo Header data from 4 different sources----

    CTE_SalesHeaderData (DocN, NoSeries, OrderNo, PostingDate, Cust_Name)       
    AS      
(SELECT SIH.[No_], SIH.[No_ Series], SIH.[Order No_], SIH.[Posting Date], SIH.[Sell-to Customer Name]       
FROM [Sales Invoice Header] SIH         

UNION ALL           
SELECT SCRMH.[No_], SCRMH.[No_ Series], SCRMH.[Order No_], SCRMH.[Posting Date], SCRMH.[Sell-to Customer Name]          
FROM [Sales Cr_Memo Header] SCRMH   
        
UNION ALL           
SELECT SVIH.[No_], SVIH.[No_ Series], SVIH.[Order No_], SVIH.[Posting Date], SVIH.[Bill-to Name]    
FROM [Service Invoice Header] SVIH  

UNION ALL           
SELECT SVCRMH.[No_], SVCRMH.[No_ Series], SVCRMH.[Order No_], SVCRMH.[Posting Date], SVCRMH.[Bill-to Name]          
FROM [Service Cr_Memo Header] SVCRMH),

----This CTE Gathers Sales Invoice / Cr. Memo Line data for the 4 sources (relates to the 4 sources in 1st CTE) ----

CTE_SalesLinesData (DocNo2,ItemNo,Qty)  
AS
(SELECT SIL.[Document No_], SIL.[No_], SIL.[Quantity]
FROM [Sales Invoice Line] SIL
WHERE SIL.[Type] <> '1'
GROUP BY SIL.[Document No_], SIL.[No_], SIL.[Quantity]

UNION ALL
SELECT SCRML.[Document No_], SCRML.[No_], SCRML.[Quantity]
FROM [Sales Cr_Memo Line] SCRML
WHERE SCRML.[Type] <> '1'
GROUP BY SCRML.[Document No_], SCRML.[No_], SCRML.[Quantity]

UNION ALL
SELECT SVIL.[Document No_], SVIL.[No_], SVIL.[Quantity]
FROM [Service Invoice Line] SVIL
WHERE SVIL.[Type] = '1' OR SVIL.[Type] = '2'
GROUP BY SVIL.[Document No_], SVIL.[No_], SVIL.[Quantity]

UNION ALL
SELECT SVCRML.[Document No_], SVCRML.[No_], SVCRML.[Quantity]
FROM [Service Cr_Memo Line] SVCRML
WHERE SVCRML.[Type] = '1' OR SVCRML.[Type] = '2'
GROUP BY SVCRML.[Document No_], SVCRML.[No_], SVCRML.[Quantity])

SELECT
SHD.[PostingDate],
SHD.[OrderNo],
SHD.[Cust_No],
SHD.[Cust_Name],
SLD.[ItemNo],

---- The qty. has to be manipulated as it needs to be negative if it was a Cr. Memo ----
CASE
WHEN SHD.[NoSeries] = 'SI' THEN SLD.[Qty]
WHEN SHD.[NoSeries] = 'PSI' THEN SLD.[Qty]
WHEN SHD.[NoSeries] = 'SC' THEN SLD.[Qty] * (-1)
WHEN SHD.[NoSeries] = 'PSC' THEN SLD.[Qty] * (-1)
END AS [Qty.]

---- The 1st CTE is joined to the 2nd CTE ----
FROM CTE_SalesHeaderData SHD
LEFT JOIN CTE_SalesLinesData SLD ON SLD.DocNo2 = SHD.[DocN]

GROUP BY
SHD.[PostingDate],
SHD.[DocN],
SHD.[NoSeries],
SHD.[OrderNo],
SHD.[Cust_No],
SHD.[Cust_Name],
SLD.[ItemNo],
SLD.[Qty]

HAVING
SLD.[Qty] > 0
AND (SHD.[PostingDate] BETWEEN @StartDate AND @EndDate)
AND (SLD.[ItemNo] = @ItemNo)

ORDER BY
SHD.[PostingDate]

如果需要更多信息或任何其他问题，请告诉我。谢谢。

SQL-Server Reporting-Services parameter-passing SSRS-2012

评论

0赞 Dale K 8/17/2023
我经常使用单独的查询来获取摘要信息。
0赞 adhocEY 8/17/2023
@DaleK 一个单独的查询肯定是有意义的。但是如何构建它，以便它能够根据开始/结束日期参数获取过去的销售数据？
0赞 Dale K 8/17/2023
嗯，和你获取当前数据的方式完全一样？
0赞 adhocEY 8/17/2023
对不起，我的意思是单独的数据集。我可以对只有“发布日期”和“数量”的摘要信息使用单独的数据集来简化所涉及的内容。但要做到这一点，两个数据集都必须使用相同的查询和参数。希望有一种方法可以完全不将其作为单独的查询。
0赞 Dale K 8/17/2023
好吧，我会使用一个存储过程来做到这一点，该存储过程接受您现有的参数和一个附加参数，然后在内部您可以使用相同的查询，但如果为 true，则在返回结果之前对结果进行分组。ShowSummaryShowSummary

答：

0赞 Patrick Hurst 8/17/2023 #1

你没有提供你自己的DDL/DML（这确实有助于回答这类问题），所以我使用了我自己的随机数据，这是一个非常简单的销售类型设置。

DECLARE @StartDate DATETIME = '2023-01-01', @EndDate DATETIME = '2023-02-28 23:59:59.997', @ProductID INT = 5;

;WITH ProductHistory AS (
SELECT ii.ProductID, SUM(CASE WHEN InvoiceDateTimeUTC > DATEADD(MONTH,-6,@StartDate) THEN ii.Quantity END) AS LastSixMonths, 
                     SUM(CASE WHEN InvoiceDateTimeUTC BETWEEN DATEFROMPARTS(DATEPART(YEAR,@StartDate)-1,1,1) AND DATEFROMPARTS(DATEPART(YEAR,@StartDate),1,1) THEN ii.Quantity END) AS PreviousCalendarYear
  FROM InvoiceItems ii
    INNER JOIN Invoices i
      ON ii.InvoiceID = i.InvoiceID
 WHERE ii.ProductID = @ProductID
   AND i.InvoiceDateTimeUTC >= DATEFROMPARTS(DATEPART(YEAR,@StartDate)-1,1,1)
 GROUP BY ProductID
)

SELECT i.InvoiceDateTimeUTC, i.InvoiceID, c.FirstName, c.LastName, ii.ProductID, SUM(ii.Quantity) AS TotalQuantity, ph.LastSixMonths, ph.PreviousCalendarYear, DATEPART(YEAR,@StartDate)-1 AS PreviousYearNumber
  FROM InvoiceItems ii
    LEFT OUTER JOIN ProductHistory ph
      ON ii.ProductID = ph.ProductID
    INNER JOIN Invoices i
      ON ii.InvoiceID = i.InvoiceID
    INNER JOIN Customers c
      ON i.CustomerID = c.CustomerID
 WHERE ii.ProductID = @ProductID
   AND InvoiceDateTimeUTC BETWEEN @StartDate AND @EndDate
 GROUP BY i.InvoiceDateTimeUTC, i.InvoiceID, c.FirstName, c.LastName, ii.ProductID, ph.LastSixMonths, ph.PreviousCalendarYear;

我们使用 here 通过操作输入参数来执行您想要的聚合，然后只需加入该参数即可。恢复动态列名称并非易事，但您可以将 PreviousYearNumber 列添加到标题中。CTECTE

发票日期时间UTC 发票 ID 名字 姓氏 产品 ID 总数量 上半年 上一页日历年 上一页YearNumber
2023-01-11 14:55:15.9633333 357 乔治 坎贝尔 5 1 9030 8135 2022
2023-02-03 15:41:15.9633333 951 多 萝 西 里维拉 5 1 9030 8135 2022
2023-02-24 16:23:15.9633333 1722 黛布拉 门多萨 5 1 9030 8135 2022
2023-01-23 15:19:15.9633333 3080 夏佳理 戈麦斯 5 1 9030 8135 2022
2023-01-05 14:43:15.9633333 4455 瑞 秋 休斯 5 1 9030 8135 2022
2023-02-27 16:28:15.9633333 5898 黛博拉 罗伯茨 5 1 9030 8135 2022
2023-02-16 16:06:15.9633333 6070 夏佳理 戈麦斯 5 1 9030 8135 2022
2023-01-27 15:26:15.9633333 7163 威廉 埃尔南德斯 5 1 9030 8135 2022
2023-02-12 15:59:15.9633333 7482 雪 莉 奥尔蒂斯 5 1 9030 8135 2022
2023-01-17 15:07:15.9633333 7959 莱恩 柯林斯 5 1 9030 8135 2022

评论

0赞 adhocEY 8/19/2023
你是对的，我应该包括 DDL/DML。我已经用代码更新了我的帖子。我正在努力使您的方法适应我的代码，因为 CTE 的数量和一些格式让我感到困惑。您将如何将其应用于我的代码？
0赞 Patrick Hurst 8/21/2023
您的问题仍然不包含 DDL/DML。您可以使用类似 ： for 尽可能多的表是相关的。我建议至少使用发票标题和行项目。DECLARE @TableName (ColumnOne INT, ColumnTwo VARCHAR(20)...); INSERT INTO @TableName (ColumnOne, ColumnTwo...) VALUES (1, 'One'...),(2, 'Two'...) ...;
0赞 Patrick Hurst 8/21/2023
在此示例中，客户和日期信息存储在表中，行项目存储在表中。将这两者连接在一起，我们可以过滤日期和产品（按 ）。使用它，我们可以汇总您要求的时间段。然后，可以将其联接到原始表中，并根据您的要求显示每张发票。InvoicesInvoiceItemsProductID
0赞 Alan Schofield 8/17/2023 #2

这是一个非常通用的示例，说明我将如何处理这个问题。

据我所知，您目前有一个工作报告，它显示了两个日期之间的销售数据。因此，我假设您的数据集查询看起来是这样的

SELECT * 
    FROM mySalesTable 
    WHERE ItemNo = @ItemNo 
        AND PostingDate Between @StartDate AND @EndDate

现在，您想要添加一些其他摘要信息，但数据不会在主数据集中捕获，因为主数据集仅在两个特定点之间提供数据。

因此，我将创建另一个数据集，例如，并为此编写一个新查询。类似的东西..dsSummary

declare @Last6Months INT
declare @LastYear INT 
declare @ThisYear INT 

-- get last 6 monmths from startdate
SELECT @Last6Months = SUM(qty) 
    FROM mySalesTable 
    WHERE ItemNo = @ItemNo 
        AND PostingDate BETWEEN DATEADD(m, -6, @StartDate) AND @StartDate

-- get last year 
SELECT @LastYear = SUM(qty) 
    FROM mySalesTable 
    WHERE ItemNo = @ItemNo 
        AND YEAR(PostingDate) = YEAR(@StartDate)-1

-- get this year 
SELECT @ThisYear = SUM(qty) 
    FROM mySalesTable 
    WHERE ItemNo = @ItemNo 
        AND YEAR(PostingDate) = YEAR(@StartDate)

SELECT 
    @Last6Months as Last6Months,
    @LastYear as LastYear,
    @ThisYear as ThisYear

这仅使用您已有的参数，并基于该参数运行 3 个查询，并返回包含三个结果的单行。@StartDate

你可以用不同的方式写这个，因为它不是很有效，但我保持简单，所以很容易理解。

现在，你有了新的数据集，可以向报表添加文本框，并将表达式设置为 例如。=FIRST(Fields!Last6Month.Value, "dsSummary")

评论

0赞 adhocEY 8/19/2023
我已经添加了我自己的代码（我之前应该这样做！我一直在尝试调整您的方法并使其正常工作，但到目前为止还没有运气。考虑到我的代码的布局/格式，你会如何处理它？

上一个：如何根据参数化 rmarkdown 报表上的另一个输入刷新 sliderInput

下一个：将自定义 Matrix 类传递给 Lapack 子例程