Chris Hays's Reporting Services Sleazy Hacks Weblog

"All" Parameter Value Redux

Question:
How can I enable users to select "All" as the value of a multivalue query parameter?

Answer:
Since multivalue parameters cannot be null, you cannot use the approach previously described here.  Fortunately, a simple modification of that approach to use a non-null "ALL" token is all that is required.

Step 1:  Start with a report that already has a parameterized query and Valid Values queries.

Step 2:  Edit your base query to account for "All".
Whever you have something like this:
 ... AND Field IN (@Parameter) ...
Replace it with this:
 ... AND (Field IN (@Parameter) OR 'ALL' IN (@Parameter)) ...

Step 3:  Edit your Valid Values queries to insert an explicit "All" and separate ids from labels
For example, for this original query:
     SELECT name FROM productcategory
Change it to this:
     SELECT 'ALL' as ID, 'All' as Label
     UNION ALL
     SELECT name as ID, name as Label FROM productcategory

Step 4:  Update the report parameters
Set the default value of each parameter to ALL
Change the valid values settings to account for the new field names (if needed).

Step 5 (Optional):  Update Valid Values queries to account for "All".
If you have hierarchical parameters, in some cases, you may want the dependent parameters to be independently selectable when the parent parameter's value is "All".  In this case, repeat step 2 for each of your Valid Values queries.  Note: Don't do this if you have extremely large numbers of possible values, as performance will suffer and your users wouldn't be able to nagivate an excessively long list anyhow.

A full working sample of multivalue "All" parameters is attached.

Published Tuesday, April 18, 2006 5:54 PM by ChrisHays
Filed under:

Attachment(s): SelectAllMultiValue.rdl
Anonymous comments are disabled

This Blog

Syndication

News

This blog is provided 'AS IS' with no warranties, and confers no rights. All rights reserved. Some assembly required. Batteries not included. Your mileage may vary. Objects in mirror may be closer than they appear. No user serviceable parts inside. Opening cover voids warranty. Keep out of reach of children under 3.

© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Microsoft
Page view tracker