reporting services - SSRS report arranging rows to columns

Question

I need to generate this kind of (please see the image) report in SSRS, and I wrote below 2 SQL's

1. Active Appointments created by Online and Call center

2. Cancelled Appointments by Online and Call center

   with cte as ( select ATS.[Description] as AppointmentSource, AST.AppointmentStatusName, Month(A.Appointmentdate) as MonthNumber, Count(A.AppointmentID) as NumberOfAppointments from Appointment A inner join AppointmentStatus AST ON AST.AppointmentStatusID = A.AppointmentStatusID inner join AppointmentSource ATS ON ATS.AppointmentSourceID = A.AppointmentSourceID where A.AppointmentDate between '01/01/2020' and '12/31/2020' AND ATS.AppointmentSourceID in (1,3) AND A.AppointmentStatusID = 1 -- Active Group by Month(A.AppointmentDate), ATS.[Description], AST.AppointmentStatusName

   UNION

   select ATS.[Description] as AppointmentSource, AST.AppointmentStatusName, Month(A.Appointmentdate) as MonthNumber, Count(A.AppointmentID) as NumberOfAppointments from Appointment A inner join AppointmentStatus AST ON AST.AppointmentStatusID = A.AppointmentStatusID inner join AppointmentSource ATS ON ATS.AppointmentSourceID = A.AppointmentSourceID where A.AppointmentDate between '01/01/2020' and '12/31/2020' AND ATS.AppointmentSourceID in (1,3) AND A.AppointmentStatusID = 2 -- Cancelled Group by Month(A.AppointmentDate), ATS.[Description], AST.AppointmentStatusName )

   select AppointmentSource, MonthNumber, NumberOfAppointments, AppointmentStatusName from cte order by AppointmentSource, AppointmentStatusName asc, MonthNumber

How to arrange the results by Month in the SSRS report ? Is there a better way to write the SQL and arrange in SSRS report?

question from:https://stackoverflow.com/questions/65830747/ssrs-report-arranging-rows-to-columns

Answer 1

Ignoring the actual SQL for now (it would be better to show the results of this rather than the t-sql)...

I think you have enough to build the report, with the exception of the Year which you don't appear to be returning. Once you have corrected this...

1. Create a new report and create your dataset as normal
2. Add a Matrix (not a table) to your report.
3. Add 2 Row Groups first goruped by Year and the second (child group) grouped by AppointmentSource
4. Add a column group, grouped by MonthNumber
5. Choose NumberOfAppointments as the value

That's it, you should get the results as above more or less.