Demo : I want to create a drill through report like this one. If you click on the country, you'll be transferred into the second report containing the detail for cities, and if you click on the city, you'll be transferred into the third report containing the details for the company. You need to create 3 independent reports and set the ACTION in the data field property to GO TO REPORT.
Data source: Northwind database
Dataset:
Select c.Country,
SUm(od.UnitPrice* od.Quantity*(1-od.Discount)) As NetSales
from Customers c
inner join Orders o
on c.CustomerID = o.CustomerID
inner join [Order Details] od
on od.OrderID = o.OrderID
Group by c.Country
This is the first dataset, as you click on the country you will be transferred into the second report containing more details.
Step 1
Creating Dataset from the query above.
Step 2
Create the first report with grouped data.
Step 3
Add a new report > Level1Report< after I click on the country it will take me into this new report with more details on that.
Step 4
Create a dataset for the second report. This is an embedded dataset.
Notice, in the WHERE clause, I am using a parameter @Country because I want to return the data for a specific country.
Select c.Country,c.City,c.CompanyName,
SUm(od.UnitPrice* od.Quantity*(1-od.Discount)) As NetSales
from Customers c
inner join Orders o
on c.CustomerID = o.CustomerID
inner join [Order Details] od
on od.OrderID = o.OrderID
Where c.Country = @Country
Group by c.Country,c.City,c.CompanyName
Step 5
Create a report for the 1. level of details. I don not need the @Country parameter to be displayed. Go to parameter properties and set it as Hidden.
Step 6
Go back to the first report containing the data grouped by the country. Right click on the country detail field > Text box Properties...
General tab > write something in the ToolTip.
Step 7
Go to Action tab
Enable as an action: Go to report
Specify a report and parameters.
Report preview
Step 8
Adding Leve2 report displaying details by the Company
Step 9
Adding an embedded dataset
Select c.City,
c.CompanyName,
OD.UnitPrice,
OD.Quantity,
OD.Discount
from Customers c
inner join Orders o
on c.CustomerID = o.CustomerID
inner join [Order Details] od
on od.OrderID = o.OrderID
WHERE CITY = @City
If someone click on the city in the previous report Level1Report will be transferred into the Level2Report, which is this one.
Step 10
Insert a table and create the report for the company details. Set the @City parameter as Hidden.
Step 11
Go back to the Level1Report, right click on the City detail field.
Go to Text Box Properties, Action tab. Specify the report with company details and @City parameter
Report preview: