So erstellen Sie einen Pivot-Style-Bericht in Reporting-Services

Ich muss einen Pivot-Tabellenstil-Bericht wie dieses Beispiel in SSRS erstellen:

Tischbeispiel

Das ist meine erste von dieser Art. Wie soll ich die data formatieren? Machen Sie meine SQL-Abfrage Ausgabe im Pivot-Format oder kann ich dieses Format verwenden, um den Bericht zu füllen?

sql Ausgabe

Ich bin ein bisschen verloren

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

BEARBEITEN:

Wäre diese Frage besser zu verwenden als die erste?

Bildbeschreibung hier eingeben

 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 

Sie wollen mit einer Matrix in SSRS suchen. Hier sind einige Dinge, die Sie begonnen haben.