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.
![](downloads/images/scr_8.jpg)
Overview
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.
![](/Downloads/Tutorial_20060614Recursive_Data.zip/gr.gif)
![](/Downloads/Tutorial_20060614Recursive_Data.zip/gr.gif)
![](/Downloads/Tutorial_20060614Recursive_Data.zip/bc.gif)
![](/Downloads/Tutorial_20060614Recursive_Data.zip/gr.gif)
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();
}
}
}
![](/Downloads/Tutorial_20060614Recursive_Data.zip/gr.gif)
![](/Downloads/Tutorial_20060614Recursive_Data.zip/gr.gif)
![](/Downloads/Tutorial_20060614Recursive_Data.zip/bc.gif)
![](/Downloads/Tutorial_20060614Recursive_Data.zip/gr.gif)
I started to code in 1990 using C and GW basic, then moved to 4GL world with DBIII, Clipper and FoxPro etc. and continued till I found and hooked to MS goody bag Visual Basic. For last four years, I am working extensively on .NET technologies and scripting both VB.NET and C#. My ideal choice of database is SQL Server; however, I do interact with Oracle when required. I have worked on all sort of Business applications, but my ideal project would be anything which has some Financial part associated with it, I wish I can put a Balance-sheet report in all the project I do… I also teach .NET related technologies as part-time faculty with local community college and try to help share my knowledge with others.
See All Postings From Asif Sayed >>
Reviews
Be the first to write a review
You must me logged in to write a review.