Creating a Drill-down report in SSRS

In this article, let us start to create a Drill-down report, another common type of report, using SSRS 2012.

I will be using the same Parameterized report that I created in one of my earlier articles (Go to Creating a parameterized report in SSRS using mdx ). You can see that we have 4 columns in this report – Category, Subcategory, Product and Internet Sales Amount. When we execute the report in its current form we see all the column values by default.

But consider a scenario where the requirement is that only the Category values (with sub-totals at Category Level) should be visible when the report is initially rendered. If the user wanted to view the next level of details, like viewing the report at the Sub category or product level, then he should be able to do it easily.

The Drill-down report is what we need in this scenario.

1. Open report

Step 1: The first step is to add a Parent Group at the Category level. Right-click the details group (in the Row Groups section as shown in the screenshot) and select Add Group > Parent Group…

2a. Add 1st Parent Group

In the Tablix Group, select Group by: [Category] option.

2b. Add 1st Parent Group

Two things are added to the report after creating the group. Firstly, you can see an additional column also named Category (which is associated with the group we just had created). We will delete the original Category column later. The second change would be in Row Groups section. Now we can see a new group named Category above Details group indicating that the Category group is the parent group of Detail group.

Step 2: Now we need to add a second group, at the SubCategory level. Right click Details group again and select Add Group > Parent Group…

3a. Add 2nd Parent Group

Select Group by: [Subcategory] and click OK.

3b. Add 2nd Parent Group

Step 3: You can see the additional Subcategory column in the tablix and an additional Group in the Row groups section. Now we need to add the final group to the report. Right-click the Details group again and select Add Group > Parent Group…

4a. Add 3rd Parent Group

Select Product in the Group by: drop down and click OK.

4b. Add 3rd Parent Group

We can again see an additional column, Product, in the Tablix section and an additional group in the Row Groups section.

Step 4: In the tablix, select the three original columns – Category, Subcategory and Product. Right-click the header of the selected columns and select Delete Columns.

5. Delete Old Original Columns

Step 5: Modify the expression of the Internet Sales Amount which has the measure value by right-clicking and selecting Expression (as shown in the below screenshot).

6. Adjust column Width & updated expression

Replace the existing expression from =Fields!Internet_Sales_Amount.Value  TO  =SUM(Fields!Internet_Sales_Amount.Value)

7a. Update Expression - OLD

7b. Update Expression - NEW

Step 6: Now we need to add the Drill-down functionality and we do this in the groups. Since Category is the top level and we need to see this by default when the report is rendered, we do not need to make any changes to this group. However, some of the properties for the second group, Subcategory, need to be changed. Right-click the Subcategory group and select Group Properties.

8a. Add Drill Down functionality - 2nd Group

Go to the Visibility tab and change the visibility option from Show to Hide. Tick mark the ‘Display can be toggled by this report item:’ check box and select the Category1 column from the drop down list and click OK.

8b. Add Drill Down functionality - 2nd Group

Step 7: Similarly, right-click on the Product Group and select Group Properties.

9a. Add Drill Down functionality - 3rd Group

In the Visibility tab, change the visibility setting from Show to Hide.
Tick mark the ‘Display can be toggled by this report item:’ check box and select the Subcategory1 column from the drop down list and click OK.

9b. Add Drill Down functionality - 3rd Group

 

Step 8: Change the background of the three detail cells (not header cells) – Category, Subcategory and Product as shown in the below screenshot. Change the color to Grey (or any color you like using the Background color selector button.

10. Change background color for detail columns

11. Clear the Headers - Rename headers

Step 9: Also, for these three detail cells, change the BorderStyle property to None.

12. Change BorderStyle to None

Step 10: Now let’s preview the report.

13. Preview Report

We can see that when the report is rendered, it is a summarized report, summarized at the category level.

14 DrillDown Report Main Level

If we want to view more details about the report, then we need to click the + button against the category and the report will expand to show the subcategory details.

15 DrillDown Report Second Level

Expand the Subcategory to view the report at the Product level.

16 DrillDown Report Third Level

Note: Since we had changed the expression for the Internet Sales Amount to SUM(Fields!Internet_Sales_Amount.Value), we are getting the correct aggregated values.

In the next report, we will see how we can create other types of report.