Sql-server – Where and how to add selective totals in SSRS

cross-applypivotsql serverssrst-sql

I need to get some totals output in SSRS that I am having difficulty putting together. I am not sure if I should do the totals for the below query within the query, or in SSRS. The output is going to be presented as one long table in SSRS.

I have some example DDL and fake desired output below, but to describe what I am doing, here is a quick snapshot of what I am trying to mimick in SSRS.

However, while only two states are pictured here, there could be 50 states. That part is done and looking good. The trouble I am having is to have the totals across all states at the bottom.

At the very bottom of this post is a quick look at my parent report in SSRS design view, if that seems the best way to go.

Below is an example source data table and my current query. The ordering doesn't really matter, it is just for ease of viewing. I am using a cross apply, but whether cross apply or pivot, it doesn't matter to me.

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

And below is the desired fake output

--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

If it matters, here is a screenshot of what I have in the detail view of the parent report. I am showing this to give context. Basically the parent report is a single cell of a table, which will feed a parameter to the sub reports, so the sub reports will only display records for a single payroll at a time. For instance, page one will be everything from payroll 1, etc…

Best Answer

maybe this might help..

first unpivot the SDI_States like this:

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;

then unpivot the 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;

then UNION the two sets and apply some aggregation with Over clause.

the final script should be something like this:

;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;

Best Answer

Related Solutions

How to add rows/columns to the table in runtime in SSRS 2008

Sql-server – connections to databases made from when using SSRS

Related Question