在SSRS中添加选择性总计的位置和方法

Question

我需要在SSRS中得到一些总输出，这是我在拼凑方面遇到的困难。我不确定是否应该在查询中或在SSRS中对下面的查询执行汇总。输出将在SSRS中显示为一个长表。

下面我有一些DDL示例和假的期望输出，但是为了描述我正在做的事情，下面是我试图在SSRS中模仿的一个快速快照。

然而，虽然这里只有两个州，但可能有50个州。这部分做好了，看上去也不错。我遇到的麻烦是，所有州的总数都排在最下面。

在这篇文章的最底部是快速查看我的父母报告在SSRS设计视图，如果这似乎是最好的方式。

下面是一个示例源数据表和我的当前查询。排序并不重要，只是为了便于查看。我使用交叉申请，但无论交叉申请或枢轴，这对我来说并不重要。

CREATE TABLE #EmployeeTaxes
(
     Employee int
    ,Payroll int
    ,SDI_State char(2)
    ,SUI_State char(2)
    ,SIT  DECIMAL(19,2)
    ,Employee_SDI DECIMAL(19,2)
    ,Employer_SDI DECIMAL(19,2)
    ,Employee_SUI DECIMAL(19,2)
    ,Employer_SUI DECIMAL(19,2)
    ,SIT_Gross  DECIMAL(19,2)
    ,Employee_SDI_Gross DECIMAL(19,2)
    ,Employer_SDI_Gross DECIMAL(19,2)
    ,Employee_SUI_Gross DECIMAL(19,2)
    ,Employer_SUI_Gross DECIMAL(19,2)
)

--Data in source table
INSERT INTO #EmployeeTaxes
VALUES   (1, 1, 'CA', 'CA', 100.00, 150.00,  50.00, 200.00, 200.00, 20000.00, 20000.00, 20000.00, 20000.00, 20000.00)
        ,(2, 1, 'OR', 'OR', 200.00, 250.00,  70.00, 300.00, 300.00, 21000.00, 21000.00, 21000.00, 21000.00, 21000.00)
        ,(3, 1, 'CA', 'CA', 100.00, 150.00,  50.00, 200.00, 200.00, 20000.00, 20000.00, 20000.00, 20000.00, 20000.00)
        ,(1, 2, 'CA', 'CA', 200.00, 250.00, 150.00, 300.00, 300.00, 25000.00, 25000.00, 25000.00, 25000.00, 25000.00)
        ,(2, 2, 'OR', 'OR', 300.00, 350.00, 250.00, 400.00, 400.00, 26000.00, 26000.00, 26000.00, 26000.00, 26000.00)
        ,(3, 2, 'CA', 'CA', 200.00, 250.00, 150.00, 300.00, 300.00, 25000.00, 25000.00, 25000.00, 25000.00, 25000.00)
        ,(1, 3, 'CA', 'CA', 300.00, 350.00, 250.00, 400.00, 400.00, 30000.00, 30000.00, 30000.00, 30000.00, 30000.00)
        ,(2, 3, 'OR', 'OR', 400.00, 450.00, 350.00, 500.00, 500.00, 31000.00, 31000.00, 31000.00, 31000.00, 31000.00)
        ,(3, 3, 'CA', 'CA', 300.00, 350.00, 250.00, 400.00, 400.00, 30000.00, 30000.00, 30000.00, 30000.00, 30000.00)

--My Current query
SELECT Payroll
      ,v.Item                       AS [Witholding]
      ,SUM(v.TaxValue)              AS Tax  
FROM #EmployeeTaxes
CROSS APPLY (
    VALUES (SDI_STATE + ' Employee SDI', EMPLOYEE_SDI)
          ,(SDI_STATE + ' Employer SDI', EMPLOYER_SDI)
          ,(SDI_STATE + ' Employee SDI Gross', EMPLOYEE_SDI_GROSS)
          ,(SDI_STATE + ' Employer SDI Gross', EMPLOYER_SDI_GROSS)
          ,(SDI_STATE + ' SIT', SIT)
          ,(SDI_STATE + ' SIT Gross', SIT_GROSS)
          ,(SUI_STATE + ' Employee SUI', EMPLOYEE_SUI)
          ,(SUI_STATE + ' Employer SUI', EMPLOYER_SUI)
          ,(SUI_STATE + ' Employee SUI Gross', EMPLOYEE_SUI_GROSS)
          ,(SUI_STATE + ' Employer SUI Gross', EMPLOYER_SUI_GROSS)
          ,(SDI_STATE + ' Total', SIT + EMPLOYEE_SDI + EMPLOYER_SDI + EMPLOYEE_SUI + EMPLOYER_SUI)
) v (Item, TaxValue)
GROUP BY Payroll, v.Item
ORDER BY PAYROLL, Witholding
DROP TABLE #EmployeeTaxes

下面是所需的假输出

--Fake desired output
CREATE TABLE #FakeDesiredOutput
(
     Payroll int
    ,Witholding varchar(100)
    ,Tax DECIMAL(19,2)
)
INSERT INTO #FakeDesiredOutput
VALUES (1,'CA SIT',           200.00)
      ,(1,'CA Employee_SDI',  300.00)
      ,(1,'CA Employer_SDI',  100.00)
      ,(1,'CA Employee_SUI',  400.00)
      ,(1,'CA Employer_SUI',  400.00)
      ,(1,'CA Total',        1400.00)
      ,(2,'CA SIT',           400.00)
      ,(2,'CA Employee_SDI',  500.00)
      ,(2,'CA Employer_SDI',  300.00)
      ,(2,'CA Employee_SUI',  600.00)
      ,(2,'CA Employer_SUI',  600.00)
      ,(2,'CA Total',        2400.00)
      ,(3,'CA SIT',           600.00)
      ,(3,'CA Employee_SDI',  700.00)
      ,(3,'CA Employer_SDI',  500.00)
      ,(3,'CA Employee_SUI',  800.00)
      ,(3,'CA Employer_SUI',  800.00)
      ,(3,'CA Total',        3400.00)
      ,(1,'OR SIT',           200.00)
      ,(1,'OR Employee_SDI',  250.00)
      ,(1,'OR Employer_SDI',  70.00)
      ,(1,'OR Employee_SUI',  300.00)
      ,(1,'OR Employer_SUI',  300.00)
      ,(1,'OR Total',        1120.00)
      ,(2,'OR SIT',           300.00)
      ,(2,'OR Employee_SDI',  350.00)
      ,(2,'OR Employer_SDI',  250.00)
      ,(2,'OR Employee_SUI',  400.00)
      ,(2,'OR Employer_SUI',  400.00)
      ,(2,'OR Total',        1700.00)
      ,(3,'OR SIT',           400.00)
      ,(3,'OR Employee_SDI',  440.00)
      ,(3,'OR Employer_SDI',  350.00)
      ,(3,'OR Employee_SUI',  500.00)
      ,(3,'OR Employer_SUI',  500.00)
      ,(3,'OR Total',        2190.00)
      --gross
      ,(1,'CA SIT_Gross',           20000.00)
      ,(1,'CA Employee_SDI_Gross',  20000.00)
      ,(1,'CA Employer_SDI_Gross',  20000.00)
      ,(1,'CA Employee_SUI_Gross',  20000.00)
      ,(1,'CA Employer_SUI_Gross',  20000.00)
      ,(2,'CA SIT_Gross',           25000.00)
      ,(2,'CA Employee_SDI_Gross',  25000.00)
      ,(2,'CA Employer_SDI_Gross',  25000.00)
      ,(2,'CA Employee_SUI_Gross',  25000.00)
      ,(2,'CA Employer_SUI_Gross',  25000.00)
      ,(3,'CA SIT_Gross',           30000.00)
      ,(3,'CA Employee_SDI_Gross',  30000.00)
      ,(3,'CA Employer_SDI_Gross',  30000.00)
      ,(3,'CA Employee_SUI_Gross',  30000.00)
      ,(3,'CA Employer_SUI_Gross',  30000.00)
      ,(1,'OR SIT_Gross',           21000.00)
      ,(1,'OR Employee_SDI_Gross',  21000.00)
      ,(1,'OR Employer_SDI_Gross',  21000.00)
      ,(1,'OR Employee_SUI_Gross',  21000.00)
      ,(1,'OR Employer_SUI_Gross',  21000.00)
      ,(2,'OR SIT_Gross',           26000.00)
      ,(2,'OR Employee_SDI_Gross',  26000.00)
      ,(2,'OR Employer_SDI_Gross',  26000.00)
      ,(2,'OR Employee_SUI_Gross',  26000.00)
      ,(2,'OR Employer_SUI_Gross',  26000.00)
      ,(3,'OR SIT_Gross',           31000.00)
      ,(3,'OR Employee_SDI_Gross',  31000.00)
      ,(3,'OR Employer_SDI_Gross',  31000.00)
      ,(3,'OR Employee_SUI_Gross',  31000.00)
      ,(3,'OR Employer_SUI_Gross',  31000.00)
      --Totals
      ,(1,'Total SIT',          400.00)
      ,(1,'Total Employee_SDI', 550.00)
      ,(1,'Total Employer_SDI', 170.00)
      ,(1,'Total Employee_SUI', 700.00)
      ,(1,'Total Employer_SUI', 700.00)
      ,(2,'Total SIT',          700.00)
      ,(2,'Total Employee_SDI', 850.00)
      ,(2,'Total Employer_SDI', 550.00)
      ,(2,'Total Employee_SUI', 1000.00)
      ,(2,'Total Employer_SUI', 1000.00)
      ,(3,'Total SIT',          1000.00)
      ,(3,'Total Employee_SDI', 1140.00)
      ,(3,'Total Employer_SDI', 850.00)
      ,(3,'Total Employee_SUI', 1300.00)
      ,(3,'Total Employer_SUI', 1300.00)
      ,(1,'Total SIT_Gross',          41000.00)
      ,(1,'Total Employee_SDI_Gross', 41000.00)
      ,(1,'Total Employer_SDI_Gross', 41000.00)
      ,(1,'Total Employee_SUI_Gross', 41000.00)
      ,(1,'Total Employer_SUI_Gross', 41000.00)
      ,(2,'Total SIT_Gross',          51000.00)
      ,(2,'Total Employee_SDI_Gross', 51000.00)
      ,(2,'Total Employer_SDI_Gross', 51000.00)
      ,(2,'Total Employee_SUI_Gross', 51000.00)
      ,(2,'Total Employer_SUI_Gross', 51000.00)
      ,(3,'Total SIT_Gross',          61000.00)
      ,(3,'Total Employee_SDI_Gross', 61000.00)
      ,(3,'Total Employer_SDI_Gross', 61000.00)
      ,(3,'Total Employee_SUI_Gross', 61000.00)
      ,(3,'Total Employer_SUI_Gross', 61000.00)

SELECT * FROM #FakeDesiredOutput
--ORDER BY option, just for ease of reading
ORDER BY Payroll, Witholding
DROP TABLE #FakeDesiredOutput

如果这很重要，下面是我在父报表的详细视图中的截图。我展示这是为了给出背景。基本上，父报表是表的单个单元格，它将为子报表提供一个参数，因此子报表一次只显示单个薪资的记录。例如，第一页将是所有从工资1，等等.

Answer 1

也许这会有帮助..。

首先，像这样解除SDI_States的枢轴：

SELECT  Payroll ,
        CONCAT([SDI_State], ' ', TaxType) AS Item ,
        [SDI_State] AS StateName ,
        TaxValue ,
        TaxType
FROM    #EmployeeTaxes UNPIVOT ( TaxValue FOR TaxType IN ( [SIT],
                                                           [Employee_SDI],
                                                           [Employer_SDI],
                                                           [SIT_Gross],
                                                           [Employee_SDI_Gross],
                                                           [Employer_SDI_Gross] ) ) AS Taxes;

然后解除SUI_States的枢轴：

SELECT  Payroll ,
        CONCAT([SUI_State], ' ', TaxType) AS Item ,
        [SUI_State] AS StateName ,
        TaxValue ,
        TaxType
FROM    #EmployeeTaxes UNPIVOT ( TaxValue FOR TaxType IN ( [Employee_SUI],
                                                           [Employer_SUI],
                                                           [Employee_SUI_Gross],
                                                           [Employer_SUI_Gross] ) ) AS Taxes;

然后将这两个集合合并起来，并应用带Over子句的聚合。

最后的脚本应该如下所示：

;WITH    Result ( Payroll, Item ,StateName, TaxValue, TaxType )
      AS ( SELECT   Payroll ,
                    CONCAT([SDI_State],' ',TaxType) AS Item,
                    [SDI_State] AS StateName ,
                    TaxValue ,
                    TaxType
           FROM     #EmployeeTaxes UNPIVOT ( TaxValue FOR TaxType IN ( [SIT],
                                                          [Employee_SDI],
                                                          [Employer_SDI],
                                                          [SIT_Gross],
                                                          [Employee_SDI_Gross],
                                                          [Employer_SDI_Gross] ) ) AS Taxes
           UNION
           SELECT   Payroll ,
                    CONCAT([SUI_State],' ',TaxType) AS Item,
                    [SUI_State] AS StateName ,
                    TaxValue ,
                    TaxType
           FROM     #EmployeeTaxes UNPIVOT ( TaxValue FOR TaxType IN ( [Employee_SUI],[Employer_SUI],[Employee_SUI_Gross],[Employer_SUI_Gross] ) ) AS Taxes
         )
SELECT  Result.Payroll,
        Result.Item AS [Witholding],
        SUM(Result.TaxValue)OVER(PARTITION BY Result.Payroll,Result.Item) AS Tax,
        SUM(CASE WHEN Result.TaxType IN ('SIT','EMPLOYEE_SDI' , 'EMPLOYER_SDI' , 'EMPLOYEE_SUI', 'EMPLOYER_SUI') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll,Result.StateName) AS [Tax Total],
        SUM(CASE WHEN Result.TaxType IN ('Employee_SDI') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll) AS [Employee_SDI Total],
        SUM(CASE WHEN Result.TaxType IN ('Employer_SDI') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll) AS [Employer_SDI Total],
        SUM(CASE WHEN Result.TaxType IN ('SIT_Gross') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll) AS [SIT_Gross Total],
        SUM(CASE WHEN Result.TaxType IN ('Employee_SDI_Gross') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll) AS [Employee_SDI_Gross Total],
        SUM(CASE WHEN Result.TaxType IN ('Employer_SDI_Gross') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll) AS [Employer_SDI_Gross Total],
        SUM(CASE WHEN Result.TaxType IN ('Employee_SUI') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll) AS [Employee_SUI Total],
        SUM(CASE WHEN Result.TaxType IN ('Employer_SUI') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll) AS [Employer_SUI Total],
        SUM(CASE WHEN Result.TaxType IN ('Employee_SUI_Gross') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll) AS [Employee_SUI_Gross Total],
        SUM(CASE WHEN Result.TaxType IN ('Employer_SUI_Gross') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll) AS [Employer_SUI_Gross Total],
        SUM(CASE WHEN Result.TaxType IN ('SIT') THEN Result.TaxValue END)OVER(PARTITION BY Result.Payroll) AS [SIT Total]
FROM    Result;