Dynamic pivot table and SSRS

Question

Dynamic pivot table and SSRS

NineIron

SSChampion

Points: 12529
More actions
November 30, 2016 at 7:36 am

#332475

I need to create a report on hospital discharges over a rolling 12 month period. The data should be pivoted with month as columns, doctor group as rows, and number of discharges as the value. I've read that SSRS doesn't work with dynamic SQL. Is there a solution to this problem?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63436 More actions · Answer 1

Maybe I'm missing something, but if you have a bunch of events over a span of time, you could have EventType on columns, Months on rows, and then a count or sum at the intersection.

Sounds like you're trying to pre-aggregate in T-SQL. Don't. Then you don't need any dynamic SQL. You can just have a single date filter, and you're off to the races.

Or did I misunderstand something?

NineIron SSChampion Points: 12529 More actions · Answer 2

I'm actually trying to recreate a report that was done in EXCEL. It was a rolling 12 months so, the first column might be March then, the next 11 months. The rows had about 10 different doctor groups and the data was the number of discharges by each doctor group.

pietlinden SSC Guru Points: 63436 More actions · Answer 3

Oh, I get it... you're trying to do the matrix as a crosstab or whatever inside SQL instead of doing it in SSRS. Doing it in SSRS is really simple.

Drop a Matrix on your design surface. Put "Doctor Groups" on Columns, "Month/Year" on Rows, COUNT(EventID) in the intersection. The stored procedure would be something like this... note that all the procedure does is return all the events in the time frame (well, it's off by a day...) The pivoting and grouping that I think has you confused is all done by SSRS under the covers. You just tell it you want to COUNT the instances of the Discharge events, and you're home free.

CREATE PROC getDoctorAppts

@StartDate DATE

AS

DECLARE @EndDate DATE

SET @EndDate = DATEADD(month, 12, @StartDate)

SELECT c.Year

, c.Month

, a.DoctorGroup

, a.ApptID

FROM Calendar c LEFT JOIN Appointment a ON c.CalendarDate = a.ApptDate

WHERE a.ApptDate>=@StartDate AND a.ApptDate<=@EndDate

Then you could have one prompt for @StartDate in your report, and just build a simple matrix. (the math may be a little off, but hopefully you get the idea... the endpoint/end date is 12 months away (maybe minus a day).

Since the span crosses years, you would have to include month and year in your stored procedure columns, because you would group by them to get your proper count (and sort by the actual event date, so that Mar-2016 comes after Dec-2015.

If you want, I could bang up a report based on AdventureWorks or whatever and post it... then you could see what I'm talking about.

NineIron SSChampion Points: 12529 More actions · Answer 4

Works fine. Thanx. However, I only want to include the Doctor groups that have >11 discharges. So, somehow use the column total to exclude certain groups. Any thoughts?

pietlinden SSC Guru Points: 63436 More actions · Answer 5

You'd have to do something in your stored procedure.

Here's an example of getting all the invoices with fewer than 3 line items:

SELECT i.InvoiceID

FROM Sales.Invoices i INNER JOIN Sales.InvoiceLines il

ON i.InvoiceID = il.InvoiceID

GROUP BY i.InvoiceID

HAVING COUNT(*)>3;

You would do the same thing, but instead of Invoices, you'd have DoctorGroup, and instead of InvoiceLines you would have Appointments.

Then you could use a HAVING clause in your T-SQL to eliminate all the groups with fewer than 11 Appointments.

NineIron SSChampion Points: 12529 More actions · Answer 6

I was hoping something could be done in SSRS. I tweak the sp.

MMartin1 One Orange Chip Points: 27786 More actions · Answer 7

I only want to include the Doctor groups that have >11 discharges.

Do you mean across the whole twelve months or just 11 or less in any one month?

One thing to consider is that an Excel file can call a stored procedure and have data produced within a worksheet. Once in Excel, doing pivots and filtering becomes more friendly. Using Excel is not as accessible to all as a SSRS report, every user would need a copy of the workbook configured to call the stored proc. Just something to keep in mind.

----------------------------------------------------

pietlinden SSC Guru Points: 63436 More actions · Answer 8

Good point! Especially if you use a pivot table or PowerPivot... but you need Excel 2010 or later for PowerPivot.