Passing parameters programmatically to Crystal report

It is very easy to pass the parameters in Crystal reports editing the crystal reports ( just by adding the required parameters on the report and making "EnableParameterprompt = "True" " in the properties of the crystal report viewer. , but it has several drawbacks, like the parameters do not get added to the report automatically, we can not really modify the interface of parameter prompt and so on. 

Today we are discussing about the parameter passing to crystal reports programmatically.  

In .aspx page, drag the control to ask for the parameters and drag the control for Crystal report viewer as follows:

<table>

<tr><td style="vertical-align: top; text-align: left; height: 21px;"><asp:Label ID="lbl1" runat="server" Text="Choose the semester" style="vertical-align: top; text-align: left" />td>

<td style="height: 21px; vertical-align: top; text-align: left;"><asp:DropDownlist ID="drpSemester" runat="server" AppendDataBoundItems="True" DataSourceID="SqlDataSource1" DataTextField="Semester" DataValueField="Semester"/>td>

tr><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="" SelectCommand="SELECT [Semester] FROM [SemesterInformation] ORDER BY [BeginDate], [EndDate]">

    asp:SqlDataSource>

<tr><td><asp:Button ID="cmdGenreport" runat="server" Text="Generate Report" />td>tr>

table>

      <CR:CrystalReportViewer ID="myCrystalReportViewer" runat="server" AutoDataBind="True"

         Height="1039px" ReportSourceID="CrystalReportSource1" Width="901px" style="position: absolute" EnableDatabaseLogonPrompt="false" EnableParameterPrompt="false" />

   

Design a report with parameters. To add a parameter in a report, you have to go to the “Database expert”, in the command edit box, you will see “parameter list” at the right hand side of the box, create a new parameter there and include that in your query. Add the following code in the load event of your page:

  Dim CRreport As New ReportDocument

        If Session("report1") IsNot Nothing Then

            CRreport = Session("report1")

            myCrystalReportViewer.ReportSource = CRreport

        End If

In the click event if the “cmdgenerateReport” Button add the following code:

  Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()

        Dim strReportName As String = ""

        Dim cryRep As New ReportDocument

        myConnectionInfo.DatabaseName = "Databasename"

        myConnectionInfo.ServerName = "Servername"

        myConnectionInfo.UserID = "Username"

        myConnectionInfo.Password = "password"

        myConnectionInfo.Type = ConnectionInfoType.SQL

        report.Load(Server.MapPath("~/myreport1.rpt"))

        SetDBLogonForReport(myConnectionInfo, report)

        'Load Session variables with values from web-controls

        Session("Semester") = drpSemester.SelectedValue.ToString()

        'Load all parameter fields associated with the report into "fields" object

        Dim fields As ParameterFields

        fields = New CrystalDecisions.Shared.ParameterFields()

        fields = report.ParameterFields

        For Each field As ParameterField In fields

            report.SetParameterValue(field.Name, Session(field.Name))

        Next

        myCrystalReportViewer.ReportSource = report

        Session("report1") = report

The setDBlogonForreport is as follows:

  Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo, ByVal myReportDocument As ReportDocument)

        Dim myTables As Tables = myReportDocument.Database.Tables

        Dim myTableLogonInfos As TableLogOnInfos = myCrystalReportViewer.LogOnInfo

        Dim myTablelogonInfo As TableLogOnInfo

        For Each myTable As Table In myTables

            myTablelogonInfo = myTable.LogOnInfo

            myTablelogonInfo.ConnectionInfo = myConnectionInfo

            myTable.ApplyLogOnInfo(myTablelogonInfo)

        Next

    End Sub

To manage the proper navigation of the report include the following code in the .vb page:

  Protected Sub myCrystalReportViewer_Navigate(ByVal source As Object, ByVal e As CrystalDecisions.Web.NavigateEventArgs) Handles myCrystalReportViewer.Navigate

        report.FileName = Server.MapPath("~/myreport1.rpt")

        Dim fields As ParameterFields

        fields = New CrystalDecisions.Shared.ParameterFields()

        fields = report.ParameterFields

    End Sub

Add the following code at the unload event of the page:

Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload

        myCrystalReportViewer.Dispose()

        GC.Collect()

    End Sub

In this way, you can pass as many parameters as you need to crystal reports.

Please write comment below if you have any kinds of questions/suggestions.