At the End of Paths Taken (Modifying Where Clause when All Parameter Values are Selected)

 

With the return of “Select All” in Service Pack 2, all multi-value parameters allow the user to pick all of the available values. But what if you want to remove the predicate from your WHERE clause instead of generating a long set of values in the IN predicate?

You can accomplish this is via an expression-based query and an internal parameter.

First, create a second parameter in your report. It should be have a new name, multi-valued, use the same valid values query and the default values set to the valid values query as well. In addition, mark the "internal" flag in the parameters dialog so it is not displayed to the user.

Next, change your main report query to an conditional expression that compares the number of selected values to the number of available values (which will always be selected in the new parameter). For example:

="SELECT Hour, Month, Year, Username, Sitename, Cookie FROM dbo.GlobalIISLog " & IIF(Parameters!ComputerName.Count = Parameters!AllComputerNames.Count,"", " WHERE (sComputername IN (@ComputerName))")

 I have attached a sample report that demonstrates this technique.

Select All.rdl

Comments

• Anonymous
  March 01, 2011
  Thanks so much for this post! I have had a different kind of problem, but your post sure gave me a great idea on how to solve it!