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.

Out of the box

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

The Parameter is a set up as a plain 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.

Leave a Comment