Creating Sql Server Report (RDLC) using Dataset

Sksamantaray
Posted by in ASP.NET category on for Beginner level | Points: 250 | Views : 31409 red flag

Creating a Sql Server Report(RDLC) with the help of Dataset is straightforward task now.


 Download source code for Creating Sql Server Report (RDLC) using Dataset

Introduction

Generally we create a strongly typed data-set during design time for creating a RDLC Report which is considered to be data-source for a Report, as we saw in our last article ( http://www.dotnetfunda.com/articles/article1778-create-ssrs-client-side-report.aspx ).But this dataset does not satisfy our requirement under all circumstances. so we may need to modify its data at run-time. To achieve this we can create a dataset and populate data and bound to data-source.
In this article we will see how a data-set can provide data to a report-viewer control at run-time. 

Objective

How to bind data-set and RDLC report to a Report-viewer at run-time.

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

This is how a dataset and RDLC can render data at run-time.


Page copy protected against web site content infringement by Copyscape

About the Author

Sksamantaray
Full Name: Sanjaya Kumar Samantaray
Member Level: Silver
Member Status: Member
Member Since: 11/6/2011 3:05:05 PM
Country: India
Thanks, Sanjay

Wipro Technology

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)