Getting Started
In order to create this report, you will need to have SQL Server 2005 with the AdventureWorks database, SQL Server 2005 Reporting Services and Visual Studio 2005 correctly installed and configured. We are going to use the tables in AdventureWorks database which is coming as a part of the SQL Server 2005 needs to be installed.
Now start a new Business Intelligence project in Visual Studio 2005 or using Business Intelligence Development Studio, which installs with SQL Server 2005. Select File | New Project | Business Intelligence Projects | Report Server Project. Name the project as HierarchicalDrillDownProject. Create a new shared data source (HierarchicalDb.rds) and set the server, database, login and password to point to your copy of the database. Then create a new report, HierarchicalDrillDown.rdl.
Hierarchical Relationship
In the AdventureWorks database take a deep look into the HumanResources.Employee table, here in this table you can see the columns EmployeeId and ManagerId. The EmployeeId column will have a foreign key relationship with the column ManagerId. This relationship is called as Hierarchical relationship.
Creating the Drilldown SSRS report
Create a new dataset named HierarchicalDS with the below SQL query as the query string for the dataset and the shared data source HierarchicalDb.
- Select C.FirstName + ' ' + C.LastName + ' - ' + E.Title As Name, E.EmployeeId, E.ManagerId
- From HumanResources.Employee As E Join Person.Contact As C On E.ContactId = C.ContactId
- Order By E.ManagerId
Then goto the layout pane and add a table from the toolbar. For our report only one column is enough so remove the additional columns from the table. Also remove the Detail row and Footer row from the table as we don't require them in our report. Give a name to the Header of the column like "Employees Hierarchy". Then insert a group named "HierarchyGroup", in that group select the Group on expression as EmployeeId and Parent Group as ManagerId. Uncheck the Include Group Footer checkbox and click ok. Give name to the textbox in the HierarchyGroup like "Employees".
Creating the DrillDown
We've now performed a grouping, but we still have to enable the drilldown. Click on the table to highlight it. Right-click on the nub that appears, just to the left of the group row. Select Properties. When the Properties window opens, expand the visibility section. Set the Hidden property to True and set the ToggleItem property to Employees. The Hidden property determines the state of the row when the report is first run. If we set it to True then the data is collapsed and hidden. By setting the ToggleItem property to Employees, when the report is run a little + sign appears next to it when the report is run and it works like a tree view.
Switch to the Preview tab and run the report. Now only the employee in the top level hierarchy will appear, but you can see the employees in the next levels by using the tree-style +/- controls.
Formatting the Report
The report which we have created now appears to be unformatted with the subsequent employee levels not arranged in a tree like fashion. To have a tree like look we need to add the below expression in the Padding | Left property
=CStr(Level("HierarchyGroup") * 25) & "pt"
The Level function return integer starting with 0 for first level in the group.
SSRS Drilldown Hierarchical Report
Switch to the Preview tab and run the report. Finally we have created the Drilldown Hierarchical Report
Wrap Up
I hope this article would have provided you the steps about how to create drilldown hierarchical reports and this would help you to create reports more interactive and suit to your business needs.
PrathibaPosted Nov 13, 2012, 2:58 PM
Hi, Thanks for the article. It worked. If I need to use same logic but I need group totals on particular amount field, how can I do it.
swathi samaPosted Dec 2, 2010, 4:41 AM
Hi It is very instresting.i developed in Sql server 2008 r2.
Surendra PatilPosted Apr 15, 2010, 2:21 AM
Thanks its work But i want the drill down in export to excel also
lintoPosted May 7, 2009, 12:23 AM
good