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).

Solution

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.

Comments

[Shayki Abramczyk]

Thanks. I guess you meant to =Parameters!NewParam.Value without J. I tried that, but I got this error: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'MainQuery'. (rsErrorExecutingCommand) An expression of non-boolean type specified in a context where a condition is expected, near ','. Incorrect syntax near the keyword 'else'.

[iamdave]

Could you please add your SQL and a screenshot of the dataset parameter properties to the question?

[Shayki Abramczyk]

I don't understand.. can you give me an example, please?

[Shayki Abramczyk]

I added the dataset parameter properties, do you need the SQL MainQuery?

[iamdave]

This is very bad for report performance. Data should be filtered at the database level. By filtering in the report there is additional time required for data transfer as well as data processing in the report, which is much slower than SQL Server.

[iamdave]

Those are the FIeld Properties. Please add a screenshot of the Parameter Properties. You can see this screen by clicking on Parameters on the left of that window.

[iamdave]

On your Dataset Properties window, click onto the Parameters section on the left hand side. Also, you are not currently using your @NewParam parameter in your query. Please add relevant screenshots of your report in a state that is not working per your question.

[Shayki Abramczyk]

Are you sure? I think if I define the parameter in this way: =Join(Parameters!NewParam.Value,",") the value is not one long string, is a few strings. anyway -I will try your suggestion and will update :)

[DimUser]

If you run USE [ReportServer]; GO SELECT * FROM dbo.ExecutionLog3 ORDER BY TimeStart DESC ; you should be able to see what value(s) is/are being passed as parameters

[iamdave]

@ShaykiAbramczyk That is exactly what the join expression does and is why it isn't working in your dataset.

[iamdave]

Get rid of the join expression and reference the parameter as you would any of the other above it and you should be able to use it per my answer where Test_Number in(@NewParam)

[Shayki Abramczyk]

If I use =Parameters!NewParam.Value I got the error above :|

[iamdave]

I have built a report just yesterday that uses multi value parameters in the way that I am describing here. This is the correct way to use them. I would think that you are doing something in your report or dataset somewhere outside of the screenshots you have provided that is changing the behaviour.

[Shayki Abramczyk]

I checked the long query and I saw if else regarding this param, I removed it (is unnecessary) and it works! (with =Parameters!NewParam.Value). thank you so much!