Filter Crystal Report in ASP.Net using C# and VB.Net

In this article I will explain with an example, how to filter Crystal Report in ASP.Net using C# and VB.Net.

Crystal Report data will be filtered on selection of DropDownList in ASP.Net.

 

 

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here.

Download Northwind Database

 

 

HTML Markup

The HTML Markup consists of a CrystalReportViewer control, a DropDownList to filter Crystal Report.

Country:

<asp:DropDownList ID="ddlCountries" runat="server" OnSelectedIndexChanged="CountryChanged"

    AutoPostBack="true">

    <asp:ListItem Text="All" Value="" />

    <asp:ListItem Text="Spain" Value="Spain" />

    <asp:ListItem Text="Germany" Value="Germany" />

    <asp:ListItem Text="France" Value="France" />

    <asp:ListItem Text="Canada" Value="Canada" />

</asp:DropDownList>

<hr />

<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true"

    Height="400" Width="600" BestFitPage="False" ToolPanelView="None" />

 

 

Namespaces

You will need to import the following namespaces.

C#

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

using CrystalDecisions.CrystalReports.Engine;

 

VB.Net

Imports System.Data

Imports System.Configuration

Imports System.Data.SqlClient

Imports CrystalDecisions.CrystalReports.Engine

 

 

Designing and populating the Crystal Report from Database

Inside the Page Load event, the Crystal Report is populated from database.

Note: For more details about designing and populating Crystal Report, please refer the following article.
          Crystal Report ASP.Net Example using DataSet or DataTable in C# VB.Net and Visual Studio 2010

 

Inside the BindReport function, the selected value of the DropDownList i.e. the selected Country is passed to the GetData function where the value is passed as parameter and the records are filtered.

C#

protected void Page_Load(object sender, EventArgs e)

{

    if (!this.IsPostBack)

    {

        this.BindReport();

    }

}

 

private void BindReport()

{

    ReportDocument crystalReport = new ReportDocument();

    crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"));

    Customers dsCustomers = this.GetData(ddlCountries.SelectedItem.Value);

    crystalReport.SetDataSource(dsCustomers);

    CrystalReportViewer1.ReportSource = crystalReport;

}

 

private Customers GetData(string country)

{

    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    using (SqlCommand cmd = new SqlCommand("SELECT TOP 10 * FROM Customers WHERE Country = @Country OR @Country = ''"))

    {

        using (SqlConnection con = new SqlConnection(conString))

        {

            using (SqlDataAdapter sda = new SqlDataAdapter())

            {

                cmd.Connection = con;

                sda.SelectCommand = cmd;

                cmd.Parameters.AddWithValue("@Country", country);

                using (Customers dsCustomers = new Customers())

                {

                    sda.Fill(dsCustomers, "DataTable1");

                    return dsCustomers;

                }

            }

        }

    }

}

 

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

    If Not Me.IsPostBack Then

        Me.BindReport()

    End If

End Sub

 

Private Sub BindReport()

    Dim crystalReport As New ReportDocument()

    crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"))

    Dim dsCustomers As Customers = Me.GetData(ddlCountries.SelectedItem.Value)

    crystalReport.SetDataSource(dsCustomers)

    CrystalReportViewer1.ReportSource = crystalReport

End Sub

 

Private Function GetData(ByVal country As String) As Customers

    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Using cmd As SqlCommand = New SqlCommand("SELECT TOP 10 * FROM Customers WHERE Country = @Country OR @Country = ''")

        Using con As SqlConnection = New SqlConnection(conString)

            Using sda As SqlDataAdapter = New SqlDataAdapter()

                cmd.Connection = con

                sda.SelectCommand = cmd

                cmd.Parameters.AddWithValue("@Country", country)

                Using dsCustomers As Customers = New Customers()

                    sda.Fill(dsCustomers, "DataTable1")

                    Return dsCustomers

                End Using

            End Using

        End Using

    End Using

End Function

 

 

Filter Crystal Report using DropDownList in ASP.Net using C# and VB.Net

When a Country is selected in the DropDownList, the BindReport function is again called.

C#

protected void CountryChanged(object sender, EventArgs e)

{

    this.BindReport();

}

 

VB.Net

Protected Sub CountryChanged(ByVal sender As Object, ByVal e As EventArgs)

    Me.BindReport()

End Sub