I need to generate this kind of (please see the image) report in SSRS, and I wrote below 2 SQL's
Active Appointments created by Online and Call center
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