Passing parameter to Crystal Report in Asp.Net C# and VB

"In a previous article, I demonstrated how to add a Crystal Report using XML data in ASP.NET. Building on that, this article will show you how to pass parameters dynamically to a Crystal Report in ASP.NET, complete with an example.

I am assuming you have checked this article first. It is important to understand how crystal reports are attached to a database table.

Following a similar method, we will pass a parameter to the Crystal Report with the click of a button. When necessary, we can pass multiple parameters.

The report that we are desiging now, shows employee details which are fetched from a database table, for a selected financial year. Therefore, the parameter we will pass to the Crystal Report is a string value (it can be anything). Crystal Report will extract employee details from the table using the parameter value we will pass.

Pass dynamic parameters to Crystal Report

Recreate the Employee Detail Report (Add the “Parameter Field”)

Follow these steps

01) Select the Employee.rpt report from the Solution Explorer. The report window will open. This window will have “Field Explorer” window at the left hand corner.

If the field explorer window is not open, then look for the button named Toggle Field View button on the toolbar of the report window. See the picture below.

Toggle

02) The field explorer window will have the Parameter Fields object. Right click it to create a new parameter field.

Add Parameters to a Crystal Report

Create Parameter Field in Crystal Report

03) Once the parameter field is created, drag the newly create field on to the report and place it where you want it to be.

Field Added

Finally, we will design the Page.

The Markup

<!DOCTYPE html>
<html>
<head>
    <title>Dynamically pass parameters to Crystal Report using Asp.Net</title>

    <style type="text/css">
        .button {
            text-align:center; 
            color:#FFF; 
            font:15px, Arial;
            background-color:#6D9BF1;
            border:solid 1px #3079ED; 
            border-radius:2px; 
            -moz-border-radius:2px; 
            -webkit-border-radius:2px; 
            line-height:15px; 
            padding:3px 2px
        }
        .button:hover { 
            background-color:#4876FF; 
            cursor:pointer;
        }
        .panRep {
	        border:solid 1px #93A0AA;
	        background-color:#FFF;
	        position:absolute;
	        text-align:center;
	        top:50px; 
            left:10px;
	    }
        .divprint { 
            font:15pxArial; 
            padding:10px;
        }
        .dropdown { 
            text-align:center; 
            color:#333; 
            padding:2px;
            font:13px Arial;
        }
    </style>
</head>
<body
    <form id="form1" runat="server">
        <div>
            <asp:TextBox runat="server" ID="tbFinancialYear" Text=""></asp:TextBox> 
            <asp:Button runat="server" ID="bt" Text="Show Report" /> 
        </div>

        <asp:Panel ID="panReport" 
            CssClass="panRep" 
            Visible="false"
            runat="server">

            <div class="divprint" style="float:left">
                Select a Printer:  
                    <asp:DropDownList
                        ID="ddlPrinters" CssClass="dropdown" 
                        runat="server">
                        </asp:DropDownList>

                    <asp:Button
                        id="btPrintRep" 
                        CssClass="button" 
                        ToolTip="Print" 
                        Text="Print" 
                        runat="server" />
            </div>

            <div class="divprint" style="float:left">
                Navigators: 
                <asp:Button ID="btFirstPage" ToolTip="First Page" Text="<<" runat="server" />
                <asp:Button ID="btNextPage" ToolTip="Next Page" Text=">" runat="server" />
                <asp:Button ID="btPrevPage" ToolTip="Previous Page" Text="<" runat="server" />
                <asp:Button ID="btLastPage" ToolTip="Last Page" Text=">>" runat="server" />
            </div>

        </asp:Panel>
    </form>
</body>
</html>

Code Behind (C#)

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
partial class _Default : System.Web.UI.Page
{
    // DECLARE A "REPORT" AND A "REPORT VIEWER".
    protected CrystalDecisions.CrystalReports.Engine.ReportDocument Report = 
        new CrystalDecisions.CrystalReports.Engine.ReportDocument();

    protected CrystalDecisions.Web.CrystalReportViewer CrystalReportViewer = 
        new CrystalDecisions.Web.CrystalReportViewer();

    protected void form1_Load(object sender, System.EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            // ADD THE LIST PRINTERS IN THE DROPDOWN CONTROL.

            foreach (string printer in System.Drawing.Printing.PrinterSettings.InstalledPrinters)
            {
                ddlPrinters.Items.Add(printer);
            }
        }
    }

    protected void bt_Click(object sender, System.EventArgs e)
    {
        LoadData(tbFinancialYear.Text); // IN THE BUTTON CLICK EVENT, SHOW REPORT.
    }

    private void LoadData(string sFinacialYear)
    {
        // PASS PARAMETER TO CRYSTAL REPORT.

        CrystalDecisions.Shared.ParameterField cry_PF = 
            new CrystalDecisions.Shared.ParameterField();

        CrystalDecisions.Shared.ParameterDiscreteValue cry_PV = 
            new CrystalDecisions.Shared.ParameterDiscreteValue();

        CrystalDecisions.Shared.ParameterFields cry_PAF = 
            new CrystalDecisions.Shared.ParameterFields();

        // THE PARAMETER FIELD NAME WHICH WE HAVE CREATED IN THE REPORT.
        cry_PF.ParameterFieldName = "finacialyear";

        cry_PV.Value = tbFinancialYear.Text;        // THE VALUE WHICH IS TO BE SHOWN.
        cry_PF.CurrentValues.Add(cry_PV);
        cry_PAF.Add(cry_PF);                     // ADD ALL THE FIELDS.

        string sSql = "SELECT *FROM dbo.Employee";

        // CALL SHOW REPORTS FUNCTION WITH THE PARAMETER.
        ShowReport(sSql, "Employee.rpt", "Employee", ref panReport, cry_PAF);
    }

The "ShowReport()" function (C#)

    private bool ShowReport(string sQuery, string rptFileName, string sTableName, 
            ref Panel panReport, CrystalDecisions.Shared.ParameterFields paramFields)
    {
        bool functionReturnValue = false;

        // SET THE CONNECTION STRING.
        string sCon = "Data Source=dna;Persist Security Info=False;Integrated Security=SSPI;" + 
            "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;";

        // SHOW THE REPORT WITH ALL ITS FEATURES.
        try
        {
            System.Data.SqlClient.SqlDataAdapter objDataAdapter = 
                new System.Data.SqlClient.SqlDataAdapter(sQuery, sCon);

            DataSet obj_ds = new DataSet();

            // SET TIMEOUT PERIOD, SINCE THE ENTIRE PROCESS IS ONLINE. MIGHT TAKE SOME TIME.
            objDataAdapter.SelectCommand.CommandTimeout = 500;
            objDataAdapter.Fill(obj_ds, sTableName);

            // LOAD THE REPORT.
            Report.Load(System.AppDomain.CurrentDomain.BaseDirectory() + "reports\\" + rptFileName + "");
            Report.SetDataSource(obj_ds);            // SET REPORT DATA SOURCE.

            CrystalReportViewer.BorderStyle = BorderStyle.None;
            CrystalReportViewer.DisplayGroupTree = false;
            CrystalReportViewer.DisplayToolbar = false;             // DON'T DISPLAY TOOL BAR.
            CrystalReportViewer.Zoom(150);                          // ZOOM FACTOR 150%.
            CrystalReportViewer.BestFitPage = false;
            CrystalReportViewer.HasCrystalLogo = false;
            CrystalReportViewer.Width = "1180";
            CrystalReportViewer.Height = "770";

            // THE FINANCIAL YEAR PARAMETER WILL BE DISPLAYED.
            CrystalReportViewer.ParameterFieldInfo = paramFields;

            CrystalReportViewer.ReportSource = Report;

            // FINALLY, ADD THE VIEWER WITH A PANEL CONTROL ON THE PAGE.
            panReport.Controls.Add(CrystalReportViewer);
            panReport.Visible = true;

            functionReturnValue = true;
        }
        catch (Exception ex)
        {
            functionReturnValue = false;
        }
        finally
        {
            //
        }
        return functionReturnValue;
    }

    // NAVIGATORS (FIRST PAGE, NEXT PAGE, PREVIOUS PAGE AND LAST PAGE)
    // NOTE: TO SEE THIS CONTROLS IN ACTION, ADD MORE DATA IN THE "EMPLOYEE" TABLE.

    protected void btFirstPage_Click(object sender, System.EventArgs e)
    {
        LoadData(tbFinancialYear.Text);
        CrystalReportViewer.ShowFirstPage();
    }

    protected void btNextPage_Click(object sender, System.EventArgs e)
    {
        LoadData(tbFinancialYear.Text);
        CrystalReportViewer.ShowNextPage();
    }

    protected void btPrevPage_Click(object sender, System.EventArgs e)
    {
        LoadData(tbFinancialYear.Text);
        CrystalReportViewer.ShowPreviousPage();
    }

    protected void btLastPage_Click(object sender, System.EventArgs e)
    {
        LoadData(tbFinancialYear.Text);
        CrystalReportViewer.ShowLastPage();
    }
}

VB.Net

Option Explicit On
Imports System.Data

Partial Class _Default
    Inherits System.Web.UI.Page

    ' DECLARE A "REPORT" AND A "REPORT VIEWER".
    Protected Report As CrystalDecisions.CrystalReports.Engine.ReportDocument = _
        New CrystalDecisions.CrystalReports.Engine.ReportDocument
    Protected CrystalReportViewer As CrystalDecisions.Web.CrystalReportViewer = _
        New CrystalDecisions.Web.CrystalReportViewer

    Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
        If Not Page.IsPostBack Then
        
            ' ADD THE LIST PRINTERS IN THE DROPDOWN CONTROL.
            For Each printer As String In System.Drawing.Printing.PrinterSettings.InstalledPrinters
                ddlPrinters.Items.Add(printer)
            Next printer
        End If
    End Sub

    Protected Sub bt_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bt.Click
        LoadData(Trim(tbFinancialYear.Text))    ' IN THE BUTTON CLICK EVENT, SHOW REPORT.
    End Sub

    Private Sub LoadData(ByVal sFinacialYear As String)

        ' PASS PARAMETER TO CRYSTAL REPORT.
        Dim cry_PF As New CrystalDecisions.Shared.ParameterField
        Dim cry_PV As New CrystalDecisions.Shared.ParameterDiscreteValue
        Dim cry_PAF As New CrystalDecisions.Shared.ParameterFields

        ' THE PARAMETER FIELD NAME WHICH WE HAVE CREATED IN THE REPORT.
        cry_PF.ParameterFieldName = "finacialyear"
        cry_PV.Value = Trim(tbFinancialYear.Text)       ' THE VALUE WHICH IS TO BE SHOWN.
        cry_PF.CurrentValues.Add(cry_PV)
        cry_PAF.Add(cry_PF)                             ' ADD ALL THE FIELDS.

        Dim sSql As String = "SELECT *FROM dbo.Employee"

        ' CALL SHOW REPORTS FUNCTION WITH THE PARAMETER.
        ShowReport(sSql, "Employee.rpt", "Employee", panReport, cry_PAF)
    End Sub

The "ShowReport()" function (VB)

    Private Function ShowReport(ByVal sQuery As String, ByVal rptFileName As String, 
        ByVal sTableName As String, ByRef panReport As Panel, 
            ByVal paramFields As CrystalDecisions.Shared.ParameterFields) As Boolean

        ' SHOW THE REPORT WITH ALL ITS FEATURES.
        Try
            Dim objDataAdapter As New System.Data.SqlClient.SqlDataAdapter(sQuery, clsConn.myConn)
            Dim obj_ds As New DataSet

            ' SET TIMEOUT PERIOD, SINCE THE ENTIRE PROCESS IS ONLINE. MIGHT TAKE SOME TIME.
            objDataAdapter.SelectCommand.CommandTimeout = 500
            objDataAdapter.Fill(obj_ds, sTableName)

            ' LOAD THE REPORT.
            Report.Load(System.AppDomain.CurrentDomain.BaseDirectory() & "reports\" & rptFileName & "")
            Report.SetDataSource(obj_ds)                        ' SET REPORT DATA SOURCE.

            CrystalReportViewer.BorderStyle = BorderStyle.None
            CrystalReportViewer.DisplayGroupTree = False
            CrystalReportViewer.DisplayToolbar = False          ' DON'T DISPLAY TOOL BAR.
            CrystalReportViewer.Zoom(150)                       ' ZOOM FACTOR 150%.
            CrystalReportViewer.BestFitPage = False
            CrystalReportViewer.HasCrystalLogo = False
            CrystalReportViewer.Width = "1180"
            CrystalReportViewer.Height = "770"

            ' THE FINANCIAL YEAR PARAMETER WILL BE DISPLAYED.
            CrystalReportViewer.ParameterFieldInfo = paramFields    

            CrystalReportViewer.ReportSource = Report

            ' FINALLY, ADD THE VIEWER WITH A PANEL CONTROL ON THE PAGE.
            panReport.Controls.Add(CrystalReportViewer)
            panReport.Visible = True

            ShowReport = True
        Catch ex As Exception
            ShowReport = False
        Finally
            '
        End Try
    End Function

    ' NAVIGATORS (FIRST PAGE, NEXT PAGE, PREVIOUS PAGE AND LAST PAGE)
    ' NOTE:  TO SEE THIS CONTROLS IN ACTION, ADD MORE DATA IN THE "EMPLOYEE" TABLE.

    Protected Sub btFirstPage_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles btFirstPage.Click
            
        LoadData(Trim(tbFinancialYear.Text))
        CrystalReportViewer.ShowFirstPage()
    End Sub

    Protected Sub btNextPage_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles btNextPage.Click
            
        LoadData(Trim(tbFinancialYear.Text))
        CrystalReportViewer.ShowNextPage()
    End Sub

    Protected Sub btPrevPage_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles btPrevPage.Click
            
        LoadData(Trim(tbFinancialYear.Text))
        CrystalReportViewer.ShowPreviousPage()
    End Sub

    Protected Sub btLastPage_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles btLastPage.Click
            
        LoadData(Trim(tbFinancialYear.Text))
        CrystalReportViewer.ShowLastPage()
    End Sub
End Class

Conclusion

I am hoping this article and its example will help you understand how you can pass parameters, dynamically, to a Crystal report in your Asp.Net project.

We have also seen how you can attach a database table to the Crystal report, design the Crystal report and finally pass parameters from an Asp.Net project to the report.

← PreviousNext →