DrillDown Report in SSRS

DrillDown Report in SSRS

Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see.

Now in this article, we will create Drilldown Report.

1. First of all open Visual Studio 2010 and open your SSRS project. Then add report named as DemoDrillDownReport in your project.

Now create a Dataset. In the dataset, we will use following query :

SELECT		Name AS State,FirstName + ' ' + MiddleName + ' ' + LastName as Name,AddressLine1 + ',' + City as Address
FROM		Person.Person
INNER JOIN	Person.BusinessEntityAddress on Person.Person.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID
INNER JOIN	Person.Address on Person.BusinessEntityAddress.AddressID = Person.Address.AddressID
INNER JOIN	Person.StateProvince ON Person.Address.StateProvinceID=Person.StateProvince.StateProvinceID
WHERE		MiddleName is not null
AND	        Name in ('Wyoming','Utah','Brandenburg','Tasmania','Somme')

2. Then right click on report area and go to Insert–>Table.

2-DrillDown Report in SSRS

3. Now select data fields as a column in table. After that your Report Design will look like below.

3-DrillDown Report in SSRS

4. Then right click on the Text box of State and Select Add Group–> Parent Group from Row Group.

4-DrillDown Report in SSRS

5. A Tablix Group window pops-up. Select [State] from the list of Group By Dropdown list.

Also check Add Group Header checkbox, then click OK.

5-DrillDown Report in SSRS

then click on Ok button.

6. Now your report design will look like below :

6-DrillDown Report in SSRS

7. From Row groups, select Details and then click on Group Properties.

7-DrillDown Report in SSRS

8. A Group Properties window opens, select Visibility from left pane. Then select Hide radio button and check the Display can be toggled by this report item check-box. Then from the drop down list, select the name of the group i.e. State1.

8-DrillDown Report in SSRS

Then click on Ok button.

9. Now delete 2nd State Column as shown in below screen.

9-DrillDown Report in SSRS

 

10. Now report design looks like below.

10-DrillDown Report in SSRS

11. So we have done with our drill-down report. To see the preview, click on preview tab.

11-DrillDown Report in SSRS

Congratulations! We successfully completed Drilldown Report in SSRS.