SSRS Multilookup Function for Names with Comma Sep

Question

I would like to put the following in a multilookup drop-down in reporting services :

"Smith, John", "Jones, Tim", "Kelly, Andy"

The comma separating the first and last name prevents the multilookup from working correctly. I am also unable to use ticks (' or ") in reporting service to keep the separating comma from working like a multilookup comma that separates the fields I want to select from.

Is there a way to keep the comma in the names while still allowing multilookup?

Any help would be much appreciated. Thank you!

Answer 1

Assuming you already have the names in the LastName, FirstName format in a dataset:

Ensure your report dataset query has the appropriate WHERE clause defined:

...
WHERE Name IN (@Name)
...

Modify the @Name parameter enabling multiple values:

And set the Available Values of the parameter to the prompt dataset:

When the report is run, parameter selection will look like this:

Take a look at this set of report development tutorials for practice.