Reporting Services supports recursive hierarchies and Analysis Services supports parent-child
dimensions…
…and these two powerful features ought to just work together seamlessly, right? Well,
they can if you know what to do.
To follow this example I assume that you have a fundamental knowledge of columnar
report design in Reporting Services and that you know how to define groups and to
set group and report item properties.
Parent-Child Hierarchies
In SQL Server Analysis Services 2005 and 2008, a parent-child hierarchy is based on
a set of dimensional attributes that are related through parent key and primary key
values present in the underlying data. This means that the arrangement of the members
into their respective levels in a hierarchy is dependent on the data and won't be
known at design time. Common recursive and parent-child hierarchies are commonly found
in business scenarios like a financial chart of accounts, employee/supervisor assignments
and product material assemblies. In each of these scenarios, every item "belongs to"
a parent member, which in-turn belongs to another member until everything rolls-up
into the top-level member. This top-of-the-food chain member may be the general ledger,
CEO or final product assembly – using the prior examples.
In a data warehouse or data mart, a single dimension table may contain the records
for all of these members with records inter-related through the primary key and a
parent key column participating in a foreign key constraint or logical relationship.
Note the following example in the AdventureWorksDW2008 sample database:
The Employee dimension in the corresponding sample Analysis Services database uses
these same fields to provide key values. For the Employee attribute, the KeyColumns property
is based on the EmployeeKey field and, for the parent-child Employees
attribute, the KeyColumns property is based on the ParentEmployeeKey field:
Creating the Report Dataset
Creating the dataset is very simple. When you create the data source, choose Microsoft
SQL Server Analysis Services for the Type and then connect
to your Analysis Services database. When you define a dataset, the graphical MDX query
designer is displayed. Choose the appropriate cube and then drag the parent-child
hierarchy into the query design pane along with any measures, calculated members and
KPIs. The following example shows a dataset query using the Employees hierarchy
in the Adventure Works cube:
…of course, you can add additional dimension members and filtering expressions to
the query in a more involved scenario.
Report Design
To visualize the hierarchal data, add a Table data region to the report and drag fields
from the dataset to the details row. Define a group only on the details row of the
table. The following example is a report created in Reporting Services for SQL Server
2008 but the same technique applies to SQL Server 2005. The design interface is just
a little different.
When the MDX query is generated by the query designer, special report field properties
are defined and mapped to attribute member properties that are available through the
OLAP metadata returned by the query. You will use two of these field properties to
enable the recursive hierarchy in the report.
In the details group, create a group expression referring to the UniqueName property
of the Employees field. Use the Expression Builder to create the expression =Fields!Employees.UniqueName.
The Recursive Parent property (called the Parent Group property in SSRS 2005) should
be set to the ParentUniqueName property of the Employees field.
On the Advanced page (General page in SSRS 2005) of the Group Properties dialog, use
the Expression Builder to create the expression =Fields!Employees.ParentUniqueName.
Click OK to accept these settings. Any other report design elements are not specific
to reporting on OLAP cube sources.
The Level function is used to determine the position of a row within the recursive
hierarchy derived from the parent-child hierarchy of the dimension. Pass the group
name as a string. If you would like to indent the items in the table to show employees'
relative position within the recursive hierarchy, use the following expression to
set the Left Padding property of the textbox used to display the Employees field:
=((LEVEL("table1_Details_Group") * 20) + 2).ToString & "pt"
The final rendered report shows employees arranged in the organization hierarchy and
indented to indicate their position and reporting structure. This example uses the
typical technique to define drill-down functionality by changing the group visibility
and toggle item properties:
Download a copy of this sample report:
Child.rdl (13.56 kb)
Weblog by Paul Turley and SQL Server BI Blog.
