SQL Server Magazine

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:

1. Click File, New, Project (or press Ctrl+Shift+N).
2. From the resulting New Project dialog box, select the Visual C# node under Project types, then select the Windows project type.
3. In the Templates pane, select Windows Application.
4. 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.
5. 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.

   Prev. page   [1] 2     next page