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

To see how this works, create a new report and define a data set using the AdventureWorks shared data
source. The name of the new data set will be EmployeesAndManagers . Enter this SQL expression into
the third pane of the Query Builder:
SELECT
Person.Contact.FirstName
, Person.Contact.LastName
, HumanResources.Employee.EmployeeID
, HumanResources.Employee.ManagerID
FROM
HumanResources.Employee
INNER JOIN Person.Contact
ON HumanResources.Employee.ContactID = Person.Contact.ContactID
ORDER BY
Person.Contact.LastName
, Person.Contact.FirstName
Add a table to the report in the Layout tab, and drag the FirstName field from the Dataset window to the
first cell in the detail row. This will set the DataSetName property for you. A single group will provide all
of the recursive functionality for the table. Click on the table to show the selection handles, and then click
the selector to the left of the detail row. You're not adding a new group but simply using the detail group
that already exists for this row. Right-click the detail row selector, and choose Edit Group from the
menu. In the Details Grouping dialog, enter the Name as table1_Details_Group_Employees . In the
first row of the Group on list box, select the expression =Fields!EmployeeID.Value . Drop down the
Parent group list, and select the expression =Fields!ManagerID.Value and verify that your selections
match Figure 6-42. The reporting engine recognizes this as a recursive grouping because both of these
fields are in the same table. Click OK to close the dialog.
Figure 6-42