How to create a pivot style report in reporting services

Question

I need to create a pivot table style report like this example in SSRS:

This is my first of this kind. How should I format the data? Do make my SQL query output in pivot format or can I use this format to populate the report?

I'm a bit lost.

--Total Petitions Filed
SELECT
    '' AS EventType,
    'Total Petitions Filed' AS EventDesc,
    CASE
        WHEN DATEPART(MONTH, e.EventDt) = '1' THEN 'Jan'
        WHEN DATEPART(MONTH, e.EventDt) = '2' THEN 'Feb'
        WHEN DATEPART(MONTH, e.EventDt) = '3' THEN 'Mar'
        WHEN DATEPART(MONTH, e.EventDt) = '4' THEN 'Apr'
        WHEN DATEPART(MONTH, e.EventDt) = '5' THEN 'May'
        WHEN DATEPART(MONTH, e.EventDt) = '6' THEN 'Jun'
        WHEN DATEPART(MONTH, e.EventDt) = '7' THEN 'Jul'
        WHEN DATEPART(MONTH, e.EventDt) = '8' THEN 'Aug'
        WHEN DATEPART(MONTH, e.EventDt) = '9' THEN 'Sep'
        WHEN DATEPART(MONTH, e.EventDt) = '10' THEN 'Oct'
        WHEN DATEPART(MONTH, e.EventDt) = '11' THEN 'Nov'
        WHEN DATEPART(MONTH, e.EventDt) = '12' THEN 'Dec'
        ELSE ''
    END AS [Month],
    COUNT(*) PetitionsFiled
FROM    jw50_Case c
    INNER JOIN jw50_Event e ON e.CaseID = c.CaseID
WHERE   c.caseTypeCode IN ('TY703','TY704') 
    AND e.EventTypeCode = 'DE704' 
    AND e.EventDt >= @PetitionStartDt 
    AND e.EventDt <= @PetitionEndDt
GROUP BY DATEPART(MONTH, e.EventDt)
ORDER BY DATEPART(MONTH, e.EventDt)

EDIT:

Would this query be better to use than the first?

DECLARE @PetitionStartDt datetime, @PetitionEndDt datetime, @DispoStartDt datetime, @DispoEndDt datetime

SET @PetitionStartDt = '1-1-2015'
SET @PetitionEndDt = '12-31-2015'
SET @DispoStartDt = '1-1-2015'
SET @DispoEndDt = '12-31-2015'

--CREATE TABLE #FeeArb (EventType varchar(256), EventDesc varchar(256), EventDt smalldatetime, ItemCount int)

    --Total Petitions Filed
    SELECT
        '' AS EventType,
        'Total Petitions Filed' AS EventDesc,
        ISNULL(COUNT(case when month(e.EventDt) = 1 then e.EventDt end), 0) Jan,
        ISNULL(COUNT(case when month(e.EventDt) = 2 then e.EventDt end), 0) Feb,
        ISNULL(COUNT(case when month(e.EventDt) = 3 then e.EventDt end), 0) Mar,
        ISNULL(COUNT(case when month(e.EventDt) = 4 then e.EventDt end), 0) Apr,
        ISNULL(COUNT(case when month(e.EventDt) = 5 then e.EventDt end), 0) May,
        ISNULL(COUNT(case when month(e.EventDt) = 6 then e.EventDt end), 0) Jun,
        ISNULL(COUNT(case when month(e.EventDt) = 7 then e.EventDt end), 0) Jul,
        ISNULL(COUNT(case when month(e.EventDt) = 8 then e.EventDt end), 0) Aug,
        ISNULL(COUNT(case when month(e.EventDt) = 9 then e.EventDt end), 0) Sep,
        ISNULL(COUNT(case when month(e.EventDt) = 10 then e.EventDt end), 0) Oct,
        ISNULL(COUNT(case when month(e.EventDt) = 11 then e.EventDt end), 0) Nov,
        ISNULL(COUNT(case when month(e.EventDt) = 12 then e.EventDt end), 0) Dec,
        ISNULL(COUNT(e.EventDt), 0) Total

        --CASE
        --  WHEN DATEPART(MONTH, e.EventDt) = '1' THEN 'Jan'
        --  WHEN DATEPART(MONTH, e.EventDt) = '2' THEN 'Feb'
        --  WHEN DATEPART(MONTH, e.EventDt) = '3' THEN 'Mar'
        --  WHEN DATEPART(MONTH, e.EventDt) = '4' THEN 'Apr'
        --  WHEN DATEPART(MONTH, e.EventDt) = '5' THEN 'May'
        --  WHEN DATEPART(MONTH, e.EventDt) = '6' THEN 'Jun'
        --  WHEN DATEPART(MONTH, e.EventDt) = '7' THEN 'Jul'
        --  WHEN DATEPART(MONTH, e.EventDt) = '8' THEN 'Aug'
        --  WHEN DATEPART(MONTH, e.EventDt) = '9' THEN 'Sep'
        --  WHEN DATEPART(MONTH, e.EventDt) = '10' THEN 'Oct'
        --  WHEN DATEPART(MONTH, e.EventDt) = '11' THEN 'Nov'
        --  WHEN DATEPART(MONTH, e.EventDt) = '12' THEN 'Dec'
        --  ELSE ''
        --END AS [Month],
        --COUNT(*) PetitionsFiled
    FROM    jw50_Case c
        INNER JOIN jw50_Event e ON e.CaseID = c.CaseID
    WHERE   c.caseTypeCode IN ('TY703','TY704') 
        AND e.EventTypeCode = 'DE704' 
        AND e.EventDt >= @PetitionStartDt 
        AND e.EventDt <= @PetitionEndDt
    --GROUP BY DATEPART(MONTH, e.EventDt)
    --ORDER BY DATEPART(MONTH, e.EventDt)

UNION ALL

--Total Awards Issued
SELECT
        '' AS EventType,
        'Total Awards Issued' AS EventDesc,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 1 then dispo.DispoDt end), 0) Jan,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 2 then dispo.DispoDt end), 0) Feb,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 3 then dispo.DispoDt end), 0) Mar,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 4 then dispo.DispoDt end), 0) Apr,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 5 then dispo.DispoDt end), 0) May,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 6 then dispo.DispoDt end), 0) Jun,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 7 then dispo.DispoDt end), 0) Jul,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 8 then dispo.DispoDt end), 0) Aug,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 9 then dispo.DispoDt end), 0) Sep,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 10 then dispo.DispoDt end), 0) Oct,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 11 then dispo.DispoDt end), 0) Nov,
        ISNULL(COUNT(case when month(dispo.DispoDt) = 12 then dispo.DispoDt end), 0) Dec,
        ISNULL(COUNT(dispo.DispoDt), 0) Total
FROM    jw50_Condition co
    LEFT JOIN (
                SELECT DISTINCT CaseID, MIN(DispoDt) DispoDt 
                FROM    jw50_CountInvPers 
                WHERE   DispoCode = 'CD718' 
                    AND DispoDt >= @DispoStartDt 
                    AND DispoDt <= @DispoEndDt 
                GROUP BY CaseID) dispo ON dispo.CaseID = co.CaseID
    INNER JOIN  jw50_Case c ON c.CaseID = co.CaseID
WHERE   c.caseTypeCode IN ('TY703','TY704') 
    AND co.ConditionTypeCode IN ('SC727','SC728') 
    AND DispoDt >= @DispoStartDt 
    AND DispoDt <= @DispoEndDt  

UNION ALL

-- Dispositions
SELECT
        'Dispo' AS EventType,
        cip.DispoDesc AS EventDesc,
        ISNULL(COUNT(case when month(cip.DispoDt) = 1 then cip.DispoDt end), 0) Jan,
        ISNULL(COUNT(case when month(cip.DispoDt) = 2 then cip.DispoDt end), 0) Feb,
        ISNULL(COUNT(case when month(cip.DispoDt) = 3 then cip.DispoDt end), 0) Mar,
        ISNULL(COUNT(case when month(cip.DispoDt) = 4 then cip.DispoDt end), 0) Apr,
        ISNULL(COUNT(case when month(cip.DispoDt) = 5 then cip.DispoDt end), 0) May,
        ISNULL(COUNT(case when month(cip.DispoDt) = 6 then cip.DispoDt end), 0) Jun,
        ISNULL(COUNT(case when month(cip.DispoDt) = 7 then cip.DispoDt end), 0) Jul,
        ISNULL(COUNT(case when month(cip.DispoDt) = 8 then cip.DispoDt end), 0) Aug,
        ISNULL(COUNT(case when month(cip.DispoDt) = 9 then cip.DispoDt end), 0) Sep,
        ISNULL(COUNT(case when month(cip.DispoDt) = 10 then cip.DispoDt end), 0) Oct,
        ISNULL(COUNT(case when month(cip.DispoDt) = 11 then cip.DispoDt end), 0) Nov,
        ISNULL(COUNT(case when month(cip.DispoDt) = 12 then cip.DispoDt end), 0) Dec,
        ISNULL(COUNT(cip.DispoDt), 0) Total
FROM    jw50_Case c 
    INNER JOIN  jw50_CountInvPers cip ON cip.CaseID = c.CaseID
WHERE   c.caseTypeCode IN ('TY703','TY704') 
    AND cip.DispoDt >= @DispoStartDt
    AND DispoDt <= @DispoEndDt
    AND cip.DispoCode IS NOT NULL
GROUP BY cip.DispoDesc

In general, it will be much easier to use queries whose output is structured like your first one. In an SSRS matrix, you can create both row and column groups to dynamically shape your output horizontally and vertically. With the second query's output, you will have to make explicit calls to each month's column in the report. — Wesley Marshall, Jan 14 '16 at 15:37

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

You want to look into using a matrix in SSRS. Here are some things to get you started.

edited May 23 '17 at 11:52

Community

1
1

answered Jan 13 '16 at 21:47

db_brad

903
6
22

How to create a pivot style report in reporting services

1 Answers1