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:
- Right-click any open area on the Report Designer,
then from the context menu, select Database, Database
Expert.
- From the resulting Database Expert dialog box, click
Project Data and expand the ADO.NET DataSets node.
- Select the dtEmployee DataTable from the DataSet,
click the greater than (>) button to move the Data-Table
to the Selected Tables section, and click OK.
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.
End of Article
Prev. page
1
[2]
next page -->
