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
