Example for IN Operator?

I think I'd like to use the IN operator in a filter expression in a SQL
Reporting Services report and I cannot find documentation for the syntax or
an example showing its use. The variations of delimiters I've tried have not
worked. Is there documentation or an example out there showing use of the IN
operator in a filter expression?
Sorry if I'm overlooking something obvious.
Thank you,
MikeMike,
See if this link helps:
http://solidqualitylearning.com/Blogs/dejan/archive/2004/10/22/200.aspx
Craig|||Thanks, Craig, but no. To clarify, I have a matrix in my report. I want to
create a filter in the properties of the matrix, and I want to use the IN
operator in that filter expression. The link you provide, as best I can tell,
discusses SQL syntax. The data source for my report is a dataset so the data
has already been retrieved from the database. The unfiltered data is used
elsewhere in the report so I can't look to filter at the query level.
I thought the syntax in the Value portion of the filter would be something
like
=("value1", "value2")
but that gives me a syntax error.
Thanks,
Mike
"Craig" wrote:
> Mike,
> See if this link helps:
> http://solidqualitylearning.com/Blogs/dejan/archive/2004/10/22/200.aspx
> Craig|||Mike,
OK I see what you are doing.
Did you notice that there is an IN in the dropdown list for Operators.
Then I believe the value field just accepts a comma seperated string list,
but I haven't tested it myself.
Craig|||Hi Craig:
Yes, I did notice that there is an IN operator in the dropdown list for
Operators when building a filter. That's how I discovered using IN was a
possibility.
As noted in my previous post, I too thought a simple comma-delimited list of
strings would work in the Value field, but I get a syntax error in the
Expression editor when I try that. Specifically, the following when supplied
as the Value for a filter expression is signalled as invalid in the
Expression editor, with the red squiggly line under the comma seperating the
strings:
=("value1", "value2")
So, I'm still looking for documentation or an example showing use of the IN
operator in a matrix filter expression. Are there any out there?
Thanks,
Mike
"Craig" wrote:
> Mike,
> OK I see what you are doing.
> Did you notice that there is an IN in the dropdown list for Operators.
> Then I believe the value field just accepts a comma seperated string list,
> but I haven't tested it myself.
> Craig|||Hi Wally,
The "IN" operator expects a set of values as in the following XML:
<Filter>
<Operator>In</Operator>
<FilterValues>
<FilterValue>=1</FilterValue>
<FilterValue>=2</FilterValue>
</FilterValues>
<FilterExpression>=Fields!ProductID.Value</FilterExpression>
</Filter>
If you put multiple expressions into the filter value field, the designer
will interpret that as one string/expression, which wouldn't work. In order
to achieve what you want here, you can use the "=" operator, and add one
filter per value. The designer is smart enough to detect the filter
expressions are the same, and convert it into the above syntax in the RDL
file to use the in operator.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you, Wei Lu, I see now: the IN operator is really not a
"user-enterable" operator, but is instead an operator that the Report
Designer uses to handle OR conditions. There are no examples showing how to
enter filter expressions with an IN operator because you cannot enter such
expressions. In fact, it would probably be a good idea if the IN operator
didn't appear in the dropdown list because you can't really use it.
Thanks!
"Wei Lu [MSFT]" wrote:
> Hi Wally,
> The "IN" operator expects a set of values as in the following XML:
>
> <Filter>
> <Operator>In</Operator>
> <FilterValues>
> <FilterValue>=1</FilterValue>
> <FilterValue>=2</FilterValue>
> </FilterValues>
> <FilterExpression>=Fields!ProductID.Value</FilterExpression>
> </Filter>
> If you put multiple expressions into the filter value field, the designer
> will interpret that as one string/expression, which wouldn't work. In order
> to achieve what you want here, you can use the "=" operator, and add one
> filter per value. The designer is smart enough to detect the filter
> expressions are the same, and convert it into the above syntax in the RDL
> file to use the in operator.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hello Wally,
Actually, if you are using the Multi-value parameter, you could use the IN
operator.
For example, assuming the EmployeeID parameter type is set to string, the
following filter should work:
Expression: =CStr(Fields!EmployeeID.Value)
Operator: IN
Value: =Parameters!EmployeeID.Value
The Parameter EmployeeID should be a Multi-value parameter so that you
could use it in the IN operator.
Hope this additional information will be helpful for you to understand the
IN operator.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.