Online courses in ASP.NET MVC, ASP.NET Core, and Design Patterns conducted by Bipin Joshi. Read more...
Learn ASP.NET MVC, ASP.NET Core, and Design Patterns through our online training programs. Courses conducted by Bipin Joshi on weekends. Read more details here.

Working with ReportViewer control Of ASP.NET 2.0

Introduction

Reporting is one of the common requirements of any data driven business application. Since version 1.x, ASP.NET was lacking this important feature. In spite of add ons such as Crystal Report Viewer the .NET framework always lacked native support for reporting engine. Fortunately, .NET 2.0 comes with a ReportViewer control that meets your basic reporting needs. Though it lacks some of the fancy frills provided by some other third party vendors it certainly fills the gap. In this article I am going to show you how to use this new control. The ReportViewer control is available for Web as well as Windows applications. However, I am going to restrict this article to Web applications alone.

Sample Report

Let's assume that you wish to generate a country wise customer listing report as shown below:

The report shown above a simple listing of customers grouped upon their country. The data of the report is fetched from the Customers table of Northwind database. By default it shows all the customers. However, you also want to display customers belonging to a specific country.

The reports designed using the ReportViewer control can accept data from strongly typed DataSet (default) or custom business objects. Many real world applications use 3-tier architecture and data is often received from some business object in the form of a DataSet or generic collection. Hence, I am going to use business object as the source of data instead of strongly typed DataSet.

Creating a Class Library

To begin with, open Visual Studio and create a project of type Class Library named ReportViewerLib. Add a class called Customer in it and code it as shown below:

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace ReportViewerLib
{
public class Customer
{
    public string strCustomerID;
    public string strCompanyName;
    public string strContactName;
    public string strCountry;

    public string CustomerID
    {
        get
        {
            return strCustomerID;
        }
        set
        {
            strCustomerID = value;
        }
    }

    public string CompanyName
    {
        get
        {
            return strCompanyName;
        }
        set
        {
            strCompanyName= value;
        }
    }

    public string ContactName
    {
        get
        {
            return strContactName;
        }
        set
        {
            strContactName= value;
        }
    }

    public string Country
    {
        get
        {
            return strCountry;
        }
        set
        {
            strCountry= value;
        }
    }


    public static List<Customer> GetCustomersForCountry
    (string country)
    {
        SqlConnection cnn=new SqlConnection(
        ConfigurationManager.ConnectionStrings
        ["NorthwindConnectionString"].ConnectionString);
        SqlCommand cmd=new SqlCommand();
        cmd.Connection=cnn;
        cmd.CommandText="select 
        CustomerID,CompanyName,ContactName,Country 
        from customers where country=@country";
        SqlParameter p=new SqlParameter
        ("@country",country);
        cmd.Parameters.Add(p);
        cnn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        List<Customer> list = new List<Customer>();
        while (reader.Read())
        {
            Customer c = new Customer();
            c.CustomerID = reader.GetString(0);
            c.CompanyName = reader.GetString(1);
            c.ContactName = reader.GetString(2);
            c.Country = reader.GetString(3);
            list.Add(c);
        }
        cnn.Close();
        return list;
    }

    public static List<Customer> GetAllCustomers()
    {
        SqlConnection cnn = new SqlConnection(
        ConfigurationManager.ConnectionStrings
        ["NorthwindConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cnn;
        cmd.CommandText = "select 
        CustomerID,CompanyName,ContactName,Country from 
        customers";
        cnn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        List<Customer> list = new List<Customer>();
        while (reader.Read())
        {
            Customer c = new Customer();
            c.CustomerID = reader.GetString(0);
            c.CompanyName = reader.GetString(1);
            c.ContactName = reader.GetString(2);
            c.Country = reader.GetString(3);
            list.Add(c);
        }
        cnn.Close();
        return list;
    }

}
}

The Customer class defines four public properties viz. CustomerID, CompanyName, ContactName and Country. Further, it contains two static methods - GetCustomersForCountry() and GetAllCustomers(). Both of these methods are similar except that the former returns customers belonging to a specific country whereas the later returns all the customers. Inside a connection is opened with the Northwind database and a SELECT query is executed with the help of command object. The returned results are obtained as an SqlDataReader. A while loop iterates through the data reader. With each iteration a new Customer object is created, its properties are set to appropriate column values and it is added to a generic list of Customer objects. Finally, the generic list of Customer objects is returned to the caller.

Creating a Data Source

Designing a report requires that you have a Data Source in your project. To add a data source to your project, select Data > Add New Data Source menu option. This will open a dialog as shown below:

Your data source can be a database, a web service or an object. For our example choose Object. On the next step you will be asked to select the object that will be acting as a data source. Choose Customer class (see below).

Complete the wizard by clicking on Finish button. You just added a new data source to your class library. To see the data source select Data > Show Data Sources menu option and you should see Data Sources windows as shown below:

Designing a Report

To add a new report to the class library, right click on it and choose Add > New Item. In the resulting dialog select Report and click Add button. This will add a file named Report1.rdlc. The .rdlc files are report definition files and store the report layout and data mapping.

Once the Report1.rdlc file is open in Visual Studio the toolbox will show a set of reporting related controls (see below).

Out of the available controls TextBox and Table controls are very commonly used. The TextBox control is used to display a static text or an expression (including database column value). The Table control displays your data in row-column fashion. This generally forms the body of your report.

Now design the report as shown below:

At the top i.e. in report header region, there is a TextBox whose Value property is set to Customer Listing. Below this TextBox there is another TextBox and its Value property is set to =Parameters!SubTitle.Value. This indicates that the Value of that TextBox is coming from a parameter named SubTitle. But how to define a parameter? To add a parameter you need to set ReportParameters collection of the Report. You can access this collection from the properties windows as shown below:


This parameter will be set from our .NET code.

To display the date on the report you need to set Value property of the relevant TextBox to =FormatDateTime(ToDay(),DateFormat.ShortDate). A report can take advantage of several inbuilt functions such as Today() and FormatDateTime. Here we used FormatDateTime() function to display current date (ToDay()) in ShortDate format.

Now drag and drop a Table control from Report Items toolbox. By default the Table control has three rows - header, details and footer - and three columns. You can of course add extra rows and columns. Drag and drop CustomerID, CompanyName, ContactName and Country properties from Data Source windows onto the detail row of the Table. Doing so will automatically add TextBoxes with their Value property set to =Fields!CustomerID.Value, =Fields!CompanyName.Value and so on. Column heading are also added automatically for you. You can of course change them as per your requirement.

We need to group the records as per country. To add a group, right click on the margin of detail row and select Insert Group.

This will open a dialog as shown below:

On the General tab of the dialog select Expression as =Fields!Country.Value and on Sorting tab select sorting direction for the same expression as Ascending.

That's it! You just completed the report design

Displaying the Report

Create a new IIS based web site called ReportViewerDemo. Add a reference to ReportViewerLib assembly so that it gets copied into the BIN folder of the web site. Also, add Report1.rdlc file to the web site. Open Default.aspx and drag and drop a ReportViewer control from Data node of the toolbox. Open its smart tag and choose Report1.rdlc from Choose Report dropdown.

Choosing a report file automatically adds an Object Data Source control with its TypeName property set to Customer class. You can verify this by running Object Data Source configuration wizard.

Now drag and drop a DropDownList control at the top and add four items to it namely All, USA, UK and Brazil. Also set its AutoPostBack property to True. Now open configuration wizard for the Object Data Source control and set select method to SelectCustomersForCountry.

Set the parameter source for country parameter of SelectCustomersForCountry method to DropDownList1.

By default all the customers will be displayed in the report. When you select a country from the DropDownList customers from the selected country are displayed. This is achieved by handling SelectedIndexChanged event of the DropDownList.

protected void DropDownList1_SelectedIndexChanged
(object sender, EventArgs e)
{

if (DropDownList1.SelectedValue == "All")
{
    ObjectDataSource1.SelectMethod = "GetAllCustomers";
    ObjectDataSource1.SelectParameters.Clear();
    ReportParameter param = new ReportParameter
    ("SubTitle", "List of all the customers");
    ReportParameter[] p ={ param };
    ReportViewer1.LocalReport.SetParameters(p);

}
else
{
    ObjectDataSource1.SelectMethod = "GetCustomersForCountry";
    ObjectDataSource1.SelectParameters[0].DefaultValue 
    = DropDownList1.SelectedValue;

    ReportParameter param = new ReportParameter
    ("SubTitle", "List of customers for a country");
    ReportParameter[] p ={ param };
    ReportViewer1.LocalReport.SetParameters(p);

}

}

The code checks the SelectedValue property of DropDownList control. It it is "All" then we set SelectMethod property to GetAllCustomers. We also need to clear the SelectParameters collection because GetAllCustomers method doesn't take any parameters. Next, we create an instance of ReportParameter class and pass parameter name and value in its constructor. Recollect that we defined this parameter while designing the report. Further an array of ReportParameter is created. Then we call SetParameters() method and pass this array to it. The code in the else block is similar except that it uses GetCustomersForCountry method.

That's it! Your report is ready. You can now run Default.aspx and see the report in action. Notice that the ReportViewer control has inbuilt export feature that allows you to export the report in Excel and PDF formats. It has many other options which you can explore from the properties window.

Summary

ASP.NET ReportViewer control provides basic charting functionality that you need in many data driven business applications. In this article you developed a report bound with an Object Data Source control. You created a class library and a data source. The data source was later used while designing the report. Finally you used ReporViewer control to display the report.




Bipin Joshi is a software consultant, trainer, author and a yogi having 21+ years of experience in software development. He conducts online courses in ASP.NET MVC / Core, jQuery, AngularJS, and Design Patterns. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced Yoga way of life he also teaches Ajapa Meditation to interested individuals. To know more about him click here.

Get connected : Twitter  Facebook  Google+  LinkedIn

Posted On : 22 Mar 2007



Tags : ASP.NET Data Access Web Forms Server Controls Visual Studio Data Controls