Rather Ripped (Select All in Service Pack 1)


If you’ve applied Service Pack 1 to your SQL Server 2005 Reporting Services installation (I hope you did, as there are lots of good things in it), you might have noticed the disappearance of “Select All” from the drop down list for multi-valued parameters.












Before SP1

After SP1

Here’s the story behind this disappearance and how you can get it back.


With SQL Server 2005, we introduced multi-valued parameters, which allow for you to specify a set of values for a single report parameter. If you think about how multi-valued parameters work against a SQL source, you build a SELECT statement with an IN clause and the values get substituted before it is sent to the server, e.g.



SELECT name FROM customer WHERE country IN (@country)


becomes:



SELECT name FROM customer WHERE customer.country IN (‘United States’, ‘Canada’, ‘Mexico’)


The problem comes from the addition of “Select All” to the dropdown list. In summary, the functionality of the “Select All” checkbox is simply, “check all of the values in the list unless they are already all checked. In this case, then uncheck them all.” While this functionality works fine with a small set of values, having several hundred values in an IN clause is not a very efficient SQL statement.


The performance implications for Analysis Services is even more significant; you can select all of the individual members of a dimension as well as the “All” member of a dimension. If your parameter contains the entire member hierarchy (not just a single level), selecting all of the items in the list can cause major performance issues.


A more efficient “Select All” function would actually modify the statement to exclude the clause entirely. Selecting “all countries” in the the above statement would result in:



SELECT name FROM customer


This is quite a bit trickier than the simple parameter substitution logic as you need to maintain the integrity of the statement (What if there is more than one search condition?) Also, we would need to write query parsing routines for each language we supported (T-SQL, MDX, PL-SQL, etc.)


We thought a long time about how to address the performance issues of this feature in SP1. Ideally, you would be able to add a flag to the report definition that would say whether or not to add the “Select All” checkbox. However, as a policy, we don’t change the report definition in service packs due to the upgrade and compatibility issues. We also thought about auto-detecting whether a report is against Analysis Services but couldn’t really do this without changing the execution SOAP API. We thought about making it a flag in the configuration file but this would force people to decide one way or another for all reports. Finally, we thought about putting “Select All” at the end of the list instead of the beginning.


In the end, we decided to remove it entirely. Big mistake. 🙂


Although we gave people a chance to give us feedback in the SP1 CTP, we didn’t really hear about it until after SP1 was released. Once SP1 was available, we heard from many customers that having the functionality available was more important than the potential performance impact. After revisiting the initial issue, we decided to put it back in the product in the form of a hotfix (as well as in the upcoming SP2). We also learned an important lesson that removing a feature is much harder than adding one.


If you would like to get a patch before SP2 is released, you can contact Support Services and ask for Knowledge Base Article #919478 (the actual KB article has not been published on the web yet).

Comments (36)

  1. furmangg says:

    This hotfix will put back the "select all" option within Report Builder, too, right?

  2. bwelcker says:

    Yes, this change is for both Report Manager as well as Report Builder.

  3. UT says:

    Hi Brian,

    I have a report with stored procedure as my main dataset with one parameter. I use multi-valued parameter as my parameter and pass it into stored procedure. My stored procedure has simple query statement as below:

    SELECT name FROM customer WHERE country IN (@country)

    When I previewed the report, it displayed a blank report. That’s what happen when I select all countries and it works fine if I only select one country. I reckon that the multi-valued parameter return as a single line without comma-delimited (‘Australia, Indonesia’), while we expecting (‘Australia’, ‘Indonesia’).

    What do you think? Did I miss something in here?

    Thanks n Regards,

    UT.

  4. bwelcker says:

    Stored procedures do not support muti-valued parameters. You could do something like concatenate the values together and pass them as a single string and then break them apart inside the proc.

  5. Lior says:

    "SAVED!"

    That’s what I thought when I saw this post 🙂

    The thought of rebuilding all those Report Builder reports from scratch to support some kind of workaround kept me up at nights and without energy at day.

    SAVED!

  6. Jim Evans says:

    Why not implement something like this.

    Allow "Select All" to pass a NULL value for the parameter to the sproc. Then write the sproc like this:

    — Parameter declaration

    @Country varchar(3000) = NULL

    WHERE ((@Country  IS NULL) OR (table.counrty IN(@Country )))

  7. gbn says:

    WHERE ((@Country  IS NULL) OR (table.counrty IN(@Country )))

    SQL will not evaluate this.

    It will not parse the string.

    It will look effectively do table.counrty = "1,2,3,4"

  8. Eric says:

    We’ve actually implimented our own logic where OUR "Select All" does pass a null.  Will we be able to turn the default "Select All" feature off?

  9. TeeNah says:

    Quick note – the KB article is now public:

    http://support.microsoft.com/?id=919478

  10. Jim Johnson says:

    Regarding passing a multi-value parameter into a stored procedure, what you are passing is this ‘value1 ,value2, value3’.  In the stored procedure this will split out the parameter into a table, which you then use in the sproc:

    CREATE PROCEDURE [dbo].[something]

         (@code varchar(1024)  )

    declare @temp table(Code varchar(3) primary key)

    If IsNull(@code ,”) = ”

    BEGIN

    Set @code = ‘ALL’

    END

    ELSE

    WHILE LEN(@code ) > 0

    BEGIN

    IF PATINDEX(‘%,%’, @code ) > 0

    BEGIN

    INSERT INTO @temp VALUES(RTRIM(SUBSTRING(@code ,1,PATINDEX(‘%,%’, @code )-1)))

    SET @code = LTRIM(SUBSTRING(@code ,PATINDEX(‘%,%’, @code )+1,1024))

    END

    ELSE

    BEGIN

    INSERT INTO @temp VALUES(LTRIM(RTRIM(@code )))

    BREAK

    END

    END

  11. Data Wizard says:

    Well done for putting your hand up and admitting a mistake, but if you want my opinion the entire suite of products SSRS, SSAS etc is just too damn confusing.  

    SQL 2005 just looks and feels like it was designed by a committee.

    The learning actually should be that it is FAR HARDER to build something SIMPLE than it is to build something that WORKS…

    This written on the day we decided to go with what we have in SQL and forget SSRS…

  12. bwelcker says:

    I’m sorry that you find the product confusing. While SQL 2005 was not designed by "committee", it was designed by a large development team and addresses a very broad set of scenarios. It is hard to develop products that are best in class (best reporting engine, best relational engine, best OLAP engine) as well as seamlessly integrated. You often make tradeoffs between simplicity and flexibility. I think if you told users who are familiar with competitive reporting tools whether SSRS is hard to use, they would not all agree. Anyway, we will continue to work on usability and hope that you will try it again in the future.

  13. Jason says:

    <Friday, August 18, 2006 1:55 AM by bwelcker>
    Stored procedures do not support muti-valued parameters. You could do something like concatenate the values together and pass them as a single string and then break them apart inside the proc.
    </Friday, August 18, 2006 1:55 AM by bwelcker>

    That’s not totally true:
    http://msdn.microsoft.com/msdnmag/issues/06/06/datapoints/default.aspx#S5

    Using a (custom-written) split function as explained in the provided link will allow you to use multi-values with stored procedures. Make sure to set the command type to storedproc.

  14. Karim Sameh says:

    That’s really great I was getting confused , because on our testing machine (which we had not installed the SP1 on it), it was there and our client saw it like that and he was using the select all option, but when deploying on life server (with SP1 on it) we couldn’t find it ????

    However , i still can not find the HotFix, I found the KB page but is the only way to get the Hotfix is to send an E-mail to Microsoft support ????!!!!!!!

  15. Jerry says:

    Karim

    That IS the correct behaviour and the exact problem

    SP1 removes the "Select All" option

    Yes, the only way thus far (before SP2 comes out) is to call Microsoft Support (I did, spent 30 mins getting it, FREE of charge as it should) ~ 300MB download

    http://support.microsoft.com/kb/919478

  16. Trevor Howe says:

    Hi Brian

    I am both glad and sad to see that there are a lot of issues around parameters in RS2005. Glad because I see that I am not the only sucker who spends endless hours searching the internet for the solution to what appears to be a simple problem and sad because it adds a level of frustration to what is otherwise a great product (not only RS but the whole SQL 2005 suite).

    I am primarily using MDX to supply the data for my reports. I have moved away from using too many parameters in the main MDX mainly due to the 4**** tuples error message and also because the stupid things kept falling over when I made a change during development. What I am doing now is using SQL queries run against my datamarts to supply the list values and default values for my parameters. I have more control using this method, especially when I want to filter a paramter list for example to show me the dimension values that were valid at a particular point in time (Type 2 slowly changing dimensions). I can also default a month parameter to the latest month that has been loaded into a particular datamart table. Then instead of filtering the MDX query, I let a larger dataset come back and then filter the table using the parameter value. This might not be so good for larger datasets, but I seem to be fine for now.

    Multi value parameters are great, but I see that the Select All functionality has been removed only to be promised tom make a triumphant return in the next service pack. Even then, I dont think this will solve problem. As an example, I have one parameter that allows the user to select one or many regions from a region dimension table. I use the method above to populate the dataset with only values that were during the month of my "month" parameter value. The problem comes when I do the default value setting. It is a multi value parameter so I specify the same query as the available values query. So the parameter defaults to all Regions. Great so far, but here is my next problem. Because most of my reports are printable, I have to create a number of text fields in the header of my report to display the parameters that were selected. So I use the Join function to display the parameter values (or labels) for the region. I have about 20 regions in the datamart, so you can imagine how busy the text box is to start with. What I would really like is for my expression to detect that all regions have been selected and then display the text "All" or something similar.

    Thats my griping over, but I seriously think MS must put in a bit of extra work related to parameters or at the very lease for someone to accumulate all the parameter gripes and publish a white paper of work arounds to known bugs related to parameters. Which reminds me of another gripe. Why, when you make a change in the MDX designer, does some parameters automatically change back to multi value when I only want a single value select, like in the case of my month selection.

    There I feel much better now. Eagerly awaiting responses from my fellow "parameter challenged" RS colleagues

  17. vince says:

    Brian –

    I am looking for a stable and usable multi-select dropdown for C#.Net much like the one in RS 2005 for multi-value parameters.  Does Microsoft provide this as a custom control?  If not, can you point me in the right direction on any info?

    thanks

  18. Vince Bossio says:

    Is there away to create multi-select dropdowns such as those used in Report Manager for other pages in a Visual Studio .NET project?

    In other words, does MS make the custom control used for multi-select dropdowns available for developers to use elsewhere?

  19. W Stuart says:

    The simple solution is to use:

    Join(Parameter!PARMLIST_IN!Value,"’,’") when you define the stored procedure parameter "Value".

    This places a single quote around each "," so your parameter list looks like:   X1′,’X2′,’X3.

    Then in the stored procedure use:

    select * from X where parmlist in (‘||PARMLIST_IN||’)

    This then creates the "in" PARMLIST_IN" to look like ‘X1′,’X2′,’X3’.

  20. Gerhard says:

    Hi Brian,

    I am using SP2.

    Is there realy no option to turn "Select All" off.

    Performance is bad.

    Best Regards

    Gerhard

  21. Simon Elms says:

    I hope when SP2 comes out that there is some switch that can hide/remove "Select All" in a multivalue parameter list.  While it is definitely useful in short lists, it’d be a nightmare in long lists populated from a database query.  

    I’m currently designing a report where the list for one of the parameters can have several hundred items in it.  Originally designed prior to SP1, if anyone clicked the "Select All" option it would run like a dog.  Now, under SP1, I’ve added an ‘ALL’ value to the query the list is based on.  I’ve used the JOIN and SPLIT functions suggested in prior comments above so I can pass a comma-separated list of values to a stored procedure which produces the recordset the report uses.  Much more efficient than the old "Select All".  

    Now I’m not looking forward to SP2 if it means my list will now have "Select All" as well as my custom "[Select All Warehouses]" (which has the value ‘ALL’).

    The one problem with a custom "[Select All Warehouses]" (or similar) is that it doesn’t have the same behaviour as "Select All".  If a user selects one or more items, then selects the "[Select All Warehouses]" item, the other items aren’t cleared.  Does anyone know if there is a parameter list click event, or something similar, that I could handle to clear all other items in the list when my custom "[Select All Warehouses]" is selected?

  22. Unaware of this Select All problem I’ve done all my dev on SP1 and now I’m trying out SP2 I see I have no way of disabling this system generated option.

    This is crazy.

    I use stored procedures and pass CSV and split it out in the proc into dynamic SQL or a set and do a join.

    This "feature" needs a method of disabling it’s appearance; we don’t want a huge IN clause, I don’t even want an IN clause period if I don’t need to join – it just causes work and for complex SQL which is what I am doing the performance hit is too great.

    If you can find time to put other features into these service packs then why can’t you put a simple option in to allow us to control this Select All appearance?

    I’ve raised a bug on connect if anybody would like to vote….

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249227

  23. Chris McGuigan says:

    SP2 – Return of ALL, Noooo!!!!!

    ‘ALL’ is to return to parameter drop downs in SP2, in exactly the same way i existed prior o SP1.

    I’ve already spent time fixing reports because of the removal of ‘all’ in SP1. I know it was for a valid reason and my changes made the query more efficient when ‘ALL’ was selected. But now you’re reverting back with SP2!

    I think you should make an exception to the rule about functionality change in an SP here. At least make it optional. Can you tell from the report def, exactly which version it was created in, if its not SP’d default to that versions behaviour. If it’s SP1, default it not to have auto ‘ALL’ if it’s brand new – default it to Yes if that’s the consensus.

    You made an exception in RS2000 SP2(?)  when you added the print functionality which was fine.

    Cheers

    Chris

  24. bwelcker says:

    I understand all of the angst around this decision. We will definitely make this configurable in the future, either on a server level or a per-report level.

  25. I would prefer server level.

  26. sam says:

    Here is how I built my param list:

    SELECT * FROM

    (

    (
    
        SELECT Fieldname FROM table WHERE yada yada )
    
    UNION
    
    (
    
        SELECT ' &lt;SELECT ALL&gt;' AS Fieldname FROM table WHERE recodid = 1
    
    )
    

    ) BINS

    ORDER BY BINS. BINADDR

    And this is in the WHERE criteria of the report query:

    .

    .

    .

    AND (BIN.BINADDR IN (:Bin) OR ‘ <SELECT ALL>’ IN (:Bin) )

    .

    .

    Its a little cheezy but it works.

    Question for the masters here:

    I need to use a very large parameter list such as customer list, product list where there are thousands of records.  Is it possible that yall can make the dropdown lists for parameters MUCH larger in the report manager.  It is pretty frustrating to to have screen resolution of 1200 x 1200 and then have this little tiny list to work from showing you six of the three thousand or so records you have to select from.  Also, search as you type would be most helpful, auto-fill-in for the address bar in IE.

  27. Chris McGuigan says:

    Hi Brian,

    I’m coming in a bit late on this thread but just so you get some feedback.

    I’m not whining about this reversal on the ‘All’ feature – I’ve done that on the MS forum a while ago 😉

    But it does mean that I will not be updating to SP2 because this one thing has such a significant impact in so many reports.

    Now I’m use to it, I can really see the sense of taking the feature out in SP1. It really allows us to implement an ‘All’ option as efficiently as possible. However, I see the flipside, whilst I am an accomplished SQL programmer, many are not and would probably find implementing this themselves quite a task.

    Cheers

    Chris

  28. bwelcker says:

    Chris, I understand your reluctance to update to SP2 because of the change. It was a very hard decision and in the end, we went with the direction that we thought most people would be happy with. I hope that we can provide a more granular choice in the future.

  29. The back- story: Once upon a time, when Reporting Services 2005 was just barely gold, one was able to

  30. My thoughts says:

    The ‘Select All’ option in the multi-valued parameter is again back with SP2. Yeah…it was released

  31. Lasantha says:

    Hi all,

    Finally can any one tell how to pass multi value parameters to a stored procedure. example would be great.

    Thanks in advance

  32. Jan Morten says:

    Hi

    Is it possible to hide (select all)? I want to do that because when we have large drop down lists the performance is not very good.

    Thanks!

    Jan Morten

  33. With the return of “Select All” in Service Pack 2, all multi-value parameters allow the user to pick

  34. anaylor01 says:

    When I try to use Select All I get This "Incorrect syntax near ‘,’