locked
Table "Or" Filter Expression using the "IN" operator RRS feed

  • Question

  • User-2051256729 posted

    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

    Tuesday, May 25, 2010 1:53 PM

Answers

  • User1471008070 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 28, 2010 4:32 AM

All replies

  • User1471008070 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 28, 2010 4:32 AM
  • User-2051256729 posted

    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!

    Friday, May 28, 2010 8:34 AM
  • User1471008070 posted

    Hi,

    Since my reply solve your issue, could you please mark it as answer, Thanks.

    Regards,

    Challen Fu

    Friday, May 28, 2010 9:40 AM
  • User1823070883 posted

    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

    Thursday, February 21, 2013 7:48 AM