Another bit for dynamic ReportViewer control-handling: Parameters in more depth

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!

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>

16 thoughts on “Another bit for dynamic ReportViewer control-handling: Parameters in more depth”

Thanks for the tips. 😉

I am gettign an error with

Microsoft.Reporting.WebForms.ReportParameter

as type to be defined what do I need to do

Hi Vijaya,

Are you really getting an error or is this just a failure of intellisense?

If the latter, it happens to people all the time, but it should be okay at runtime.

If the former, when you dragged and dropped the control into your form, a Microsoft.Reporting.WebForms reference should have been added to your project. If this did not happen, you need to put one there…

Great procedure to walk through. Just wondering why I cannot get my parameter to be accepted? I have a server report which has a hidden parameter named “cCo” and it is displayed in my label but not set to my report. I just copied and pasted your code and for some reason it doesn’t sent the parameter to the report. Any thoughts?

Little did I know I had to put a “r:” in front of my parameter in order for it to work!
Thanks

>> Little did I know

You don’t really have to put an “r:” in front of the parameter!

As I said in the post:

>>determine which ones are “mine” by looking for a naming convention — here, I’ll use a prefix of r: for “my” example parameters

… in my sample code, I distinguished “my” parameters from other parts of the query string in which I was not interested. Remember that the URL might have other parts to its query string that you don’t want to pass on to the Reporting Server. This was just a convention that I adopted for the example, and then (of course) carried through in my generic code that parsed the parameters to figure out what to send on:

>>
If Left(s, 2) = “r:” Then
‘ this querystring name value pair fits our naming convention.
‘ [… we’ll process this parameter here… ]
Else
‘ Don’t send — the querystring contains
‘ a name value pair that does not belong to us.
End If
<<

But there is nothing “sacred” about it. (It *is* a sacred responsibility to read the comments in sample code, they are trying to tell you something <g>.)

Good luck.

Hi Lisa

I’ve used your example, (converted into c#) to pass multi-valued parameters to my report as below. It works, except only the first parameter value is being used in the report. Any tips?

Thanks
Gareth
//—————–
Array.Resize(ref repParamArr, i);
string[] vals = Request.QueryString[s].Split(‘,’);
repParamArr.SetValue(new ReportParameter(s.Substring(6), vals, false), i – 1);
this.ReportViewer1.ServerReport.SetParameters(repParamArr);

Sorry. My mistake. I had used the same value for the 2nd parameter.

Thanks

Gareth

It’s always something <g>. Thanks for reporting back…

it is working perfectly for me.thanks lisa

Nice post.I am writing some Silverlight modules for an ASP.NET MVC framework and this fits perfectly..

Do you have other parameters that are being accepted, and this is the only one that is not? Or is this your only parameter?

In the former case, what’s the value you’re trying to send?

In the later case, please notice that I used a prefix to distinguish what was being sent that I want to pass on to the report, and other parameters that might be in the query string but I don’t want to send on. In my example, the prefix is r:. So you have to either use that prefix or remove the part of the sample code that is *looking* for that prefix.

Great Great great. Last post on Reportviewer was mind blowing and this one added a lot of information … Keep sharing stuff like this
Kevin Leo

At least now I have an idea on how a server mode report looks like. I’ve actually heard one before, but never got the chance to take a look at the code itself. Thanks for posting this great information.

I tried and it worked fine – thanks Lisa!

16 thoughts on “Another bit for dynamic ReportViewer control-handling: Parameters in more depth”

Leave a Reply Cancel reply