Multi value parameter not working in SSRS report

Question

I have a SSRS report. there is a long SQL query on the main query, in the last SELECT I want to filter the results with WHERE expression, the filter should be with a multi value parameter.

I set the parameter in this way:

• Create a new Dataset with a query.

• Add a new parameter to the Parameters folder (with name NewParam).

• Check the "Allow multiple values" checkbox.

• Add the parameter to the "Main Query" and set the value with this expression:

=Join(Parameters!NewParam.Value,",") 

• At the end of the Main Query I filter the results:

select * 
from @FinalStatusTbl 
where Test_Number in (@NewParam) 
order by Priority

The problem is:

On the report when I choose one value from the list I got expected results, but If I choose multi values the results are empty (not got an error.)

Do you have any idea why?

(When I try this: where Test_Number in ('Test 1', 'Test 2') it works well).

Answer 1

This is what I did and it works well to me. You can try it also.

=sum(if(Fields!Business_Code.Value = "PH" 
and (Fields!Vendor_Code.Value = "5563"
and Fields!Vendor_Code.Value = "5564"
and Fields!Vendor_Code.Value = "5565"
and Fields!Vendor_Code.Value = "5551")
, Fields!TDY_Ordered_Value.Value , nothing ))

Answer 2

What you need to do is split your string in the database. What is being passed to your query is 'Test 1, Test 2' as a complete string, NOT 'Test 1' and 'Test 2'. This is why a single value works, and multiple values do not.

Here is a really good link on how to split strings, in preparation for your scenario. The function I most often use is the CTE example, which returns a table of my split strings. Then I change my SQL query to use IN on the returned table.

In your example, you will want to write WHERE Test_Number IN (SELECT Item FROM dbo.ufn_SplitStrings(@NewParam) , where ufn_SplitString is the function you create from the link previously mentioned.

Answer 3

You could try taking the parameter out of the where clause and use the parameter in the filters section of the dataset properties.

This will effectively shift the filtering from the SQL to SSRS.

Answer 4

When you create a dataset with a sql query, multi valued parameters work with the in(@ParamName) without any changes.

Replace your =Join(Parameters!NewParam.Value,",") with just =Parameters!NewParam.Value and you should be fine.

---

That said, the reason you see people using that join expression is because sometimes your query will slow down considerably if your parameter has a lot of potential selections and you data is reasonably large. What is done here is to combine the join expression with a string splitting function in the dataset that converts the resulting Value1,Value2,Value3 string value in a table that can be used in the query via inner join.

This is also a requirement if passing multiple values as a parameter to a stored procedure, as you can't use the in(@ParamName) syntax.