Multi value parameter not working in SSRS report
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.
Shayki Abramczyk
DevOps Engineer @ Tipalti. You can find me iמ Linkedin and Twitter.
Updated on June 05, 2022Comments
-
Shayki Abramczyk almost 2 years
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 withWHERE
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). -
Shayki Abramczyk over 5 yearsThanks. 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 over 5 yearsCould you please add your SQL and a screenshot of the dataset parameter properties to the question?
-
Shayki Abramczyk over 5 yearsI don't understand.. can you give me an example, please?
-
Shayki Abramczyk over 5 yearsI added the dataset parameter properties, do you need the SQL MainQuery?
-
iamdave over 5 yearsThis 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 over 5 yearsThose 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 over 5 yearsOn 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 over 5 yearsAre 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 over 5 yearsIf 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 over 5 years@ShaykiAbramczyk That is exactly what the
join
expression does and is why it isn't working in your dataset. -
iamdave over 5 yearsGet 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 answerwhere Test_Number in(@NewParam)
-
Shayki Abramczyk over 5 yearsIf I use
=Parameters!NewParam.Value
I got the error above :| -
iamdave over 5 yearsI 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 over 5 yearsI 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!