question

David 2 1 avatar image
David 2 1 asked

How To Use Multiple SSRS Paramaters To Filter Tablix Data

Hi there, I am trying to create an expression to filter the tablix data based on multiple report parameters by using an OR clause. I can create 1 parameter and filter the data based on this data however I'd like to filter the data on something like parameter1.value OR parameter2.value. TIA
ssrsparametersfilterexpression
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kringen avatar image
kringen answered
Hi David, You have a couple options: Option 1: Nested IIF statement that will test all options and then finally return False if no matches **Expression:** `=IIF(Fields!column1.Value= Parameters!Parameter1.Value,IIF(Fields!column2.Value = Parameters!Parameter2.Value,True,False),False)` **Type:** `Boolean` **Value:** `True` Option 2: IIF statement with "Or" operator **Expression:** `=IIF(Fields!column1.Value= Parameters!Parameter1.Value Or Fields!column2.Value = Parameters!Parameter2.Value,True,False)` **Type:** `Boolean` **Value:** `True`
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kringen avatar image
kringen answered
Hi David, You have a couple options: *Option 1:* *Nested IIF statement that will test all options and then finally return False if no matches* **Expression:** `=IIF(Fields!column1.Value= Parameters!Parameter1.Value,IIF(Fields!column2.Value = Parameters!Parameter2.Value,True,False),False)` **Type:** `Boolean` **Value:** `True` *Option 2:* *IIF statement with "Or" operator* **Expression:** `=IIF(Fields!column1.Value= Parameters!Parameter1.Value Or Fields!column2.Value = Parameters!Parameter2.Value,True,False)` **Type:** `Boolean` **Value:** `True`
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fantastic thanks for your help Kringen. I went for option 2 but what has me stumped now is how to get the parameters to accept multivalues. I've ticked the parameter tick box and scripted this but doesn't seem to work because if a column is blank then it still returns even although I hadn't ticked it as a parameter value: =IIF(InStr(Join(Parameters!parameter1.Value,","), Fields!column1.Value) Or InStr(Join(Parameters!parameter2.Value,","), Fields!column2.Value),True,False) I'm assuming that the a blank column is still joined with a "," through the Join( function so returns it as true but can you advise if I'm missing something? If a column is blank, i.e. "" then how not to get the Join(? Or how do I remove a single "," from the blank results? Hope this makes sense?
0 Likes 0 ·
kringen avatar image
kringen answered
Since InStr is returning the position of the first occurrence, you may need to add "> 0" to the evaluation. Can you try this? =IIF(InStr(Join(Parameters!parameter1.Value,","), Fields!column1.Value) > 0 Or InStr(Join(Parameters!parameter2.Value,","), Fields!column2.Value) > 0,True,False)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks again. Yes I did try the below but with no luck: =IIF(InStr(Join(Parameters!parameter1.Value,","), Fields!column1.Value) > 0 Or InStr(Join(Parameters!parameter2.Value,","), Fields!column2.Value) > 0,True,False) Only thing I can think is that a blank field in the column with the Join( is adding a comma so evaluating as true?
0 Likes 0 ·
Managed the get the below to work: =IIF(InStr(Join(Parameters!parameter1.Value,","), Iif(IsNothing(Fields!column1.Value),-1,Fields!column1.Value)) > 0 Or InStr(Join(Parameters!parameter2.Value,","), Iif(IsNothing(Fields!column2.Value),-1,Fields!column2.Value)) > 0 ,True,False) Thanks for your help pointing me in the correct direction.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.