FWIW I dearly love URL Access in SQL Server Reporting Services. Well, to be exact, I love REST interfaces in general. (I don't know why MS couldn't use the perfectly good name already available for this feature.)
I didn't discuss this in much detail in my last post on dynamic use of ReportViewer controls, although it contains a quick parameterization of the control as part of the general dynamic process that the RDLDocumenter TestHarness goes through. But it seems that this is something people need, so here goes...
This time, just for fun, we'll be using server mode reports (RDLs) instead of local mode (RDLCs), and the webform ReportViewer control instead of the winform version of XMLRSDocs' TestHarness form uses. Same process, though.
Barry Williams would like his users to use a REST-style API to call up reports in a webform. An optimal solution will (a) take little to no programming -- the reports are server-based, not local-mode -- and (b) not take any adjustment when the parameters available for his report(s) change. Let's put something together to handle that.
Barry is a data guy, so I will expect him to drive this solution from a database with more smarts than I'm showing in this example. Here, I mostly want to show how you pull lthe parameters out of the URL request and apply them to the report and, rather than show a full data-driven solution, I'll use the following simple strategy to make the solution generic:
- iterate through all the parameters sent in the URL
- determine which ones are "mine" by looking for a naming convention -- here, I'll use a prefix of r: for "my" example parameters
- apply those values to my report
As an example, in the following URL, "test" will be ignored and the other two parameters will be applied:
http://myServer/myApp/ReportViewerParametersDemo.aspx?
r:Status=Cancelled&r:ExcludeInternal=false&test=Something
Ready?
If you want to play along at home, or use this code, all you need to do in Visual Studio to reproduce my ASPX page is this:
- Create a new ASPX page ("New web form")
- In the Code view, blow away the code-behind information (CodeFile="Default2.aspx.vb" Inherits="Default2") in the page directive, so we're left with
<%@ Page Language="VB" AutoEventWireup="false" %>
... I honestly see no reason to use a code-behind page rather than a real class built into a DLL if you have serious code to put into an ASPX page. IAC, we don't have serious code here.
-
In the Design view, drag and drop a ReportViewer control and a Label control (for some debugging and eventual validation feedback) onto the page surface.
-
"Point" the ReportViewer control at your server report, using the ReportViewer.ServerReport ReportPath and ReportServerURL properties.
Now go back to the Code view and add the server-side block you see below into the page, to handle the page load event.
There are only a couple of tricky things about this, so I'll just note them quickly for you here:
- For those of you who are not used to doing Asp.NET work, we're using Request.QueryString. For more extensive requirements beyond simple URLAccess GET syntax, you would use Request.Params.
- You may be surprised, or confused, by the use of the Split function on the value of being passed through the querystring. A list of values that all have the same parameter name -- for example, the choices in a multi-select HTML listbox -- are passed by browser-clients as a comma-delimited string to the server. Essentially, multi-value report parameters model this behavior; they hold collections of values as an array. If there is no comma, there's only one value, but if there are more, you need to split them out into the array that the report "expects" to see for this parameter.
Here is a parameterized URL example showing three values for the "Status" multi-valued parameter:
http://myServer/myApp/ReportViewerParametersDemo.aspx?
r:Status=Cancelled,Pending,Tentative&r:ExcludeInternal=false
.. the Split function will take care of passing Cancelled, Pending, and Tentative as the separate values to this parameter.
Enjoy!
<script runat="server">
Protected Sub Page_Load( _
ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim sb As New System.Text.StringBuilder()
Dim p(0) As Microsoft.Reporting.WebForms.ReportParameter
Dim i As Integer = 0
' If we were truly datadriving this we would validate values by type
' as well as the actual param names
For Each s As String In Request.QueryString.Keys
' feedback to put in Label:
sb.Append(s & "=" & Request.QueryString.Item(s) & " ")
If Left(s, 2) = "r:" Then
' this querystring name value pair fits our naming convention.
i += 1
Array.Resize(p, i)
p.SetValue( New
Microsoft.Reporting.WebForms.ReportParameter(s.Substring(2), _
Request.QueryString.Item(s).Split(",")), i - 1)
Else
' Don't send -- the querystring contains
' a name value pair that does not belong to us.
End If
Next
If sb.Length = 0 Then
Me.Label1.Text = "You didn't send a query string."
Else
Me.Label1.Text = "You sent: " & sb.ToString()
If i > 0 Then
' this should have a try-catch around it
Me.ReportViewer1.ServerReport.SetParameters(p)
' Your catch could put some feedback into the page,
' just as I've put the user's choices into
' the label which is the only other control on this page...
End If
End If
sb = Nothing
End Sub
</script>
