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:
- Click File, New, Project (or press Ctrl+Shift+N).
- From the resulting New Project dialog box, select the
Visual C# node under Project types, then select the
Windows project type.
- In the Templates pane, select Windows Application.
- Provide a name and storage location for the application.
I called the sample project HierarchicalReport, and
saved it to C:\articles. You can store the application files
anywhere you’d like.
- Click OK, and VS 2005 will create a new project, adding
Form1 to it as the default.
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.
Previous
