Tuesday, July 28, 2015

Asp.net: Pass parameter to RDLC report

In this article I am going to explain How to pass a parameter to RDLC report in asp.net

Description:

I have a table Tb_Student and having records. I want to filter the records and show in RDLC report based on city of Students.

Implementation: 
I have create a store to fetch and filter the record from table
Create Proc Sp_GetStudentData
(
@city varchar(50)
)
As begin
if @city ='All'
Select * from Tb_Student
Else
Select * from Tb_Student where Student_Address like '%'+@city+'%'
end


After complete all steps you see an App_code folder created in project/website with DataSet (DemoDataSet) as shown in below attached snapshot:

Asp.net: Pass parameter to RDLC report


Now add a webform to project. Drag and drop the required control from toolbox to webform (ReportViwer and ScriptManager).

HTML Markup of webform:
  <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
          <table><tr><td>City Name:</td><td><asp:TextBox ID="txtcity" runat="server"></asp:TextBox></td></tr>
            <tr><td></td><td> <asp:Button ID="Button1" runat="server" Text="Search"/></td></tr>
        </table>   
        <rsweb:ReportViewer ID="RptviwerStudent" runat="server"></rsweb:ReportViewer>

Add the namespace to code file
C#:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Reporting.WebForms;

VB:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports Microsoft.Reporting.WebForms

Binding the Reporviewer :
Write the below given code to bind the reportviewer
C#:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindReport();
        }
    }
    private void BindReport()
    {
        try
        {
            RptviwerStudent.ProcessingMode = ProcessingMode.Local;
            //report path
            RptviwerStudent.LocalReport.ReportPath = Server.MapPath("~/RptStudent.rdlc");           
            SqlDataAdapter adp = new SqlDataAdapter("Sp_GetStudentData", con);
            adp.SelectCommand.CommandType = CommandType.StoredProcedure;
            adp.SelectCommand.Parameters.AddWithValue("@city", "All");
            //object of Dataset DemoDataSet
            DemoDataSet ds = new DemoDataSet();
            adp.Fill(ds,"Sp_GetStudentData");
            //Datasource for report
            ReportDataSource datasource = new ReportDataSource("DataSet1", ds.Tables[0]);
            RptviwerStudent.Width = 600;
            RptviwerStudent.LocalReport.DataSources.Clear();
            RptviwerStudent.LocalReport.DataSources.Add(datasource);
        }
        catch (Exception ex)
        {
        }
    }

VB:
Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindReport()
        End If
    End Sub
    Public Sub BindReport()
        Try
            RptviwerStudent.ProcessingMode = ProcessingMode.Local
            'report path
            RptviwerStudent.LocalReport.ReportPath = Server.MapPath("~/RptStudent.rdlc")
            Dim adp As New SqlDataAdapter("Sp_GetStudentData", con)
            adp.SelectCommand.CommandType = CommandType.StoredProcedure
            adp.SelectCommand.Parameters.AddWithValue("@city", "All")
            'object of Dataset DemoDataSet
            Dim ds As New DemoDataSet()
            adp.Fill(ds, "Sp_GetStudentData")
            'Datasource for report
            Dim datasource As New ReportDataSource("DataSet1", ds.Tables(0))
            RptviwerStudent.Width = 600
            RptviwerStudent.LocalReport.DataSources.Clear()
            RptviwerStudent.LocalReport.DataSources.Add(datasource)
        Catch ex As Exception
        End Try
    End Sub

Search the records:
On button click write the given code search the records based on city name.
C#:
protected void Button1_Click(object sender, EventArgs e)
    {
        RptviwerStudent.ProcessingMode = ProcessingMode.Local;
        RptviwerStudent.LocalReport.ReportPath = Server.MapPath("~/RptStudent.rdlc");
        SqlDataAdapter adp = new SqlDataAdapter("Sp_GetStudentData", con);
        adp.SelectCommand.CommandType = CommandType.StoredProcedure;
        adp.SelectCommand.Parameters.AddWithValue("@city",txtcity.Text);
        DemoDataSet ds = new DemoDataSet();
        adp.Fill(ds, "Sp_GetStudentData");
        ReportDataSource datasource = new ReportDataSource("DataSet1", ds.Tables[0]);
        RptviwerStudent.Width = 600;
        RptviwerStudent.LocalReport.DataSources.Clear();
        RptviwerStudent.LocalReport.DataSources.Add(datasource);
    }

VB:
Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        RptviwerStudent.ProcessingMode = ProcessingMode.Local
        RptviwerStudent.LocalReport.ReportPath = Server.MapPath("~/RptStudent.rdlc")
        Dim adp As New SqlDataAdapter("Sp_GetStudentData", con)
        adp.SelectCommand.CommandType = CommandType.StoredProcedure
        adp.SelectCommand.Parameters.AddWithValue("@city", txtcity.Text)
        Dim ds As New DemoDataSet()
        adp.Fill(ds, "Sp_GetStudentData")
        Dim datasource As New ReportDataSource("DataSet1", ds.Tables(0))
        RptviwerStudent.Width = 600
        RptviwerStudent.LocalReport.DataSources.Clear()
        RptviwerStudent.LocalReport.DataSources.Add(datasource)
    End Sub


Now build, run the project and check out the result.
Result:

Asp.net: Pass parameter to RDLC report
    In this article we have learn how to pass parameter to RDLC reoprt in Asp.net (C#, VB). I hope you enjoyed this article. 

2 comments:

  1. Replies
    1. Glad to know this article helps you. Keep visiting for more articles.

      Delete