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 Add New Report option from the context.

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

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.

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

Select Tabular as the report type and click Next.

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.

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.

Now you can see the auto-generated SSRS drill down table report from the wizard.

Auto-generated Drill Down Table Using Report Wizard

Let me do some quick formatting to the Table font and colors.

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.