Sunday, October 9, 2011

Using WCF Web Service as SSRS Report Data Source

From SQL Server 2005, Reporting Services supports XML data source type, which allows report consuming Web service data source. Here are some examples:

There is no a detailed documentation about this topic. All examples I found on the Internet also not cover complex scenarios, ex. passing multiple parameters. As supplement, I'd like to share my findings and best practices in an example.

Example

The data contract:

[DataContract]
public class MyDataContract

    [DataMember]
     public int Id { get; set; }
      [DataMember]
     public string Name { get; set; }
     [DataMember]
     public DateTime Birthday { get; set; }
}

The message contracts:

[MessageContract]
public class MyReportMessageContractRequest
{
     [MessageBodyMember(Order = 0)]
     public DateTime StartDate { get; set; }
     [MessageBodyMember(Order = 1)]
     public DateTime FinishDate { get; set; } }
[MessageContract]
public class MyReportMessageContractResponse
{
     [MessageBodyMember]
     public List<MyDataContract> DataItems { get; set; } }

The service contract:

 public class IMyReportService
{
    [OperationContract]
    MyReportMessageContracttResponse GetMyReportDataMethod(MyReportMessageContractRequest request);
}

The Query of the report Dataset:

<Query>
  <Method Name="MyReportMessageContractRequest" Namespace="http://tempuri.org/">
    <Parameters>
      <Parameter Name="StartDate">Parameters!StartDate.Value</Parameter>
      <Parameter Name="FinishDate">Parameters!FinishDate.Value</Parameter>
    </Parameters>
  </Method>
  <SoapAction>http://tempuri.org/IMyReportService/GetMyReportDataMethod</SoapAction>
  <ElementPath IgnoreNamespaces="true">     MyReportMessageContractRespose{}/DataItems{}/MyDataContract {Id (integer), Name, Birthday (Date)}
  </ElementPath>
</Query>

The Best Practices

  1. The WCF service must be exposed via BasicHttpBinding.
  2. auth.
  3. Uses Message Contracts and specify the Order of MessaeBodyMember.The order of the body elements is alphabetical by default, if the order is different than specified in the query, report will not pick it up.
  4. Uses type name of the service method parameter (MyReportMessageContractRequest) as the method name in the query, instead of the actual method name (GetMyReportDataMethod).
  5. The namespace should be "http://tempuri.org/" (ends with "/") rather than "http://tempuri.org".
  6. A parameter referred as Parameters!(parameter name).Value is defined in the Dataset (not the report parameters).
  7. Uses IgnoreNamespaces="true" to simplify the namespace processing.
  8. Specify the field name and data type. These data types are supported: String, Integer, Boolean, Float, Decimal, Date, XML. If not specified, report will cast it to string.
  9. When to Use Web Service Data Source? Here are two typical scenarios when Web services are used:
    • Data is not in the database (ex. it requires to satisfy certain report output requirements before saving the data, in this case, Web service can cache the data in the memory, and report will pick it up without touching the database).
    • Report data is a result of complicated calculations that are difficult to be implemented and debugged using SQL scripts.
  10. Trouble: when using a shared report server, it may cause trouble in debug mode – you need to consider which machine report server should call, and configure it properly.
  11. When design Web services, consider how SSRS process the message: it maps the XML returned by the Web service to Datasets, and it will make one Web service call per Dataset.

4 comments:

  1. This post just helped me a lot. Many thanks! MessageContracts was the key to my issues :)

    ReplyDelete
  2. Awesome post! Solved my issues as well!

    ReplyDelete
  3. Great, thank man. It saved my time.

    ReplyDelete
  4. Congratulation for this post. It's simple and quick and helped me a lot. Awesome!

    ReplyDelete