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.
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)
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).