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

Now the fun begins; Reporting Services recognizes that recursive groupings have special characteristics.
I use the Level() function to return the group level number within the recursive hierarchy. Each row is
assigned an integer value that represents its relative position to parent and child rows in the hierarchy. You
will also use a Count() function, indicating that you want the count of the recursive group's children.
Figure 6-43 shows the final solution. Note that for the first of the two function calls, detailed in the call-
outs, you'll build this in two stages. I'll give you instructions to add and test only the Level() function
and then you'll go back and add the Choose() function in a second pass.
Figure 6-43
You want to see the employee's full name, so modify the expression as it appears in Figure 6-43 to show
the FirstName , a space, and the LastName . Modify this column header and enter column headers for
the second and third columns as Level and Reports, respectively. The Level column will show the
employee's level within the organization and the Reports column will show the number reports they
have (the number of employees who report to this employee.) All aggregate functions accept an optional
group name for the scope argument which causes it to calculate the aggregation only within the scope of
that group. For the Level() function this is the only argument.
In the second detail cell (labeled Level), set the Value property to:
=Level(“table1_Details_Group_Employees”) + 1
This returns an integer that I would like translated to a more meaningful value. Now, I'll embellish this
expression so that it returns the employee level from the top of the food chain down (the number 1 rep-
resents an executive, 2 is a director, 3 is a manager, and so on). Passing the level number to the Choose()
function, I can provide this translation:
=Choose(Level(“table1_Details_Group_Employees”) + 1, “Exec”, “Director”, “Manager”,
“Supervisor”, “Peon”)
1 is added to this value because the Level() function returns 0 for the first level, 1 for the second, and
so on. The expression for the third column will use the group name for the Count() function's scope
argument and the Recursive keyword in the third argument. This indicates that the aggregate function
should be applied to child rows of this group. Set the value to the expression: