SQL Server - Drill Down Techniques in SSRS 2008 issue

Asked By Tridip Bhattacharjee on 20-Mar-12 03:05 PM
i am showing location,department and employee name & salary. all the fields are coming from same table. now i create a group on location first and then second group on department. all the group has group header. detail row has employee name & salary. my report is showing perfect but i want to implement drill down technique. i want to show plus minus symbol for the group. i want that first report shows all only location name with plus sign. when user click on plus sign then all department will show under that location. the same way when user click on department plus sign then all employee details will show under that department.

i search google and follow links are

http://forums.asp.net/t/1573640.aspx/1?May+i+use+the+collapse+and+expand+feature+like+tree+menu+in+Report

http://www.mssqltips.com/sqlservertip/1329/drill-down-techniques-in-ssrs-with-the-visibility-property/

http://www.sqlservercurry.com/2009/08/creating-drill-down-report-with-sql.html

 and i followed their various instruction to bring expand collapse functionality into my report but failed. so please guide me how to show report location & department wise with expand collapse functionality.
thanks
[)ia6l0 iii replied to Tridip Bhattacharjee on 20-Mar-12 09:49 PM
The last link in what you posted appears to be the way. 

But, I would suggest you to read the article at Technet titled "Drillthrough, Drilldown, Subreports, and Nested Data Regions (Report Builder 3.0 and SSRS)". It talks about adding a Drillthrough, Sub Report or a Nested data region.

Click the following link to read more about this.  In particular, visit the "InThisSection". It has links to each one of the methods described above.
http://technet.microsoft.com/en-us/library/dd207141.aspx#InThisSection

And the following link in particular talks about Creating a drilldown report.
http://technet.microsoft.com/en-us/library/ff519554.aspx

Thank you.
D Company replied to Tridip Bhattacharjee on 21-Mar-12 12:09 AM
Hi,
For this kind of requirement ,u can  accomplished by just one report by creating a self-referencing action that links to the same report. or also  can be done even if the report has parameters on it.

Step by step process with the screen shots

http://www.mono-software.com/blog/post/Mono/139/Drill-down-in-SQL-Server-Reporting-Services-2008/

A complete example is given here

http://blogs.msdn.com/b/seanboon/archive/2009/02/09/enabling-drilldown-on-ssrs-reporting-services-2008-charts.aspx


Hope this helps
Regards
D