May 16, 2006 at 3:22 pm
I have a report with a multi-value parameter (list of products). What I would like to do is detect when a user chooses the "(Select All)" option.
I suppose I could somehow query the number of products in my query and then compare it to a count of the parameter values, but I thought there might be an easier solution.
I'm using Report Services 2005. Thanks for your time.
May 16, 2006 at 4:43 pm
Assuming your parameter is called "productID", you can set the value for the "Select All" option to something arbitrary that won't be a valid value normally like -1 and using IIF to check the value you then use one of two queries.
The first query as used below doesn't use the productID in the WHERE clause.
=IIf(Parameters!productID.Value = "-1", "SELECT...", "SELECT...WHERE...")
May 16, 2006 at 7:50 pm
What I came up with, and never have liked, was to create two additional parameters. The first identical to the multi-valued one the user sees. The defaults are set to ‘All’ using the same query ( or procedure ) that populates the list to begin with. A second hidden parameter ( bit ) is defaulted to 0 or 1 based on the following expression
=IIF(Parameters!UserPicked.Count = Parameters!All.Count,1,0). Bad news is that if you install SP1 on your report server the 'Select All' check box disapears. This leaves us with the option of adding ( unioning ) an 'All' option to the pick lists.
Mike
May 16, 2006 at 7:51 pm
There's a pretty good walkthrough in the books online (Walkthrough - Using a Dynamic Query in a Report). Hope it helps
May 17, 2006 at 12:07 am
I have similar problem
perhaps you can do as I do in data set property
SELECT NULL AS Code, 'None' AS Description, 'None' AS Name
UNION
SELECT Code, Description,Name
FROM your table name or view
May 17, 2006 at 3:17 pm
This solution worked - Thanks!
Summed up as follows:
I have a multi-value parameter listing a list of products, named "products"
I created a second parameter named "all_products_count" that is set as multi-value and hidden, using the same query as "products"
Now I can figure out if the user choose all the products in the list:
Parameters!all_products_count.Count = total number of products available to user
Parameters!products.Count = number of products user chose from the list
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply