TechSpoken

"Any ideas?" is the most frequently-asked question in technical forums. My answer is: yes.

Over-complicating drilldown and report parameters: Linked Reports

I have a situation in which I need to use linked reports in a ReportViewer web control. 

One of the parameters -- which, if you viewed it in the Report Manager, would be visible -- should not be seen by most viewers, because it sets security information for the report according to complex criteria.  The value of this hidden parameter then cascades into the selectable values they will see in the additional, user-settable, parameters.

Yes, I'm sorry, this is yet another post showing how much better life is when you do not expose users to the Report Manager, or vice-versa.

Setting the ReportViewer.ServerReport.ReportPath value to a linked report works fine, as you'd expect.  I use a little code to set the value of the hidden parameter in the Load for the page, like so:

  param(0) = New ReportParameter("X", "123456", 0)
  ' The first argument (X) is the parameter name.
  ' The second argument ("123456") is the value of the parameter, 
  ' and actually takes a bunch of work to figure out,
  ' not included because your rules would be completely different.
  ' The third arg hides the parameter in the parameter panel
  ' of the report viewer control.  It is mostly for emphasis here.

I won't belabor this, because I've shown this type of technique a number of times before.  You might think I didn't need the linked report since I could dynamically hide the security parameter, as shown above.

But I wanted the initial report to allow for drilldowns into more detailed reports, preserving not only the value of the hidden security parameter but also the choices the user had made in the visible parameters. I wanted to ensure that, even when no code was involved to set the security parameter (definitely in a postback, possibly in a report link action), the security parameter remained hidden.

Here's where I got flustered

Normally, you'd implement this drilldown using the action type "Go to report" and then set the parameters in the drilldown using the values from the current report.  But I found that I couldn't get the linked reports to show up when I Browsed the server -- because they're not actually RDLs -- and of course, without the Browse, the dialog can't provide those nice lists of settable parameters for "Go to report".  

To illustrate that happy path, here's what you would normally expect to do and see:

So, like an idiot

I couldn't get the linked report to show up in the Browse, so I just figured I couldn't use "Go to report" for linked reports.  

Instead, I wrote a bunch of code that re-accessed the current web application page, and passed all the user-settable parameter information, using the "Go to URL" action. My thinking and method was as follows:

  1. create a hidden report parameter with a templated URL for the page, something like this:
    "http://MyServer/Default.aspx?ReportPath={0}"
  2. create an expression for the URL that leverages this template and adds the user-settable parameters into it, like this:
    String.Format(Parameters!LinkURL.Value, "LinkedReport") & "&y=" & 
       Parameters!y.Value.Join(",") & "&z=" & Parameters!z.Value.ToString()
    ... where y and z are the user-settable parameters and y is a multi-valued parameter.
  3. change the web page Load to understand the ReportPath parameter referenced in the report URL, and to look for and apply any other parameters included in the QueryString.  Here's a sketch from my original test code:
        Dim rp As String = Request.QueryString("ReportPath")
        If Not Me.IsPostBack() Then  
            ' you will need to be careful about postbacks if you are going back to the same report for any reaspon.
            With Me.ReportViewer1.ServerReport
                If Len(rp) > 0 Then
                    .ReportPath = rp
                Else
                    ' you can choose whether to set a default here or not, 
                    ' set it in the configuration of the report viewer control, etc... 
                End If
                Dim numparams As Integer = 0
                For Each p In Request.QueryString
                    If p <> "ReportPath" AndAlso p <> "X" Then 
                      ' X is the one we always want to pass to send security info;
                      ' if the user took a look at the query string and decided to send it,
                      ' we would want to ignore that information, obvs.
                        numparams += 1
                    End If
                Next
                ' handle the  non-user-settable security information:
                Dim param(numparams) As ReportParameter
                param(0) = New ReportParameter("X", "123456", 0)
                ' third arg hides the parameter, just for emphasis, although it is already hidden in the linked report
                If numparams > 0 Then
                    Dim ps As String()
                    Dim i As Integer = 1
                    For Each p In Request.QueryString.Keys
                        If p <> "ReportPath" AndAlso p <> "X" Then
                            ' in the production version, I have built up
                            ' a collection of settable params in the first block,
                            ' rather than accessing the QueryString collection
                            ' again here and having to evaluate these 
                            ' conditions a second time
                            ps = Request.QueryString(p).Split(",")
                            ' the Split will bring y back into an array form suitable for a multi-value param
                            param(i) = New ReportParameter(p, ps, 1)
                            ' third arg just emphasizes that this is a user-settable parameter, 
                            ' should they desire to re-set it and keep exploring after the drilldown.
                            i += 1
                        End If
                    Next
                End If
                .SetParameters(param)
            End With
        End If

You're probably way ahead of me

Why didn't I just try it?  

You know (and I certainly do) that you can use an expression in the "Go to report"  Action, although, when you do, the Report Builder or Designer can't show you a handy list of parameters by interrogating the RDL on the server.

Using linked reports in the "Go to report" action works just fine.  Go on, just type it in.  (Or, if you need to, use an expression to reference it.)  Then type in the parameter passing the way you would for an RDL specified as an expression. 

The linked report takes care of hiding the security parameter automatically, of course, multi-valued parameters are also automatically handled, of course, and the world is a good place.

It even works fine if there are spaces in the report path, with no need to encode or translate the spaces.

In fact

You can even get the parameter drop downs to work.  Just Browse to the original RDL first, and then manually correct the "Specify a report" textbox to be the path and name of the linked copy.  The parameter dropdowns will be available when you press the "Add" button, because apparently they are loaded for the Browsed report and don't get unloaded when you type in the replacement.

Maybe that's a bug, but if it is, don't tell anybody.

Why bother

I included that longish code block mostly so you can see that, even in this limited sketch, it's an annoying amount of code. (In the actual, production version it's quite a bit longer, and a little more performant, but not much.) But I guess you might have a real need to set a bunch of reportviewer-hosted parameters dynamically, some time, from a RESTful web call or something.  Right now I can't think of a scenario that requires it, but drop me a line if you do.

Add comment

Loading