Of all the important things my Dad taught me, here are three that easily come to mind:

• think critically
• be flexible
• get back on that bicycle

Sometimes you gotta use the Report Manager

I have said, and detailed, about a billion times: I don’t believe Microsoft meant us to use Report Manager as a production user experience.  But what happens when you don’t have a choice?

At Dominican University of California, we have over a thousand user-contributed reports, and a system of permissions and visibility that has been maintained partly on a network level but partly, unfortunately, deeply, and almost inextricably, on assigning privileges directly through SSRS without another lookup source or backend.  Additionally, the organization and look-and-feel of reports within Report Manager is what users are accustomed to, and expect.

In this situation, far be it from me to mandate wholesale changes based on philosophical differences.  Instead, it’s important to make Report Manager into the best little user experience it can be.

Start by branding it, upgrading it, documenting it (and this should include RDL usage analysis), providing report authors appropriate skills/guidance and tools, and facilities.  Then tackle the presentation of individual reports.

Making parameters more friendly in Report Manager

I haven’t changed my opinion.  Parameters (as presented in the un-enhanced Report Viewer through Report Manager) are still one of the most unfriendly parts of the default UI experience.  In particular there is not much to like about the default prompt and validation behavior. But, when you have to use it, and when your report parameters are nuanced, you can still provide good feedback to your users, in the body of the report itself.

Use cases and scenarios

Suppose you want to limit a number to a certain range, but it’s not appropriate to use a fixed drop-down of possible values.  

Suppose you have two or more parameters where any of them may be blank, but not all of them.  A typical example here would be a combined lookup and reverse-lookup; you can enter a list of zip codes if you want to know what states they’re in, or a state to get a list of zip codes.  One or the other should be used, but both blank or both filled is not valid.

Suppose you have a text value that should be entered in a specific format, or must contain specific letters, or shouldn’t contain certain symbols.

Given any of these requirements, and many more…. you can build the instructions into your parameter caption/prompt, but what should you do if they put in something else?  In some cases, it might even crash your query; in others, it’s just not going to provide a proper result, and you want them to know why.

The solution is not built into the Report Manager, which is limited… but it is built into RDL functionality, so use it.

Walkthrough

So, let’s take a simple example, based on data you all have:  The ReportServer db’s Catalog table.  

1. Setup/sample report

Our basic query for the data looks like this:

   select top (@Max) Path, Name,Description from catalog 
      where (Path like  ltrim(@Path) + ‘%’) 

… here, we have a couple of parameters with specific requirements:

• for performance reasons, we don’t want to return more than 1000 reports.  Also, to avoid an ugly default response when we run the query, we want to make sure that the Max number of reports specified by the user is not negative.
• we will allow the user to specify a partial path and, for the purposes of illustration, we’ll say that the path must be entered beginning with a backslash (in reality, any complex requirement for the composition of a string may be substituted)

2. Add a hidden parameter for validation

Note:  You can actually do this whole thing without another parameter but this version of the solution — using a parameter to fix parameters in Report Manager — seems like poetic justice, and is pretty straightforward and clean.  Note that, to work properly, the new parameter should follow your exposed user selection parameters, so it can “react” to changes in them.

We need to put some code in place that evaluates our special conditions for executing our query, and the work you’re going to do for this is pretty much the same no matter how complex your requirement are.

This parameter will provide the actual feedback we want to show the user if s/he chooses values for the other parameters that are inappropriate, mutually incompatible, etc.  We will allow it to be blank, signifying everything is okay.  In this example, I’m going to get its value from a query, which is really fine; you can ignore the warning about possible perf issues, considering that this query will only ever have one result row, but you could also get the value from an expression, and write a VB or C# function to evaluate your other parameters.  It all depends on which language (SQL or .NET) suits your validation requirements best.

Here’s how the new dataset query provides the ErrMsg value, in my particular scenario:

3. Adjust your Main data query to respect your new, hidden parameter

This is really easy.  All you have to do is change what you provide when @ParameterFeedback is blank or has a value; it doesn’t have to know the reasons:

if len(@ParameterFeedback) = 0
begin
   select top (@Max) Path, Name,Description from catalog where (Path like  ltrim(@Path) + ‘%’)
end
else
begin
    select space(0) as Path, space(0) as Name, space(0) as Description from catalog where 1=2
end

As you see, all we’re doing is providing an empty set rather than something inappropriate (or crashy) if the parameters don’t meet our requirements.  

Note that, in my particular case, because there was a TOP clause in my query, I could simplify even further:

declare @thisMax as int = @Max
if len(@ParameterFeedback) > 0
begin
   set @thisMax = 0
end
select top (@thisMax) Path, Name,Description from catalog where (Path like  ltrim(@Path) + ‘%’)

So now, the report is much more polite when things are wrong.

All that’s left is to tell the user something we already know — and as I said, your RDL is up to the task.

4. Use NoRows!  Duh.

NoRows is one of the RDL’s most underutilitized capabilities, IMHO.  It can be formatted however you want — including, for example, using different sizes or colors depending on the nature of your error problem (add an ErrorLevel column to your ParameterFeedback dataset and you’ve got the idea…).  Most important for our requirements, it can be filled using an expression, just like any textbox:

Now your report gives polite, specific, and totally-owned-by-you feedback.

If you like, you can also hide the report title or other report elements, using something like the following in their Visibility/Hidden expressions.

=len(Parameters!ParameterFeedback.Value) > 0

… in my example, I’ll do that so that the only thing showing on the page is what I want to show, if the parameter values are not to my liking.  I’ve also added a little padding to the NoRows element, since it’s the only thing on the page. Here’s the final result:

That’s all there is to it.

NoRows is so simple, so useful and (again IMHO) so underappreciated.  Like everything else in life, it’s easy to see the shortcomings of Report Manager, and a little harder to make constructive use of everything we’ve been given “underneath”.