reporting-services - 如何在 SSRS Report Builder 中按日期范围分组？

Question

我有一份健康计划个人订阅者列表。每个订阅者都有一个日期范围来表明该计划的资格。为了帮助说明，这里是 2018 年第一季度符合条件的订阅者的模拟记录列表，用 T-SQL 为 MS SQL Server 编写:

-- qryEligibleSubscribers2018-Q1Simple

WITH
    S
AS
(
-- MOCK LIST OF SUBSCRIBERS TO HEALTH PLAN
-- WITH ELIGIBILITY TIME FRAMES
    SELECT
-- CONTINUOUSLY ELIGIBLE SINCE 2017
        '0001' AS ID,
        'MOUSE' AS LASTNAME,
        'MICKEY' AS FIRSTNAME,
        '1/1/2017' AS EFFECTIVE,
        NULL AS TERM
    UNION ALL SELECT
-- CURRENT SUBSCRIBER SINCE FEB. 2018
        '0002' AS ID,
        'MOUSE' AS LASTNAME,
        'MINNIE' AS FIRSTNAME,
        '2/1/2018' AS EFFECTIVE,
        NULL AS TERM
    UNION ALL SELECT
-- SUBSCRIBED TO PLAN FOR JAN. 2018 ONLY
        '0003' AS ID,
        'DUCK' AS LASTNAME,
        'DONALD' AS FIRSTNAME,
        '1/1/2018' AS EFFECTIVE,
        '1/31/2018' AS TERM
    UNION ALL SELECT
-- SUBSCRIBED TO PLAN STARTING IN 2018-Q2
        '0004' AS ID,
        'GOOF' AS LASTNAME,
        'GOOFY' AS FIRSTNAME,
        '4/1/2018' AS EFFECTIVE,
        NULL AS TERM
)
-- LIST OF SUBSCRIBERS
-- WHO WERE ELIGIBLE IN 2018-Q1 ONLY
SELECT
    S.ID,
    S.LASTNAME,
    S.FIRSTNAME,
    S.EFFECTIVE,
    S.TERM
FROM
    S
WHERE
-- LIMIT LIST TO 2018-Q1
    S.EFFECTIVE <= '3/31/2018'
AND
    ISNULL(S.TERM,GETDATE()) >= '1/1/2018'

运行时的结果是这三个(四个中的)订阅者:

ID      LASTNAME    FIRSTNAME   EFFECTIVE   TERM
-----------------------------------------------------
0001    MOUSE       MICKEY      1/1/2017
0002    MOUSE       MINNIE      2/1/2018
0003    DUCK        DONALD      1/1/2018    1/31/2018
-----------------------------------------------------

从结果中我们可以看出，米老鼠在 2018-Q1 的所有三个月都符合条件，米妮老鼠仅在 2 月和 3 月符合条件，唐老鸭仅在本季度的第一个月符合条件，而高飞则获得了资格2018-Q2 的计划，因此未包含在结果集中。

我需要报告每个月的订阅者总数。我可以使用这个更复杂的查询来做到这一点，它也是用 T-SQL 为 MS-SQL Server 编写的:

-- qryEligibleSubscribers2018-Q1Complex

WITH
-- LIST OF MONTHS IN 2018-Q1
    R(BOR,EOR)
AS
(
-- FIXED RANGE OF MONTHS IN 2018-Q1
    SELECT
        DATEADD(M,DATEDIFF(M,0,'1/1/2018'),0) AS BOR,
        DATEADD(M,DATEDIFF(M,0,'3/31/2018'),0) AS EOR
-- RECURSIVELY ADD A MONTH
    UNION ALL SELECT
        DATEADD(M,1,R.BOR) AS BOR,
        R.EOR
    FROM
        R
    WHERE
        R.BOR < R.EOR
),
    S
AS
(
-- MOCK LIST OF SUBSCRIBERS TO HEALTH PLAN
-- WITH ELIGIBILITY TIME FRAMES
    SELECT
-- CONTINUOUSLY ELIGIBLE SINCE 2017
        '0001' AS ID,
        'MOUSE' AS LASTNAME,
        'MICKEY' AS FIRSTNAME,
        '1/1/2017' AS EFFECTIVE,
        NULL AS TERM
    UNION ALL SELECT
-- CURRENT SUBSCRIBER SINCE FEB. 2018
        '0002' AS ID,
        'MOUSE' AS LASTNAME,
        'MINNIE' AS FIRSTNAME,
        '2/1/2018' AS EFFECTIVE,
        NULL AS TERM
    UNION ALL SELECT
-- SUBSCRIBED TO PLAN FOR JAN. 2018 ONLY
        '0003' AS ID,
        'DUCK' AS LASTNAME,
        'DONALD' AS FIRSTNAME,
        '1/1/2018' AS EFFECTIVE,
        '1/31/2018' AS TERM
    UNION ALL SELECT
-- SUBSCRIBED TO PLAN STARTING IN 2018-Q2
        '0004' AS ID,
        'GOOF' AS LASTNAME,
        'GOOFY' AS FIRSTNAME,
        '4/1/2018' AS EFFECTIVE,
        NULL AS TERM
),
    X
AS
(
-- EXTENDED LIST OF SUBSCRIBERS
-- WHO WERE ELIGIBLE IN 2018-Q1 ONLY
-- LISTING EACH MONTH OF ELIGIBILITY
    SELECT
        R.BOR AS MONTH,
        FORMAT(R.BOR,'MMMM yyyy') AS LABEL,
        S.ID,
        S.LASTNAME,
        S.FIRSTNAME,
        S.EFFECTIVE,
        S.TERM
    FROM
        S
    INNER JOIN
        R
    ON
        S.EFFECTIVE < DATEADD(M,1,R.BOR)
    AND
        ISNULL(S.TERM,GETDATE()) >= R.BOR
)
SELECT
    X.LABEL AS MONTH,
    COUNT(X.ID) AS TOTAL
FROM
    X
GROUP BY
    X.LABEL,
    X.MONTH
ORDER BY
    X.MONTH

运行结果显示，第一季度每个月共有两名订阅者符合条件:

MONTH       TOTAL
-----------------
January 2018    2
February 2018   2
March 2018      2
-----------------

要查看详细/未汇总的结果——具体来说，哪个订阅者在哪个月份符合条件，我们可以运行此查询(也用 T-SQL 编写用于 MS-SQL Server):

-- qryEligibleSubscribers2018-Q1Detailed

WITH
-- LIST OF MONTHS IN 2018-Q1
    R(BOR,EOR)
AS
(
-- FIXED RANGE OF MONTHS IN 2018-Q1
    SELECT
        DATEADD(M,DATEDIFF(M,0,'1/1/2018'),0) AS BOR,
        DATEADD(M,DATEDIFF(M,0,'3/31/2018'),0) AS EOR
-- RECURSIVELY ADD A MONTH
    UNION ALL SELECT
        DATEADD(M,1,R.BOR) AS BOR,
        R.EOR
    FROM
        R
    WHERE
        R.BOR < R.EOR
),
    S
AS
(
-- MOCK LIST OF SUBSCRIBERS TO HEALTH PLAN
-- WITH ELIGIBILITY TIME FRAMES
    SELECT
-- CONTINUOUSLY ELIGIBLE SINCE 2017
        '0001' AS ID,
        'MOUSE' AS LASTNAME,
        'MICKEY' AS FIRSTNAME,
        '1/1/2017' AS EFFECTIVE,
        NULL AS TERM
    UNION ALL SELECT
-- CURRENT SUBSCRIBER SINCE FEB. 2018
        '0002' AS ID,
        'MOUSE' AS LASTNAME,
        'MINNIE' AS FIRSTNAME,
        '2/1/2018' AS EFFECTIVE,
        NULL AS TERM
    UNION ALL SELECT
-- SUBSCRIBED TO PLAN FOR JAN. 2018 ONLY
        '0003' AS ID,
        'DUCK' AS LASTNAME,
        'DONALD' AS FIRSTNAME,
        '1/1/2018' AS EFFECTIVE,
        '1/31/2018' AS TERM
    UNION ALL SELECT
-- SUBSCRIBED TO PLAN STARTING IN 2018-Q2
        '0004' AS ID,
        'GOOF' AS LASTNAME,
        'GOOFY' AS FIRSTNAME,
        '4/1/2018' AS EFFECTIVE,
        NULL AS TERM
)
-- EXTENDED LIST OF SUBSCRIBERS
-- WHO WERE ELIGIBLE IN 2018-Q1 ONLY
-- LISTING EACH MONTH OF ELIGIBILITY
SELECT
    FORMAT(R.BOR,'MMMM yyyy') AS LABEL,
    S.ID,
    S.LASTNAME,
    S.FIRSTNAME,
    S.EFFECTIVE,
    S.TERM
FROM
    S
INNER JOIN
    R
ON
    S.EFFECTIVE < DATEADD(M,1,R.BOR)
AND
    ISNULL(S.TERM,GETDATE()) >= R.BOR
ORDER BY
    R.BOR,
    S.ID

详细的逐月结果是:

MONTH           ID      LASTNAME    FIRSTNAME   EFFECTIVE   TERM
---------------------------------------------------------------------
January 2018    0001    MOUSE       MICKEY      1/1/2017
January 2018    0003    DUCK        DONALD      1/1/2018    1/31/2018
February 2018   0001    MOUSE       MICKEY      1/1/2017
February 2018   0002    MOUSE       MINNIE      2/1/2018
March 2018      0001    MOUSE       MICKEY      1/1/2017
March 2018      0002    MOUSE       MINNIE      2/1/2018
---------------------------------------------------------------------

通过最初的简单查询，结果集提供了三个记录，每个符合条件的订阅者在整个感兴趣的日期范围内(2018 年第一季度)都有一个。

ID      LASTNAME    FIRSTNAME   EFFECTIVE   TERM
-----------------------------------------------------
0001    MOUSE       MICKEY      1/1/2017
0002    MOUSE       MINNIE      2/1/2018
0003    DUCK        DONALD      1/1/2018    1/31/2018
-----------------------------------------------------

通过更复杂、更详细的查询，结果集会提供 6 条记录，每个月一条，订阅者有资格在感兴趣的季度的特定月份使用。

MONTH           ID      LASTNAME    FIRSTNAME   EFFECTIVE   TERM
---------------------------------------------------------------------
January 2018    0001    MOUSE       MICKEY      1/1/2017
January 2018    0003    DUCK        DONALD      1/1/2018    1/31/2018
February 2018   0001    MOUSE       MICKEY      1/1/2017
February 2018   0002    MOUSE       MINNIE      2/1/2018
March 2018      0001    MOUSE       MICKEY      1/1/2017
March 2018      0002    MOUSE       MINNIE      2/1/2018
---------------------------------------------------------------------

实际上，我们有数十万订阅者。要使用更复杂的查询，会产生一个笨重的结果集(例如，在 12 个月的时间段内，数十万条记录中的每条记录最多可以乘以 12 次……每个月一次。)我需要保持我的简单查询作为驱动我的 SSRS 报告的引擎。使用简单的结果集...:

ID      LASTNAME    FIRSTNAME   EFFECTIVE   TERM
-----------------------------------------------------
0001    MOUSE       MICKEY      1/1/2017
0002    MOUSE       MINNIE      2/1/2018
0003    DUCK        DONALD      1/1/2018    1/31/2018
-----------------------------------------------------

...我似乎无法弄清楚如何在 SSRS 中构建一个报告来提供每月的订阅者总数。换句话说，我想要并需要 SSRS Reporting Services 来完成将记录乘以/拆分/复制到相应合格月份的工作，而不是基础 SQL 查询:

MONTH       TOTAL
-----------------
January 2018    2
February 2018   2
March 2018      2
-----------------

问题的本质是找出(如果可能的话)如何在 SSRS 中拆分和分组一个日期范围，就像我在更复杂的 SQL 查询中所做的那样。

我尝试在 Report Builder 中使用各种风格的 Lookup()，但这些都需要一对一匹配，例如，MultiLookup() 从名称-值对的数据集中检索一组值，其中每个pair 具有一对一的关系。

在我的示例中，季度中的特定月份必须与订阅者集中的特定月份(在一定范围内)相匹配。在这里，正如我们在简单查询中看到的那样，订户可以在一定范围内符合条件。我相信我可以在 Report Builder 中成功构建一个 SSRS 报告，如果存在 Lookup() 的一些变体，它打开了一个数据集中的特定键(例如，2018 年 1 月)是否存在于另一个数据集(2016 年 8 月)的一系列键中- 2018 年 5 月)，而不是精确的一对一匹配。

结果的格式无关紧要。可以是图表或 tablix。如果可能的话，现在只是想得到结果。

Answer 1

不 - 不幸的是，我想不出一种简单的方法让 SSRS 按月分组并根据需要计算每个月。

如果您只是想避免运行多个查询，适合您的情况的方法是使用临时表。

您的第一个数据集会将详细数据放在临时表中，然后从表中选择详细信息。

第二个数据集将使用第一个数据集中的详细信息临时表来汇总您的每月总计。对于使用在先前数据集中创建的临时表的数据集，您需要在字段 选项卡中手动输入字段名称。

选中 Data SOURCE 属性中的Use Single Transaction 框，以便它保留临时表。