Using multi-select drop-down boxes as parameters in reports

One of the options for a parameter is for it to be multi-selection. This is very useful for some report scenarios, and transforms the normal combo-box into something much more like the Excel filter drop down (why isn’t this a native .NET control yet!)

However, the format of the data returned by this drop-down can cause problems if you’re trying to run SQL from it. I recently had just this problem, and I’m still working around it.

It took me a while to realise that the data returned from the multi-select box is in the format:

‘choice 1, choice 2, choice 3’

This isn’t much good for use in SQL – especially if you were hoping to pipe this variable straight into an IN command. There’s probably not an entry in your table called ‘choice 1, choice 2, choice 3’, which is what you’re asking for.

The problem is clearer if what you’re looking for is an integer, rather than a varchar – as otherwise you can get held up thinking that you just need to put enough quotes in; you can’t! SQL Server is interpreting your variable as a single entry in the IN list, however you dice it.

You have some options here. The most appealing, but actually the most dangerous, slowest, least preferable option is to opt for some sort of dynamic SQL option and build up the IN statement yourself. I’m sure this would work, but just the fact that the code won’t be optimised was enough to turn me off: this is a hefty report and I didn’t want it to be slower than it was already going to be. If the values are variables, you’re also going to have to find a way of decorating them with quotes.

If the total number of records you would be returning without the multi-choice filter is quite small, or if you were returning them anyway for something else, then you can cheat and avoid the problem by bringing everything back and then filtering on the report side. Within the matrix or chart, or whatever is housing your data, you can filter, using the ‘In’ operator and your parameter name in this format: [@VariableName] (with the square brackets). Don’t do this though unless you’re sure the extra load on the server won’t be a problem.

There is another way, which I will be implementing soon. That is to use a table variable within your SQL stored procedure, break out the values in the variable, insert them into the table variable, and then use that instead of the IN command. I’ll update this post with code just as soon as I’ve done it.