Crystal Report in Visual Studio 2005
Monday, December 1st, 2008Crystal Report: Creating 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.
Steps to create the crystal report in Visual Studio 2005:
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.
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.
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 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.
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. 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.
The crystal report is ready.
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. 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.
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.
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}).
Setting Values: Setting the value of the CustomerID to be dependent on the parameter.
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.Data
Imports 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
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.
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:
Add the following tables to the report:
Customers
Order Details
Orders
Products
Choose the following fields to display:
Customers.CompanyName
Products.ProductName
Order Details.Quantity
After Configuration: The completed report with the chart.
Group the report by Customers.CompanyName.
For the Summaries section, ensure that only Sum of Order Details.Quantity is present.
In the Chart dialog, check the Pie Chart option.
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
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.
