Filter RDLC Report ReportViewer control using DropDownList selection in ASP.Net

Last Reply on Feb 02, 2015 03:31 AM By Shashikant

Posted on Feb 01, 2015 11:28 PM

hi i hope every one is in health

how can make dropdownlist filter report viewer ...the dropdown list show

the department name when i choose department name it fliter data of department in reportviewer

any one have idea of this will appreciated

You are viewing reply posted by: Shashikant on Feb 02, 2015 03:31 AM.
Posted on Feb 02, 2015 03:31 AM Modified on on Feb 02, 2015 03:47 AM

Reference:

RDLC (Local SSRS) Report ASP.Net Example using DataSet or DataTable in C# VB.Net and Visual Studio 2010

HTML

<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
Please Select Country :&nbsp;&nbsp;&nbsp;
<asp:DropDownList ID="ddlCountries" runat="server" OnSelectedIndexChanged="Countries_SelectedIndexChanged"
    AutoPostBack="true">
</asp:DropDownList>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="600">
</rsweb:ReportViewer>

Code

string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.GetCountries();
        this.BindRDLCReport();
    }
}

private void BindRDLCReport()
{
    ReportViewer1.ProcessingMode = ProcessingMode.Local;
    ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
    Customers dsCustomers = GetData("SELECT TOP 20 * FROM Customers WHERE Country = @Country OR @Country = ''");
    ReportDataSource datasource = new ReportDataSource("Customers", dsCustomers.Tables[0]);
    ReportViewer1.LocalReport.DataSources.Clear();
    ReportViewer1.LocalReport.DataSources.Add(datasource);
}

protected void Countries_SelectedIndexChanged(object sender, EventArgs e)
{
    this.BindRDLCReport();
}

private Customers GetData(string query)
{
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@Country", this.ddlCountries.SelectedItem.Value);
            sda.SelectCommand = cmd;
            using (Customers dsCustomers = new Customers())
            {
                sda.Fill(dsCustomers, "DataTable1");
                return dsCustomers;
            }
        }
    }
}

private void GetCountries()
{
    SqlCommand cmd = new SqlCommand("SELECT DISTINCT Country FROM Customers");
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            DataTable dtCountries = new DataTable();
            sda.Fill(dtCountries);
            ddlCountries.DataSource = dtCountries;
            ddlCountries.DataTextField = "Country";
            ddlCountries.DataValueField = "Country";
            ddlCountries.DataBind();
            ddlCountries.Items.Insert(0, new ListItem("Select", ""));
        }
    }
}

Screenshots

1) On Page Load

2) After Selecting Country