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.
3. Now select data fields as a column in table. After that your Report Design will look like below.
4. Then right click on the Text box of State and Select Add Group–> Parent Group from Row Group.
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.
then click on Ok button.
6. Now your report design will look like below :
7. From Row groups, select Details and then click on Group Properties.
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.
Then click on Ok button.
9. Now delete 2nd State Column as shown in below screen.
10. Now report design looks like below.
11. So we have done with our drill-down report. To see the preview, click on preview tab.
Congratulations! We successfully completed Drilldown Report in SSRS.