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. 

Comments

Post a Comment

Popular posts from this blog

“Apple tablet”: How will it look like?

People are talking about “Apple tablet”. We can see so many news, views, rumors and speculations regarding “Apple tablets” these days. Many designers are sketching their ideas of Apple tablet. I googled “apple tablet” and saw so many pictures about the product which is not even released. I am wondering what will be the name of the tablet, may be something like iTablet (like iPhone, iPod. People are also saying that the name might be iSlate). This might be the fusion of iPod and iPhone in one, or might be way more powerful and useful than those, or might be just another model of Mac book Pro. I am also wondering how this highly anticipated gadget will look like. Will it look like one of these ? Will it bring a new technological breakthrough or it will be just another gadget? I hope Mr. Jobs announces the new gadget soon, and I will get all my answers. :-)
Read more

Some Grad students' jokes

You know you're a grad student when... you can identify universities by their internet domains. you are constantly looking for a thesis in novels. you have difficulty reading anything that doesn't have footnotes. you understand jokes about Foucault. the concept of free time scares you. you consider caffeine to be a major food group. you've ever brought books with you on vacation and actually studied. Saturday nights spent studying no longer seem weird. the professor doesn't show up to class and you discuss the readings anyway. you've ever travelled across two state lines specifically to go to a library. you appreciate the fact that you get to choose which twenty hours out of the day you have to work. you still feel guilty about giving students low grades (you'll get over it). you can read course books and cook at the same time. you schedule events for academic vacations so your friends can come. you hope it snows during spring break so you can get more studyi...
Read more