I will start with a question here.  How many of you had chance to interact with Employee table from sample database Northwind?  There you go… I can imagine countless hands in air, and why not it is one of the standard databases comes with both Access and SQL server.  All right, are we going to discuses Northwind database here? NO. Is Employee table is something special? I would say YES to this.  Why special? Well, if you pay a close attention, it is just like any other standard table, however, two fields from the table, “EmployeeID” and “ReportsTo” are related to each other in an interesting way! Yes, you got it right; I am talking about Hierarchical relationship which we also call commonly as Recursive data.  I am trying to shade some light on reporting of data which is recursive in nature.

 

 

 

Introduction

I will start with a question here.  How many of you had chance to interact with Employee table from sample database Northwind?  There you go… I can imagine countless hands in air, and why not it is one of the standard databases comes with both Access and SQL server.  All right, are we going to discuses Northwind database here? NO. Is Employee table is something special? I would say YES to this.  Why special? Well, if you pay a close attention, it is just like any other standard table, however, two fields from the table, "EmployeeID" and "ReportsTo" are related to each other in an interesting way! Yes, you got it right; I am talking about Hierarchical relationship which we also call commonly as Recursive data.  I am trying to shade some light on reporting of data which is recursive in nature.

What is Recursive Data?

I am sure you must have come face to face with this challenge called Recursive Data if you have to deal with databases.  Hierarchical data which defines the level of association with a defined relationship ca be called recursive in nature.  A typical example would be to take Accounting Application Database which has a table called ChartOfAccounts, the Primary Key "Account_Id" will have foreign key relationship with another column called "Reporting_Account_Id".  Another example is the one which I am using in this article is that each Employee has a Manager assigned.

Do you recall "Self-Join"? As you can see in above image, this is one way we display the recursive nature of data, just putting here for some better understanding.

Reporting Challenge for Recursive Data

I am ready with my second question here, before I ask you; I would like your kind attention to the image with Employee level output. Now the question: Do you think generating report like that without any custom code is piece of cake?  I am pretty sure this time I will see many less hands in air compare to my first question! Or you can say yes it is piece of cake if you have already tried your hands on MS Reporting Services.

It is very common that when we have to deal with a situation like this, we do end up writing some sort of custom code in order to find out the level of hierarchy etc. etc.  A typical developer mindset will always have endless list of wishes for software vendors, one of my wish was if something was done to address this issue of handling recursive data built right into the reporting engine.  Some how I felt my telepathy worked and guys at Microsoft put this feature into reporting services and here I am acknowledging them by writing few lines here.  Though, I would like to clarify one point here,  I have worked with several other reporting engines and did enjoyed them, however, since I started to work with Reporting Services, I personally felt much at ease.