SSRS: Create Grouped Report with subtotals
Data source: connecting to database Northwind
SUM(od.UnitPrice) UnitPrice ,
from Customers c
inner join Orders o
on c.CustomerID = o.CustomerID
inner join [Order Details] od
on od.OrderID = o.OrderID
group by Country,City,CompanyName,Discount
order by Country,City;
This is what I what to create
Create basic report containing 3 columns by inserting the table and text box for the heading.
There are two way how to do it.
- Right click on the details on the table, add group, parent group.
- Go to Row Groups on the bottom, Add Group, Parent Group.
After the Tablix group shows up, in the Group by: find the column you want to group the data by.
I am adding group header and group footer as well.
Group Header creates an additional row above the detail row.
Group Footer creates an extra row below the detail row.
Add Child Group : City with footer [ footer = row bellow ]
Add Total by City
I have two numeric values on this report. Right click on Quantity > Add Total. Repeat the same for UnitPrice.
Add Total by Country
Go to Row Groups window, right click on the group City > Add Total > After
Using an Expression to write> Total by: Country
Add Grand total.
If you add one more total it creates the grand total. That is the last total you can add on the report.