reating reports is one of the most common things developers are asked to do when building a typical business application. Businesses want to know how well a product has sold, the sales total for the month, the inventory status, etc. All these require specialized tools for easily generating the reports. And developers who are migrating from Visual Basic 6 would be happy to know that the Crystal Report they have been familiar with, is now available on Visual Studio 2005.

This article shows you how to get started with Crystal Report for Visual Studio 2005. You’ll learn how to create simple reports and how to add charts to your reports.

Creating a Report
To see the power of Crystal Report for Visual Studio 2005, let’s first create a report that shows some customers’ order information stored in the Northwind sample database. Figure 1 shows the sample report that you will create.

Installing the Sample Database
Since SQL Server 2005 Express does not come with any sample databases, you need to install the sample databases yourself.

You can install the Northwind sample databases by downloading their installation scripts here.

Once you’ve installed the scripts on your system, go to the Visual Studio 2005 Command Prompt (from the Start menu, choose Programs, Microsoft Visual Studio 2005, and then Visual Studio Tools, and finally Visual Studio 2005 Command Prompt) and change to the directory containing your installation scripts. Type in the following to install the Northwind database:

C:SQL Server 2000 Sample Databases>sqlcmd -S .SQLEXPRESS -i instnwnd.sql

Figure 1. The Sample Report: This report shows some customers order information stored in a Northwind sample database.

?

Figure 2. Table Relationships: The relationships of the four tables in the Northwind database.

As you can see, the products ordered by each company are listed, together with each product’s unit price and quantity ordered. In addition, report shows the country where the product is being shipped. All the relevant data are stored in the following four tables in the Northwind database:

• Customers
• Orders
• Order Details
• Products

The four tables are related in the following manner (see Figure 2).

Getting Started
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:

• Customers
• Orders Details
• Orders
• Products

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:

• Customers.CompanyName
• Orders Details.UnitPrice
• Orders Details.Quantity
• Orders.ShipCountry

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.

Previewing a Report
After the report is created, you can preview how it will look like by clicking the Main Report Preview button located at the bottom of the report. The report will be shown in two columns?the left column shows a list of companies that you can select and the right shows the report with the selected company’s name shown highlighted in blue.

Viewing a Report in a Windows Form
A report is only useful if it can be displayed in a Windows form during runtime. To do that, you will need to use the CrystalReportViewer control, a control that displays the report in a page-based layout.

First, add a new Windows Form to the project and use its default name of Form2. Double-click on the CrystalReportViewer control (located in the Toolbox) to add a new instance onto Form2 (see Figure 6). By default, the CrystalReportViewer control will fill the entire form. If you do not want it to fill the entire form, simply set the Dock property of the control to either left, right, top, bottom, or none. For this example, leave it as it is.

Figure 6. Form2: Adding the CrystalReportViewer control to Form2.

Back to Form1, add a Button control to it and set is Text property to View Report. Name the control btnViewReport. Double-click the button and code its Click event handler as follows:

Private Sub btnViewReport_Click( _   ByVal sender As System.Object, _   ByVal e As System.EventArgs) _   Handles btnViewReport.Click        Dim report As New CrystalReport1        With Form2            .CrystalReportViewer1.ReportSource = report            .ShowDialog()        End With    End Sub

Press F5 to test the application. When you click the View Report button, Form2 will appear, displaying the report (see Figure 7).

Figure 7. Displaying Form2: This image displays the report in runtime.

At the top of Form2, there are several controls associated with the report:

• Export Report: Saves the report in various file formats (.rpt, .pdf, .xls, .doc, .rtf, etc.)
• Print Report: Prints the report to the printer.
• Refresh Report: Refreshes the report.
• Toggle Group Tree: Hides/displays the left column of the report.
• Navigational Buttons: Navigates between pages in the report.
• Find Text: Performs a search for specific words.
• Zoom: Adjusts the view proportion of the report.

Adding Parameters to the Report
The report created in the previous section lists out all the customers’ orders. This might not be too useful as most of the time you might only want to view the orders for a particular customer. Hence, you should modify the report so that during runtime you can specify the particular customer to list.

In the Main Report view of the report, right-click on the Parameter Fields items located in the Field Explorer and select New?. This will create a new parameter for your report so that you can pass it a value during runtime.

In the Create Parameter Field dialog, specify Customer_ID as the name and use the other default values. Click OK. In the Choose Field dialog, choose CustomerID and click OK.

Click the Select Expert button ocated in the toolbar of Visual Studio 2005.

In the Select Expert dialog, set the values of the controls as shown in Figure 8. This indicates that the customer ID used for the report will be dependent on the values passed into the Customer_ID parameter (represented as {?Customer_ID}).

Figure 8. Setting Values: Setting the value of the CustomerID to be dependent on the parameter.

?

Figure 9. Adding Controls: Adding the controls to Form1.

Back in Form1, add a Label and ComboBox control as shown in Figure 9.

Switch to the code-behind of Form1 and import the following namespaces:

Imports System.DataImports System.Data.SqlClient

In the Form1_Load event, code the following to populate the ComboBox control with all the customers’ ID:

Private Sub Form1_Load( _   ByVal sender As System.Object, _   ByVal e As System.EventArgs) _   Handles MyBase.Load        Dim connStr As String = _           "Data Source=.SQLEXPRESS;" & _            "Initial Catalog=Northwind;" & _           "Integrated Security=True"        Dim sql As String = "SELECT CustomerID FROM Customers"        Dim conn As SqlConnection = New SqlConnection(connStr)        Dim comm As SqlCommand = New SqlCommand(sql, conn)        conn.Open()        Dim reader As SqlDataReader = comm.ExecuteReader        While reader.Read            ComboBox1.Items.Add(reader(0))        End While        conn.Close()    End Sub

Figure 10. Parameters Added: Viewing the orders made by the selected customer.

Modify the Click event of the button control to set the parameter with a value (which is the customer ID selected in the ComboBox control):

Private Sub btnViewReport_Click( _   ByVal sender As System.Object, _   ByVal e As System.EventArgs) _   Handles btnViewReport.Click        Dim report As New CrystalReport1        report.SetParameterValue( _           "Customer_ID", ComboBox1.Text)        With Form2            .CrystalReportViewer1.ReportSource = report            .ShowDialog()        End With    End Sub

That’s it! Press F5 to test the application. When the form is loaded, select a customer ID and click the View Report button. The report will now only contain the orders made by the selected customer (see Figure 10).

Displaying Charts
Crystal Report also supports the creation of graphical charts. In this section, you will see how you can display a chart showing the percentages of a particular product ordered by the each customer.

Add a new Crystal Report to the project and use its default name of CrystalReport2.rpt. Follow the same steps as before:

1. Add the following tables to the report:
   1. Customers
   2. Order Details
   3. Orders
   4. Products
2. Choose the following fields to display:
   1. Customers.CompanyName
   2. Products.ProductName
   3. Order Details.Quantity

Figure 11. After Configuration: The completed report with the chart.

3. Group the report by Customers.CompanyName.
4. For the Summaries section, ensure that only Sum of Order Details.Quantity is present.
5. In the Chart dialog, check the Pie Chart option.

Figure 11 shows how the report looks after configuration.

Now, add a new Parameter Field to the report and name the parameter Product_Name. Click the Select Expert button and bind the parameter to the Products.ProductName field of the report.

Finally, add the following controls to Form1:

• Label
• ComboBox
• Button

Add the following code in bold so that when the form is loaded, you will add the list of product names to the second ComboBox control:

Private Sub Form1_Load( _   ByVal sender As System.Object, _   ByVal e As System.EventArgs) _   Handles MyBase.Load        Dim connStr As String =            "Data Source=.SQLEXPRESS;" & _           "Initial Catalog=Northwind;" & _           "Integrated Security=True"        Dim sql As String = "SELECT CustomerID FROM Customers"        Dim conn As SqlConnection = New SqlConnection(connStr)        Dim comm As SqlCommand = New SqlCommand(sql, conn)        conn.Open()        Dim reader As SqlDataReader = comm.ExecuteReader        While reader.Read            ComboBox1.Items.Add(reader(0))        End While        conn.Close()        sql = "SELECT ProductName from Products"        comm.CommandText = sql        conn.Open()        reader = comm.ExecuteReader        While reader.Read            ComboBox2.Items.Add(reader(0))        End While        conn.Close()    End Sub

Code the Click event of the View Chart button as follows:

Private Sub btnViewChart_Click( _   ByVal sender As System.Object, _   ByVal e As System.EventArgs) _   Handles btnViewChart.Click        Dim report As New CrystalReport2        report.SetParameterValue("Product_Name", ComboBox2.Text)        With Form2            .CrystalReportViewer1.ReportSource = report            .ShowDialog()        End With
Figure 12. Distribution of Orders: Viewing the orders for a product by the various customers in a pie chart.
    End Sub

That’s it! Press F5 to test the application. Figure 12 shows the distribution of “Genen Shouyu” ordered by the various customers.

Worth Further Investigation
In this article, you have seen the versatility offered by Crystal Report for Visual Studio 2005. What’s been covered is just a small tip of the Crystal Report iceberg; it’s definitely worth it to explore Crystal Report’s other features. Let me know if this article is helpful to you and what other topics you want to see me cover in my future articles on Crystal Report.