1.1. Adding a Crystal Report to an ASP.NET Application

The examples in this Wrox Blox are written for an ASP.NET application, so first create an ASP.NET application using Visual Studio. You can use Visual Studio 2008 or 2005. Crystal Reports is also part of 2003, but since I haven't tested the samples in this Wrox Blox in 2003, I can't swear by them. To create the ASP.NET application, follow these steps:

Visual Studio will create a web site on your computer with a Default.aspx page, Default.aspx.cs file, and web.config file.

The report we are going to create is a list of all employees in the database. The first step is to add a Crystal Report file to your web application.

Visual Studio will add the Employee.rpt file to your web application and automatically display the Crystal Reports Gallery dialog box shown in Figure 1.

Figure 1. Figure 1

This dialog gives you the option to use the Report Wizard to build a report, create a blank report (which you then have to build from scratch), or create a report off an existing report. The bottom of the screen allows you to select a specific type of report such as Standard, Cross-Tab, or Mail Label. We'll create the Standard report to display the employees, which is great for displaying lists of data, grouping, and subtotaling. The Cross-Tab report allows you to transform your data into columns and rows. If you wanted to build a report for each sales person and break out their sales by week, the Cross-Tab would be a good option. The Mail Label type of report allows you to create a report that would work with standard mailing labels. This is great for the supersensitive, ultraorganized, completely OCD, consumed bride who wants to handle her own invitations for her wedding. This won't be covered.

From the Crystal Reports Gallery dialog, choose "Using the Report Wizard" and select Standard from the "Choose an Expert" options. Now click OK. This launches the Standard Report Creation Wizard shown in Figure 2.

Figure 2. Figure 2

The first screen in the Wizard asks you to select the database you want to connect to. You have a couple of options here. You can connect to a database directly, report off the tables directly, perform the joins and filtering right in the Crystal Report file; or you can define a Dataset in your application and link your report to the Dataset. If you use the Dataset, you are working in Disconnected mode and won't be able to preview the report when you are designing it; however, you can achieve better performance using a Disconnected Dataset rather than connecting directly to the database. For this example, we will connect directly to the database.

Click the plus sign (+) next to "Create New Connection." Since this project connects directly to a SQL Server database, click on the plus sign next to the OLE DB (ADO) node. This opens the OLE DB (ADO) Wizard shown in Figure 3.

Figure 3. Figure 3

Select "SQL Native Client" and click Next. The next screen asks you for your connection information to the database. Enter the server name where you installed the AdventureWorks database. If you set up a SQL login for this database, enter the User ID and Password or use Integrated Security to use your Windows login to connect to the database. I checked the Integrated Security checkbox, and now the Database dropdown list is populated. Select AdventureWorks and click Next. The Advanced Information screen appears. Leave all the settings at their default value and click Finish. You should now see your server listed under the OLE DB (ADO) node and the AdventureWorks database under the server node, as shown in Figure 4.

Figure 4. Figure 4

Click on the plus sign next to the AdventureWorks database. This displays the schemas that have been defined in the database. If you were using a different database that didn't have schemas defined, you would see the dbo node. Click the plus sign next to HumanResources. This shows three nodes for Tables, Views, or Stored Procedures. Crystal Reports lets you build a report off any of these three types of objects. This Wrox Blox will report directly off the tables, so click on the plus sign next to Tables. This displays all the tables defined in the HumanResources schema. While holding down the [Ctrl] key, click on the Department, Employee, and EmployeeDepartmentHistory tables to highlight them. Now click on the > button to move these tables to the "Selected Tables" list. The name information for employees is stored in the Contact table in the Person schema, so expand the Person schema, expand the Tables node, click on the Contact table, and click on the > button to move this table to the "Selected Tables" list. This tells the Crystal Report that these are the four tables the report will work with and allows you to select any of the fields in these tables to add to the report. Your screen should look like Figure 5.

Figure 5. Figure 5

Click OK. This will open the report up in Design mode in Visual Studio. You should see a new window on either the right or left side of Visual Studio called the Field Explorer, as shown in Figure 6.

Figure 6. Figure 6

The Field Explorer contains all types of fields that you can add to the report. The tables that you just added appear under the Database Fields node. Before you can correctly display the data, you must define the relationships between the tables. To do this, right-click on the Database Fields node in the Field Explorer window. Select "Database Expert" from the menu. This displays the Database Expert dialog, which has two tabs. The first tab, Data, looks like the Report Wizard dialog that allows you to select your database. The second tab, Links, allows you to define the relationships between your tables. Click on the Links tab. You will see the four tables and all their fields in the dialog. For this database, the EmployeeDepartmentHistory table represents a junction table between the Employee and Department tables. This is a typical many-to-many relationship structure. If you cannot see the entire table, you can make the window larger by dragging on the bottom-right corner of the dialog. You can rearrange the tables by clicking and dragging on the table names. Arrange the tables so they are in the order of Contact, Employee, EmployeeDepartmentHistory, and Department from left to right. Left-click on the EmployeeID field in the Employee table, and drag your mouse pointer to the EmployeeID in the EmployeeDepartmentHistory table. This will create a line between the two tables to represent the relationship. This creates an inner join between the two tables by default. If you double-click on the relationship line, the Link Options dialog will appear. This allows you to define outer joins. Leave "Inner Join" selected and click OK. Now click on the DepartmentID field in the Department table, and drag the mouse pointer over the DepartmentID in the EmployeeDepartmentHistory table. Create a relationship between the Contact.ContactID field and the Employee.ContactID field the same way. Your screen should look like Figure 7.

Figure 7. Figure 7

Now click OK. You are ready to start building the report.