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.

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…

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

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…

Select Group by: [Subcategory] and click OK.

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…

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

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.

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).

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

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.

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.

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

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.

 

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.

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

Step 10: Now let’s preview the report.

We can see that when the report is rendered, it is a summarized report, summarized at the category 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.

Expand the Subcategory to view the report at the Product 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.