I ran in to a need to use SQL Server 2005 Reporting Services' Report Viewer control in a C# 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.
- In Visual Studio, create a new Visual C# Windows Application.
- 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.
- When you created the Windows Application, a Form1.cs 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.
- 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.
- 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.
- Back in the code for Form1.cs, 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
void LoadReport()
{
//reset the report viewer properties
reportViewer1.Reset();
//set the report viewer to process a local report
this.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)"
this.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
this.reportViewer1.RefreshReport();
}
- 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:
using
System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Xml;
using System.Xml.Serialization;
namespace
LocalReportSample
{
class ObjectArray
{
private Object[] _objectArray;
public ObjectArray(Object[] objectArray)
{
this._objectArray = objectArray;
}
public DataSet ToDataSet()
{
DataSet ds = new DataSet();
XmlSerializer xmlSerializer = new XmlSerializer(_objectArray.GetType());
StringWriter writer = new StringWriter();
xmlSerializer.Serialize(writer, _objectArray);
StringReader reader = new StringReader(writer.ToString());
ds.ReadXml(reader);
return ds;
}
}
}
- 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 ReportDataSource GetMyData()
{
//establish an instance of the web service
LocalReportSample.mySampleWebService.ReportService rs;
rs = new ReportService();
//create a new array for the data that is returned
ReportSummaryMsg[] dataList;
//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
ObjectArray objectArray = new ObjectArray(dataList);
//create a new DataSet
DataSet ds = 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]);
}
Compile this and you're good to go! You will see your report appear in the ReportViewer control.