none
Table "Or" Filter Expression using the "IN" operator

    Question

  • 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 6:03 PM

Answers

  • Hi dirtfoot,

    Just replace Parameters!MyMultiValueParameter1.Value with Parameters!MyMultiValueParameter1.Label in the parameter's expression. You ask the same question in this link http://forums.asp.net/t/1561893.aspx I have give you the solution, please click it to get a reference.

    Regards,

    Challen Fu

     

    Monday, May 31, 2010 5:49 AM

All replies

  • You could do this on the T-SQL side:

    WHERE TRIM(MyField) in JOIN(@MyMultiValueParameter1,',')



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    Tuesday, May 25, 2010 6:14 PM
  • You could do this on the T-SQL side:

    WHERE TRIM(MyField) in JOIN(@MyMultiValueParameter1,',')



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.

    This would work if I didn't need to filter on both parameters with an "IN".  One of them I am doing on the query side like you stated above and the other I am not.  They are both filtering on the same value, but in a UNION query.  It would be complicated to take both parameters to the query side.  When I limit the table with a filter on the report side, it will filter the other parameter that is already limited from the query side since it is already filtered with an "IN" on the query side.  Is there a way that I can filter with an "IN" on the table filter, but exclude certain values?
    Tuesday, May 25, 2010 7:26 PM
  • As far as I understand, "in" is not an accepted operator within an SSRS function, so that would make it very difficult to do this on the SSRS side.

    I suppose I'm not understanding why it would be difficult to bring both parameters to the query side.  Couldn't your WHERE clause be like this:

    WHERE (TRIM(MyField) in JOIN(@MyMultiValueParameter1,',')
    or TRIM(MyField) in JOIN(@MyMultiValueParameter2,','))

    Also, if you wanted to exclude certain values, you could have:

    and TRIM(MyField) not in ('Value1','Value2','Value3')



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    Tuesday, May 25, 2010 7:35 PM
  • As far as I understand, "in" is not an accepted operator within an SSRS function, so that would make it very difficult to do this on the SSRS side.

    I suppose I'm not understanding why it would be difficult to bring both parameters to the query side.  Couldn't your WHERE clause be like this:

    WHERE (TRIM(MyField) in JOIN(@MyMultiValueParameter1,',')
    or TRIM(MyField) in JOIN(@MyMultiValueParameter2,','))

    Also, if you wanted to exclude certain values, you could have:

    and TRIM(MyField) not in ('Value1','Value2','Value3')



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.

    It may be possible to bring both parents to the query side.  I think it is going to be tricky though.  There is a lot of special logic pertaining to one of the parents and not the other on the query side. 

    I am using the "IN" operator in the table filter and it works fantastic, but will filter both parents as they are the same field through a union query.  You are correct though that I can't use an "IN" on an expression on the reporting side.
    Wednesday, May 26, 2010 11:04 AM
  • Hi dirtfoot,

    Just replace Parameters!MyMultiValueParameter1.Value with Parameters!MyMultiValueParameter1.Label in the parameter's expression. You ask the same question in this link http://forums.asp.net/t/1561893.aspx I have give you the solution, please click it to get a reference.

    Regards,

    Challen Fu

     

    Monday, May 31, 2010 5:49 AM