Reporting Services supports recursive hierarchies and Analysis Services supports parent-child
…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.
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.
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:
Weblog by Paul Turley and SQL Server BI Blog.