SSRS Stacked Bar Chart with multi grouping

Question

I have requirement like this: We have SharePoint List with requests that are opened and closed. I want to show these requests in stacked bar chart with open vs closed for the given period. I acheived this by creating a dataset that filters the requests list for the given date by using creation date column.

But my client is asking, open requests count should be displayed based on creation date and then closed requests count should be displayed based on modified date. For example, I open a ticket today and close that ticket tomorrow so this ticket should be displayed under tomorrow's date. Please help me if any body has done grouping with different columns while stacking in SSRS.

Thanks for your help.

Chris Latta Chris Latta · Accepted Answer · 2012-10-30T13:10:54

Just let SQL do the heavy lifting for you:

SELECT ReportDate, SUM(Opened) AS Opened, SUM(Closed) AS Closed
FROM (
    SELECT Created AS ReportDate, 1 AS Opened, 0 AS Closed
    FROM RequestTable
    UNION ALL
    SELECT Modified AS ReportDate, 0 AS Opened, 1 AS Closed
    FROM RequestTable
    WHERE Modified IS NOT NULL
)
GROUP BY ReportDate

assuming the modified date gets set when the request is closed and the dates have no time component.

SSRS Stacked Bar Chart with multi grouping

1 Answers