Advanced Report Design - Professional SQL Server 2005 Reporting Services - page 214

main report, the viewer redirects to the detailed report for the specific product by passing the ProductID
parameter value. This is shown in Figure 6-41.
Figure 6-41
Recursive Relationships
Representing recursive hierarchies has always been a pain for reporting and often a challenge to effec-
tively model in relational database systems. Examples of this type of relationship (usually facilitated
through a self-join) may be found in the Employee table of the AdventureWorks sample database. Most
reporting tools were designed to work with data organized in traditional multi-table relationships.
Fortunately, our friends at Microsoft built recursive support into the reporting engine to deal with this
common challenge. A classic example of a recursive relationship (where child records are related to a
parent record contained in the same table) is the employee-manager relationship. The Employee table
contains a primary key, EmployeeID , that uniquely identifies each employee record. The ManagerID is a
foreign key that depends on the EmployeeID attribute of the same table, and it contains the EmployeeID
value for the employee's manager . The only record that won't have a ManagerID would be the president
of the company or any such employee who doesn't have a boss.
Representing the hierarchy through a query would be quite difficult. However, defining the data set for
such a report is very simple. You simply expose the primary key, foreign key, employee name, and any
other values that you want to include on the report.