Create Drill Down Group report in SSRS

This article demonstrate how to create a Drill Down group report in SSRS.

In general, a drill down report allows users to click on plus or minus button to expand (show) or collapse (hide) a section of a report to show detail data in place.

Creating a Drill down group report means, allowing user to expand or collapse a section of a report to show customer details in place based on Grouping.

Assume that you have a requirement, to create a report that displays country by customer details, with the functionality of showing or hiding customer’s details based on Country. For such requirement, you can create a Drill Down group report.

Lets see the step by step implementation of Drill Down Group report in SSRS.

As you can see, here we have created a new report named RptDrillDownGroup and created an embedded data source and embedded data set for this report as shown below.

If you do not know how to create a new report, and embedded data source and embedded data set then you can refer following posts.

Create an new Project in SSRS, Create an embedded data source in SSRS, Create an embedded data set in SSRS.

You can also use shared data source and shared data set for report as per your requirement.

Lets create a Drill Down group report, right click on Design surface then navigate to Insert and select Table from context menu.

Now quickly create a simple basic report that displays a customer basics details as shown below.

You may notice that we have not taken the country column in report for now, that will be added later in report.

Lets format the report, set font size, back ground color for header columns.

After that, lets take one text box and give the name to report.

Right click on report, then navigate to Insert and select Text Box from context menu as shown below.

After that, format the text box, Just increase the font size and make them bold.

Lets remove the time part quickly from date of birth, write expression for this.

Now, we will write an expression that would return only data part from birth day date.

Now click on OK button, and see the report preview.

You can see, it simple table report that displays customer details.

Now Lets add add country column, and group the customer data based on country.

Right click on Details then navigate to Add Group and select Parent Group..

Now, a Tablix Group dialog box opens, select a grouping column from drop down list. In our case it would be the Country column.

After that, check the Add group header checkbox, then click on OK button.

Once you click on Ok button, you can see a country column is added to report and customer data is grouped based on country column.

Lets see the report preview, you can see now customer data is grouped based on country.

As of now you can see only country Australia as there are many records for customers who belong to country Australia, so the other country might be shown when we go to next pages on report.

Lets make this report drill down group report by adding show and hide functionality.

So the idea is that, when report is initially run hide the customer details and allow user to expand and collapse customer details based on Country.

Go to Row Groups pane, Right click on (Details) and select Group Properties..

Once you click on Group Properties.., a Group Properties window opens.

Now go to Visibility tab and select Hide radio button, next check the Display can be toggled by this report item option.

After that select country from drop down list.

Once you done with this, just click on OK button.

Lets see the report preview, and you can see now customer data is grouped based on Country and it can be expand and collapse to see and hide customer data.