RS Howto: Use the IN operator in a filter expression
Filters are not always a good thing in reports as it
generally indicates you are getting more data back from the database than you
need. However I often find that I want to use virtually the same data in a
report jst formatted differently, i.e. a chart, a table, etc. This is were
filters come into their own.
You can filter on many things in a report. Generally these are anything that
has a set of data, i.e. a table, a group, a series for a chart.
I recently wanted to filter using the IN operator, but couldn't find out
how.
I guessed based on previous experience that a delimted lst of values probably
wasn't going to cut it. So I took my delimited list and used the SPLIT
function on it. This returns an array, and it works.
I can only guess that RS loads the array into a dictionary object and does a
lookup against it. For that reason it needs an array that it can loop over to
load into the dictionary.
So an example of an expression is as follows
="Simon,Sabin,SQL,Server".Split(,)
Go into an filters dialog, select the field (or expression) you want to
filter on, select the "In" operator and then enter the expression as above.
Interestingly the Value property of a Multivalue parameter is an array object
already so if you ave one of those in your report you should be able to do
something like,
=Parameters!MyMultiValueParameter.Value
-