0

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))
Dave Kraemer
  • 3
  • 4

1 Answers1

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.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Thanks for the response! It almost works for what I'm trying to do, however I'm still missing some of the data, albeit a very small amount, but is still important. The only case that I'm missing here is if the parameter is left blank as ''. This method misses the rows where all 4 columns have data in them, and only returns rows where [ID 1], [ID 2], [ID 3], or [ID 4] contain '' as its data. Is there any way to incorporate something similar to the IIF() statement I put in the original question? – Dave Kraemer May 25 '17 at 14:52
  • I wanted to update this because I found the answer for what I needed. The WHERE clause should look like: `WHERE ('' IN (@IDs) OR [ID 1] IN (@IDs) OR [ID 2] IN (@IDs) OR [ID 3] IN (@IDs) OR [ID 4] IN (@IDs))` The first one allows all rows to return if @IDs is blank. – Dave Kraemer May 25 '17 at 15:08