x

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

more ▼

asked Mar 23, 2016 at 01:37 PM in Default

avatar image

David 2 1
1.3k 56 61 69

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Mar 23, 2016 at 08:11 PM

avatar image

kringen
50 2 1

(comments are locked)
10|1200 characters needed characters left

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)

more ▼

answered Mar 24, 2016 at 01:13 PM

avatar image

kringen
50 2 1

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?

Mar 24, 2016 at 01:32 PM David 2 1

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.

Mar 24, 2016 at 03:26 PM David 2 1
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 23, 2016 at 08:11 PM

avatar image

kringen
50 2 1

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?

Mar 24, 2016 at 12:19 PM David 2 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x698
x93
x24
x11

asked: Mar 23, 2016 at 01:37 PM

Seen: 476 times

Last Updated: Mar 24, 2016 at 03:26 PM

Copyright 2017 Redgate Software. Privacy Policy