Sunday, September 1, 2013

SSRS - How to implement Drilldown feature in the report



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                      :           Hide
2. 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--------------------------------------------------

2 comments:

  1. In this report intially Sales Region was not there.I mean no data was there but still it showing the column.
    How to hide show Sales Region on drill down.

    ReplyDelete
  2. To know more drill down features in SSRS, you should be visit at SSRS - Drill-down SSRS Report
    Other Drill Reports in SSRS
    1) Row Drill Down Report
    2) Row Drill Through Report
    3) Column based Drill Down Report
    4) Chart based Drill Down Report
    5) How to create your first sub report

    ReplyDelete