Checking a multi-value parameter against multiple columns in SSRS

Question

I've been trying to get this function to work in SQL Server Reporting Studio 2014 for a while now, and I haven't been able to find a workaround here through lots of searching.

I'm fairly new to SSRS, but I've seen that you cannot create filters with ORs in between them in 2014 (it used to be possible in earlier versions). I have a situation where I want to use a single multi-value parameter (@IDs) to check if a value exists in 4 separate columns of the same table (ID 1, ID 2, ID 3, and ID 4).

In theory, this would be:

WHERE [ID 1] IN @IDs
   OR [ID 2] IN @IDs
   OR [ID 3] IN @IDs
   OR [ID 4] IN @IDs

This works if there is a single value input into the parameter. However, this obviously doesn't work when the parameter has multiple values or is blank (''). In other parameters, I use the expression:

[column_name] IN =IIF(Parameters!parameter.Value(0) = "",Fields!column_name.Value,Parameters!parameter_name.Value)

This provides me with the correct results when checking against a single column. I haven't been able to figure out a way to translate this into what I need to do with the other parameter checking against 4 columns.

In summary, if @IDs = '', return all rows from the dataset, else check for a valid value in [ID 1], [ID 2], [ID 3], or [ID 4] and return the correct rows.

UPDATE WITH ANSWER

Alan Schofield provided most of the answer below with a little further tweaking. The Dataset query should look as follows:

SELECT *
FROM myTable
WHERE 
    ('' IN (@IDs) OR
     [ID 1] IN (@IDs) OR
     [ID 2] IN (@IDs) OR
     [ID 3] IN (@IDs) OR
     [ID 4] IN (@IDs))

Answer 1

Do this in your dataset and it will be much easier. Your WHERE clause is basically correct except you need to put parentheses around the @IDs parameter.

So you dataset query would look something like

SELECT *
    FROM myTable
    WHERE 
        ([ID 1] IN (@IDs) OR
         [ID 2] IN (@IDs) OR
         [ID 3] IN (@IDs) OR
         [ID 4] IN (@IDs))

Don't declare @IDs in your dataset and make sure @IDs is an excact case-sensitive match to your Report parameter name.

SSRS Will automatically pass the values (not the labels) from your report parameter as a comma separated string and inject it into the SQL statement.

Note You need to put the query code in the dataset not a stroed proc, it won't work it's magic if you call a stored proc.