SSRS Use 1st Parameter (Multi choice) or 2nd Parameter (Text) for same SQL Parameter

Question

I'm not sure if this is possible, but I would like to use 2x Report Parameters for the same SQL parameter.

Currently I have the following in my WHERE clause which relates to a multi-select drop-down:

 and traderid in(@traderid)

But I would like to have something along the lines of:

and (traderid in (@traderid) OR traderid = (@traderid2)

so that users can either type the name that they require as opposed to finding in in the vast drop-down list, or choose multiple traders from the drop-down.

In trying so far, I cannot leave the multi-select blank as SSRS will not run the report and you cannot specify the 'Allow Null option' for multi-select parameters.

Does anyone know if there is a way around this?

Answer 1

You need to add a value to your multiselect parameter that effectively means "Ignore this and use the text box".

For instance, add an option to the multiselect with value -1 and label -Enter Manually-.

In your query, do this:

and (traderid in (@traderid) or (-1 in (@traderid) and traderid = @traderid2))

The above assumes your traderid is integer. If not, adjust accordingly.