Using a parameter as a filter - Multiple values not working

0
votes

The report works for just 1 choice, but when I add more than one, it does not return anything ( no errors, just nothing in the report).

My SQL statement includes the parameter @Region

where Region_Name IN (@Region)

In the Region parameter's properties, I set to allow multiple values.

in the dataset filter's properties:

Expression: =Fields!Region.Value
Operator: IN
Value: =Parameters!Region.Value(0)

*EDIT I removed the dataset filter as suggested.

Below are the properties for the parameter. The available values come from another data set that is a distinct list of regions.

enter image description here

enter image description here

2
reporting-servicesssrs-2012
Why are you referencing the Region parameter in both the Expression and Value? – iamdave
@iamdave, not really sure to be honest. I was following the thread and adapting. – Brent
I updated my question to using a @Region Parameter in the SQL statement – Brent
Using =Parameters!Region.Value(0) specifies the first parameter value with the (0). Try just using =Parameters!Region.Value. – Hannover Fist

2 Answers

0
votes

Your edit makes a lot more sense. To use multivalue parameters you just need to reference the parameter in the usual way, except use in within the SQL:

select cols
from table
where specificcol in @Region

And then don't have a dataset filter set, just make sure there is a reference to the Region parameter in the Parameters property page. The filtering of the data is handled by the inclusion of the parameter in the SQL, so you don't need to also have a filter on the SSRS dataset.

0
votes

Instead of using a parameter to go back and forth between the stored procedure and the report, I created the parameter and used it in the tablix properties.

Expression: =Fields!Region.Value Operator: IN Value: =Parameters!Region.Value

When you first create the value, it adds a (0) to the end of the expression. Remove that and it works as expected.

Thank you @iamdave for the help!

EDIT:

I came back to this and was able to filter using the parameter and stored procedure.

I followed a link from a question to here: http://www.codeulike.com/2012/03/ssrs-multi-value-parameters-with-less.html

I created a dataset parameter and removed the one from the tablix properties. Then created the function provided and it worked. I did not mess with the "ALL" features in the post.

Thanks for all the tips. Brent