Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

reporting services - 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?

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...