The ReportViewer control, a local SSRS 2005 Report, and data from a Web Service.

I ran in to a need to use SQL Server 2005 Reporting Services' Report Viewer control in a VB.Net Windows Form project.  That sounds easy enough.  The catch was the report had to run without the support of the SSRS server (local mode) and the report data had to be returned from a web service method that returned its result in an array.  I scoured SQL Books Online and the internet for a quick explanation but surprisingly found nothing that quite met my needs.

Using Reporting Services 2005 allows you to use a Microsoft supplied report viewer control in Visual Studio 2005.  This control is embeddable in .NET 2.0 Windows and web forms.  A great resource for more information on this control can be found at http://www.gotreportviewer.com/.

Reporting Services 2005 introduces the concept of local reports.  These are reports that are processed on the client without a connection to the SSRS report server.  They make use the same report definition language (RDL) server reports use but are embedded in your project.  Local SSRS reports have the file extension rdlc, where the 'c' stands for client.

The steps in this article assume you have Visual Studio 2005 and SQL Server 2005 with Reporting Services 2005 installed on your development environment.

1. In Visual Studio, create a new Visual Basic.Net Windows Application.
2. Add a new Web Reference in your project pointed to your web service.  The web service I had to call returned an array of results.  That fact is important to note because the Report Viewer control is going to expect a data table as its source.
3. When you created the Windows Application, a Form1.vb form was automatically added to the project.  Using the Toolbox in Visual Studio, select the ReportViewer control from the Data tools and drag it on your form.  The control's smart tag will prompt you to either choose an existing report or design a new report.  Choose the option to create a new report.  A new report file will be created and named Report1.rdlc.
4. While in design mode for Report1.rdlc, explore the Data Sources pane.  You will see your web service reference listed.  This contains the data elements that will make up the report.  Design the report as needed making use of those data elements.
5. While still in design mode for the report, from the menu options in Visual Studio, select 'Report' à 'Data Sources'.  You will be presented with a Report Data Sources dialog.  Select the Project Data Source from the dropdown that contains the data elements you are reporting on.  Next, click the 'Add to Report' button.  Note that this data source is given a name like <project name>_<web service name>_<results collection name>.  That name is important to note because you'll need it in a future step.  Click the 'OK' button.
6. Back in the code for Form1.vb, add an import directive for your web service and another for the Microsoft.Reporting.WinForms namespace.  Create a new private method named LoadReport() as shown in the code below.  This method will set the report viewer to local mode.  It will then set the path of the report.  I chose to output my report to the bin directory after build process.  It then assigns the data source to the report by calling a function that returns a data table from your web service; that code is a few steps away.

    

   Private Sub LoadReport()

       'reset the report viewer properties

       reportViewer1.Reset()

       'set the report viewer to process a local report

       Me.reportViewer1.ProcessingMode = ProcessingMode.Local

       'set the local report path (note: the properties of the project post-build event

       ' include this command in order to use this embedded report from the bin directory:

       ' copy "$(ProjectDir)\Report1.rdlc" "$(TargetDir)"

       Me.reportViewer1.LocalReport.ReportPath = "Report1.rdlc"

    

       'add a datasource to the report viewer

       ' note: this must be a data table

       reportViewer1.LocalReport.DataSources.Add(GetMyData())

    

       'this will refresh the report in the viewer with the above settings

       Me.reportViewer1.RefreshReport()

   End Sub 'LoadReport

7. So if the data is returned from the web service as an array, how did I get a data table out of it?  Simple-I created a new class named ObjectArray and used the code provided from Tom Gilki's web site to do just that (http://tom.gilki.org/programming/net/Utils/ObjectArrayDataSet/index.shtml).  Using the XML Serializer, it creates a dataset which you will then use to return a ReportDataSource.  The code for this class is here:

8. Imports System

   Imports System.Collections.Generic

   Imports System.Text

   Imports System.Data

   Imports System.IO

   Imports System.Xml

   Imports System.Xml.Serialization

    

   Namespace LocalReportSample

    

       Class ObjectArray

           Private _objectArray() As [Object]

    

           Public Sub New(ByVal objectArray() As [Object])

               Me._objectArray = objectArray

           End Sub 'New

    

    

           Public Function ToDataSet() As DataSet

               Dim ds As New DataSet()

               Dim xmlSerializer As New XmlSerializer(_objectArray.GetType())

               Dim writer As New StringWriter()

               xmlSerializer.Serialize(writer, _objectArray)

               Dim reader As New StringReader(writer.ToString())

               ds.ReadXml(reader)

               Return ds

           End Function 'ToDataSet

       End Class 'ObjectArray

   End Namespace 'LocalReportSample
   
    

9. Next, create a function with a return type of ReportDataSource.  Create an instance of the web service and an array to hold the web service's results.  Assign the array the return value of your web service method.  Create a DataSet and use the ObjectArray class created in the previous step to transform the array to a DataSet.  Finally, return a new instance of ReportDataSource with the following arguments: the name of your report data source (see step 6 for the name of this) and the table in your DataSet.  The code for this is as follows:

   /// <summary>
   /// Data Set using the web service method that returns the data you need.
   /// </summary>
   /// <returns></returns>
   

   Private Function GetMyData() As ReportDataSource

       'establish an instance of the web service

       Dim rs As LocalReportSample.mySampleWebService.ReportService

       rs = New ReportService()

    

       'create a new array for the data that is returned

       Dim dataList() As ReportSummaryMsg

    

       'assign the array to the results of the web service method

       dataList = rs.MyWebServiceMethod()

    

       'instantiate the ObjectArray which will convert the array returned from the web service to a DataSet

       Dim objectArray As New ObjectArray(dataList)

       'create a new DataSet

       Dim ds As New DataSet()

       'use the ObjectArray to convert the array to a DataSet

       ' (this is needed because the ReportDataSource must be a Table)

       ds = objectArray.ToDataSet()

       'return the ReportDataSource

       ' The string value must be the same name as the report's datasource (go to Report -> DataSources to see this name)

       ' The table is from the the DataSet.

       Return New ReportDataSource("LocalReportSample_mySampleWebService_ReportSummaryMsg", ds.Tables(0))

   End Function 'GetMyData

Compile this and you're good to go!  You will see your report appear in the ReportViewer control.