Drill down in SQL Server Reporting Services 2008

11/06/2011 -

Let's start with a quick definition for the topic of this article: a drill down in this context is a procedure that changes views, from summary to detailed and vice versa by focusing in on something (e.g. mouse click on a specific portion of the report). For the following example, I will use SQL Server sample Northwind database. The three tables that I’ll use are Product, ProductCategory and ProductModel. This is the query we will be using for our data source: 

SELECT
    ProductCategory.Name AS ProductCategory,
    ProductModel.Name AS ProductModel,
    Product.Name AS ProductName
FROM
    SalesLT.Product
    INNER JOIN SalesLT.ProductCategory ON Product.ProductCategoryID = ProductCategory.ProductCategoryID
    INNER JOIN SalesLT.ProductModel ON Product.ProductModelID = ProductModel.ProductModelID
  
WHERE
    ProductCategory.Name IN ('Gloves', 'Jerseys', 'Mountain Bikes')
  
ORDER BY
    ProductCategory.Name,
    ProductModel.Name,
    Product.Name

It returns the following values (we will use them in our report example):

Basically, what we have here are 3 Product Categories (Gloves, Jerseys and Mountain Bikes), each with two or more Product Models, while each Product Model has three or more Products. To demonstrate drill-down, I created a simple report. This is the table:

There are two groups (Product Category and Product Model) and a details section:


These are the results before we implemented the drill-down:

This is how it looks like after the drill down has been implemented and a few sections have been "drilled-down":

As you can see, the visibility of some items can be triggered by clicking on a certain portion of the table. In this case, we chose to trigger visibility of Product Names (details) by clicking on its parent group Product Model. Similarly, the visibility of the Product Models can be controlled by its parent group Product Category. How I did it? –Fairly simply, actually.

Let’s first set the drilldown for our Product Model: Open groupProductModel properties. Here, you need to check the Display can be toggled by this report item and select txtProductCategory which will be the trigger for showing/hiding the Product Model group. You can optionally set this group to be hidden when the report is generated.

To set the drill-down for the details, open details properties, check the Display can be toggled by this report item and select txtProductModel.


What should be noted is that this function will only work when a report is exported to excel, or a reporting service online viewer tool is being used.

Rated 4.70, 10 vote(s).