Search code examples
reporting-servicesreport

SSRS report arranging rows to columns

enter image description here

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?

Solution

  • 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.