将按 MTD、上个月等进行的查询限制为 SSRS

Question

我正在尝试从以下查询中形成报告，我遇到的问题是：如何让它创建一个可以在 SSRS 中调用它的结构，比如调用 MTF、PMonth、PPMonth 作为"类型"选择，当我将其放入 SSRS 时，我希望 MTD、PMonth 和 PPMonth 作为单独的实体出现在报告中。

-- ResWare.dbo.rptClosingPipeline3
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @today varchar(20)
SET     @today = Convert(varchar, GetDate(), 101)
DECLARE @startdate  datetime
SET @startdate = dbo.GetUTCDateTimeFunc(DateAdd(m, 0, convert(datetime, convert(char(8), current_timestamp, 112)) - day(current_timestamp) + 1))--
DECLARE @enddate    datetime  --today at midnight.  first moment of the day
SET @enddate = dbo.GetUTCDateTimeFunc(@today)--
DECLARE @MTD    datetime
SET @MTD = dbo.GetUTCDateTimeFunc(DateAdd(m, 0, convert(datetime, convert(char(8), current_timestamp, 112)) - day(current_timestamp) + 1))--
DECLARE @PMonth datetime
SET @PMonth = dbo.GetUTCDateTimeFunc(DateAdd(m, -1, convert(datetime, convert(char(8), current_timestamp, 112)) - day(current_timestamp) + 1))
DECLARE @PMonthEnd  datetime
SET @PMonthEnd = dbo.GetUTCDateTimeFunc(DateAdd(m, 0, convert(datetime, convert(char(8), current_timestamp, 112)) - day(current_timestamp) + 1))--
DECLARE @PPMonth    datetime
SET @PPMonth = dbo.GetUTCDateTimeFunc(DateAdd(m, -2, convert(datetime, convert(char(8), current_timestamp, 112)) - day(current_timestamp) + 1))
DECLARE @PPMonthEnd datetime
SET @PPMonthEnd = dbo.GetUTCDateTimeFunc(DateAdd(m, -1, convert(datetime, convert(char(8), current_timestamp, 112)) - day(current_timestamp) + 1))--

SELECT  FM.FileNumber AS FileNumber,
        ISNULL(IU.TEAMNAME,'') AS Closer,
        dbo.GetLocalDateTimeFunc(FM.ActualSettlementDate) AS Scheduled,
        dbo.GetLocalDateTimeFunc(FM.DisbursementDate) AS DisbursementDate,
        dbo.GetLocalDateTimeFunc(FM.EstSettlementDate) AS EstSettlementDate,
        CASE WHEN EXISTS (SELECT * FROM FilePartnerRel FPR1
        WHERE FPR1.PartnerTypeID = 10033 AND FPR1.FileID = FM.FileID)
        THEN P.State+'-(W)'
        ELSE P.State END AS State,
        ISNULL(P.StreetNumber,'')+' '+ISNULL(P.StreetDirection,'')+' '+ISNULL(P.StreetName,'')+' '+ISNULL(P.Unit,'')AS PropertyAddress,
        S.Name,
        (SELECT CONVERT(varchar(7000),Body) FROM Note WHERE NoteID = 
        (Select MAX(N.NoteID)
         FROM Note N
         JOIN FileActions A1 ON A1.FileID=FM.FileID 
         AND A1.ActionDefID IN (141,386,306)
        JOIN FileActionNoteRel AN ON AN.FileActionsID=A1.FileActionsID
         AND N.NoteID = AN.NoteID)) AS Note,
         (SELECT COUNT(DISTINCT FA1.FileID) FROM FileActions FA1
         JOIN FileMain FM1 ON FM1.FileID = FA1.FileID
              AND FM1.FileNumber NOT LIKE 'PA-%'
        JOIN    Coordinator C1 ON C1.FileID = FA1.FileID
        AND C1.CoordinatorTypeID IN (2, 6)
        AND C1.CoordinatorID = C.CoordinatorID
        WHERE FA1.ActionDefID IN (61,315,393)
        AND FA1.ReceivedDate >= @startdate
        AND FM1.StatusID != 8),
         (SELECT COUNT(DISTINCT FA1.FileID) FROM FileActions FA1
         JOIN FileMain FM1 ON FM1.FileID = FA1.FileID
              AND FM1.FileNumber NOT LIKE 'PA-%'
        WHERE FA1.ActionDefID IN (61,315,393)
        AND FA1.ReceivedDate >= @startdate 
        AND FM1.StatusID != 8) AS TotalDisb
        , TRT.Name CurativeItem
        , SD.CommitmentEffDate
FROM        FileMain FM
JOIN        ActionListTransactionType ALTT ON ALTT.ActionListTransactionTypeID = FM.ActionListTransactionTypeID
JOIN        ActionListProductType ALPT ON ALPT.ActionListProductTypeID = FM.ActionListProductTypeID
JOIN        Coordinator C ON C.FileID = FM.FileID
            AND C.CoordinatorTypeID IN (2)
LEFT JOIN   FilePartnerRel FPR ON FPR.FileID = FM.FileID
            AND FPR.PartnerTypeID = 5
JOIN        Property P ON P.FileID = FM.FileID
LEFT JOIN   PartnerCompany PC ON PC.PartnerCompanyID = FPR.PartnerCompanyID
JOIN        PartnerCompany PC1 ON PC1.PartnerCompanyID = FM.ClientID
JOIN        TEAM IU ON IU.TEAMID = C.CoordinatorID
JOIN        Status S ON S.StatusID = FM.StatusID
LEFT JOIN   TitleReview TR ON TR.FileID = Fm.FileID
            AND (TR.InternalClear IS NULL OR TR.InternalClear = 0)
LEFT JOIN   TitleReviewType TRT ON TRT.TitleReviewTypeID = TR.TitleReviewTypeID
LEFT JOIN   SearchData SD ON SD.FileID = FM.FileID
WHERE   FM.OfficeID = 1
        AND FM.FileNumber NOT LIKE 'PA-%'
        AND FM.StatusID IN (2,4,5,10,11,14,15,18) 
UNION ALL
SELECT  FM.FileNumber AS FileNumber,
        ISNULL(IU.TEAMNAME,'') AS Closer,
        dbo.GetLocalDateTimeFunc(FM.ActualSettlementDate) AS Scheduled,
        dbo.GetLocalDateTimeFunc(FM.DisbursementDate) AS DisbursementDate,
        dbo.GetLocalDateTimeFunc(FM.EstSettlementDate) AS EstSettlementDate,
        CASE WHEN EXISTS (SELECT * FROM FilePartnerRel FPR1
        WHERE FPR1.PartnerTypeID = 10033 AND FPR1.FileID = FM.FileID)
        THEN P.State+'-(W)'
        ELSE P.State END AS State,
        ISNULL(P.StreetNumber,'')+' '+ISNULL(P.StreetDirection,'')+' '+ISNULL(P.StreetName,'')+' '+ISNULL(P.Unit,'')AS PropertyAddress,
        S.Name,
        (SELECT CONVERT(varchar(7000),Body) FROM Note WHERE NoteID = 
        (Select MAX(N.NoteID)
         FROM Note N
         JOIN FileActions A1 ON A1.FileID=FM.FileID 
         AND A1.ActionDefID IN (141,386,306)
        JOIN FileActionNoteRel AN ON AN.FileActionsID=A1.FileActionsID
         AND N.NoteID = AN.NoteID)) AS Note,
         (SELECT COUNT(DISTINCT FA1.FileID) FROM FileActions FA1
         JOIN FileMain FM1 ON FM1.FileID = FA1.FileID
              AND FM1.FileNumber NOT LIKE 'PA-%'
        JOIN    Coordinator C1 ON C1.FileID = FA1.FileID
        AND C1.CoordinatorTypeID IN (2, 6)
        AND C1.CoordinatorID = C.CoordinatorID
        WHERE FA1.ActionDefID IN (61,315,393)
        AND FA1.ReceivedDate >= @MTD
        AND FM1.StatusID != 8),
         (SELECT COUNT(DISTINCT FA1.FileID) FROM FileActions FA1
         JOIN FileMain FM1 ON FM1.FileID = FA1.FileID
              AND FM1.FileNumber NOT LIKE 'PA-%'
        WHERE FA1.ActionDefID IN (61,315,393)
        AND FA1.ReceivedDate >= @MTD 
        AND FM1.StatusID != 8) AS TotalDisb
        , TRT.Name CurativeItem
        , SD.CommitmentEffDate
FROM        FileMain FM
JOIN        ActionListTransactionType ALTT ON ALTT.ActionListTransactionTypeID = FM.ActionListTransactionTypeID
JOIN        ActionListProductType ALPT ON ALPT.ActionListProductTypeID = FM.ActionListProductTypeID
JOIN        Coordinator C ON C.FileID = FM.FileID
            AND C.CoordinatorTypeID IN (2)
LEFT JOIN   FilePartnerRel FPR ON FPR.FileID = FM.FileID
            AND FPR.PartnerTypeID = 5
JOIN        Property P ON P.FileID = FM.FileID
LEFT JOIN   PartnerCompany PC ON PC.PartnerCompanyID = FPR.PartnerCompanyID
JOIN        PartnerCompany PC1 ON PC1.PartnerCompanyID = FM.ClientID
JOIN        TEAM IU ON IU.TEAMID = C.CoordinatorID
JOIN        Status S ON S.StatusID = FM.StatusID
LEFT JOIN   TitleReview TR ON TR.FileID = Fm.FileID
            AND (TR.InternalClear IS NULL OR TR.InternalClear = 0)
LEFT JOIN   TitleReviewType TRT ON TRT.TitleReviewTypeID = TR.TitleReviewTypeID
LEFT JOIN   SearchData SD ON SD.FileID = FM.FileID
WHERE   FM.OfficeID = 1
        AND FM.FileNumber NOT LIKE 'PA-%'
        AND FM.StatusID IN (2,4,5,10,11,14,15,18) 
UNION ALL
SELECT  FM.FileNumber AS FileNumber,
        ISNULL(IU.TEAMNAME,'') AS Closer,
        dbo.GetLocalDateTimeFunc(FM.ActualSettlementDate) AS Scheduled,
        dbo.GetLocalDateTimeFunc(FM.DisbursementDate) AS DisbursementDate,
        dbo.GetLocalDateTimeFunc(FM.EstSettlementDate) AS EstSettlementDate,
        CASE WHEN EXISTS (SELECT * FROM FilePartnerRel FPR1
        WHERE FPR1.PartnerTypeID = 10033 AND FPR1.FileID = FM.FileID)
        THEN P.State+'-(W)'
        ELSE P.State END AS State,
        ISNULL(P.StreetNumber,'')+' '+ISNULL(P.StreetDirection,'')+' '+ISNULL(P.StreetName,'')+' '+ISNULL(P.Unit,'')AS PropertyAddress,
        S.Name,
        (SELECT CONVERT(varchar(7000),Body) FROM Note WHERE NoteID = 
        (Select MAX(N.NoteID)
         FROM Note N
         JOIN FileActions A1 ON A1.FileID=FM.FileID 
         AND A1.ActionDefID IN (141,386,306)
        JOIN FileActionNoteRel AN ON AN.FileActionsID=A1.FileActionsID
         AND N.NoteID = AN.NoteID)) AS Note,
         (SELECT COUNT(DISTINCT FA1.FileID) FROM FileActions FA1
         JOIN FileMain FM1 ON FM1.FileID = FA1.FileID
              AND FM1.FileNumber NOT LIKE 'PA-%'
        JOIN    Coordinator C1 ON C1.FileID = FA1.FileID
        AND C1.CoordinatorTypeID IN (2, 6)
        AND C1.CoordinatorID = C.CoordinatorID
        WHERE FA1.ActionDefID IN (61,315,393)
        AND FA1.ReceivedDate >= @PMonth
        AND FM1.StatusID != 8),
         (SELECT COUNT(DISTINCT FA1.FileID) FROM FileActions FA1
         JOIN FileMain FM1 ON FM1.FileID = FA1.FileID
              AND FM1.FileNumber NOT LIKE 'PA-%'
        WHERE FA1.ActionDefID IN (61,315,393)
        AND FA1.ReceivedDate >= @PMonth 
        AND FM1.StatusID != 8) AS TotalDisb
        , TRT.Name CurativeItem
        , SD.CommitmentEffDate
FROM        FileMain FM
JOIN        ActionListTransactionType ALTT ON ALTT.ActionListTransactionTypeID = FM.ActionListTransactionTypeID
JOIN        ActionListProductType ALPT ON ALPT.ActionListProductTypeID = FM.ActionListProductTypeID
JOIN        Coordinator C ON C.FileID = FM.FileID
            AND C.CoordinatorTypeID IN (2)
LEFT JOIN   FilePartnerRel FPR ON FPR.FileID = FM.FileID
            AND FPR.PartnerTypeID = 5
JOIN        Property P ON P.FileID = FM.FileID
LEFT JOIN   PartnerCompany PC ON PC.PartnerCompanyID = FPR.PartnerCompanyID
JOIN        PartnerCompany PC1 ON PC1.PartnerCompanyID = FM.ClientID
JOIN        TEAM IU ON IU.TEAMID = C.CoordinatorID
JOIN        Status S ON S.StatusID = FM.StatusID
LEFT JOIN   TitleReview TR ON TR.FileID = Fm.FileID
            AND (TR.InternalClear IS NULL OR TR.InternalClear = 0)
LEFT JOIN   TitleReviewType TRT ON TRT.TitleReviewTypeID = TR.TitleReviewTypeID
LEFT JOIN   SearchData SD ON SD.FileID = FM.FileID
WHERE   FM.OfficeID = 1
        AND FM.FileNumber NOT LIKE 'PA-%'
        AND FM.StatusID IN (2,4,5,10,11,14,15,18) 
UNION ALL
SELECT  FM.FileNumber AS FileNumber,
        ISNULL(IU.TEAMNAME,'') AS Closer,
        dbo.GetLocalDateTimeFunc(FM.ActualSettlementDate) AS Scheduled,
        dbo.GetLocalDateTimeFunc(FM.DisbursementDate) AS DisbursementDate,
        dbo.GetLocalDateTimeFunc(FM.EstSettlementDate) AS EstSettlementDate,
        CASE WHEN EXISTS (SELECT * FROM FilePartnerRel FPR1
        WHERE FPR1.PartnerTypeID = 10033 AND FPR1.FileID = FM.FileID)
        THEN P.State+'-(W)'
        ELSE P.State END AS State,
        ISNULL(P.StreetNumber,'')+' '+ISNULL(P.StreetDirection,'')+' '+ISNULL(P.StreetName,'')+' '+ISNULL(P.Unit,'')AS PropertyAddress,
        S.Name,
        (SELECT CONVERT(varchar(7000),Body) FROM Note WHERE NoteID = 
        (Select MAX(N.NoteID)
         FROM Note N
         JOIN FileActions A1 ON A1.FileID=FM.FileID 
         AND A1.ActionDefID IN (141,386,306)
        JOIN FileActionNoteRel AN ON AN.FileActionsID=A1.FileActionsID
         AND N.NoteID = AN.NoteID)) AS Note,
         (SELECT COUNT(DISTINCT FA1.FileID) FROM FileActions FA1
         JOIN FileMain FM1 ON FM1.FileID = FA1.FileID
              AND FM1.FileNumber NOT LIKE 'PA-%'
        JOIN    Coordinator C1 ON C1.FileID = FA1.FileID
        AND C1.CoordinatorTypeID IN (2, 6)
        AND C1.CoordinatorID = C.CoordinatorID
        WHERE FA1.ActionDefID IN (61,315,393)
        AND FA1.ReceivedDate >= @PPMonth
        AND FM1.StatusID != 8),
         (SELECT COUNT(DISTINCT FA1.FileID) FROM FileActions FA1
         JOIN FileMain FM1 ON FM1.FileID = FA1.FileID
              AND FM1.FileNumber NOT LIKE 'PA-%'
        WHERE FA1.ActionDefID IN (61,315,393)
        AND FA1.ReceivedDate >= @PPMonth 
        AND FM1.StatusID != 8) AS TotalDisb
        , TRT.Name CurativeItem
        , SD.CommitmentEffDate
FROM        FileMain FM
JOIN        ActionListTransactionType ALTT ON ALTT.ActionListTransactionTypeID = FM.ActionListTransactionTypeID
JOIN        ActionListProductType ALPT ON ALPT.ActionListProductTypeID = FM.ActionListProductTypeID
JOIN        Coordinator C ON C.FileID = FM.FileID
            AND C.CoordinatorTypeID IN (2)
LEFT JOIN   FilePartnerRel FPR ON FPR.FileID = FM.FileID
            AND FPR.PartnerTypeID = 5
JOIN        Property P ON P.FileID = FM.FileID
LEFT JOIN   PartnerCompany PC ON PC.PartnerCompanyID = FPR.PartnerCompanyID
JOIN        PartnerCompany PC1 ON PC1.PartnerCompanyID = FM.ClientID
JOIN        TEAM IU ON IU.TEAMID = C.CoordinatorID
JOIN        Status S ON S.StatusID = FM.StatusID
LEFT JOIN   TitleReview TR ON TR.FileID = Fm.FileID
            AND (TR.InternalClear IS NULL OR TR.InternalClear = 0)
LEFT JOIN   TitleReviewType TRT ON TRT.TitleReviewTypeID = TR.TitleReviewTypeID
LEFT JOIN   SearchData SD ON SD.FileID = FM.FileID
WHERE   FM.OfficeID = 1
        AND FM.FileNumber NOT LIKE 'PA-%'
        AND FM.StatusID IN (2,4,5,10,11,14,15,18) 

GROUP BY        FM.FileNumber,
        PC1.Name,
        PC.Name,
        ISNULL(IU.TEAMNAME,''),
        ALTT.Name +': '+ALPT.DisplayName,
        dbo.GetLocalDateTimeFunc(FM.ActualSettlementDate),
        dbo.GetLocalDateTimeFunc(FM.DisbursementDate),
        dbo.GetLocalDateTimeFunc(FM.EstSettlementDate),
        FM.FileID,
        P.State,
        P.StreetNumber,
        P.StreetDirection,
        P.StreetName,
        P.Unit,
        S.Name,
        FM.EstSettlementDate,
        C.CoordinatorID,
        TRT.Name,
        SD.CommitmentEffDate

Answer 1

选项 1：

将

硬编码列添加到查询中，如下所示：

SELECT ...
, 'StartDt' as Period
From QUERY1
UNION ALL
SELECT ...
, 'MTD' as Period
FROM QUERY2
UNION ALL
SELECT ...
, 'Pmonth' as Period
FROM QUERY3

然后，我会添加一个报告参数"@filter"（或任何您喜欢的名称），其中"StartDt"，"MTD"，"Pmonth"等作为其可用值。 启用多个值并确保它是文本参数。

然后设置报表过滤条件的每个部分，以查看"时间段"列是否与过滤条件的参数值匹配。

选项 2：

添加报表参数@filter如上所述。创建一个表变量，并将 filter 参数传入的值插入其中。

使用案例 When 将 1 或 0 传递回最后一列中的 SSRS，表示该部分是否应可见。

,Period = CASE
WHEN 'StartDt' IN (SELECT * FROM @table) then 1
ELSE 0
END

然后将筛选器设置为仅包含期间值为 1 的数据，并相应地设置报表部分的可见性。