Improving the multi-select parameter in SSRS

The usability of a text box with ‘the Allow Multiple Values’ is very poor. Typically users see the blank box and think something has failed, rather than click into it and start typing.

Here’s a better way. Create a standard text box.

Users can type a single value into this text box, or type multiple values separated by commas

The TSQL below loads the comma separated values into rows of a temp table

drop table if exists #tmpAccRep
select value as accrep into #tmpAccRep from STRING_SPLIT(replace(@AccountRepCode, ' ', ''), ',')

The SQL query can now join to the multi-row temp table. There’s even an OR clause to allow a blank text box to not filter anything.

  join dwh.common.d_account_rep DAR
    on DAR.account_rep_sk = DC.account_rep_sk

  join dwh.common.#tmpAccRep tAR
   on (DAR.account_rep_code = tAR.accrep OR @AccountRepCode = '')