Reporting Services and WCF

Ingredients: Visual Studio 2008, SSRS 2008, AdventureWorks2008

While stand alone *.rdlc reports allow one to use data objects as a data source, it would be convenient to be able to do the same thing using published reports.  The XML data source indirectly makes this possible by accepting a path to an *.asmx web service as a valid “connection string” value. 

There seems to be some confusion over whether this can be done with WCF Services as well.  The short answer is yes, as long as the WCF service endpoint appears as a typical web service.  In other words, it must use basicHttpBinding as its binding type.  On the client side, consequently, the reporting services data source must use integrated security (Windows Authentication) for its credential type. 

Given these strictures, if you already have web services exposed for your data, it should take little to no effort to consuming those WCF services from a report.  I will walk through some simple examples on how to do this.  Ultimately the complications are derived not from structuring the WCF service methods themselves, but rather in figuring out how to use the Query syntax that comes with reporting services – which basically serves as a declarative style for generating SOAP service calls.  This problem, it should be pointed out, is not peculiar to WCF services, but was already a road block in figuring out how to make reporting services talk to *.asmx web services.

In working through the details of making reporting services consume a WCF Service, I was greatly aided by two sources.  The first is Jesse Ezell’s blog which beautifully summarizes the steps required, although he makes the task seem rather harder than it actually is and almost convinced me to abandon the effort early on.  The second is an MSDN article written by Jonathan Heide that deals with the query syntax for using reporting services with XML data sources.  I highly recommend both.

The following walkthrough will introduce five services and five reports, each progressively more complicated.  All examples are included in the download linked at the top.

1. A Simple Service

The simplest meaningful service for reporting returns data without taking any parameters.  For this example, we will not implement our own message contract, although the next example will demonstrate some advantages to doing so.

The service method in this example returns an enumeration of Employee types.  Below is the Employee Data Contract and the Interface definition for the service method:

namespace MyReportingService
{
    [DataContract]
    public class Employee
    {
        [DataMember]
        public string FirstName { get; set; }
        [DataMember]
        public string MiddleName { get; set; }
        [DataMember]
        public string LastName { get; set; }
        [DataMember]
        public string JobTitle { get; set; }
        [DataMember]
        public string Email { get; set; }
        [DataMember]
        public int EmailPromotion { get; set; }
    }
}

namespace MyReportingService
{
    [ServiceContract]
    public interface IEmployeesService
    {
        [OperationContract]
        IEnumerable<Employee> GetAllEmployeesRPCStyle();
    }
}

In the actual implementation of the service method, I happen to be retrieving records from the AdventureWorks2008 database.  For our purposes, however, we now have everything we need with just the interface and the data contract to hook reporting services up to our WCF service.  Make sure the binding is set to basicHttpBinding as Visual Studio likes to default to wsHttpBinding when one creates a new service.

Make sure the service is running before continuing to the next step.  If the Reports project and the WCF project are in the same solution, the easiest way to do this is to right click on the service project and select Debug|Start new instance.

The first step in setting up a report that consumes a WCF service is to create an XML Data Source in Visual Studio.  When we do so, we will be prompted for the following information.  Be sure to set XML as the Type and enter the URI of the service as the Connection String:

We also will need to set the Credentials used for your data source.  As far as I know, web service calls through reporting services always requires that Windows Authentication be used, though perhaps a future release of SSRS will allow us to create a data source that talks to a secured service:

Having done that, we can now use the Report Wizard to create a new report.  On the first screen, set the data source we just created as the Shared Data Source for the report.

The next screen, the query screen, is where we’ll be spending most of our time from now on.  Select the Query Builder… at the top of the query screen.  Using the query builder will allow us to test our XML query as we go along.  In the Query Builder window, choose Text as the Command type if it isn’t already selected.

The basic syntax for making a service call will include a Query tag with a nested Method, SoapAction, and ElementPath.

<Query>
<Method Name="GetAllEmployeesRPCStyle" Namespace="http://tempuri.org/"/>
<SoapAction>http://tempuri.org/IEmployeesService/GetAllEmployeesRPCStyle</SoapAction>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

There are some things worth noticing here. 

• We are using the default namespace for our service.  If one overrides the default namespace, the custom namespace will need to go into the Method and the SoapAction.
• The Method Name, for a service method that doesn’t use its own message contract, is just the name of the service method.
• The SoapAction specifies the full path to the service, which includes the service namespace, the service name (we are using an interface for our *.svc service, so we want the name of the interface rather than the implementing class), and the service method.
• ElementPath, which is a specialized version of XPath, parses the data returned from the call.  IgnoreNamespaces should be set to true to enable us to be ignorant of some of the specifics of the SOAP message that has been returned by the service.
• On this first swipe we will use the “*” as a wildcard syntax to return any repeating data that is found in the result.

If we click on the red bang (or hit F5) the query will run and we can immediately see if the query is written correctly.  The query above will return the following data in the Query Builder:

This is basically the data we want returned but it isn’t shaped quite right.  The “xmlns”, “a” and “i” columns are present because the enumeration we want is actually wrapped up in several other elements, each with attributes that show up in the flattened data.  To get rid of these, we need to specify more precisely the data we want in the ElementPath.

The enumeration of Employee types is wrapped in a Result element, which in return is wrapped in a Response element.  By convention, the names of these elements are based on the name of the service method like this: [ServiceMethodName]Response/[ServiceMethodName]Result.

Instead of simply wildcarding it, we can rewrite the ElementPath to fully specify the data we want:

<Query>
<Method Name="GetAllEmployeesRPCStyle" Namespace="http://tempuri.org/"/>
<SoapAction>http://tempuri.org/IEmployeesService/GetAllEmployeesRPCStyle</SoapAction>
<ElementPath IgnoreNamespaces="true">
GetAllEmployeesRPCStyleResponse/GetAllEmployeesRPCStyleResult/Employee
</ElementPath>
</Query>

This query actually still returns the same data in the image above, but it puts in a good position to start shaping the data the way we want.  Squiggly brackets can be appended to any element in our ElementPath in order to specify the attributes we want to be displayed.   In this case, however, we will use empty squiggly brackets to indicate that we do not want any attributes to be returned for our wrapper elements.  The following ElementPath:

<ElementPath IgnoreNamespaces="true">
GetAllEmployeesRPCStyleResponse{}/GetAllEmployeesRPCStyleResult{}/Employee
</ElementPath>

will return us the following shaped result:

This gets us closer to the shape we want.  You may notice, however, that the columns are in the wrong order.  To be more precise, they are in alphabetical order, which may not be exactly what we want.  There are two ways to fix this.  First, in the Data Contract for the Employee type, we can set a particular order for the Employee properties to be rendered in the returned SOAP message.  This is done by using the Order property off of the DataMember attribute of our properties:

 

[DataMember(Order = 0)]
public string FirstName { get; set; }
[DataMember(Order = 1)]
public string MiddleName { get; set; }
[DataMember(Order = 2)]
public string LastName { get; set; }
[DataMember(Order = 3)]
public string JobTitle { get; set; }
[DataMember(Order = 4)]
public string Email { get; set; }
[DataMember(Order = 5)]
public int EmailPromotion { get; set; }

While this is typically a good idea for versioning Data Contracts, there is a simpler way to order the columns using, once again, the squiggly brackets:

<ElementPath IgnoreNamespaces="true">
GetAllEmployeesRPCStyleResponse{}/GetAllEmployeesRPCStyleResult{}/
Employee{FirstName, MiddleName, LastName, JobTitle,Email, EmailPromotion}
</ElementPath>

There is a final thing we will want to do with out ElementPath.  The underlying type for EmailPromotion is actually an Integer.  If we use the ElementPath as it currently stands, however, EmailPromotion will be used as a String (the default type) when it is consumed by the report.  Sometimes this doesn’t matter.  If we wanted to aggregate on EmailPromotion, however, in order to do sum values or average values, we would have to convert the value of EmailPromotion in an expression to make it possible.

Fortunately, we can also specify the type of each Employee column in the ElementPath.  Return types are indicated by placing the Visual Basic type name – Short, Integer, Long, Decimal, Single, Double, Date, String, Boolean, etc – between ellipses after the column name.

I should point out that since we are simply parsing XML using the ElementPath syntax, and since the SOAP message tells us nothing about the underlying types of the data that is returned, the actual property types of the Employee Data Contract are in effect irrelevant as far as reporting services is concerned.  They could all be typed as objects and it would not matter to the design of the report.  The typing occurs in the ElementPath and nowhere else.

The query for our report should now look like this:

<Query>
<Method Name="GetAllEmployeesRPCStyle" Namespace="http://tempuri.org/"/>
<SoapAction>http://tempuri.org/IEmployeesService/GetAllEmployeesRPCStyle</SoapAction>
<ElementPath IgnoreNamespaces="true">
GetAllEmployeesRPCStyleResponse{}/GetAllEmployeesRPCStyleResult{}/
Employee{FirstName, MiddleName, LastName, JobTitle,Email, EmailPromotion(integer)}
</ElementPath>
</Query>

and our data will look like this:

I will continue this series by talking about WCF Message Contracts and how to pass parameters in the next post.