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
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`
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`
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?
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)
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?
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.
18 People are following this question.
