Filters using an 'OR' condition

I am dealing with a set of 5 parameters. I want to filter the data based on
these parameters using the 'or' condition instead of an 'And'. In the filter
tab the condition defaults to an 'And' and is greyed out. How do I change it
to use an 'Or' condition.You can rewrite filter to use singe expression that combines all parameters
instead of 5 separate expressions.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sumi" <Sumi@.discussions.microsoft.com> wrote in message
news:9BD59230-0A07-4AA3-9E58-4F11640D3D4A@.microsoft.com...
>I am dealing with a set of 5 parameters. I want to filter the data based on
> these parameters using the 'or' condition instead of an 'And'. In the
> filter
> tab the condition defaults to an 'And' and is greyed out. How do I change
> it
> to use an 'Or' condition.|||Combine the in one expression
fe
filter-expresion:
Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
filter-value:
=1
"Sumi" wrote:
> I am dealing with a set of 5 parameters. I want to filter the data based on
> these parameters using the 'or' condition instead of an 'And'. In the filter
> tab the condition defaults to an 'And' and is greyed out. How do I change it
> to use an 'Or' condition.|||Thank you very much! This made things very clear.
"Antoon" wrote:
> Combine the in one expression
> fe
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
> "Sumi" wrote:
> > I am dealing with a set of 5 parameters. I want to filter the data based on
> > these parameters using the 'or' condition instead of an 'And'. In the filter
> > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > to use an 'Or' condition.|||Antoon,
Thanks a bundle.I tried this and it works wonderfully. But this has
triggered off another question.
I have a stored proc that takes in Startdate and Enddate as two parameters.
Then I have a set of 5 parameters which I am using to further filter the
data, this is to give the user flexibility to choose whichever parameter they
want. These parameters have a drodown box which has all the values they can
select along with a blank row, incase the user does not want to use that
parameter to filter.
Using the expression described below, I can filter by one or more
parameters, but if I select blank for all the 5 parameter, I was expecting
the expression to be ignored and the result set to have all data for the
entered Startdate and EndDate. Which are not a part of this filter
expression. But the report pulls up blank.
Any ideas as to how to fix this?
filter-expresion:
Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
filter-value:
=1
"Antoon" wrote:
> Combine the in one expression
> fe
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
> "Sumi" wrote:
> > I am dealing with a set of 5 parameters. I want to filter the data based on
> > these parameters using the 'or' condition instead of an 'And'. In the filter
> > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > to use an 'Or' condition.|||I use a specific value for the all parameter
Select parname, parvalue from parlist
union
select '<All>', -1 from dual
You can then filter in the query
Select ...
where
... (table.value = :parameter or :paremeter = -1)
or you can change the filter, and add
Parameters!par1.Value = -1, 1
to the switch statement
(I work in oracle)
"Sumi" wrote:
> Antoon,
> Thanks a bundle.I tried this and it works wonderfully. But this has
> triggered off another question.
> I have a stored proc that takes in Startdate and Enddate as two parameters.
> Then I have a set of 5 parameters which I am using to further filter the
> data, this is to give the user flexibility to choose whichever parameter they
> want. These parameters have a drodown box which has all the values they can
> select along with a blank row, incase the user does not want to use that
> parameter to filter.
> Using the expression described below, I can filter by one or more
> parameters, but if I select blank for all the 5 parameter, I was expecting
> the expression to be ignored and the result set to have all data for the
> entered Startdate and EndDate. Which are not a part of this filter
> expression. But the report pulls up blank.
> Any ideas as to how to fix this?
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
>
> "Antoon" wrote:
> > Combine the in one expression
> > fe
> > filter-expresion:
> > Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> > filter-value:
> > =1
> > "Sumi" wrote:
> >
> > > I am dealing with a set of 5 parameters. I want to filter the data based on
> > > these parameters using the 'or' condition instead of an 'And'. In the filter
> > > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > > to use an 'Or' condition.|||The only way I've achieved any 'clever' from parameters regardin
optional parameters ands and ors is by building up a where string o
the fly
DECLARE @.select as varchar(100
DECLARE @.where as varchar(100
DECLARE @.sql as varchar(100
SET @.select = 'SELECT * FROM poext WHERE '
SET @.where = ''
IF @.vendor <> 'not set' SET @.where = @.where + 'vendor = '''
@.vendor + ''''
IF len(@.where) > 0 SET @.where = @.where + ' and '
IF @.buyer <> - 1 SET @.where = @.where + 'buyer = '
cast(@.buyer as varchar(5))
if len(@.where)>0 and right(@.where,5) <> ' an
' SET @.where = @.where + ' and '
set @.where = @.where + 'orddate between '''+@.orderdatefrom + ''' an
''' + @.orderdateto + ''''
SET @.sql = @.select + @.where
EXEC sp_executesql @.sq
And use that as my dataset query. This works fine, although you hav
to refresh it a few times or the preview doesn't work, it tends t
get a little confused as it has no idea about the data you're pullin
until it's got it.[/code