Share via

Implementing Parent-Child Relationships in Report Builder - Well Sort Of...

  • Article

Ok...everyone should know by now that SQL Server 2005's Report Builder doesn't support parent child relationships within a report model (think about the classic employee to supervisor hierarchy...or for another example, how a company may roll up into a parent company/entity). So...how do you overcome this limitation of the SQL Server 2005's Report Builder/Model? Well, unfortunately there isn't a simple answer or solution...but there is a partial workaround. The workaround leverages SQL Server 2005's Common Table Expressions (CTE) and a compromise by the business users.

First...lets tackle the compromise by the business users. Since a parent-child hierarchy can be infinitely long...you'll need to pin down from the business users the anticipated largest depth of the hierarchy. For example, does the employee to manager hierarchy have three levels, or four levels, or five levels deep? Once you determine the longest "known" depth of the hierarchy, you'll use this knowledge to create a recursive query within the database to produce known levels for traversal.

The next step is to create a recursive query within the SQL Server 2005 database. If you need to learn about the recursive query CTE, then read this SS2K5 BOL entry, or check out this great blog entry on the topic. The CTE based recursive query should give you a result set something like "parent, child, level". Once you have the recursive query in place, you'll then want to construct a view from the CTE, whereby you'll leverage the view to join the identifier parent and child pairs to the decode table to return identifier names (assuming your model is normalized in this manner) down to the number of levels deep as compromised by the business users.

Once the recursive query is defined, you'll most likely then create a named query with Report Builder's "Data Source View" (DSV). This named query can be simple, or rather complex depending upon your database model and the number of custom Report Builder "click through" reports you want developed. Meaning, if you want a custom click through report for level 1 versus a different custom click through report for level 2, you'll need to create two separate named queries within the DSV, since an entity can only have one custom report per scalar or aggregate value. (For more information on creating custom click through reports for Report Builder, view this SS2K5 BOL entry).

Ok...that's it. You should now be able to traverse a parent-child relationship successfully within Report Builder...albeit with a compromise from the business end-users.

Comments