Reporting Services with Multi-selects

I just finished a project for a client that required quite a few multi-selects and I found a key that I’ll use again to preprocess the IDs required for the select procedure that will create the report. On the reports side I used this line to combine the items into a csv list although you could change the separator easily.

=Join(Parameters!Branches.Value,",")

In the stored procedure, I check this string’s element count against the distinct values in the DB in case they have chosen the select all option. That will allow me to make a query without an “IN ()” clause which will run faster. In the latter case (selecting a subset), I parse the string into a table variable to use in my “IN” clause like this:

WHERE branchID IN (SELECT id FROM @branches)

It works pretty slick for under 100 options otherwise the “IN” gets kinda unwieldy because of the large set of in options to go through for each line of the returned select result.