SSRS Reports: Show Data from Non-standard Provider

Introduction

Reporting Services retrieves report data from various predefined data sources. However, when you need to use non-standard data source, you have a few options:

1. Data processing extensions and data providers for non-standard data source could be developed, installed and registered on the report servers.
2. Use XML data source in junction with WEB Services.

The first way sometimes could be problematical: big companies usually do not allow doing this.

The second way is relatively easy to implement, and developer will not change mutually used environment.

The schema is very simple. Web Service should accept parameters selected on report, produce the data table, and return back data in XML format. Web Service could be very generic. Template of the code is shown below. Main method has 2 parameters:

1. Report name – This is provider-specific parameter. In SQL Server world, it could be the name of stored procedure.
2. reportParameters – name-value collection of parameters selected on report.

WebMethod Code

Code should produce dataset which will be converted to XML and sent back to report.

Code below can give an idea of possible implementation:

[WebMethod]
public XmlDocument RunReportWizard(string report, string reportParameters)
{
    StringBuilder sb = new StringBuilder();
    string[] parms = reportParameters.Split('|');
    XmlDocument xmlDoc = new XmlDocument();

    Dictionary<string, string> parameters = new Dictionary<string, string>();

    using (ReportWizard rwz = new ReportWizard())
    {
       // prepare parameters
       Dictionary<string, string> parameters = new Dictionary<string, string>();

       for (int i = 0; i < parms.Length; i++)
       {
           string[] namevalue = parms[i].Split('=');
           parameters.Add(namevalue[0], namevalue[1]);
       }
       // Method FillDataSet returns data table from non-standard data set
       DataSet ds = rwz.FillDataSet(Report, parameters);

       // code below is building XML
       sb.Append("<records>");
       foreach (DataRow dr in ds.Tables[0].Rows)
       {
           sb.Append("<record>");
           foreach (DataColumn dc in ds.Tables[0].Columns)
           {
              sb.Append("<" + dc.Caption + ">");
              sb.Append(dr[dc].ToString());
              sb.Append("</" + FixName(dc.Caption) + ">");
           }

           sb.Append("</record>");
       }

       sb.Append("</records>");

       xmlDoc.LoadXml(sb.ToString());

       return xmlDoc;
 }

As soon as web service is published to web server, it is a time to prepare a report.

SSRS Report

Data source could be created as shown below:

Data set properties screen looks like:

Expression in the query window is:

=  Code.BuildXMLQuery("My Report Name","Fund=" & Parameters!Legal_Entity.Value & 
"|Investor=" & Parameters!Investor.Value & "|GL_Begin_Date=" & 
Parameters!GL_Begin_Date.Value & "|GL_End_Date=" & Parameters!GL_End_Date.Value)

The function used by this expression is created on report level - report properties window, on code tab:

Public Shared Function BuildXMLQuery(Report As String, Parameters As String) As String
Dim Query As String

   Query = "<Query><Method Namespace=""http://tempuri.org/"" _
   Name=""RunReportWizard"">"
   Query = Query & "<Parameters>"
   Query = Query & "<Parameter Name=""report"">_
   <DefaultValue>Report=" & Report & "</DefaultValue></Parameter>"
   Query = Query & "<Parameter Name=""prm"">_
   <DefaultValue>" & Parameters & "</DefaultValue></Parameter>"
   Query = Query & "</Parameters>"
   Query = Query & "</Method><SoapAction>_
   http://tempuri.org/RunReportWizard</SoapAction></Query>"

   BuildXMLQuery = Query
End Function

As soon as the function is added, new data source could be created, and fields could be refreshed.

Developer can add a table and drop fields to the report layout, format fields, add grouping, sorting, add totals calculation and publish report to the report server.

Web service shown above treated every field as a character string. It will be good to make your XML strongly-typed, but this is another story.