Crystal Report with Parameters

Here is how we can simply pass the parameter through Crystal Report.  Here I am passing two parameter as Name (Last) and Joining Year (Year part).

First created crystal report file (as empReport.rpt) and go to database filed and created new Database Connection (OLEDB Connection).

Added the table or procedure name from left to right pane.  Now database fields is created just drag and drop the field name into the report area in Section 3 (details) and preview the report and it works fine.

Now it’s time to add the parameter in our running report, just here it is how to do.

Go to parameter fields in field explorer of created crystal report (.rpt file) and create parameter name as Year and Last Name and go to Select Expert icon just below to Debug button  and write formula as below

DATEPART("yyyy",{EmployeeMaster.JoiningDate}) = {?year} and  --for the year date part

{EmployeeMaster.LastName} = {?lName}                                            --here it is last name filer

Here is .aspx page code:

<asp:ScriptManager ID="scriptmyReport" runat="server"></asp:ScriptManager>

    <div>

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

     <asp:DropDownList ID="ddlYear" runat="server">

     <asp:ListItem Value="2012"></asp:ListItem>

     <asp:ListItem Value="2011"></asp:ListItem>

     <asp:ListItem Value="2010"></asp:ListItem>

     <asp:ListItem Value="2009"></asp:ListItem>

     <asp:ListItem Value="2008"></asp:ListItem>

     <asp:ListItem Value="2007"></asp:ListItem>

     </asp:DropDownList>

    <asp:Button ID="btnReport" runat="server" OnClick="showReport" Text="Go" />

        <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" EnableParameterPrompt="false" />

    </div>

Here is .aspx.cs code:

Use the below namespace for Crystal Report instance:

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

on Button click of Show Report, write below code:

protected void showReport(object sender, EventArgs e)

    {

        ReportDocument rptDoc = new ReportDocument();

        ReportDocument reportdocument = new ReportDocument();

        reportdocument.Load(Server.MapPath("empReport.rpt"));  //place the report file path above the report parameter

        reportdocument.SetParameterValue("lName", txtLName.Text);

        reportdocument.SetParameterValue("year", ddlYear.SelectedValue);

        //reportdocument.Load(Server.MapPath("empReport.rpt"));      //shows an error Invalid report file path

        CrystalReportViewer1.ReportSource = reportdocument;

    }

Cheers,