Data source: 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;
go
Note:
Inserting columns into GROUP allows you to create Stepped or Block reports with subtotals and drilldown.
1. Tabular Report
Page> empty
Group> empty
Detail> all the columns
2. Tabular Report
Page > Country
Group> empty
Details > the rest of the column
3. Tabular Report
Page> Country, City
Group> empty
Details> the rest of the columns
Country is not repeating itself on each page by default.
4. Tabular Stepped Report with drilldown
Page> Country
Group> City, Company
Details> Quantity, UnitPrice, Discount
City and Company in the Group allow me to create drilldown by these two columns.
5. Tabular Stepped Report with subtotals and drilldown
Page> Country, City
Group> Company
Details> Quantity, UnitPrice, Discount
As I have added Company in the Group. It allows me to create subtotals and enable drilldown by Company
6. Tabular Block Report
Page> Country
Group> City, Company
Details> Quantity, UnitPrice, Discount
7. Tabular Block Report with Subtotals
Page>
Group> Country, City, Company
Details> Quantity, UnitPrice, Discount
