Walkthrough on Blank/Null Multi-Value Parameters: The Quick and the Dead

Today I’m going to show you a simple trick that might help you out with multi-value parameters. Then I’ll show you a bug with parameter deployment that you’ll easily see as we walk through this trick.

The bug isn’t specific to the trick, and in fact in the walkthrough scenario it doesn’t do any real harm. However, in more complex real-life report deployments, you may find it bites you very hard in the rear end — as I happened to find last week.

You may already know the trick (it’s VERY simple), but read on anyway in case this deployment bug surfaces in your professional life.

I’m going to show you all this in 2008 R2. In production, where the bug showed up, we’re not using R2 yet. When I easily duplicated the problem in R2, I thought I’d better show you. So let’s get started.

A quick sidebar

Speaking of versionitis, I was going to upload and discuss a bug-repro having to do with different optimizations of code attached to conditionally Hidden (or Toggled) objects done by different renderers, having found that neither the XML renderer nor the Excel renderer faithfully reproduced the actual contents (never mind the formatting) of the interactive version.

I haven’t done so because I found that, in R2, the interactive version also <cough> optimized the code away.  It didn’t execute until you toggled the detail code visible — at which time all the summary lines for details later than this one suddenly had the right contents (the summary lines above the toggled one remained incorrect). 

This was mildly amusing to watch… in a bitter sort of way.  But, as a result, I was much less motivated to share the renderer-specific behavior with you, and much more motivated to come up with a completely different way to handle the original requirement.  It’s still in an SSRS format, of course, but the code is stripped out and placed in TSQL code that creates a DW table, refreshed nightly, with the results of all the calculations.

For any of you that are in the California K12 world, you might be interested to know that the calculations in question evaluate a student’s  status with respect to state university entrance requirements, are non-trivial, and can be found here.

Setup

Suppose you have a report with a couple of cascading parameters, both derived from a query.  In my example I’ll use the Continent->Country->City relationship that exists in my standard SQLWorld database, borrowed from the MySQL tutorials.  The sample report is simplicity itself.  The cascading parameters part probably isn’t even necessary, you just need a single parameter that derives from a query.  We’ll focus on the Country parameter here.

The Country parameter gets its default, as well as its available values, from the paramCountry query.  When you first deploy it to the SSRS server, here’s how the parameters look from the vantage point of the Report Manager interface.  For our purposes it doesn’t matter what method you use to deploy and, again, remember that while your interface might look different if you’re not using R2, the results and behavior are the same: 

… so far so good. Because you get the available values from the query, in this example, the list of values for Country will refresh each time the user chooses a different Continent.  Because you get the default values from the query, all the Country values are initially selected after the refresh.

Multi-value dilemma

So now, for various reasons, let’s say you don’t want to rely on the Report Manager UI “select all” behavior. You want an explicit value that indicates what you want to do for country selection to the main query for the report.

In my case, it was because I wanted to call the report programmatically.  There were also some security complications, not relevant here.  The point is that a multi-valued param makes this all a bit difficult to do.  As you know, they don’t allow a null value, which would have been my first choice. for a revision of the main query… 

 

… and, while they do seemingly allow blank values, this leads to awkward behavior in interactive use:

 

Here’s the simple trick

Instead of null or blank as a default value, define an explicit value (usually “All”, but in my case “Any” seemed more appropriate), and add it to your parameter query, like this:

 

The added Priority column simply lets you position the special value wherever you want in the interactive list, in this case at the top:

 

While there isn’t much difference between the query-based “All” and the explicit “Any” in this case, the simple trick is quite valuable all the same. 

Remember what you really want is a NULL or BLANK in this value, to allow the main query to use a completely different parameter (based on security levels) to lock down to a specific Country list in some instances.  The specific Country or Country list in the different parameter (which is hidden and usually blank) might not cascade from Continent, might be completely unrelated to the concerns or privileges of the standard user. 

In other scenarios, what you really want is a NULL or BLANK for performance reasons.  Ignoring the value of the parameter will turn out to be a good idea, as compared with an explicit IN list containing all possible values, where the list is large.

In such a case, the “All” or “Any” value might have a different visual token associated with it, such as “Unselected”.  It really doesn’t matter.

The point is: this is how you get a functional NULL or BLANK for your multi-valued parameter.

You don’t always realize you’re going to need to do this, when you first design a report. In my recent case, you re-vamp the report to allow the functional NULL or BLANK later on.  This brings me to the real point of my story.

 Here’s the bug

You go ahead and make this change, and then you redeploy the report.  But here’s what you (still) see after the redeploy:

 

Changing the method of deriving the default value, in the RDL, does not alter the method of deriving the default value in the uploaded report.

I’m usually the first one to defend the behavior of Report Manager and the RS APIs when a report is re-deployed.  It makes sense to me that a report deployed to production shouldn’t automatically (for example) get its datasource revised every time a developer uploads a change.  It even makes sense to me (unlike many other people) that the actual default value for a parameter doesn’t change on a re-deploy; after all, production values may often be quite different.

But, in this case, if you need to fix the default value by hand in the deployed version, you really can’t. You don’t have any ability to alter the method of derivation (ie, explicit value/expression versus query-based) here. That’s why I think this is a bug.

As far as I can see, your only choice is to delete the deployed report and re-introduce it to the installation with a fresh start.   This workaround can have all kinds of other unintended consequences if your production deployment is significantly different from your development version, and you have a lot of parameters to adjust for production. And if you forget to make those adjustments, even once — or if you don’t even notice this weird behavior on the first re-deploy, as happened to me — the report will definitely not run as intended.

You’ve been alerted.  If you tend to use multi-valued parameters often, check this out.