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.