Creating Sql Server Report (RDLC) using Dataset

Objective

Using the code

In our last Article we used a fixed query at design time i.e
select * from employees
This report always going to return all employees .However sometime we need to display employees of a particular country ,or employees of hired between a specified date range or employees of a city.
For that we can create a data-set and bind it to data source. In this article i will show how employees of City : London can be bound to Report-viewer. User Can use Drop-down or other controls to pass the value for the conditional queries.

Refer to my previous article : http://www.dotnetfunda.com/articles/article1778-create-ssrs-client-side-report.aspx

Steps:

1. Refer to your defined connection string of web.config.

 in general declaration section   :                                                           string connn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString();

2. Bind your dataset data to Report-Viewer

// The original dataset will be cleaned and will have new set of data depending upon query output

// Here we want the output to be the employeedetails of city London                                                                                         protected void getData()
    {

        SqlDataAdapter da = new SqlDataAdapter("select * from Employees where city='London'", connn);
        DataSet ds = new DataSet();
        da.Fill(ds);

        ReportDataSource rdc = new ReportDataSource("DataSet1_Employees", ds.Tables[0]);
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(rdc);
        ReportViewer1.Visible = true;
        ReportViewer1.LocalReport.Refresh();
        
    }

3. Call your getData Method in Page_Load

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
           getData();
        }
    }

//Output

Once you get the output in Report-Viewer, can export to PDF or Excel using the red color highlighted dropdown and clicking on export button then.

Conclusion