Drill Down Table Using SSRS Report Wizard

This SSRS article shows how to create a drill down table using a enable drilldown option in the report wizard with an example. For this demonstration, we use the shared data source pointing to Adventure Works DW 2019. 

Create a Drill Down Table Using SSRS Report Wizard

In the Solution Explorer, right-click on the Reports folder and select the Add New Report option from the context.

Add New Report option

Open the report wizard with the following welcome page. Click the next button to continue.

Welcome Page

The next page is to select the data source. You can either use the shared data source you created earlier or create the new one here. For now, we are going with a shared data source pointing to Adventure Works DW.

Shared data Source

The next page is about designing a query. The Sql query that we use for this SSRS example is:

SELECT Geo.EnglishCountryRegionName AS Country, Geo.StateProvinceName AS State,
Geo.City,SUM(Fact.OrderQuantity) AS Orders, SUM(Fact.TotalProductCost) AS ProductCost,
SUM(Fact.SalesAmount) AS Sales, SUM(Fact.TaxAmt) AS Tax
FROM FactInternetSales AS Fact INNER JOIN DimCustomer
ON DimCustomer.CustomerKey = Fact.CustomerKey
JOIN DimGeography AS Geo ON DimCustomer.GeographyKey = Geo.GeographyKey
GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName, Geo.City
Sql Query for DataSet

Select Tabular as the report type and click Next.

Choose Tabular Report Type

On the next page, we have to design the table with the available fields. Here we have three options Page, Group, and Details. In this SSRS wizard example, we want to perform a drill down action on a table report. So, add the country to the Group section to achieve drill down action on country names. Next, add the remaining fields to the details section.

Available Columns to design SSRS Stepped Table using Report Wizard

Next, we must choose the Table Layout as Stepped to enable the drilldown to the report. Finally, checkmark the Include subtotals option and hit the Next button.

Enable Drilldown option to create Drill Down Table Using Report Wizard

Change the default report name and click the Finish button.

Change the Default name

Now you can see the auto-generated SSRS drill down table report from the wizard. Let me do some quick formatting to the Table font and colors.

Format the Font style, size, and color

The report preview shows a little plus button beside each country to expand or drill down into details. Apart from that, it also displays the subtotals for each country.

Drill Down Table Using Report Wizard preview

Let me expand two regions to show the state-wise orders and sales.

Expand Drill Down Table Using Report Wizard