HomeASP.NET Working with Parameters with Crystal Repor...
Working with Parameters with Crystal Reports and ASP.NET 2.0
This is the second article in a series that covers programming with Crystal Reports with ASP.NET 2.0. In this article, we will focus on working with parameters using Crystal Reports and passing the parameter values from an ASP.NET 2.0 web site.
If you are new to Crystal report programming, I strongly suggest that you read the first article in this series, Programming Crystal Reports with ASP.NET 2.0. This article uses and enhances the solution (source code) provided at the above link.
The entire solution (source code) for this article is available as a free download in the form of a zip. The source code in this article has been developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows XP Professional Edition with Microsoft SQL Server 2005 Express Edition. I used the same version of Crystal Reports which comes with Visual Studio 2005 Professional Edition. I didn't test the code in any other tools/IDEs/servers/editions/versions. If you have any problems, please feel free to post in the discussion area.
Adding a single parameter to the Crystal Report at design time
Before proceeding further, open the "SampleWebSite01" website (available as a download with this article) and add a new Crystal Report (named "SampleParam1.rpt") focusing on the Orders table of the Northwind database. Make sure you add OrderID, CustomerID, EmployeeID and OrderDate to the report. All of this is explained in the first article of this series.
The following are the steps required to add a parameter to the Crystal report:
Using the "FieldExplorer," right click on "Parameter Fields" and click "New" as shown in the following figure (01).
Enter "Name" as "p_EmployeeID," "Prompting Text" as "Enter Employee ID," specify "Value Type" as "Number" and click on "OK" as shown below in figure 02.
Click on "Select Expert" on the tool bar, as highlighted in red in figure 03.
In the "Choose Field" dialog box, select "Orders.EmployeeID" and click on OK as shown in figure 04.
In the "Select Expert" dialog box, select the operator as "is equal to," select "{?p_EmployeeID}" as the value (shown in Figure 05) and click OK.
In the previous section, we added a parameter named "p_EmployeeID" to the report. Now it is time to access the report (say, get a list of orders) based on the user specified value of Employee ID.
Let us modify the web page so that it looks like the following (Fig 06):
The Source for the above page design is as follows:
First of all, you must observe that the CrystalDecisions.Shared namespace is added to work with Crystal Reports-related classes and objects. In the previous section's code, the database connection information is stored in ConnInfo and is defined as follows:
Dim ConnInfo As New ConnectionInfo
With ConnInfo
.ServerName = ".sqlexpress"
.DatabaseName = "Northwind"
.UserID = "sa"
.Password = "eXpress2005"
End With
The above connection information is assigned to the CrystalReportViewer control using the following code:
For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo
cnInfo.ConnectionInfo = ConnInfo
Next
Finally, the ReportSource and Parameter are added as following:
Dim ParamFields As ParameterFields = Me.CrystalReportViewer1.ParameterFieldInfo
Dim p_EmpID As New ParameterField
p_EmpID.Name = "p_EmployeeID"
Dim p_EmpID_Value As New ParameterDiscreteValue
p_EmpID_Value.Value = Me.txtEmployeeID.Text
p_EmpID.CurrentValues.Add(p_EmpID_Value)
ParamFields.Add(p_EmpID)
We can add as many numbers of parameters as we want to the "ParameterFields" collection to provide values at run-time. The following is the code which deals with paging:
Protected Sub btnFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFirst.Click
Me.CrystalReportViewer1.ShowFirstPage()
End Sub
Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
Me.CrystalReportViewer1.ShowPreviousPage()
End Sub
Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
Me.CrystalReportViewer1.ShowNextPage()
End Sub
Protected Sub btnLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLast.Click
Me.CrystalReportViewer1.ShowLastPage()
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
btnShow_Click(Nothing, Nothing)
End If
End Sub
Adding a Range Value parameter to Crystal Report
Add a new report named "SampleParam2.rpt" with the same fields, in a way that is similar to the previous report. Now, we shall add a range value parameter to the Crystal Report.
The following are the steps to take to add a range value parameter:
In the "FieldExplorer," right click on "Parameter Fields" and click "Add."
In the "Create Parameter Field" dialog box, provide "Name" as "p_OrderDateRange," "Prompting Text" as "Enter Start and End Dates," "Value Type" as "DateTime," select "Range Values" in the "Options" and click OK (Fig 07).
In the "Choose Field" dialog box, select "Orders.OrderDate" and click on OK (as shown in figure 08).
Using the "Select Expert" provide information as follows (Fig 09) and click OK.
In the previous section, we added a parameter named "p_OrderDateRange" to the report. Now, it is time to access the report (say, get a list of orders) based on the user-specified range of dates.
Let us modify the web page so that it looks like the following (Fig 10):
The source for the above page design is as follows:
This is very similar to the previous report except that this accepts multiple values (both discrete and range parameters).
In a way similar to the previous reports, add a new report named "SampleParam3.rpt" with the same fields. Now we shall add a new parameter to the Crystal Report.
The following are the steps required to add multiple value (Discrete and Range) parameters:
In the "FieldExplorer," right click on "Parameter Fields" and click "Add."
In the "Create Parameter Field" dialog box, provide "Name" as "p_OrderDate_Discrete_Range," "Prompting Text" as "Enter Start and End Dates or single date," "Value Type" as "DateTime," switch "Allow multiple values" to on in the "Options," select "Discrete and Range Values" from the radio button list and click OK (Fig 11).
Using the "Select Expert," provide the information for the "Choose Field" dialog box as being from "Orders.OrderDate" and click on OK (as shown in figure 12).
Using the "Select Expert" provide information as follows (Fig 13) and click OK.
In the previous section, we added a parameter named "p_OrderDate_Discrete_Range" to the report. Now it is time to access the report (say, get a list of orders) based on the user-specified range of dates.
Let us modify the web page so that it looks like the following (Fig 14):
The source for the above page design is as follows:
ASP.NET 
Programming Crystal Reports with ASP.NET 2.0
