SSRS Grouping data for multi-level reports
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.
In the Try it for this section, a report is built that shows data in a hierarchy, starting with year, then month, and finally individual employees sales within each month.
A REPORT WITH MULTIPLE LEVELS. THE VERTICAL BARS SHOW GROUPS
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:
p.FirstName + p.LastName AS FullName,
SUM(soh.TotalDue) AS TotalDue,
YEAR(soh.OrderDate) AS [Year],
MONTH(soh.OrderDate) AS [Month]
INNER JOIN Person.Person p
ON p.BusinessEntityID = soh.SalesPersonID
p.FirstName + p.LastName,
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.
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.