Filter Multivalue Parameter on Dataset

user_8197715 0
Viewed 11580

So I have a multiple value parameter than contains 3 options. >250K, <250K, >2M. I also have a table that consists of multiple columns. Image 1 Image 2. Because the parameter is a multivalue, i am having difficulties filtering the dataset.

I need to filter the dataset by checking, (if > 250K is selected, filter the dataset accordingly), (if < 250K is selected, filter the dataset accordingly) and (if > 2M is selected, filter the dataset accordingly).

I was told to use a join and split on the parameter within the (>250K condition, then do a contains to see if it contains any of the parameter values) but I am not as advanced in my knowledge of coding to be able to do that.

Any Suggestion? Thanks in Advance

I previously tried the method below but then i came to realise that it wont work because the parameter is a multi value.

enter image description here

ssrs-2012 parameter-passing reporting-services
4 Answers

I know its been a while since you raised this, you were on the right track but all you should need to do is add a filter to the Tablix on the field you will be filtering, use the 'in' operator and in the Value type [@Yourparametername] the square brackets and case sensitivity are important. Also ensure the expression type is correct, in your case it looks like you are using Integer. The image should help.Tablix

user_64199230

Once you return the value you can also use charindex or patindex* and look for where the value in your where clause is a pattern where the index number is > 0 . For instance if the returned string from SSRS is '01,02,03' and then your where clause has something like this right(field, 2) which would result in value '03'. you change your where clause to be where patindex('%' + right(field, 2) + '%', @returnedstring) > 0 which will give you results. The keeps you from having to parse apart the @returnedstring parameter in your sql code.

user_114835680
Related