Binding Data to the ReportViewer Control Dynamically in ASP.NET 2.0

If you are new to working with the ReportViewer control, I strongly suggest you go through my first article in this series. That article really gives you a step-by-step approach to working with the ReportViewer control.

The entire source code for this article is available in the form of a downloadable zip file. The solution was developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows Server 2003 Enterprise Edition together with Microsoft SQL Server 2005 Developer Edition.  I didn’t really test the solution with any other or previous editions. 

Binding a SELECT statement to a ReportViewer control in ASP.NET 2.0: design

I discussed several types of reporting strategies to implement in ASP.NET 2.0 applications in my first article. I also gave a step-by-step approach to working with the ReportViewer control in the same article.  So I will not be repeating any of that material here.

In my previous article, I developed reports by directly connecting to database tables/views. That scenario won't be helpful every time. Let us imagine that I would like to develop a report based on a dynamic SELECT statement. This is a little bit tricky when compared to the approach I discussed in my previous article.

First of all, we need to keep in mind that any “table” in a report (at local mode) must adhere to a structure. This structure will generally be a “strongly-typed-dataset.” Without creating a strongly-typed-dataset, it is very difficult to design a report. Creating a strongly-typed-dataset was covered in my previous article.

Even now, to bind a report to a SELECT statement, we need to create a strongly-typed-dataset which contains exactly the same columns we retrieve using the SELECT statement. In other words, we will make filters, joins, sorting etc. dynamic, but not the structure (or set of columns). The structure (or set of columns) must always remain the same when designing or developing reports.

For this scenario, I created a strongly-typed-dataset (called SalesData.xsd) as follows.

It is simply a data table (dragged from the toolbox) with user specific columns.  It has no relation to any database, and no tableadapters are created.

I added a new report (called SampleReport.rdlc) to the project and attached it to the dataset as follows:

Binding a SELECT statement to a ReportViewer control in ASP.NET 2.0: code

Once you have created the datasets and reports as given in the previous section, we need to work with the ReportViewer control to render the data based on the dynamic SELECT.

Drag a ReportViewer control onto the default.aspx  and modify your code-behind file in such a way that it looks something like the following:

ImportsMicrosoft.Reporting.WebForms

ImportsSystem.Data

ImportsSystem.Data.Sqlclient

PartialClass _Default

    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Set the processing mode for the ReportViewer to Local

        ReportViewer1.ProcessingMode = ProcessingMode.Local

        Dim rep As LocalReport = ReportViewer1.LocalReport

        rep.ReportPath = "SampleReport.rdlc"

        Dim ds As DataSet = GetSalesData()

        'Create a report data source for the sales order data

        Dim dsSalesOrder As New ReportDataSource()

        dsSalesOrder.Name = "SalesData_Data"

        dsSalesOrder.Value = ds.Tables("SalesData")

        rep.DataSources.Add(dsSalesOrder)

    End Sub

    Private Function GetSalesData()

        Dim ds As New DataSet

        Dim sqlSalesData As String = _

            "SELECT SalesPersonID, FirstName, " & _

            "       SalesQuota, SalesYTD, SalesLastYear " & _

            "FROM   Sales.vSalesPerson "

        Using connection As New SqlConnection( _

                      "Data Source=(local)sql2k5; " & _

                      "Initial Catalog=AdventureWorks; " & _

                      "Integrated Security=SSPI")

            Dim command As New SqlCommand(sqlSalesData,
connection)

            Dim salesOrderAdapter As New SqlDataAdapter(command)

            salesOrderAdapter.Fill(ds, "SalesData")

            salesOrderAdapter.Dispose()

            command.Dispose()

        End Using

        Return ds

    End Function

EndClass

Binding an XML document to a ReportViewer control in ASP.NET 2.0

XML Document

In previous sections, I demonstrated how to work with a simple SELECT statement and bind it to the ReportViewer control.  Now, I shall demonstrate how to bind an XML document to the ReportViewer control.

Before binding, we need to add an XML document to our project (called SalesDataFile.xml) with a structure that exactly matches that of the dataset created in the previous sections.  It is also recommended that we have the schema reference the same dataset.

The following is the simple XML document I created for the purpose of this demonstration.

<?xml version="1.0" encoding="utf-8"?>

<SalesData xmlns="http://tempuri.org/SalesData.xsd">

      <Data>

            <SalesPersonID>1001</SalesPersonID>

            <FirstName>Jag</FirstName>

            <SalesQuota>20000</SalesQuota>

            <SalesYTD>21000</SalesYTD>

            <SalesLastYear>19000</SalesLastYear>

      </Data>

      <Data>

            <SalesPersonID>1002</SalesPersonID>

            <FirstName>Chat</FirstName>

            <SalesQuota>30000</SalesQuota>

            <SalesYTD>32000</SalesYTD>

            <SalesLastYear>15000</SalesLastYear>

      </Data>

      <Data>

            <SalesPersonID>1003</SalesPersonID>

            <FirstName>Winner</FirstName>

            <SalesQuota>25000</SalesQuota>

            <SalesYTD>19000</SalesYTD>

            <SalesLastYear>25000</SalesLastYear>

      </Data>

      <Data>

            <SalesPersonID>1004</SalesPersonID>

            <FirstName>Dhan</FirstName>

            <SalesQuota>15000</SalesQuota>

            <SalesYTD>30000</SalesYTD>

            <SalesLastYear>21000</SalesLastYear>

      </Data>

</SalesData>

From the above document you can observe that the root element will be nothing but the dataset name.  Each row gets stored in the form of “Data” (or data table within the dataset).  And every row is framed with the columns exactly matching the dataset we created earlier.

Source Code

Add a new web form (called XMLbasedReport) and drag a ReportViewer control onto it.  Modify your code-behind file in such a way that it looks something like the following:

ImportsMicrosoft.Reporting.WebForms

ImportsSystem.Data

PartialClass XMLbasedReport

    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Set the processing mode for the ReportViewer to Local

        ReportViewer1.ProcessingMode = ProcessingMode.Local

        Dim rep As LocalReport = ReportViewer1.LocalReport

        rep.ReportPath = "SampleReport.rdlc"

        Dim ds As DataSet = GetSalesData()

        'Create a report data source for the sales order data

        Dim dsSalesOrder As New ReportDataSource()

        dsSalesOrder.Name = "SalesData_Data"

        dsSalesOrder.Value = ds.Tables("Data")

        rep.DataSources.Add(dsSalesOrder)

    End Sub

    Private Function GetSalesData()

        Dim ds As New DataSet

        'ds.ReadXml(Server.MapPath("SalesDataFile.xml"))

        ds.ReadXml(HttpContext.Current.Request.MapPath("SalesDataFile.xml"))

        Return ds

    End Function

EndClass

The heart of the above code is the following statement:

ds.ReadXml(HttpContext.Current.Request.MapPath
("SalesDataFile.xml"))

The above statement reads the entire XML document and gets populated in the form of a dataset.

You can also work with the XMLDataSource control to deal with this scenario.  I shall cover that in my upcoming articles.

Binding a text file (with comma separated values or CSV) to a ReportViewer control in ASP.NET 2.0

In this section, I would like to deal with ASP.NET 2.0 reporting using text files.  The text files will contain comma separated values (csv) of data. 

Add a text file to your project (called SalesData.csv) and add few rows based on the following figure.

Add a new web form (called TextBasedReport.aspx) to your project, drag a ReportViewer control from the toolbox and modify the code as follows:

ImportsMicrosoft.Reporting.WebForms

ImportsSystem.Data

ImportsSystem.Text

ImportsMicrosoft.VisualBasic.FileIO

PartialClass TextbasedReport

    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

        'Set the processing mode for the ReportViewer to Local

        ReportViewer1.ProcessingMode = ProcessingMode.Local

        Dim rep As LocalReport = ReportViewer1.LocalReport

        rep.ReportPath = "SampleReport.rdlc"

        Dim ds As DataSet = GetSalesData()

        'Create a report data source for the sales order data

        Dim dsSalesOrder As New ReportDataSource()

        dsSalesOrder.Name = "SalesData_Data"

        dsSalesOrder.Value = ds.Tables("Data")

        rep.DataSources.Add(dsSalesOrder)

    End Sub

    Private Function GetSalesData()

        Dim ds As New DataSet("SalesData")

        Dim dt As New DataTable("Data")

        dt.Columns.Add("SalesPersonID")

        dt.Columns.Add("FirstName")

        dt.Columns.Add("SalesQuota")

        dt.Columns.Add("SalesYTD")

        dt.Columns.Add("SalesLastYear")

        Dim fileName As String =
HttpContext.Current.Request.MapPath("SalesData.csv")

        Using parser As New TextFieldParser(fileName)

            parser.Delimiters = New String() {","}

            parser.HasFieldsEnclosedInQuotes = False

            Dim fields As String()

            fields = parser.ReadFields()

            Do While fields IsNot Nothing

                Dim dr As DataRow = dt.NewRow

                dr("SalesPersonID") = fields(0)

                dr("FirstName") = fields(1)

                dr("SalesQuota") = fields(2)

                dr("SalesYTD") = fields(3)

                dr("SalesLastYear") = fields(4)

                dt.Rows.Add(dr)

                fields = parser.ReadFields()

            Loop

        End Using

        ds.Tables.Add(dt)

        Return ds

    End Function

EndClass

Binding a customized ObjectDataSource (or business logic) to a ReportViewer control in ASP.NET 2.0

In general, every project contains several classes to deal with business logic.  Sometimes I may need reports based on the logic/data given through business logic classes. I try to cover this scenario in this section.

Let us now develop some simple business logic to retrieve information from a text file and provide itself as a data source (or report data source) to the ReportViewer control.  The following is the class (SalesInfo.vb), which imitates the columns of the dataset created earlier:

ImportsMicrosoft.VisualBasic

PublicClass SalesInfo

    Dim _SalesPersonID As String

    Dim _FirstName As String

    Dim _SalesQuota As String

    Dim _SalesYTD As String

    Dim _SalesLastYear As String

    Public Property SalesPersonID() As String

        Get

            Return _SalesPersonID

        End Get

        Set(ByVal value As String)

            _SalesPersonID = value

        End Set

    End Property

    Public Property FirstName() As String

        Get

            Return _FirstName

        End Get

        Set(ByVal value As String)

            _FirstName = value

        End Set

    End Property

    Public Property SalesQuota() As String

        Get

            Return _SalesQuota

        End Get

        Set(ByVal value As String)

            _SalesQuota = value

        End Set

    End Property

    Public Property SalesYTD() As String

        Get

            Return _SalesYTD

        End Get

        Set(ByVal value As String)

            _SalesYTD = value

        End Set

    End Property

    Public Property SalesLastYear() As String

        Get

            Return _SalesLastYear

        End Get

        Set(ByVal value As String)

            _SalesLastYear = value

        End Set

    End Property

EndClass

The above class simply contains all the properties which get logically mapped to the dataset columns.  Now we need to create another class, which can expose several objects based on the above class. The next section will deal with this issue.

Binding a customized ObjectDataSource (or business logic) to a ReportViewer control in ASP.NET 2.0: code

This is an extension from the previous section. Add one more class (Sales.vb), which is mainly used to expose the objects based on the above class. The code should look like the following:

ImportsMicrosoft.VisualBasic

ImportsSystem.Data

ImportsSystem.Collections.Generic

PublicClass Sales

    Public Function ListSales() As ArrayList

        Dim Sales As New ArrayList

        Dim fileName As String =
HttpContext.Current.Request.MapPath("SalesData.csv")

        Using parser As New FileIO.TextFieldParser(fileName)

            parser.Delimiters = New String() {","}

            parser.HasFieldsEnclosedInQuotes = False

            Dim fields As String()

            fields = parser.ReadFields()

            Do While fields IsNot Nothing

                Dim info As New SalesInfo

                With info

                    .SalesPersonID = fields(0)

                    .FirstName = fields(1)

                    .SalesQuota = fields(2)

                    .SalesYTD = fields(3)

                    .SalesLastYear = fields(4)

                End With

                Sales.Add(info)

                fields = parser.ReadFields()

            Loop

        End Using

        Return Sales

    End Function

EndClass

Now, add a new web form (ObjectBasedReport.aspx), drag a ReportViewer control and finally set “SampleReport.rdlc” at the “Choose Report” option in the Smart Tag.

That automatically adds an “ObjectDataSource” control to your designer. 

Using the “ObjectDataSource,” open the smart tag menu and choose “Configure Data Source.”

You will be provided with a wizard.  Provide “Sales” as your “business object” and click "Next."

In the next screen, for the SELECT tab, choose “ListSales(), returns ArrayList” as the method.

For all the rest of the tabs, you can select “none.”  Finally click “Finish.” When you execute, your reports get bound to the customized business logic object source!

Any feedback, problems, suggestions, bugs, errors, improvements etc., are highly appreciated at http://jagchat.spaces.live.com.