Free - Reporting on Hierarchical Recursive data using MS Reporting Services

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.

3. Show me the Report!

I know after going through all that preparation, we are eager to see the output for report, aren't we? 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 windows forms application framework, the same can be manipulated easily for a ASP.NET application framework and further, could can be easily converted 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();
        }
    }
}