Home Dashboard Directory Help
Search

Multi-Value (Select All) parameter in Reporting Services by Tony Rogerson SQL


Status: 

Resolved
 as Postponed Help for as Postponed


84
0
Sign in
to vote
Type: Bug
ID: 249227
Opened: 1/2/2007 2:45:58 AM
Access Restriction: Public
5
Workaround(s)
view
29
User(s) can reproduce this bug

Description

Change of behaviour is causing a problem - SP2 introduces a (Select All) option for multi-value parameters which cannot be switched off; this is useless without an option to turn it off.

Essentially select three options from the list eg. 1,3,4 passes that CSV through to the stored procedure and we can easily sort that into a set; we can put our own option for 'Show All' and in code determine this and for dynamic searches we can just exclude that filter clause.

Now we have a situation where we have the system generated (Select ALL) option and our own user (Show All), the former (sys gen) one forces all items to be checked which means a big CSV passed into the stored procedure which forces you to filter by that column on a dynamic search which is totally and utterly useless and inhibits performance.

We need an option to switch this (Select All) off, if its just in RDL then it needs to be exposed clearly through the GUI.
Details
Sign in to post a comment.
Posted by DaveRuijter on 1/18/2012 at 4:51 AM
Looks like this item is ignored, however I would like to know what the plans are about this item.
When using a SSAS cube as the datasource for your parameter, the All-member is already there and the other (Select All) item is causing errors and is not user-friendly.
Please give us an update!
Posted by Raul Herrero on 1/31/2011 at 2:34 PM
Any updates on this? I am running in to issues if I pass the parameters via URL in SharePoint because of URL length limitations.
Posted by Will Durning on 7/21/2010 at 2:51 PM
Wow...another example of how completely lame the design or prod mgmt for SQL Server has become. Like many, I just discovered this "feature" that can't be turned off. I have a ton of reports that already have my own "All" for the default and now I find out that all my reports are breaking because users are selecting the "Select All" check box.

I'd offer a sarcastic "Thanks alot Microsoft!", but I don't think that anyone over there would understand our pain nor would they care. That is obvious by the way that the constantly make breaking changes to a product that we're all trying to use.
Posted by Amit Pasalkar on 4/12/2010 at 2:34 PM
Hi Microsoft,

Does their any update on this? I am using SSRS 2008, but still this issue persists in this version. In my report I have so many number of parameter values and if user selects “Select All” for parameter then user gets reports timeouts error. If I am able to disable the “Select All” then I can add my own "All" value and then manipulate further...

This issue is affecting negatively about SSRS as good solution for BI.

And I am not sure why this bug is marked as resolved. I think Postponing is not the resolution of any problem.

Thanks for Understanding.
Posted by Mustafa S. Ali on 4/6/2010 at 2:16 PM
This is utterly required!!!
Posted by EPM on 3/2/2010 at 4:31 PM
We have been stuck on SQL 2005 SP1 for years and now don't know what to do. We are considering replacing SSRS with another solution if this isn't resolved in SQL 2008 R2.
Posted by Kevin_B on 9/3/2009 at 11:55 AM
I entered an enhancement to allow multi-value parameters to be passed to a procedure. Please vote for that here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=469003
Posted by BenRosato on 8/20/2009 at 9:45 AM
Allowing null would be nice too.
Posted by drsch3 on 3/26/2009 at 4:43 PM
The work around here is 'not to instalal SP2 by now'?!!!

I've got another one. Type out users reports for them on paper and deliver them personally. It's a work around isn't it??????

There is a work around for not having the select all by creating your own but there is NO work around for having it there. Apart from never installing another service pack ever or the above suggestion, neither of which is any good to any one.
Posted by Niccola on 2/26/2009 at 12:19 AM
High priority.
Posted by tymberwyld on 7/25/2008 at 12:27 PM
Also, another issue that crept in with this Multi-valued parameter is that it is searching and replacing the Text of the Query with the actual "Value" of the parameter instead of creating a parameter that can be used in the sp_ExecuteSQL command text.

Ex.
-- Assume that the value of the parameter is "1,2":
Select * From SomeTable Where ID IN(Select Value From dbo.ufn_Split(@Param1)) Or @Param1 Is Null

Instead, SRSS is re-factoring my query into this:
Select * From SomeTable Where ID IN(Select Value From dbo.ufn_Split(N'1', N'2')) Or N'1', N'2' Is Null

Which then gives the following Error:
"Incorrect syntax near ',' "

Please stop refactoring the query text!
Posted by Steve Chema on 5/5/2008 at 9:46 AM
One major problem SelectAll causes on the front-end is that when you have a very large report param, the screen just "hangs" for minutes, with no indication to the user that anything is happening. We are using a parameter, InvestorNum, that has over 20,000 distinct values. One way to avoid this problem is not to "default" the report param and let the user select the value(s). This is not very user friendly though, especially when there are up to ten report params on any given report. A more efficient way would be to remove the SelectAll option and let the developer handle the "ALL" option, which is demonstrated in numerous internet articles.
Posted by RobGoudie on 5/2/2008 at 11:35 AM
The performances problems that came along with the creation of "(Select All)" were completely obvious and it made perfect sense to remove the offending problem in SP1. Unfortunately, a large number of casual users (small businesses?) were disturbed by the removal and had so little data that the performance problems didn't hurt them particularly. Now it's the large company developers, with large data sets and demanding reports that are paying the price with the re-addition of Select All in SP2.

Like others who are trying to make efficient reports, we built our own -ALL- option and it works great---if we can just get the obnoxious built-in (Select All) out of the report. We need this to be configurable. Either server level, report level--anything is better than the current situation.
Posted by Juho Salo on 11/9/2007 at 8:12 AM
This is a very urgent issue. We are moving our old reporting system based on separate reporting applications and templates to SSRS2005. In our old system all report parameters are delimeters meaning that if the user for example does not select any departments, then data related to all departments should be shown. But not all departments are shown in the delimitation list => select none and select all are different things.

We could implement this using a custom 'ALL' element as described, but having this element in a multi-select list is very confusing to the end user. So what we need is to make it possible to have multi-value parameters allow null values, to enable the user to just ignore this parameter.
Posted by Jordi Ramis on 3/12/2007 at 8:43 AM
Oh my god, I can't belive it. You have added the option with no way to turn it off. This is definetively terrible for some users.

How it must work (my version):

a) 'Select all' can be enabled/disabled in multi-value list. So you can have construct you 'ALL ELEMENTS' option in SQL for performance purposes.

b) A custom 'ALL' element is not the best solution, the users must un-check the 'ALL' element when they check an individual item. Would you help the programmers? Make a way to the programmer to 'detect' that the user is using 'select all' in the list so they can disable this filter programatically.

This is the best way in my opinion to have all the functionallity of 'Select all' without penalizing the performance.

Thanks and excuse my English.
Posted by way0utwest on 1/3/2007 at 1:32 PM
This should not be postponed, nor should changes like this be introduced in SPs.
Posted by Tony Rogerson SQL on 1/2/2007 at 12:57 PM
I don't think postponing it to the next version is good enough.

Too many people affected by this imho.

We need something to configure whether we want the (Select ALL) or not.

It's far easier to put your own (Select ALL) in then dealing with the consequences processing all those selected options and performance IS important.
Posted by Microsoft on 1/2/2007 at 12:22 PM
Thank you for filing this bug.

We will investigate this enhancement for a future version of SQL Server.
Sign in to post a workaround.
Posted by Tom V on 12/8/2010 at 12:51 PM
I needed a way to display the list of selections or ALL, i used to following in the hearer display as part of the heared

iif(Parameters!Underwriter.Count = CountRows("dsUnderwriterList")
    ,     "For ALL Underwriters"
    ,     "For Underwiter(s) " & join(Parameters!Underwriter.Value) & " Only"
    )

This will show 'All Underwriters if the all are selected, or a list of selected underwriters if only some were selected.

Hope this help someone.

Tom Vande Stouwe
MCT, MCSD, MCAD, MCP
Posted by JNeo on 4/16/2010 at 11:14 AM
There is a workaroud to send a NULL value to the StoredProcedure if the multi-value is selected all

- Add a multi-value parameter 'iCode' that lists the values to choose
- Add a DataSet_Total with Total = count(*) for the values you are adding to multi-value parameter 'iCode'
- Add a hidden and internal parameter Total with default value 'from query' chose the DataSet_Total and value field Total
- Alter the DataSet that uses a Stored Procedure with a parameter TEXT to =iIF(Parameters!iCode.Count = Parameters!Total.Value, Nothing, ","&Join(Parameters!iCode.Value,",")&",")
Posted by lhmaldon on 3/3/2010 at 8:13 AM
Try the following workaround. It worked for me.

Create a new dataset which will be the same as the one used to populate the Multi-value parameter, except that you would add Top 1 in your select statement. Use this new dataset to set the default value of the Multi-value parameter. This will cause the Multi-value parameter to automatically select the first value in the list instead of all the values.
Posted by drsch3 on 3/26/2009 at 4:44 PM
Type out users reports for them on paper and deliver them personally.
Posted by Jordi Ramis on 3/12/2007 at 8:42 AM
not to instalal SP2 by now