Home  >  Blog  >   SSRS

SSRS Grouping data for multi-level reports

Rating: 4
  
 
6900

Grouping Data For Multi-Level Reports

In the same way that drill-down reports allow a user to select a category and then run a report to view details of individual items within that category, you can create a multilevel report that does the same thing inside one report. The difference between multilevel reports and drill-down reports is that multi-level reports contain all the information in one place, but some of it is hidden until it is selected.

If you would like to become an SSRS (SQL Server Reporting Services) certified professional, then visit Mindmajix - A Global online training platform: "SSRS Training and Certification Course". This course will help you to achieve excellence in this domain.

In the Try it for this section, a report is built that shows data in a hierarchy, starting with the year, then a month, and finally individual employees sales within each month.

report with multi levels

A REPORT WITH MULTIPLE LEVELS. THE VERTICAL BARS SHOW GROUPS

                                                                           (Related Page: Passing Parameters To A Report In SSRS)

Try it

1. Create a new Report by right-clicking on Reports in the Solution Explorer and choosing Add > New item. Name the report MultipleLevel

2. Create a Report data source called AdventureWorks that references the shared data source of the same name.

3. Create a dataset that uses the report data source and use the following query:

SELECT
           p.FirstName + p.LastName AS FullName,
           SUM(soh.TotalDue) AS TotalDue,
           YEAR(soh.OrderDate) AS [Year],
           MONTH(soh.OrderDate) AS [Month]
FROM
           Sales.SalesOrderHeader soh
           INNER JOIN Person.Person p
                   ON p.BusinessEntityID = soh.SalesPersonID
GROUP BY
           p.FirstName + p.LastName,
           YEAR(soh.OrderDate),
           MONTH(soh.OrderDate)

ORDER BY
[Year]

MindMajix Youtube Channel

                                                                                   (Related page: Working With Report Models in SSRS)

                                    Frequently Asked SSRS Interview Questions & Answers

4. Click OK to return to the report design surface.

5. From the toolbox, drag a table on to the report and then put the FullName and TotalDue fields into the first two data cells.

Solution Explorer

6. Right-click on the FullName data cell and choose Add Group > Row Group > Parent Group from the menu.

7. Choose Month for the Group By value, select Add group header, and click OK.

8. Right-click on the newly added Month data cell (which will be two rows tall) and repeat the previous two steps, but this time select Year as the Group By value. Again, add a group header for this grouping.

9. Preview the report to see how it looks.

Preview the report

 

Explore SSRS Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!

List of Related Microsoft Certification Courses:

 SSIS Power BI
 SSAS SQL Server
 SCCM SQL Server DBA
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator

 

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
SSRS TrainingMar 23 to Apr 07View Details
SSRS TrainingMar 26 to Apr 10View Details
SSRS TrainingMar 30 to Apr 14View Details
SSRS TrainingApr 02 to Apr 17View Details
Last updated: 03 Apr 2023
About Author

Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

read more
Recommended Courses

1 / 15