Skip to main content

Configure Microsoft SSRS to use a Web Service as a Data Source...


I recently created a SQL Server Reporting Services (SSRS) report which used a Stored Procedure as a datasource, but the report needed some extra customization we're getting by using PaxScript in a similar web page. This isn't easily achievable using SQL stored procedures, so I decided to use a web service instead as the data source for the report. This way I have the advantage of LINQ/C# and PaxScript. Also, we have a need to run different versions of the web service, each for a different client. So the data source's web service URL needs to be dynamic, rather than static.

Here's how it's done...

In the report Data Source properties, the Type is XML and the Connection string value is a Report Parameter:
screenshot - SSRS Datasource

Next, the report's Dataset Text should be in the following format:
screenshot - SSRS Dataset

Note: There is a Parameter for each Web Service method parameter with the correct Name and Type. The ElementPath value is derived partly from the WSDL and partly from the XML that the Web Service method returns:

From WSDL:
"GetPatientVentDataLinqResponse{}/GetPatientVentDataLinqResult{}"


from the Web Service returned XML.
"/PatientVentData{}/Result{ParameterName,ParameterData,UpdateTime}"

Sample XML:

  01:  <?xml version="1.0" encoding="utf-8" ?>

  02:  <PatientVentData>

  03:      <Result>

  04:          <ParameterName>Tidal Vol exp (ml)</ParameterName>

  05:          <ParameterData>342.0</ParameterData>

  06:          <UpdateTime>1/1/2008 07:31:03 [Gallileo - 9]</UpdateTime>

  07:      </Result>

  08:  </PatientVentData>



Here is the Web Service method:


   01: [WebMethod]

   02: public XmlDocument GetPatientVentDataLinq(string patientId, 

   03:     string beginDate, string endDate, string deviceID, string userTimeZone) {

   04:     DataTable result = PatientController.GetVentData(true, 

   05:             SafeCast.Int(patientId), 

   06:             SafeCast.Date(beginDate), 

   07:             SafeCast.Date(endDate), 

   08:             SafeCast.Int(deviceID),

   09:             userTimeZone);

   10:     IEnumerable<DataRow> rows = result.AsEnumerable();

   11:     List<TReads[]> _reads = new List<TReads[]>();

   12:     foreach (DataColumn _col in result.Columns) {

   13:         if (_col.ColumnName.Contains("Column") ||

   14:             _col.ColumnName == "Parameter") {

   15:             var set = (from ro in rows

   16:                        select new TReads (

   17:                            ro.Field<string>(result.Columns[0].ColumnName),

   18:                            (result.Columns.Contains(_col.ColumnName) ?

   19:                                 ro.Field<string>(_col.ColumnName) : null),

   20:                            _col.Caption

   21:                        )).OrderByDescending(o => o.UpdateTime);

   22:             _reads.Add(set.ToArray());  // ToArray() is to offset LINQ's deferred execution.

   23:         }

   24:     }

   25:     if (_reads.Count > 0) {

   26:         var re = from o in _reads

   27:                  select o;

   28:         XElement xDoc = 

   29:             new XElement("PatientVentData",

   30:                 from x in re

   31:                 select (

   32:                     from t in x

   33:                     where t.UpdateTime != "Parameter"

   34:                     select new XElement("Result",

   35:                         new XElement("ParameterName", t.ParameterName),

   36:                         new XElement("ParameterData", t.ParameterData),

   37:                         new XElement("UpdateTime", t.UpdateTime)

   38:                     )

   39:                 )

   40:             );

   41:         XmlDocument xmlDocument = new XmlDocument();

   42:         xmlDocument.LoadXml(xDoc.ToString());

   43:         return xmlDocument;

   44:     }

   45:     return GetEmptySet();

   46: }

   47:  



Here is part the code that calls the Report Service:

(this segment of code establishes the Web Service URL relative to it's own URL and assigns it to the Report Parameters used to call the Report)


  01:  var url = Request.Url.AbsoluteUri;

  02:  var index = url.IndexOf(AppRelativeVirtualPath.Substring(1));

  03:  var webServiceUrl = url.Substring(0, index) + "/Services/BridgeTechVentHistory.asmx";

  04:   

  05:  ParameterValue[] parms = new ParameterValue[] {

  06:      new ParameterValue { Name = "patientID", Value = Request.QueryString[_requestString[0].Name] },

  07:      new ParameterValue { Name = "beginDate", Value = vdb.ToShortDateString() },

  08:      new ParameterValue { Name = "endDate", Value = vde.ToShortDateString() },

  09:      new ParameterValue { Name = "deviceID", Value = Request.QueryString[_requestString[3].Name] },

  10:      new ParameterValue { Name = "timeZone", Value = UserTimeZoneString },

  11:      new ParameterValue { Name = "webServiceUrl", Value = webServiceUrl }

  12:  };


Labels:

Comments

Post a Comment

Popular posts from this blog

Tyler Eating

Here is a picture of Tyler eating Peas. They are all over his face and he loves it! If you look closely, you can see his first 2 teeth (bottom).
Post a Comment
Read more

SQL Generation

Recently I wrote a console application that generates SQL code to populate a database with sample data. I wanted it to be as generic as possible, but it still relies on a Source database to get sample data from. The application is configurable in that it lets you specify which Database is the source database, as well which tables to get the data out of. I did not want this application to depend on any stored procedures; it needs to be self contained, which is why I'm hard-coding the table definition sql query. So I call the console app via a batch file. The batch file calls the exe file, outputting the text to a file: 01: @ECHO OFF 02: bin\debug\SqlGenerator.exe > "C:\run.sql" 03: ECHO done. Here is the main method in the console app: 01: class Program { 02: static void Main( string [] args) { 03: Console.WriteLine(Database.GenerateSQL()); 04: } 05: } Here is the relevant excerpt from the App.config file: 01: < a
Post a Comment
Read more

General practices (tomAYto tomAHto)...

I hate repeating code and sometimes generics can make things a bit easier. One of the things that needs to be done a lot with Drop-down lists is adding a "Please Select" as the first option, but I don't particularly like doing that in the data layer (stored procedure, etc.). I like to add that to the list, but why repeat the same code for every Drop-down list if there are several on the page? Here is one way I like to take care of that: Here are the supporting Classes: 01: #region Supporting Classes 02: public interface IReportObject { 03: int ID { get; set; } 04: string Name { get; set; } 05: } 06: public class CSP : IReportObject { 07: public int ID { get; set; } 08: public string Name { get; set; } 09: } 10: public class Base : IReportObject { 11: public int ID { get; set; } 12: public string Name { get; set; } 13: } 14: public class Property : IReportObject { 15: public int
Post a Comment
Read more