Consuming a WCF Service from an SSRS (RDL) Server Report

• Download source - 28.59 KB

Introduction

Microsoft SQL Services Reporting Services is a reporting tool that integrates with a variety of data sources. It permits directly querying XML data sources and Web services. This is achieved through the use of the XML data provider that flattens the XML structure into a data set that is easily consumable by the reporting engine. I needed to consume a WCF Service from a report and finding resources to point me in the right direction was not the easiest affair.

Background

Resources on how to achieve this are scarce and (sometimes) rather shallow and at the end of my foraging, I thought it was only fair to write an article that might assist someone out there. I have to confess that I am not yet an authority in the area and it is possible some of the assumptions/assertions I might make may not really be absolute. To illustrate how you can consume a WCF Service from server report, I will take you through a simple exercise.

Using the Code

After unzipping the source code, you should get a VS solution that comprises of three projects: WcfSSRSEx1, WcfHost, and RptEx1. The next thing you may need to do is configure a virtual directory named WcfHost. Let the physical path of that virtual directory point to the root of WcfHost website project that was earlier unzipped. After that, open the solution and rebuild it. Before deploying the report to a report server, ensure that the project has the correct TargetReportFolder and TargetServerURL (Right-click project, then click Properties). Right-click the RptEx1 project, then click Deploy. You can then browse Default.aspx page in WcfHost website project to view the report.

I will take you through the process of consuming a WCF Service from a server report.

We start off by creating a WCF Service Library. You will find a WCF Service Library template under WCF Project Type in VS 2008. The first thing I discovered was that most writers on the subject assumed that the typical developer’s need was to consume a WCF service in a web project.

The WCF Service Library (named WcfSSRSEx1) comprises of:

• DataContract: Book
  C#

  [DataContract]
  public class Book
  {
      [DataMember]
      public String ISBN { get; set; }
  
      [DataMember]
      public String Title { get; set; }
  
      [DataMember]
      public String Author { get; set; }
  
      [DataMember]
      public Decimal Price { get; set; }
  
      [DataMember]
      public Boolean IsAvailable { get; set; }
  }

• ServiceContract: ILibraryService together with its implementation LibraryService:
  C#

  [ServiceContract(Namespace="http://localhost/2009/libraryservice")]
  public interface ILibraryService
  {
  
  }

• OperationContract: FetchBooks that accepts two parameters: a String parameter Author and a Boolean parameter IsAvailable and returns a list of books with corresponding author and availability status.

A few things need to be said here.

The report engine will typically send a soap request to a web service. To make it possible for the report engine to invoke the operations, you can only use a single parameter. That parameter's type has to be decorated with a MessageContract attribute. The single parameter wraps individual parameters that are decorated with the MessageBodyMember attribute. That is the first step towards making the WCF service library operations consumable by the server report.

The next thing we need to do is to expose the WCF Service as a legacy web service through basicHttpBinding. For this exercise, we use IIS as a host for the WCF Service. I believe it should be possible to use a Windows service or even a simple Console project to do the same. We create the host Website (named WcfHost). The thing is, when consuming a web service from the report, you typically create a shared data source of type “XML” and specify the service path as the connection string.

To expose the service, add a new WCF service class to the WcfHost project. You can name it LibraryService for convenience. VS will dutifully add for you three files: ILibraryService.cs and LibraryService.cs in the App_Code folder and LibraryService.svc in the root project folder (i.e. if you were adding the WCF service class to the root project folder). Delete the two files (ILibraryService.cs and LibraryService.cs) that were added to the App_Code folder. The reason we are doing this is because we have our service already implemented in the WCF service library.

At this point, you will need to add a reference (Add Reference from Solution Explorer) to our WCF service library project (WcfSSRSEx1) to the Website project. Open LibraryService.svc file. Edit the only line:

<%@ ServiceHost Language="C#" Debug="true" 
Service="TestService" CodeBehind="~/App_Code/TestService.cs" %>

so that it corresponds to this:

<%@ ServiceHost Service="WcfSSRSEx1.LibraryService" %>

The next step is to configure the WCF service itself in the web.config. It’s possible that when adding the LibraryService WCF service class that the following lines were added:

<system.serviceModel>
    <behaviors>
       <serviceBehaviors>
        <behavior name="LibraryServiceBehavior">
         <serviceMetadata httpGetEnabled="true" />
         <serviceDebug includeExceptionDetailInFaults="false" />
        </behavior>
    </serviceBehaviors>
  </behaviors>
    <services>
       <service behaviorConfiguration="LibraryServiceBehavior" 
       name="LibraryService">
        <endpoint address="" 
        binding="basicHttpBinding" 
        contract="ILibraryService">
         <identity>
          <dns value="localhost" />
         </identity>
        </endpoint>
        <endpoint address="mex" 
         binding="mexHttpBinding" 
        contract="IMetadataExchange" />
       </service>
  </services>
</system.serviceModel>

You will again need to edit it (or add this section) so that it corresponds to this:

<system.serviceModel>
    <behaviors>
       <serviceBehaviors>
        <behavior name="WcfSSRSEx1.LibraryServiceBehavior">
         <serviceMetadata httpGetEnabled="true" />
         <serviceDebug includeExceptionDetailInFaults="false" />
        </behavior>
    </serviceBehaviors>
  </behaviors>
    <services>
       <service behaviorConfiguration="WcfSSRSEx1.LibraryServiceBehavior" 
        name="WcfSSRSEx1.LibraryService">
        <endpoint address="" 
        binding="basicHttpBinding"
        contract="WcfSSRSEx1.ILibraryService">
         <identity>
          <dns value="localhost" />
         </identity>
        </endpoint>
        <endpoint address="mex" 
        binding="mexHttpBinding"
        contract="IMetadataExchange" />
       </service>
  </services>
</system.serviceModel>

Ensure that at least one of the LibraryService endpoint uses httpBasicBinding. Needless to explain, other changes we have done here are obviously because our service is implemented in a referenced library.

At this point, you can browse the service by typing http://localhost/WcfHost/LibraryService.svc on your browser. Optionally, you can right-click LibraryService.svc and click View in Browser. You can even view the wsdl by typing http://localhost/WcfHost/LibraryService.svc?wsdl.

Our service is now ready to be consumed by the server report.

A few things need mentioning here.

Firstly, by installing SQL Express with Advanced Services, you will indeed get the ReportServer and ReportServer databases installed but that version of ReportServer is limited. It does not permit the server report to consume an XML data source.

Secondly, if you are using VS 2008 to design your report, you will not be able to deploy it to a 2005 ReportServer and vice versa. The reasons are well documented by Microsoft on the Internet.

Thirdly, configuring a ReportServer is not the easiest thing. I would advise you to install the ReportServer in Native Mode as you are installing SQL Server. That however does not guarantee you a smooth ride. There are a few things you may have to grapple with which I may not cover in this article but I must mention that the most annoying thing was to have the ReportManager give me its homepage stripped of its main functionality when having User Access Control enabled on my computer despite the fact that I was an administrator on my computer and was running IE – “Run As” – as administrator. The page had all functionality when I disabled UAC, restarted my computer and accessed it again.

Back to the subject, add a new Report Server Project (under Business Intelligence Projects templates – name it RptEx1). The first thing you may need to do is to configure the TargetReportFolder and TargetServerURL for the project. Right-click the project and set them appropriately.

Add a new Shared Data Source, give it an appropriate name (LibraryDS worked for me), specify XML as the type and set http://localhost/WcfHost/LibraryService.svc as the connection string.

You can perform the next step in a number of ways. You can add a Report and a DataSet (from the ReportData window) separately and then drag fields from the DataSet onto the report or you can use the Report Wizard that you get after you right-click the Reports folder and then click Add New Report from the Solution Explorer. Using the wizard, select LibraryDS as the data source on the second wizard step. The third step will allow you to enter the Query. This step requires most of your attention since any small mistake will be a show-stopper.

A query when making a soap request will take a form like:

<Query>
<Method Name="BookRequest" Namespace="http://localhost/2009/libraryservice">
<Parameters>
<Parameter Name="Author"></Parameter>
<Parameter Name="IsAvailable"></Parameter>
</Parameters>
</Method>
<SoapAction>
http://localhost/2009/libraryservice/ILibraryService/FetchBooks
</SoapAction>
</Query>

If you are familiar with our web service wsdl, the namespace and the SoapAction will be self-explanatory. The parameters also map to the parameters wrapped in a MessageContract decorated class in our WCF service.

In the next step, you are able to specify the Report Type (Tabular and Matrix) while the fifth step enables you to design the table. This is one step where the wizard might not be too smart. The wizard may list the available fields as xmlns, a, i, and whatever object is wrapped in the SOAP response. For our case, we have Books – FetchBooks OperationContract returns a wrapped List<Book> object named Books. But that is only half the truth. You can access the actual object public properties in your report. You can safely ignore this step and drag the fields onto the reports later. The next step allows you to select a table style while the last one allows you specify a name for the report.

If you used the wizard, you will end up with a report as well as a data set (DataSet1 or equivalent) on your Report Data window (View->Report Data menu item).

You can delete whatever Tablix might have been added to your report since it will most likely not give you what you want. To expose the properties on the object returned by the query, right-click the dataset and click DataSet Properties. Click the Fields tab on the left and use your knowledge about the returned object to add the appropriate Query Fields. After you have added the appropriate fields, you can proceed to add a new Tablix and drag the fields from the DataSet onto it.

From the Report Data window add two parameters, conveniently, Author and IsAvailable (You can also add some appropriate prompt text). Right-click the dataset, and click DataSet Properties. Click the Parameters tab on the left and add two parameters, Author and IsAvailable – names must match those in your query this time – and set their values to the parameters you added from the Report Data window (@Author and @IsAvailable) respectively.

At this point, you can breathe a sigh of relief since if you click the Preview tab on your Report Designer window and enter values against the prompts (Richard Price for author name, true for availability status – if you are using my example) and then click the View Report button, you should get some nice results in a grid. Am tempted to say, that simple, but it was not simple the first time I did it.

Our goal of consuming a WCF service from a server report is accomplished but other steps would involve consuming the deployed reports from a web page while passing the appropriate parameters. Deploying a server report is a simple matter. Right-click the project and click Deploy and if your TargetServerURL and TargetReportFolder settings are fine, the operation will be successful. Here is how you can reference the report from the code behind of a web page.

ReportViewer1.ProcessingMode = ProcessingMode.Remote;
ReportViewer1.ServerReport.ReportServerUrl = 
new Uri("http://localhost/ReportServer/");
ReportViewer1.ServerReport.ReportPath = "/Reports/RptEx1/BookReport";

ReportParameter[] rptParameters = new ReportParameter[2];
rptParameters[0] = new ReportParameter("Author", "Richard Price");
rptParameters[1] = new ReportParameter("IsAvailable", "true"); 
//Or ("IsAvailable", "1")
ReportViewer1.ServerReport.SetParameters(rptParameters);

Sometimes, the mapping of the appropriate handler for the report in IIS might not have happened and you may get this ugly Webpage Error:

Just go to IIS, click Default Web Site, Handler Mappings (under Server Components), add Managed Handler:

• Request Path: Reserved.ReportViewerWebControl.axd
• Type: Microsoft.Reporting.WebForms.HttpHandler
• Name: Reserved-ReportViewerWebControl-axd

And you should happily be on your way to viewing a fine server report that is consuming a WCF Service.

Points of Interest

One, even after using a MessageContract decorated class to wrap the WCF operations parameters, if you create a client (say using svcutil.exe or VS – Add Service Reference) the generated client will require you to supply the parameters to the operations separately.
Say you add a Service Reference named LibService;

LibService.LibraryServiceClient libService = 
	new LibService.LibraryServiceClient();
Book[] books = libService.FetchBooks("Richard Price", true);

It is as if you have an operation FetchBooks(String Author, Boolean IsAvailable), a signature you don't actually have, from your service. The same applies to the return types. You will not receive a type BookResponse object but rather an object of type List<Book> (Book[], actually). This is a probably a good thing since you don't have to trouble yourself with the MessageContract decorated parameter when you are invoking your WCF operations in your typical cases.

Two, I don't know how easy (or impossible) it is for the report to query the response for the target fields if the operation return object has types and generic lists as publicly accessible properties/fields. XPath, anyone?