SSRS Reports: show data from non-standard provider

0
47

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 server.
  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 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())
 {
 
 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]);
 }
 
 DataSet ds = rwz.FillDataSet(Report, parameters);

 
 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;
 }

SSRS Report

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

Data source could be created as shown below:

Data source 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 could be found on 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 fields to a report layout, format fields, add grouping, sorting, totals and publish report to the report server.

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

LEAVE A REPLY