sql-server - 如何使用 SSRS 中的一组特定数据进行行计数

Question

我有一份 SSRS 报告，显示按财政年度、财政季度和业务类型分组的总收入。我需要添加最后一列来计算构成总收入的行数，其中每行代表一个企业。

基本上，我需要知道特定业务类型中有多少个体业务占该时间范围内的总数。下面我会详细解释。

首先是提供报表数据的存储过程:

SELECT 
id
,assess_year
,CASE
    WHEN MONTH(date_received) IN (7,8,9) THEN 1
    WHEN MONTH(date_received) IN (10, 11, 12) THEN 2
    WHEN MONTH(date_received) IN (1, 2, 3) THEN 3
    WHEN MONTH(date_received) IN (4, 5, 6) THEN 3
    WHEN date_received is null THEN 0
    ELSE 0
END as Fiscal_Quarter
,s_gross_revenue
,CASE 
    WHEN LEFT(segment, 1) = 'A' THEN 'Accommodations' 
    WHEN segment BETWEEN 'B100' AND 'B699' THEN 'Restaurant' 
    WHEN segment BETWEEN 'B700' AND 'B999' THEN 'Retail' 
    WHEN LEFT(segment, 1) = 'C' THEN 'Attractions/Recreation' 
    WHEN LEFT(segment, 1) = 'D' THEN 'Travel Services' 
    WHEN LEFT(segment, 1) = 'E' THEN 'Rental Cars' 
    WHEN LEFT(segment, 1) = 'N' THEN 'Other' 
    ELSE 'UNDEFINED'
END as Segement
FROM mytable 
WHERE s_gross_revenue >0 AND assess_year IS NOT NULL 
    AND assess_year <> '' AND segment IS NOT NULL AND segment <>''
ORDER BY ASSESS_YEAR, FISCAL_QUARTER, SEGEMENT

我附上了一张图片 (Raw Data Image)显示过程返回的原始数据的示例。

我在 SSRS 报告中按年、季度和业务类型进行所有分组。

我只需要一个公式或分组来计算构成总收入的每个业务的所有行。

我还附上了报告的图片(SSRS Report Image)在行计数应该去的末尾有空列。

因此，在 SSRS 报告图像中，2014/15 财年和第一财季的业务类型“住宿”在最后一列中的行数应为 4455，因为有 4455 家单独的企业被列为住宿2014/15 年第一季度的收入。我只是不知道如何根据 SSRS 中的特定条件进行行计数。

(另外，我知道季度在 SQL 中看起来很奇怪，我们的营业年度从 7 月开始)

Answer 1

如果我正确理解你的问题并且你使用的是 SQL Server 2008+，那么你可以使用 OVER(...) 子句做 COUNT(...)同时保留您的详细数据。

对于业务计数，它看起来像:

COUNT(*) OVER (PARTITION BY assess_year, Fiscal_quarter, Segement)

这是完全修改后的查询，但上面的部分实际上是唯一的变化:

SELECT 
    id
    ,assess_year
    ,Fiscal_Quarter
    ,s_gross_revenue
    ,Segement
    ,COUNT(*) OVER (PARTITION BY assess_year, Fiscal_quarter, Segement) BusinessCount
FROM (
        SELECT 
            id
            ,assess_year
            ,CASE
                WHEN MONTH(date_received) IN (7,8,9) THEN 1
                WHEN MONTH(date_received) IN (10, 11, 12) THEN 2
                WHEN MONTH(date_received) IN (1, 2, 3) THEN 3
                WHEN MONTH(date_received) IN (4, 5, 6) THEN 4
                WHEN date_received is null THEN 0
                ELSE 0
            END as Fiscal_Quarter
            ,s_gross_revenue
            ,CASE 
                WHEN LEFT(segment, 1) = 'A' THEN 'Accommodations' 
                WHEN segment BETWEEN 'B100' AND 'B699' THEN 'Restaurant' 
                WHEN segment BETWEEN 'B700' AND 'B999' THEN 'Retail' 
                WHEN LEFT(segment, 1) = 'C' THEN 'Attractions/Recreation' 
                WHEN LEFT(segment, 1) = 'D' THEN 'Travel Services' 
                WHEN LEFT(segment, 1) = 'E' THEN 'Rental Cars' 
                WHEN LEFT(segment, 1) = 'N' THEN 'Other' 
                ELSE 'UNDEFINED'
            END as Segement
        FROM mytable 
        WHERE s_gross_revenue > 0 
            AND ISNULL(assess_year,'') <> '' 
            AND ISNULL(segement,'') <> ''
    ) data
ORDER BY ASSESS_YEAR, FISCAL_QUARTER, SEGEMENT

我还对您的 where 子句进行了轻微的句法更改，这是相同的逻辑，但是是一种简写方式。而不是检查是否 SEGEMENT和 ASSESS_YEAR是 NOT NULL AND NOT ''在单独的语句中，您可以将它们组合成一个 ISNULL(...,'') <> ''如果SEGEMENT和 ASSESS_YEAR是文本类型值，否则你将不得不使用 COALESCE(...)完成同样的事情。 ISNULL(...)可以替换为COALESCE(...)如果首选。

这也完全可以通过简单地使用 count(<field name>) 来实现。在 SSRS 报告的 id 列中。它应该给你相同的结果。