Crystal-Clear Reporting on Hierarchical Data

Hierarchical data is hardly a new phenomenon in the database world. Tables can contain data that defines a parent-child relationship, such as that between managers and employees in the Employees table. Hierarchical data within a table is defined by a primary key-foreign key relationship. In the Employees table, for example, the table’s primary key, Employee_Id, has a foreign key relationship with the Reporting_Manager_Id column, creating a parent-child relationship between one employee and another employee, a manager. The hierarchy is from the top-most manager, such as the company president or CEO, to the lowest ranking employees— those who have no reports. Hierarchical relationships are also common between tables, as with the Orders (parent) and Order Details (child) tables.

Although hierarchical data isn’t new, reporting on such data has always been a challenge because developers usually have to create a self-join of the data in a parentchild relationship to output the information in hierarchical form. However, thanks to Visual Studio (VS) 2005’s new built-in support for Crystal Reports, reporting on hierarchical data has never been easier. To illustrate the new reporting capabilities, let’s develop a report against hierarchical data within a table by using Crystal Reports, which has been part of VS 2005 since the first release of the .NET integrated development environment (IDE). In five easy steps, you’ll create a Windows Application project to host the report, add the Crystal Report Viewer control to display the report, connect to Microsoft’s sample SQL Server 2005 AdventureWorks database and create a DataSet data source, add a report to the Windows Application project, and include the C# code for the form. To work through the example, you need a basic understanding of VS 2005 and Crystal Reports. You should also be familiar with C#, T-SQL, and ADO .NET. Let’s get started.

Step 1: Create a Project

If VS 2005 isn’t already started, you can launch the IDE by clicking Start, All Programs, Microsoft Visual Studio 2005 and then clicking the Microsoft Visual Studio 2005 icon. Now, follow these steps to create a Windows Application Project, which gives you the Windows Forms client that will host the report:

Next, set Form1’s Text and Size properties, as Table 1 shows. To access these properties, select inside the Form1 design surface, then find the properties inside the properties window, typically located in the lower right corner of the IDE. (If you don’t see the properties window, use the View menu or press F4 to make it visible inside the IDE.)

Step 2: Add the CrystalReportViewer Control

You need to use the CrystalReportViewer (CRV) control to display the report to users. CRV not only previews the output for users, it also lets them generate the report in various popular formats, including PDF, Microsoft Excel, and so on. Users can also print a hard copy of the report while viewing the output.

To add CRV to Form1, first make sure Form1 is open in the designer. Select ToolBox, CrystalReports, then drag and drop CrystalReportViewer on Form1. (If the Toolbox isn’t visible, press Ctrl+Alt+X.) This process creates a new instance of CRV named Crystal- ReportViewer1 by default; you can rename it if you want. Your project should now look like the sample in Figure 1.

Step 3: Add a DataSet

You can get data to a CRV in several different ways, including directly connecting to a DataTable or executing a stored procedure. In this example, I use a DataSet with a DataTable to demonstrate how to use DataSets with Crystal Reports. To add a DataSet to the project, select Add, New Item, DataSet from Solution Explorer. Change the name of the DataSet from DataSet1 to dsEmployee, then click Add. If the wizard asks you to create a TableAdapter, cancel the TableAdapter Configuration Wizard; you’ll add a DataTable by using the DataSet Designer instead.

You’re now ready to add a Data- Table to your newly created DataSet. A DataTable, which is essential to loading the report data, gathers the data from your data source and stores it for use in your report. You’ll use the information from the DataSet and DataTable while designing the report. To add a DataTable to the dsEmployee DataSet, double-click dsEmployee from Solution Explorer to open the DataSet in the designer view. Right-click the designer surface, then select Add, DataTable. Click the header and change the Data- Table name to dtEmployee.

To add columns to the dtEmployee DataTable, rightclick dtEmployee and select Add, Column. When you add a column, the default data type is a string. But you can change a column’s data type from the properties window after you insert the column. For this example, add the following columns and data types to the DataTable: EmployeeName (String), ManagerID (Int32), EmployeeID (Int32).

Step 4: Add a Report

So far, you’ve created the Windows Application project, added a CRV control to the form, and added a DataSet to the project. Now, it’s time to add a report. First, select Add, New Item, Crystal Report from Solution Explorer. In the Add dialog box, change the report’s name from CrystalReport1.rpt to rptHierarchical.rpt, and then click Add. From the resulting Crystal Reports Gallery, select the As a Blank Report option, then click OK.

By default, rptHierarchical.rpt is open in designer mode with default settings. This example uses only the Page Header, Details, and Page Footer sections, so you can reduce the height of the Report Header and Report Footer sections. Your report designer should look similar to the one in Figure 2.

Adding a report to a project is easy. The challenge is defining the report layout. Drawing a prototype of your report on paper before starting the design process is a good idea. Typically, you design reports with a specific page size and layout in mind. The sample report is letter size and in portrait-mode layout. You can explore various properties attached to the report layout by right-clicking anywhere on the open designer surface (gray area) and selecting Report and Design Properties.

Whether this is your first report or you’re a reporting junkie, you need to understand the basic building blocks of report writing—the header, detail, and footer. Figure 3 shows the sample report in runtime mode. It features the report name and date in the header section, employee information in the details section, and page number in the footer. Let’s look at each of these sections.

Continued on Page 2.

Header section. For this report, the header consists of five text objects, two line objects, and one special field. You drag and drop the text (e.g., Hierarchical Employee List) and line objects from the Toolbox window’s Crystal Reports section designer surface. Then, from the Field Explorer, which opens automatically when you open the report in the designer, you drag and drop the Print Date special field to the report. I changed the colors and fonts after placing the report objects on the designer surface. Your header should look similar to the one in Figure 4.

Footer section. The footer contains only one special field and one line object, as Figure 4 shows. From the Field Explorer window’s Special Fields section, just drag and drop the Page N of M special field onto the designer surface.

Details section. The Details section of the report contains the hierarchical employee list data. You use the DataSet that you added to the project earlier in the article as the source of data for the report. To add a DataSet as the data source for a report, just follow these steps:

Selecting the data source adds the columns from the DataTable into the Field Explorer window. After the fields are in the window, you can simply drag and drop them into the report designer. For this report, you drag EmployeeName into the designer’s Details section. As you drag and drop the fields inside the Details section, you’ll see that the field/column title is added to the report designer’s Page Header. Because you already designed the Page Header, just delete these field headers.

Although the report design looks complete, you still need to add the report’s hierarchical functionality. As you can see in Figure 3, all employee names should form a hierarchy based on their ManagerID association. To create a hierarchy, you need to create a grouping on EmployeeID. To add a group to a report, right-click in any open space on the designer, and from the context menu, select Insert, Group. From the resulting Hierarchical Group Options dialog box, select EmployeeID, which creates a group with its own header and footer. Delete the Group Header’s default contents and adjust the height of the Group Header and Group Footer to hide them, as you did with the Report Header and Report Footer sections earlier.

After you define the EmployeeID group, you can define the hierarchical relationship by using ManagerID. Right-click in the report designer surface, then from the context menu, select Report, Hierarchical Grouping Options. From the Hierarchical Group Options dialog box, which Figure 5 shows, select the Sort Data Hierarchically check box, select ManagerID from the Parent ID Field list box, and click OK.

Step 5: Add the C# Interface Code

The last step is to add the C# code behind Form1.cs. Web Listing 1 shows the simple code to generate the report. The code starts by establishing the connection to the SQL Server database using integrated security. You can change the connection string if you’re using SQL Server user ID and password credentials.

The code then uses SQLCommand to create the SQL SELECT statement that populates the DataSet with data. The SQL statement combines FirstName and LastName from the Person.Contact table, then selects the employee title from the HumanResources.Employee table. I use an inner join to join the data from these two tables, using ContactID as the key.

Next, the code calls the ExecuteReader() method, which uses a data reader to collect the data. Note that with ADO.NET 2.0, you can directly load the data from the data reader to the DataSet by calling the Load() method, as I did here. The code then sets the DataSet as the source for the report and assigns the report to the CrystalReport-Viewer control.

Build and Run the Example

You can now build the reporting project by clicking the small green Play button on the main toolbox or pressing F5. Either option will build the application and start it running in debug mode. (If you get a compile error, check for invalid characters.)

As you’ve seen, with some simple C# code and the built-in Crystal Reports features in VS, you can easily present hierarchical data in your reports. The key is using Crystal Reports’ Hierarchical Group Option in report designer and specifying the key to create the hierarchy. In this case, it was the ManagerID-EmployeeID primary key-foreign key relationship. Without Crystal Reports, you first would have had to create a self-join query to retrieve the data, followed by padding spaces from the left to mimic the indented output of hierarchical data.