Speaking the language of business intelligence with an Australian accent

Saturday, April 29, 2006

SSRS - Disappearing "Select All" Parameter Post SP1

If you have deployed production SSRS reports with multi-valued parameters and applied SP1 you will need to alter the T-SQL code if you still want users to have the Select All option. SP1 removes what was only a piece of UI functionality in the RTM version. All that this feature did was give users the ability to check all of the boxes a parameter list with one click. Because of the checks in each parameter item box this could then result in a potentially large and inefficient IN clause. Multidimensional queries also suffered from the Select All feature. After SP1 the multi-select list will still exist but the Select All option will be gone.

To fix things you will need to alter both the parameter and main dataset/s T-SQL and implement the All functionality in much the same way we we used to do it in RS2000. Have a look at this page under Adding an All Member to a Multivalue Parameter (about half way down). The April 06 BOL update also has details on Breaking Changes in SQL Server 2005 Reporting Services.

The code change is simple and quick and as a result report queries for All will be more efficient. I can understand why this change was made in the service pack but just wish MS had added it to a more visible list or page when the pack was released so we don't have to figure things out after users call to tell us their report doesn't work the way they expect all of a sudden. All we really got was "the automated parameter-generation behavior has been improved".

UPDATE: The SSRS Team have decided to put this feature back in the upcoming SP2. Read more here

4 comments:

furmangg said...

This decision by Microsoft to remove this feature was a terrible decision in my opinion. Just because some dumb developers were abusing that feature shouldn't prompt Microsoft to remove it.

Unfortunately, the "workaround" they propose doesn't cover all the situations in which the old feature was used. It was often used as a "select/deselect all" checkbox to quickly deselect all the checkboxes. Their workaround doesn't help here and I've got lots of angry users with sore index fingers from unchecking hundreds of checkboxes.

I hope they'll provide a switch so you can re-enable the feature if you want. See this in the product feedback center... add your comments and vote for it!

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=f68f4903-5171-4fff-9d70-1b4342b03a35

kikoman8 said...

I couldn't agree more with the fact that Microsoft didn't notify the community of the removal. Since RS2000, the multivalue parameter functionality was one of the most useful in Reports Manager, and a lot of reports developed now depend on this to do away with developing custom ASP.NET pages to display these reports.

If the community were informed properly, development projects would have prepared in advance. And yes, users really give importance to this seemingly insignificant feature, at least in our project. And yes, the deselecting feature cannot be done anymore, as far as I know.

Anonymous said...

I voted in

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=f68f4903-5171-4fff-9d70-1b4342b03a35

Hope the will reconsider in the future

gfutfy said...

welcome to the wow power leveling, cheap service site, buy cheap wow gold,wow gold,world of warcraft power leveling buy wow power leveling