SSRS: Grouped Report with Drilldown
Demo: What do I want to create:
Data source: connection to NorthWind database
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
order by Country,City
ADD PARENT GROUP COUNTRY
Create a basic report by inserting a table and drag couple of fields in.
Create Group by Country:
Go to Row Groups.
Add Parent Group...
Add group header to create a stepped report.
ADD CHILD GROUP CITY
Go to Row Groups again to add another group for city.
Right click on the Country group and add a Child Group City with Add group header checked.
ADD SUBTOTAL BY CHILD GROUP CITY
Go to Row Groups, right on the Details, Add Total > After
I have created two groups: Country and city. This will add the total for the Child Group City
It looks like a subtotal by Company, but I haven't created a group for this field. This is de facto a subtotal by City. Next subtotal will be for Country and the third one will be the grand total.
you can add a simple expression to write Total by city
In the Category click on the Fields(Dataset1). Drag City field into expression builder.
Add sub total for the Parent Group Country.
Right click on the City > Add Total > After
I want to add the drilldown. I can do it by hiding the details [azure background] by the City value.
Add Drilldown functionality to Details.
Go to Row Groups > Right click Details > Group Properties
Go to Visibility tab
When the report is initially run: HIDE
Display can be toggled by this item: City
The Details are hidden, you can toggle it by City field [+]
Add Drilldown functionality to City.
I want to HIDE City Group and UNHIDE by Country Group.
Right click on the City Group > Group Properties.
Set up the Visibility tab.