Thursday, October 18, 2007

2 Shortcomings of Multi Valued Parameters in SSRS

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…

Peace

8 comments:

Anonymous said...

Hi Bob,

I try this technique but SSRS gives me an error: An error occurred during local report processing. Error during processing of AllContactsSelected report parameter

Frank said...

I can not get past the "Please select a value for the Paramter". Any suggestions?

darren.dickerson said...

I got around the "Please select a value" problem by adding a null value to my list like so:

SELECT ID, Code FROM Codes
UNION
SELECT NULL, '(All)'
ORDER BY Code

Then in my Report Parameters Dialog, I checked the "Allow Nulls" check box. By default the report shows all codes but you can select a value from the drop down to narrow the results.

Mark Salamon said...

This did not work for me. I think there is an error in your code, but even if fixed, it does not work.

For AllContactsSelected, use this expression:

=iif(Parameters!Contacts.Count = Parameters!AllContacts.Count,1,0)

that way, @AllContactsSelected gets a value of 1 or 0, not true or false.

But even if you fix this, the expression builder does not appear to like it if you use equal with the .Count fields of multi-value parameters. I even tried setting the value of this parameter to:

=(Parameters!Contacts.Count - Parameters!AllContacts.Count)

to see if there was any difference in the counts, and then in my query I used this (looking for @AllContactsSelected to have a value of 0):

Where (@AllContactsSelected = 0 or Contact in (@Contacts))

I still got the same error when I click on Preview:

"Error during processing of 'AllContactsSelected' report parameter."

If you just hardcode the value of this parameter to a number (=0), or just to a Count (Parameters!AllContacts.Count), that does not cause any errors. (Of course, neither of those is logically correct.)

Any way around this problem would be greatly appreciated.

Bob Pearson said...

I am currently using this exact code in over 50 reports, with no problems.

I am running SQL Server Reporting Services 2005 sp2.

Not sure why you are saying the expression builder does not appear to like it if you use equal with the .Count fields of multi-value parameters. What error do you get? Are both parameters setup as multi value?

BobP

Mark Salamon said...

Yes, both are set up as multi-value. If I use .Count = .Count or .Count - .Count (or just any arithmetic involving .Count), I get an error. I will try again. :(

Mark Salamon said...

I figured out what was wrong.

There are three parameters:
Contacts
AllContacts
AllContactsSelected

AllContactsSelected gets its default value from the Count of the first two. My mistake was that in defining the three parameters, I put them in this order:

Contacts
AllContactsSelected
AllContacts

So when AllContactsSelected tried to get the Count of AllContacts, AllContacts had not yet been run. A first year computer science error!

Thanks again.

NJ said...

My solution was similar but required only two parameters and you don't need to do any counts.

In your where statement add this:

Where (Contact in (@Contacts) or @AllContacts = '*')

Then in the report parameters set @Contacts as your multi-value parameter. If you give your users default values to choose from in your multi-value list, add a * as the first option. Otherwise the user can just type * in.

Set @AllContacts to a Hidden parameter that is NOT multi-value and set the default value as Non-queried and =Parameters!Contacts.Value(0)

Then if the user selects/types * as the selection for @Contacts it will set the @AllContacts = * which will then evaluate the where statement such that they will get all data.

Thoughts?