To get started, launch Visual Studio 2005 and create a new Windows project. Name the project as Report.
Add a new item to the project by right-clicking on the project name in Solution Explorer and then selecting Add | New Item. . . . Select the Crystal Report template and use its default name of CrystalReport1.rpt
|Figure 3. Table Selection: Selecting the tables to use for the report.|
You will be greeted with the Crystal Reports Gallery dialog. Choose the “Using the Report Wizard” and “Standard” options and click OK.
The Standard Report Creation Wizard dialog will now appear. In this step, you will choose the data source to connect to so that you can use it to generate the report. For this article, you will connect to a SQL Server 2005 Express database (you will use the Northwind database). Expand the Create New Connection data source and then expand the OLDE DB (ADO) item.
You will be asked to select an OLE DB provider. For SQL 2005 Server databases, select SQL Native Client.
In the next step, you need to provide the connection information for the database you are connecting to. Type in .\SQLEXPRESS for the server name and check the Integrated Security checkbox. Click the drop-down list next to the Database item and select the Northwind database. Click Next to continue. In the next dialog, simply click Finish to finish configuring the data source.
|Figure 4. Summary Information: Adding/removing the summaries fields.|
You will now be asked to choose the tables to use for the report. Expand the Northwind database, followed by dbo and then Tables. Select the following tables (see also Figure 3) and click the > button:
- Orders Details
Click Next to continue. In the next dialog, you will be asked to confirm the relationships between the various tables you have selected. Click Next to continue.
|Figure 5. Final Product: The completed report in Visual Studio 2005.|
In the next dialog, you'll select the fields to use for the report. Select the following fields and click the > button:
- Orders Details.UnitPrice
- Orders Details.Quantity
Click Next to continue. In the next dialog, you'll choose the field to group the report. Select the Customers.CompanyName
field and click the >
button to add the field.
Click Next to continue. In the next dialog, you'll add summary information to the report. Crystal Report automatically detects the numeric fields that can be summed and adds the two fields shown in Figure 4. In this case, you are more interested to know about the total quantity of a product ordered by a company, rather than the total price of products ordered. Hence, select the Sum of Order Details.UnitPrice item and click the < button to remove it from the report.
Click Next to continue. In the next three dialogs, click Next to continue. In the final dialog, select one of the available report styles that you can use to build your report. Select the Drop Table style and click Finish.
That's it! Your report is now created as shown in Figure 5.