Using Analysis Services Parent-Child Hierarchies in Reports
One of the questions that I answer somewhat frequently, is about recursive groupings in Reporting Services and how to utilize parent-child hierarchies from an Analysis Services cube.
The attachment of this posting contains a small report example I put together based on the Analysis Services 2005 Adventure Works DW sample cube, and demonstrates a recursive hierarchy using the employee dimension. It works both in Reporting Services 2005 and 2008. Partial screenshot of the attached sample report output:
Particular areas to note / steps:
- The table group is defined as a recursive grouping based on the UniqueName and ParentUniqueName property values from the Analysis Services parent-child hierarchy. Consequently, there is only one group defined at design time, that automatically expands recursively at runtime based on the parent-child hierarchy in the cube.
Group expression: =Fields!Employees.UniqueName
Parent group expression: =Fields!Employees.ParentUniqueName
- Group visibility is toggled by the textbox within the group, to enable automatic drilldown along the parent-child hierarchy.
- In the example, manager names in the employee hierarchy (i.e. they have at least one employee reporting to them) are shown in bold font. This is accomplished with a count aggregate function that takes into account the full recursive hierarchy of all children by explicitly specifying the Recursive keyword as third argument. The textbox FontStyle property is set to the following RDL expression:
=iif(Count(Fields!Employees.Value, "table1_Details_Group", Recursive) > 1, "Bold", "Normal")
- The textbox showing the employee name uses dynamic left padding (in RS 2008 paragraph indents can be used as well) based on the current recursive hierarchy level using the Level() function.
For example: =20 * Level() & "pt"
Recursive hierarchies can also be very useful for relational data sources, not just parent-child hierarchies in cubes. I hope the explanations in this posting and the attached sample report resolve some of the mystery around parent-child / recursive hierarchies, and using regular vs. recursive aggregate functions. Enjoy!