Wow. What a terrific morning!
We are getting some rain that is so desperately needed (They say the reservoir that feeds Atlanta will dry up in 90 days if we don’t get some substantial rain soon. http://abcnews.go.com/GMA/story?id=3730145&page=1), I had an awesome rehearsal/jam with my band last night, and our production servers are ready to go online to be configured!
So this morning I am going to discuss 2 shortcomings of the multi-valued parameter (MvP) without getting irritated.
The first shortcoming, and in my opinion the most important, is the inability to pass a MvP to a stored procedure with out having to parse it out in the procedure. (See this blog for more information http://otechnology.wordpress.com/2007/04/02/processing-csv-strings-t-sql/ )
I would think that since Microsoft was writing these 2 products together, they could have done a little collaboration and figured out a way to pass this. So far, I have not seen anything showing that this is being fixed in 2008, but I may have missed something. There is an entry in Connect about this, but there has been no activity (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=305511 )
For a workaround on this, I just put the SQL statement in the report. I know that forces a compile every time the report is run, however the processing time to parse up the parameters is much more than the compile.
The second shortcoming is the Select All. I know that in SP1 some developer removed the Select All check box from MvPs and the entire SSRS community replied with a unified roar. I was one of them. So in SP2, the Select All was returned with a collective sigh of relief. BUT there is still one thing missing from the Select All. The ability to handle the All in the SQL query without listing out all in an IN clause. For example, if I have a list of airports of the world in a MvP, my Where clause looks like this: Where AirportCode in (@AirportCode)
Now, if the user selects Atlanta, the SQL becomes: Where AirportCode in (‘ATL’)
However, when the user chooses Select All, the SQL is a concatenation of every value in the MvP. So even though we know that there is no need to even filter on that parameter, the SQL is formed in a way that causes the query to slow way down, even on an indexed field.
Well, here is my workaround for that. And while it is NOT elegant, it is effective.
Suppose you have 1 MvP called Contacts.
I set up a 2nd MvP called AllContacts. I populate it with the same dataset as Contacts, and then set the default values to the same dataset as well, effectively selecting all by default. I make this parameter internal.
I then setup a third parameter, type of integer, names AllContactsSelected. I also make this internal. It is NOT multi value and there is nothing in the available values. For the default value I use this expression: =iif(Parameters!Contacts.Count = Parameters!AllContacts.Count,true,false)
In my SQL where clause I add this:
Where (@AllContactsSelected = 1 or Contact in (@Contacts))
Make sure that you put it in that order, otherwise it will try to evaluate the IN clause. This way it will see that @AllContactsSelected = 1 and not even execute the IN.
Well, that’s all for now. I sure would like to see these things fixed in SQL Server 2008, but I am not expecting it.
For now, I will just go enjoy the rain…