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 :

?

1 2 3 4 5 6 7

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

1.     Background

The purpose of this article is to describe a way of implementing drilldown in the report. Drilldown is basically used to show the detail data from the summary data in the same plane and in hierarchical fashion. For creating drilldown, your report should contain some group and its details data. Initially, your detailed data is hidden and once you expand the group by clicking (+) sign then it will show its corresponding Detail data.                             .

Fig- showing drilldown data

Note: For implementing drilldown feature in the report, your report should contain grouped and detailed data. For creating a group in the report, Follow the below link:http://www.sqlcircuit.com/2013/09/ssrs-how-to-implement-row-grouping-in.html

2.     Step by Step procedure to implement Drilldown in the report:

·         Below is the sample report in which we will implement Drilldown feature. 

Sample Report


 
This report contains Fiscal Year as Group and Sales Region & Sales Amount as Details:

·         To create a drilldown, go to the grouping pane, Select the details group and go to the Grouping properties:

 

·         It will open the Grouping properties window. Go to the Visibility tab and set the below properties:

1. when the report is initially run                      :           Hide2. Display can be toggled by this report item    :          Fiscal Year Colum textbox name (e.g FiscalYear1)

Click ok.

·         Run the report and see whether drilldown feature implemented or not:
Initially report will show only summary/Grouped data

·         Expand the group and see their corresponding detailed data

Now the report is showing drilldown data.

3.     Conclusion
By using the above steps, we can implement Drilldown feature in the report.

----------------------------------------------------End of Article--------------------------------------------------