• Marek Vavrovic

SSRS: Drill Through Report


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:




2 views0 comments