Knowledge is power. We love to share it.

News related to Mono products, services and latest developments in our community.

Goran

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 2.95, 19 vote(s). 
Excellent and simple example.... Thanks a lot
Thanks for the example, it helped me with the technique.
By Alicia
This is truly perfect example for Drill down server report. Whole process is very understanding about drilling database. I am glad to see some examples for that.
By Ram
Goran,
Thank you very much for the drill down example.
I have one query..

How to Stop displaying (+/-) when there are no 'ProductNames' exists in Particular 'ProductModel'
I.e Mountain200 is not having products.. in that case... not to show Expand button..

How to achieve..

Thanks for your help in advance.

Ram
Goran
RAM,

In this particular example, you will never get an empty model, because only models with child items (products) are being shown.
That is because I'm building the query from the ground-up, from the product --> model --> category. Feel free to provide me with an example if you need my further assistance.

Goran
By johnson
Hi Bro you Did Good job .Thank you
By RAJESH
Hi
i am new to BI i have an requirement that
only columns should display when drill down occurs.

For ex:
if there is 4 columns say it as c1,c2,c3,c4 and its grouped and drilled
when i click c1 then only c2 should display including column header.
i mean to say intially only 1 column when clicking drill it should other columns and so on

please help me to achieve this.


Thanks
Rajesh




Goran
Rajesh,

This is fairly easy to achieve. Just right-click to header of the column you wish to hide, click on "Column Visibility", check the "Display can be toggled by this report item" and choose any report item you would like to be used for this purpose. I suggest choosing column header left to the selected column header.
By reza
how can i show it through ssrs "Execution Service"
Goran
Can you be more specific with your question?

Thank you,
Goran
1 2