SSRS - multi-value parameter

Question

I've got a relatively huge table with customer ID s and I'm trying to create an SSRS report where I would be able to pass in a multi-value string to my @CustomerID parameter.

For example, two separate customer id's:

'1212121, 3242342'.

The question is whether there is any way to set up my parameter so that I can type in my CustomerID's, instead of having to select from a huge drop down list with hundreds of CustomerID's.

Why would you want to let your users enter these numbers? Do they know the CustomerIDs by heart? — Wolfgang Kais
I'm short, yes, the end user will have to have CustomerID's before hand. — Redraidas

Alan Schofield Alan Schofield · Accepted Answer · 2019-02-07T00:09:41

You can type directly into a multi-value parameter. To add more then one entry by hitting Shift+Enter after each entry. Each entry will appear on a separate line. There is no need to add commas.

However this is not a great way of doing things as you are expecting users to know ids.

What I normally do is use two parameters.

The first parameter (called say @custSearch) is a simple plain text parameter.

The second parameter is populated from a dataset with a query something like this...

SELECT CustomerID, CustomerName 
    FROM myCustomerTable
    WHERE Customername like '%' + @custSearch + '%'

So, the user types a partial match into the first parameter which filters the second one down to only matching customers.

SSRS - multi-value parameter

1 Answers