How do you set “(Select All)” as the default for multi-value parameters in Reporting Services?
The short answer is that you can’t, although there is a workaround. Essentially, “(Select All)” as implemented in Reporting Services simply does this:
if (all parameter values/members are already selected)
{
deselect all values/members
}
else
{
select all values/members
}
You can still get the behavior you want however, assuming that your parameter values are query-based. If you populate your parameter list with a dataset, just use the same dataset to set your default values. This technique will work regardless of whether you’re dealing with SQL or SSAS.
In the attached example RDL the “technique” is demonstrated for both a SQL and SSAS-based data source. (You’ll need the AdventureWorks database and cube samples installed)
Comments
Anonymous
September 28, 2007
is there any way to pass 'All' as a parameter to an MDX query in SSRS?Anonymous
September 29, 2007
HI - I am pass ing the qualified member name:[Regions].[RegionsDesc].[All]to a report. This works fine, delivering all regions. However, when I click on a cjart in this report to pass this parameter along with otheres, the next report sees only the first member of the region group. Any ideas?ThanksAnonymous
October 02, 2007
Does your other report except multi-value parameters? If so, you'll need to pass the entire "array" of values you've selected in the first report do it using either split() or join(). I think there is a thread out on the MSDN forums by one of the developers that explains the whole situation. Try searching there on "split, join, and subreport"Anonymous
November 22, 2007
Thanks for this solution. One point to add - if you find that RS is still not showing your default value as 'Select All', check your dataset for null values. If it contains a null value, but your report parameter is set to not allow nulls, RS will revert to not selecting any values by default.Anonymous
September 14, 2008
The comment has been removedAnonymous
November 05, 2008
The comment has been removedAnonymous
November 25, 2008
The comment has been removedAnonymous
November 27, 2008
This is still not working. Anybody will solve this problem????Anonymous
November 27, 2008
This is still not working. Anybody will solve this problem????Anonymous
December 29, 2008
Although this works initially, the results are cached and this creates a problem. For example, if you have all of 10 items selected, then list a subset of those 10, say for example 5 of 10, all 5 will be correctly selected. But when you go back to list all 10 items, only the 5 that were selected in the last request will be selected.This should be an easy bug to fix Microsoft !! It is really an annoyance.Anonymous
April 02, 2009
Worked like a charm - thank so much!Anonymous
September 22, 2009
Almost 10 months have passed and I still haven't seen a viable solution. I see a lot of workarounds for various examples, but the problem still exists. I did see a hint though. When I added a second record with the same values (UserID, 1) and (UserID, 1), the first UserID value was selected correctly, but the second one wasn't. Hmmm.Anonymous
December 08, 2010
In reports when we want to default the multivalue parameter to 'Select All' following are the steps.
- Open the Report parameter window from the Report menu.
- Select the Report parameter from the left handside of the window.
- Select 'Multi-value' checkbox and appropriate 'Available values'.
- Under default values select 'From Query' radio button'.
- Select the appropriate 'Dataset'.
- Select appropriate 'Value Field'.
- Save the Report and select Preview Tab. You will find all the items selected in the multivalue parameter list and the result displayed for all the selected items.
Anonymous
March 21, 2011
Thank you for your solution. Very useful.Anonymous
March 31, 2011
SSRS 2008 is simplistically wonderful in these matters.
- To Select All values of a parameter set the default values to the specific value field (usually the ID field) of the dataset. For the same parameter the available values can be set in the same way. e.g. Dataset = Customers Label = CustomerName Value = CustomerGUID
Anonymous
April 14, 2011
The comment has been removedAnonymous
June 27, 2011
I have the same problem! only the first value of the multiple of values is returned eventhough "ALL" is selectedAnonymous
September 26, 2011
Apparently, the default value needs to be an array of the appropriate type. For a static set of string values, I was able to get this expression to do the job: =split("value1,value2,value3,...",",") That seems like a silly way to return an array, but it's the first thing I found that works. Fortunately, I didn't need integers or any other non-string type.I even searched a bit for a direct way to specify an array in an SSRS expression. Behold, it seems to be impossible or just not common enough to appear on Google's first few pages. Welcome to the stone age of expression languages.Anonymous
February 16, 2012
it is simply greed and ignorance that makes this so hard to do, it is poor software combined with a urgency to push your software to release because of all that money waiting. OH BILL GATES aren't you rich enough already to release good software?Anonymous
July 11, 2012
Thanks..very simple solution by Kassim.Anonymous
July 31, 2012
For a multi-select scenario:
- create a dataset for your filter that selects a label and an ID field for its query
- create a multi-select parameter and, under Available Values, set the Value field to the ID and the Label field to correspond to the dataset created above
- under Default values, select the ID field as the Value field In other words, it can't create a default selection using text values, only IDs.
Anonymous
January 19, 2013
Thanks so much!!!! You saved my google time!!!! Thanks. :-)Anonymous
November 03, 2013
Paramater contain multiple values, if we select one values, automatically it displaying the all values