As we 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 details for each and every control/elements of Report Designer; instead I will only point you to important location which needs attention in order to create Report which is using recursive data.
As you can see in the above image, this is how my reports look like in designer. Typical of report writing tool, reporting services also have interface where you can define header and footer to begin with and move on to report body etc. In the header section I have the Report Title (Magenta color) and Run Date (Blue color).
The most interesting part which I felt is the Body section, also called data region. Data region allows you to put several new exiting controls which basically decide how the data will be outputted. I have used "Table" control here which comes with ready header and footer for it when placed on designer surface for first time.
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 specify expression, all you have to do is start with "=" sign in front. You can check the Run Date example, in which I am concatenating string "Run Date" and VB.NET function "Today" to return current date.
After putting all the required control on the designer surface and making sure the layout meets out taste, it is time to spell the magic beans which will automatically handle the recursive nature of data and manage hierarchy level etc.
The trick is to put the grouping on detail section (make sure to select detail band and right click to access group menu choice), by specifying group on "EmployeeID" and parent group "ReportsTo" as per image mentioned below:
![image5.JPG]()
Report writer has useful inbuilt function like "Level", which returns the current level of depth in a recursive hierarchy.
For next output column in report Level, we will specify following expression:
=Level("tableEmployee_Details_Group") + 1
Level function return integer starting with 0 for fist level; hence I have added a 1 to end result here. So, in our example employee "Andrew Fuller", is topmost level, you can easily use function like Switch() or IIF() to take this level number and substitute with something like "CEO", "General Manager" etc.
The third and last column in report displays the count of all the employees who are reporting to given employee record. The following expression does the trick for us:
=Count(Fields!EmployeeID.Value, "tableEmployee_Details_Group", Recursive) - 1
For both the expression "tableEmployee_Details_Group" is used as reference name to group definition which we applied on detail band of data.
Did you also noticed on interesting thing about the Hierarchical formatting of EmployeeName in report output? This is also done fairly easily with following expression which you need to specify in Padding->Left property:
=Level("tableEmployee_Details_Group") * 20 & "pt"
Based on each incremental level it will add 20 pt to left side of EmployeeName and the output will look like a try structure.
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();
}
}
}