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 = '')
0 Comments
You can be the first one to leave a comment.