Reporting on Hierarchical Recursive Data Using MS Reporting Services

Share |

Introduction

I will start with a question here. How many of you had chance to interact with the Employee table from Northwind sample database? There you go.... I can imagine countless hands in air, and why not? It is one of the standard databases that comes with both Access and SQL server. All right, am I going to discuses the Northwind database here? NO. Is the Employee table is something special? I would say YES to this. Why special? Well, if you pay 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 a Hierarchical relationship, which you commonly also call Recursive data. I am trying to shed some light on reporting data that 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, can be called recursive in nature. A typical example would be to take an Accounting Application Database that has a table called ChartOfAccounts; the Primary Key "Account_Id" will have a foreign key relationship with another column called "Reporting_Account_Id". Another example is the one that I use in this article; each Employee has a Manager assigned.

(Full Size Image)

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

Reporting Challenge for Recursive Data

I am ready with my second question. 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 a piece of cake? I am pretty sure this time I will see fewer hands in air compared to my first question! Or, you can say yes, it is piece of cake, if you have already tried your hand at MS Reporting Services.

It is very common that, when you have to deal with a situation like this, you end up writing some sort of custom code to find out the level of hierarchy and so forth. A typical developer's mindset will always have an endless list of wishes for software vendors. One of my wishes is that something could be done to address this issue of handling recursive data built right into the reporting engine. Somehow, I feel my telepathy worked and guys at Microsoft put this feature into reporting services. Here I am acknowledging them by writing a few lines here. I would like to clarify one point here. I have worked with several other reporting engines and did enjoy them; however, since I started to work with Reporting Services, I personally felt much more at ease.

Now, wear your Report Writing hat...

When I look at different reporting engines out there in the market, the underlying concept remains very much the same; I am talking about headers, footers, data regions, data grouping, summaries, and so forth. So, even if you have not yet been exposed to reporting services, don't worry about having a working knowledge of any reporting engine. You will not have much difficulty grasping the concept laid down in this article.

With this article, I also would like to show the reader how reporting services can be used with a smart client windows forms application in a client environment.

I assume the reader of this article is comfortable using Visual Studio 2005, C#, SQL Server 2000, and Windows Forms. The article is not at all a "Reporting Services 101;" I will assume that you will try to play with the attached code and figure out the secrets hidden with it.

Implementing reporting services into a smart client is as easy as 1.2.3...

1. Create a DataSet.
2. Create a Report.
3. Use the Preview control to generate a report with an ADO.NET code interface.

1. DataSet at your service

To create a dataset, just click Add New Item from Solution Explorer. Select DataSet from the Visual Studio-installed templates and give it a proper name. After Dataset is created, open it in a designer window and add a DataTable to it. After you have added DataTable, add your required columns to it. In this example, I have three columns added; namely, EmployeeName, EmployeeID, and Reports_to. Please be sure to set the DataType property of each column to String, Int32, and Int32 respectively.

The DataSet typically should look like the image above. Now that you have your DataSet ready, you will shortly see a fun way to fill it by using a new technique introduced in ADO.NET: using a SqlDataReader to Fill a Dataset (I guess my telepathy worked here too).

2. Report Design

As you did with DataSet, just click Add New Item from Solution Explorer. Select Report from Visual Studio-installed templates and give it a proper name. As I clarified earlier, I am not going into detail for each and every control/elements of Report Designer; instead, I will point out the important location that needs attention to create a Report that uses recursive data.

As you can see in the image above, this is what my reports look like in Designer. Typical of a report writing tool, reporting services also have an interface where you can define the header and footer to begin with and move on to the report body, and so forth. In the header section, I have the Report Title (Magenta color) and Run Date (Blue color).

The most interesting part, I feel, is the Body section, also called the data region. The data region allows you to put several new exiting controls that basically decide how the data will be outputted. I have used a "Table" control; it comes with a ready header and footer for it when placed on the Designer surface for the first time.

The TextBox contol is used heavily to display information. If you look at the image, you can see that I just placed a textbox control and simply typed the report title inside. When it comes to specifying an expression, all you have to do is start with a "=" sign in front. You can check the Run Date example, in which I concatenate the string "Run Date" and VB.NET function "Today" to return the current date.

After putting all the required controls on the Designer surface and making sure the layout meets your taste, it is time to spill the magic beans that will handle the recursive nature of data and manage the hierarchy level automatically.

The trick is to put the grouping on the detail section (make sure to select the detail band and right-click to access the group menu choice) by specifying group on "EmployeeID" and parent group "ReportsTo" as per the image below:

The report writer has a useful built-in function, "Level", that returns the current level of depth in a recursive hierarchy.

For the next output column in the report Level, you will specify the following expression:

=Level("tableEmployee_Details_Group") + 1

The Level function returns an integer starting with 0 for the first level; therefore, I have added a 1 to the end result here. In the example, employee "Andrew Fuller" is at the topmost level. You easily can use a function such as Switch() or IIF() to take this level number and substitute something like "CEO", "General Manager", and so on.

The third and last column in the report displays the count of all the employees who report to a given employee record. The following expression does the trick:

=Count(Fields!EmployeeID.Value, "tableEmployee_Details_Group",
       Recursive) - 1

For both, the expression "tableEmployee_Details_Group" is used as a reference name to the group definition that you applied to the detail band of data.

Did you also notice an interesting thing about the Hierarchical formatting of EmployeeName in the report output? This is also done fairly easily with the following expression that you need to specify in the Padding->Left property:

=Level("tableEmployee_Details_Group")* 20 & "pt"

Based on each incremental level, it will add 20 pt to the left side of EmployeeName and the output will look like a try structure.

3. Show me the Report!

I know that, after going through all that preparation, you are eager to see the output for report, aren't you? The following code will just do that!

You can start by putting ToolBox->Data->ReportViewer control on a standard windows form. I am using C# here within the windows forms application framework. The same can be manipulated easily for a ASP.NET application framework and further, could can be converted easily to VB.NET if that is what you use as your primary scripting language.

Make sure you have the code behind Form Load method as follows:

private void Form1_Load(object sender, EventArgs e)
{
   //declare connection string
   string cnString = @"Data Source=(local);Initial Catalog=northwind;"
                   + "User Id=northwind;Password=northwind";

   //use following if you use standard security
   //string cnString = @"Data Source=(local);Initial
   //Catalog=northwind; Integrated Security=SSPI";

   //declare Connection, command and other related objects
   SqlConnection conReport = new SqlConnection(cnString);
   SqlCommand cmdReport = new SqlCommand();
   SqlDataReader drReport;
   DataSet dsReport = new dsEmployee();

   try
   {
      //open connection
      conReport.Open();

      //prepare connection object to get the data through reader
      //and populate into dataset

      cmdReport.CommandType = CommandType.Text;
      cmdReport.Connection  = conReport;
      cmdReport.CommandText = "Select FirstName + ' ' + Lastname AS
      EmployeeName, EmployeeID, ReportsTo From Employees";

      //read data from command object
      drReport = cmdReport.ExecuteReader();

      //new cool thing with ADO.NET... load data directly from
      //reader to dataset
      dsReport.Tables[0].Load(drReport);

      //close reader and connection
      drReport.Close();
      conReport.Close();

      //provide local report information to viewer
      reportViewer.LocalReport.ReportEmbeddedResource =
      "RecursiveData.rptRecursiveData.rdlc";

      //prepare report data source
      ReportDataSource rds = new ReportDataSource();
      rds.Name  = "dsEmployee_dtEmployee";
      rds.Value = dsReport.Tables[0];
      reportViewer.LocalReport.DataSources.Add(rds);

      //load report viewer
      reportViewer.RefreshReport();
   }
   catch (Exception ex)
   {
       //display generic error message back to user
      MessageBox.Show(ex.Message);
   }
   finally
   {
      //check if connection is still open, then attempt to close it
      if (conReport.State == ConnectionState.Open)
      {
         conReport.Close();
      }
   }
}