Saturday 3 August 2019

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.
 
 
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 EventArgsHandles 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 StringAs 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 ObjectByVal e As EventArgs)
    Me.BindReport()
End Sub

No comments:

Post a Comment