SSRS: Create Grouped Report with subtotals

Data source: connecting to database Northwind

Dataset:

Select c.Country,

c.City,

c.CompanyName,

SUM(od.Quantity) Quantity,

SUM(od.UnitPrice) UnitPrice ,

od.Discount

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;

Demo:

This is what I what to create

Step 1

Create basic report containing 3 columns by inserting the table and text box for the heading.

Step 2:

Add Group

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.

Step 3:

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.

Step 4:

Group Header creates an additional row above the detail row.

Group Footer creates an extra row below the detail row.

Step 5:

Add Child Group : City with footer [ footer = row bellow ]

Step 6:

Add Total by City

I have two numeric values on this report. Right click on Quantity > Add Total. Repeat the same for UnitPrice.

Report:

Step 7

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

Report

Step 8

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.

Report