I need to filter a table on two multi-value parameters, but also using the IN operator.
I need something like this
Expression: =Trim(Fields!MyField.Value) IN Parameters!MyMultiValueParameter.Value Or Trim(Fields!MyField.Value)
Operator: IN
Value: Parameters!MyMultiValueParameter2.Value
I tried doing it this way also to no avail. I know that what I have there isn't correct, but I need soemthing like that to get this to work. I can't do this on the SQL side due to the way the report is designed.
Expression: =IIf(Trim(Fields!MyField.Value) Like "*" & Replace(Parameters!MyMultiValueParameter.Value.ToString(),",","*") & "*", True, False)
Since the parameters are mutil-value parameters, you couldn’t just put the expression Parameters!MyMultiValueParameter.Value in filter’s value expression, so switch to Filters tab in the Dataset dialog box, write in as following:
Expression Operator Value
=Fields!YourField1.value In ParametersYourMultiValueParameter1.Label
=Fields!YourField2.value In ParametersYourMultiValueParameter2.Label
Note: Besides this method, you can still add two parameters for the query to retrieve dataset rather than utilizing filters. Such as, Select * from table where columnName1 in (@ parameterName1) and columnName2 in (@parameterName2)
Here is just an example, your query might be much more complicated than this. If you still have question after trying these, please feel free to ask.
Thanks for your reply. This is a great idea and it almost will work perfectly. Here is the problem though. I would have to change what you have above to this.
Expression Operator Value
=Fields!YourField1.value In ParametersYourMultiValueParameter1.Label
OR
=Fields!YourField1.value In ParametersYourMultiValueParameter2.Label
This worked for me for my single multivalue parameter. However, I don't understand why it worked and why you need to use .Label instead of .Value for the Value field?
bjkaledas
Member
7 Points
49 Posts
Table "Or" Filter Expression using the "IN" operator
May 25, 2010 05:53 PM|LINK
I need to filter a table on two multi-value parameters, but also using the IN operator.
I need something like this
Expression: =Trim(Fields!MyField.Value) IN Parameters!MyMultiValueParameter.Value Or Trim(Fields!MyField.Value)
Operator: IN
Value: Parameters!MyMultiValueParameter2.Value
I tried doing it this way also to no avail. I know that what I have there isn't correct, but I need soemthing like that to get this to work. I can't do this on the SQL side due to the way the report is designed.
Expression: =IIf(Trim(Fields!MyField.Value) Like "*" & Replace(Parameters!MyMultiValueParameter.Value.ToString(),",","*") & "*", True, False)
Operator: =
Value: =True
It would work if I could do this:
Expression: =Trim(Fields!MyField.Value)
Operator: IN
Value: Parameters!MyMultiValueParameter1.Value
And/Or = Or
Expression: =Trim(Fields!MyField.Value)
Operator: IN
Value: Parameters!MyMultiValueParameter2.Value
You-Hu Fu
Star
8174 Points
795 Posts
Re: Table "Or" Filter Expression using the "IN" operator
May 28, 2010 08:32 AM|LINK
Hi,
Since the parameters are mutil-value parameters, you couldn’t just put the expression Parameters!MyMultiValueParameter.Value in filter’s value expression, so switch to Filters tab in the Dataset dialog box, write in as following:
Expression Operator Value
=Fields!YourField1.value In ParametersYourMultiValueParameter1.Label
=Fields!YourField2.value In ParametersYourMultiValueParameter2.Label
Note: Besides this method, you can still add two parameters for the query to retrieve dataset rather than utilizing filters. Such as, Select * from table where columnName1 in (@ parameterName1) and columnName2 in (@parameterName2)
Here is just an example, your query might be much more complicated than this. If you still have question after trying these, please feel free to ask.
Regards,
Challen Fu
Feedback to us
Microsoft One Code Framework
bjkaledas
Member
7 Points
49 Posts
Re: Table "Or" Filter Expression using the "IN" operator
May 28, 2010 12:34 PM|LINK
Thanks for your reply. This is a great idea and it almost will work perfectly. Here is the problem though. I would have to change what you have above to this.
Expression Operator Value
=Fields!YourField1.value In ParametersYourMultiValueParameter1.Label
OR
=Fields!YourField1.value In ParametersYourMultiValueParameter2.Label
Thanks so much!
You-Hu Fu
Star
8174 Points
795 Posts
Re: Table "Or" Filter Expression using the "IN" operator
May 28, 2010 01:40 PM|LINK
Hi,
Since my reply solve your issue, could you please mark it as answer, Thanks.
Regards,
Challen Fu
Feedback to us
Microsoft One Code Framework
mhbrown
Member
48 Points
66 Posts
Re: Table "Or" Filter Expression using the "IN" operator
Feb 21, 2013 11:48 AM|LINK
Hi there
This worked for me for my single multivalue parameter. However, I don't understand why it worked and why you need to use .Label instead of .Value for the Value field?
Could you explain?
Thanks
Mark