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.

Contributed by
Rating: 5 stars5 stars5 stars5 stars5 stars / 48
July 24, 2007
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

A downloadable zip file is available for this article.

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.

Passing the value to Crystal Report Parameter dynamically: source code

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:

<form id="form1" runat="server">

  <div>

    <asp:Label ID="lblEmployeeID" runat="server" Text="Employee ID:"></asp:Label>

    <asp:TextBox ID="txtEmployeeID" runat="server"></asp:TextBox>

    <asp:Button ID="btnShow" runat="server" Text="Show" /><br />

    <asp:Button ID="btnFirst" runat="server" Text="First Page" />

    <asp:Button ID="btnPrevious" runat="server" Text="Previous Page" />

    <asp:Button ID="btnNext" runat="server" Text="Next Page" />

    <asp:Button ID="btnLast" runat="server" Text="Last Page" /><br />

    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" DisplayGroupTree="False" DisplayToolbar="False" EnableDatabaseLogonPrompt="False" EnableParameterPrompt="False" Height="1055px" ReuseParameterValuesOnRefresh="True" Width="789px" />

  </div>

</form>

The following is the code for the "Show" button:

Protected Sub btnShow_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnShow.Click

  Dim ConnInfo As New ConnectionInfo

  With ConnInfo

    .ServerName = ".sqlexpress"

    .DatabaseName = "Northwind"

    .UserID = "sa"

    .Password = "eXpress2005"

End With

 

  Me.CrystalReportViewer1.ParameterFieldInfo.Clear()

  If Me.txtEmployeeID.Text.Trim.Length > 0 Then

    Me.CrystalReportViewer1.ReportSource = Server.MapPath("SampleParam1.rpt")

    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)

  Else

    Me.CrystalReportViewer1.ReportSource = Server.MapPath("SampleRpt01.rpt")

  End If

 

  For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo

    cnInfo.ConnectionInfo = ConnInfo

  Next

    Me.CrystalReportViewer1.RefreshReport()

End Sub

The explanation for the above code is provided in the next section.

Passing the value to Crystal Report Parameter dynamically at run-time: explanation

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:

Me.CrystalReportViewer1.ParameterFieldInfo.Clear()

  Me.CrystalReportViewer1.ReportSource = Server.MapPath("SampleParam1.rpt")

  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.

Passing the value to the Crystal Report Range Parameter dynamically: source code

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:

<form id="form1" runat="server">

  <div>

    <asp:Label ID="lblStartDate" runat="server" Text="Start Date:"></asp:Label>

    <asp:TextBox ID="txtStartDate" runat="server"></asp:TextBox><asp:Label

      ID="lblEndDate" runat="server" Text="End Date:"></asp:Label><asp:TextBox ID="txtEndDate"

      runat="server"></asp:TextBox><asp:Button ID="btnShow" runat="server" Text="Show" /><br />

    <asp:Button ID="btnFirst" runat="server" Text="First Page" />

    <asp:Button ID="btnPrevious" runat="server" Text="Previous Page" />

    <asp:Button ID="btnNext" runat="server" Text="Next Page" />

    <asp:Button ID="btnLast" runat="server" Text="Last Page" /><br />

    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" DisplayGroupTree="False" DisplayToolbar="False" EnableDatabaseLogonPrompt="False" EnableParameterPrompt="False" Height="1055px" ReuseParameterValuesOnRefresh="True" Width="789px" />

  </div>

</form>

Following is the code for the "Show" button:

Protected Sub btnShow_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnShow.Click

  Dim ConnInfo As New ConnectionInfo

  With ConnInfo

    .ServerName = ".sqlexpress"

    .DatabaseName = "Northwind"

    .UserID = "sa"

    .Password = "eXpress2005"

  End With

 

  Me.CrystalReportViewer1.ParameterFieldInfo.Clear()

  If Me.txtStartDate.Text.Trim.Length > 0 And Me.txtStartDate.Text.Trim.Length > 0 Then

    Me.CrystalReportViewer1.ReportSource = Server.MapPath("SampleParam2.rpt")

    Dim ParamFields As ParameterFields = Me.CrystalReportViewer1.ParameterFieldInfo

    Dim p_OrderDateRange As New ParameterField

    p_OrderDateRange.Name = "p_OrderDateRange"

    Dim p_OrderDateRange_Value As New ParameterRangeValue

    p_OrderDateRange_Value.StartValue = Me.txtStartDate.Text

    p_OrderDateRange_Value.EndValue = Me.txtEndDate.Text

    p_OrderDateRange.CurrentValues.Add(p_OrderDateRange_Value)

    ParamFields.Add(p_OrderDateRange)

  Else

    Me.CrystalReportViewer1.ReportSource = Server.MapPath("SampleRpt01.rpt")

  End If

 

  For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo

    cnInfo.ConnectionInfo = ConnInfo

  Next

  Me.CrystalReportViewer1.RefreshReport()

End Sub

Adding Multiple Value (Discrete and Range) parameters to Crystal Report

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.

 

Passing the value to Multiple Crystal Report Parameters dynamically: source code

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:

<form id="form1" runat="server">

  <div>

    <asp:Label ID="lbl" runat="server" Text="Order Date:"></asp:Label>

    <asp:TextBox ID="txtParticularOrderDate" runat="server"></asp:TextBox>

    <asp:Button ID="btnShow" runat="server" Text="Show" /><br />

    <asp:Label ID="Label1" runat="server" Text="Order Date
From:"></asp:Label>

    <asp:TextBox ID="txtOrderDateFrom" runat="server"></asp:TextBox>

    <asp:Label ID="Label2" runat="server" Text="Order Date To:"></asp:Label>

    <asp:TextBox ID="txtOrderDateTo" runat="server"></asp:TextBox>

    <asp:Button ID="btnRangeShow" runat="server" Text="Show" />

    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"
AutoDataBind="true" DisplayGroupTree="False" DisplayToolbar="False"
EnableDatabaseLogonPrompt="False" EnableParameterPrompt="False"
Height="1055px" ReuseParameterValuesOnRefresh="True" Width="789px" />

 

  </div>

</form>

The following is the code for the "Show" button:

Protected Sub btnShow_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnShow.Click

.

.

.

Dim ParamCurrentValues As New ParameterValues

  Dim p_OrderDate_Value As New ParameterDiscreteValue

  p_OrderDate_Value.Value = Me.txtParticularOrderDate.Text

  ParamCurrentValues.Add(p_OrderDate_Value)

  Dim ParamFields As ParameterFieldDefinitions =
rep.DataDefinition.ParameterFields

  Dim p_OrderDate As ParameterFieldDefinition = ParamFields
("p_OrderDate_Discrete_Range")

  p_OrderDate.ApplyCurrentValues(ParamCurrentValues)

End Sub

Following is the code for the "Range Show" button:

Protected Sub btnRangeShow_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnRangeShow.Click

.

.

.

  Dim ParamCurrentValues As New ParameterValues

  Dim p_OrderDate_Value As New ParameterRangeValue

  p_OrderDate_Value.StartValue = Me.txtOrderDateFrom.Text

  p_OrderDate_Value.EndValue = Me.txtOrderDateTo.Text

  ParamCurrentValues.Add(p_OrderDate_Value)

  rep.DataDefinition.ParameterFields
("p_OrderDate_Discrete_Range").ApplyCurrentValues(ParamCurrentValues)

 

  Me.CrystalReportViewer1.ReportSource = rep

  Me.CrystalReportViewer1.DataBind()

End Sub

I hope you enjoyed the article and any suggestions, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com

blog comments powered by Disqus
ASP.NET ARTICLES

- Implementing ASP.NET 4.0 Page.MetaDescriptio...
- ASP.Net Development Tips
- Intro to Sessions in ASP.Net
- Google Maps API Introduction in ASP.NET usin...
- Creating an ASP.NET 3.5 Gridview Image Galle...
- Encrypt QueryString in ASP.NET 3.5 using VB....
- ASP.NET 3.5 Drop Down List Controls
- Connect to Access Database with ASP.Net
- Secure Audio Streaming with ASP.Net and Flash
- Dynamic Sitemap and Navigation in ASP.Net
- Implement Gzip and Deflate Compression in AS...
- Run ASP.Net in Ubuntu with Apache
- ASP.Net Mono Website Contact Forms
- ASP.Net URL Rewriting Methods
- Murach`s ASP.NET 4 Web Programming with C# 2...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 

ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 9 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials