Equal Opportunity Web Services: SSRS as a client as well as a server

Tom wrote with another question that has really important implications for anybody who uses SSRS.

Do you know if it is possible to do interaction with SSRS?  IOW, can I use a button (or a link) in an SSRS report which, when clicked, will post back to the server (for example, send some data to the server and save)?  I assume it can’t, but I want to listen to your suggestions…. 

Well, it’s heartwarming that he’s willing to listen to suggestions and I hope he’s also willing to roll up his sleeves!  The important parts of my answer are:

• It’s definitely possible to do this.  I do it a lot (and I’ll show you an example here).
• There’s some important work to do to achieve this, but the important work is not really done in SSRS at all!

To understand why, what, and how, you need to take a step back.

Web services methods of access: start with SSRS as an example server.

 In various posts on this blog, I’ve shown you how to access Report Server using SOAP — here’s a sample post — and also using URL access, which most people would call RESTfull interfaces — numerous examples, here’s one calling SSRS with URL access from within SSIS. 

Why do you suppose the Microsoft team supports two APIs?

The answer should be obvious: different clients have different abilities and prefer different methods of access.  If you support multiple methods, you support more clients.

SSRS is a “differently abled” web service client.

I would like to think that Microsoft would have done this on general principles and because the more widely you support clients, the more widely you’ll be used.  It’s just good manners and good sense. 

But the truth is that Microsoft probably had an ulterior motive here.  By internal inclination they would have supported only a SOAP API. 

I’m guessing about this, I have no inside information, but it’s pretty obvious given the extensiveness of their SOAP API that it was the darling of their hearts and URL Access is the red-headed stepchild.

They tacked on URL access to their feature set because somebody probably asked “what if we want to call a report from another report?” 

“Hah”, MS said, “we’ve already thought of that. There’s an ‘action’ option to go to another report.” 

But that same somebody probably perserved.  “Not good enough!  What if we want to call another report that’s on another report server?  You’ve allowed us to put links on text with an action to ‘go to a URL’, and you’re rendering our reports in an HTML interface in Report Manager.  We want to call Report Manager or Report Server” — most people at that stage didn’t even know the difference — “from inside another report!  And it should be as flexible to do this as to call any other web site!”

“Hah”, MS said, “no problem.  Just look at the URL for the other report in your browser, capture it, and put that into the action.”

But that same somebody probably stuck to his or her guns.  “Not good enough!  What about the parameters, and what about the other options such as rendering to another format?”

OH.

And finally MS understood what was needed. 

They added URL Access as a method of using Report Server…  because RESTful interaction was the only type of interaction that SSRS Reports themselves would be able to support as a web service client.

In a country of one-version APIs, the two-API’d server is king.

They really should have known from the start.

It’s really obvious, and if you are a .NET developer rather than a SQL developer you should already know this:  When you build a web service you should make it available from as many types of clients as you can. 

Now, back to Tom’s question. The work is really on the server side, not on SSRS’s as a client.   To have interaction from an SSRS report, he needs to specify a web service somewhere capable of serving an SSRS client, which simply means a web service that supports some form of REST.

If his company has any brains, they’ll make sure the same web service can serve other clients, which might prefer SOAP, too.  It won’t cost any more to build for both at the same time.

Here’s how I handle it.

As I’ve probably mentioned a nauseating number of times, the district runs a huge number of SSRS reports, and a fair number of them have the potential to allow interaction (I’ll show you one, below).  I don’t want to create a new methodology every time somebody suggests this requirement.

To resolve requests as quickly as possible and as flexibly as possible, I built a small ASP.NET application whose sole purpose is to address the database; it only has one method that can be called, but that method has a bunch of rules, addressable as standard argument by the client, to determine what stored procedure, in what database, using what connection string, the web service method will call internally. 

The method also has a standard argument which allows the client to specify whether it wants to receive an XML result or whether an XSLT stored on the server in a known location — which can be specific to your report, if you like — should be applied before the client receives the result.  Your xslt, in turn, has standard arguments it’s passed (such as siteRoot, for use in displaying images).

A final standard argument allows you to specify whether you are looking for a full result set from the stored procedure, which is provided as an XML-serialized dataset, or a single result value.

Now for the non-standard arguments.  These are like the ones that represent your report’s parameters, when you use SSRS’s URL Access, as contrasted with the ones that tell ReportServer that you want a PDF rather than HTML, etc.  In URL Access, you prefix the SSRS-standard parameters; in my system, you prefix the names of the custom parameters instead.  In my system, these custom parameters are the arguments you want passed to the stored procedure you’ve requested. You use the original parameter names required by the sproc, prefixing them with a special, documented prefix.  The method strips off this prefix when calling the named sproc, obviously, and passes your values along when it invokes the sproc. 

The web service method also error-handles appropriately and provides a consistent method of passing back the errors to the client, whether because the sproc you called doesn’t exist, your arguments for the sproc weren’t correct or the sproc threw some other error, or because the sproc in question has not been cleared for this type of access.  If you supply a custom XSLT in this system, naturally, there are some additional conventions your XSLT can support so that it displays error messages in conformance with its general UI requirements.

It sounds complicated, but it’s pretty simple to do, especially when looked at from the client’s side, in this case SSRS.   Here’s how simple the XML version accepted by this service looks, appropriate to being sent by a SOAPy client:

<SISXRequest>
   <p n=”_sp“>sp_xxx</p>
   <p n=”_cn“>yyy</p>
   <p n=”_db“>zzz</p>
   <p n=”_x“>my.xslt</p>
   <p n=”_pLogin“>aaa</p>
   <p n=”_pMyOtherParameter“>bbb</p>
</SISXRequest>

… and an equivalent RESTful version of the request above might look like this: 

http://myserver/myWebRequestURL.aspx?
_sp=sp_xxx&_cn=yyy&_db=zzz&_x=my.xslt&_pLogin=aaa&_pMyOtherParameter=bbb

 … an internal process turns the two request versions into an appropriate collection object, exactly the same in both cases of course, and passes that object into the sproc-handler in the engine.

What’s left for the client to do?  Nothing that an SSRS report can’t handle.  On the client (RDL) side, the work to be “interactive” now looks like what you see below. 

Here’s one I prepared earlier.

In this example, one RDL, showing students’ readiness for college according to some California state university requirements, drills down to another RDL, which provides a list of courses which the student has not yet taken, and which would fulfill some as-yet-unfullfilled requirements for this student: 

… the second RDL is the “interactive” one.  When a counselor helping the student clicks a course number, a Course Request is submitted to the Student Information System for that student, with that course information.

As you might expect, internally (or at design-time), the second RDL’s clickable link works like this:

…where the full Action URL looks like this:

 … the function to which the Action URL refers is very simple; however, notice that it opens a new, small window for the response to be returned by the webservice.  While this is a small thing, it’s critical for the user to stay on the report and simply get feedback about each individual action in this response window:

    Function GetCourseRequestURL( _
            tUser As String, tTemplate As String, _
            tStudent As String, tCourse As String, tYear As String) _
          As String
      Dim url As String = String.Format( _
         tTemplate, tUser, Trim(tStudent), Trim(tCourse), tYear)
      Return“javascript:void window.open(‘” & _
         url & “‘, ‘myWindowSISXResponse’,” & _
         “‘status=1,height=300,width=300,resizable=1’);”
   EndFunction

The “template” argument to the function, supplied by a hidden or internal report parameter value, of course, is a straightforward URL that has appropriate placeholders for the parameter requirements, for the sproc to be called, and for the request inferred from the current report row; something like this:

http://myserver/myWebRequestURL.aspx?
_sp=CreateCourseRequest&_cn=eSP&_x=html&_pChangeUID={0}&_pStudentID={1}&_pCourse={2}&_pYear={3}

 … See?

Simple as that.  No harder than any other dynamic link for an RDL to accomplish, except for a small little thoughtful trick involving the use of a separate window, supplied through javascript, for the response. 

Caveat: In your environment, if this report supports PDF and/or exports, you may want to pass the RenderFormat global values into the Code function, so that you can decide whether or not you want to use the javascript.  For exports, you don’t want or need to specify the separate result window in this way. 

Now Tom has to talk to his peers about getting a similar system working. He’ll have to negotiate the requirements for his environment and for collaboration within his team, but, if he does convince them to put these pieces together, they’re going to think he’s very, very clever indeed.  😉