Create Multi Group Drill Down Report in SSRS

This article demonstrate how to create a multi group drill down report, means drill down report on nested grouping.

Lets see the step by step implementation of Multi group Drill down report in SSRS.

As you can see, here we have taken a new report named RptDrillDownMultipleGroup.

This report using an Embedded Data source and Embedded Data Set.

Lets create a simple tabular report first, which would display a customer basics details.

Later we will group the customer data based on Country and State then add a Drill down functionality to expand and collapse the customer details by Country and State.

[Also Read.. Create a Drill Down Group Report in SSRS]

After that, quickly add customer basics details as shown below.

Lets format the report, set the background color for header columns, make the font bold as shown below.

Once you done with the formatting, lets see the report preview.

You can see, a simple report is created which display customer’s data in tabular format.

Now we will group the customer’s data based on Country.

Right click on details then navigate to Add Group, and select Parent Group.. from context menu as shown below.

Once you click on Parent Group, a Tablix Group dialog box opens, now select the group column from Group By drop down list, in our case it is country column.

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

Now you can see, the country column is added to the report and now customer data will be grouped by country.

Lets see the report preview, and you can see the customer data is grouped based on Country.

Now, we will add one more grouping based on state. Basically we are going to do multi grouping or you can say nested grouping that is grouping within a grouping.

Now the customer’s data would be grouped by country and state.

For this, Right click on Country column then navigate to Add Group, then select Child group..

Now select Group By state and check the Add group header check box.

After that click on OK button.

Once you click on OK button, you will see a State column is added into report, now customers data are grouped based on Country and State.

Lets see the report preview, and you can see now customer data are grouped based on Country and State, which is called as Multi Grouped data.

Now we will add a Drill Down functionality on this multi grouped data, so the report will be called as Multi group drill drown report.

First we will add, a drill down functionality on State, so that user can expand and collapse customer’s data based on state.

For this, go to Row Groups pane then right click on (Details) then select Group properties from context menu.

After that, a Group properties window opens, now go to visibility tab then select the Hide radio button.

Next check the Display can be toggled by this report item option.

After that select State from drop down list, After that click on OK button.

Once you done with this, just go to report preview.

And you can see, based on state you can expand and collapse the customer’s data. By default customer’s data is hidden (collapse), when you expand the state you will see state by customer data.

Lets expand the state Queensland.

You can see, all the customers who belongs to Country Australia and State Queensland.

Now we will add Drill down functionality to Country.

For this, in Row groups pane, Right click on State group and select Group Properties from context menu.

After that, a Group properties window opens, now go to visibility tab then select the Hide radio button.

Next check the Display can be toggled by this report item option.

After that select Country from drop down list, and click on OK button.

After that, Go to report preview to see the implementation.

Now you can see, when report is initially run, you can see customer data is grouped based on Country, and within the country customer data is grouped by state.