Monday, July 20, 2009

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. 

No comments:

Post a Comment