Answered by:

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.

Answers

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.

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?

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.

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.