Parameters Question! Help Please.

So I have created 6 or 7 drop down lists using seperate datasets for each
drop down and the value field is an integer value and the label field is a
text value. The dropdowns all are set to have a datatype of integer and
allow null value is checked. I also have default value on all of the drop
downs set to 0 which is the ALL value. Now the issue, how can I add them to
the where clause in the data view screen? As soon as I add them to the where
clause and ALL (0) is selected there is no way for me to select all values
back. I don't want all the fields to be manditory, that's why I added the
ALL(0) option. I know I could probably write a stored procedure to do this,
but I was wondering if anyone else has found a way around the issue. Thanks,
all help is greatly appreciatedI'm not sure I understand completely. But here's what I do.
(I use oracle, hope you can translate)
I always add a "All" to my parameters list (suppose the parameter name is
parameter1):
Select value, label from parameters
union
Select -1, 'All' from dual
In the data-queries I than write:
Select ...
from table1
where
(table1.xxx = :parameter1 or :parameter1 = -1)
(oracle uses ":" to denote a parameter, in SQLServer that is a "?" I think)
"Eddie J" wrote:
> So I have created 6 or 7 drop down lists using seperate datasets for each
> drop down and the value field is an integer value and the label field is a
> text value. The dropdowns all are set to have a datatype of integer and
> allow null value is checked. I also have default value on all of the drop
> downs set to 0 which is the ALL value. Now the issue, how can I add them to
> the where clause in the data view screen? As soon as I add them to the where
> clause and ALL (0) is selected there is no way for me to select all values
> back. I don't want all the fields to be manditory, that's why I added the
> ALL(0) option. I know I could probably write a stored procedure to do this,
> but I was wondering if anyone else has found a way around the issue. Thanks,
> all help is greatly appreciated|||Thanks for your response, but in oracle does -1 return everything for that
column? I need All to actually return all? It looks like to me that -1
would just return what ever text is associated to the value -1. Thanks
again, I appreciate your help.
"Antoon" wrote:
> I'm not sure I understand completely. But here's what I do.
> (I use oracle, hope you can translate)
> I always add a "All" to my parameters list (suppose the parameter name is
> parameter1):
> Select value, label from parameters
> union
> Select -1, 'All' from dual
> In the data-queries I than write:
> Select ...
> from table1
> where
> (table1.xxx = :parameter1 or :parameter1 = -1)
> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I think)
> "Eddie J" wrote:
> > So I have created 6 or 7 drop down lists using seperate datasets for each
> > drop down and the value field is an integer value and the label field is a
> > text value. The dropdowns all are set to have a datatype of integer and
> > allow null value is checked. I also have default value on all of the drop
> > downs set to 0 which is the ALL value. Now the issue, how can I add them to
> > the where clause in the data view screen? As soon as I add them to the where
> > clause and ALL (0) is selected there is no way for me to select all values
> > back. I don't want all the fields to be manditory, that's why I added the
> > ALL(0) option. I know I could probably write a stored procedure to do this,
> > but I was wondering if anyone else has found a way around the issue. Thanks,
> > all help is greatly appreciated|||I too use this technique. It does not have to be -1, what it should be to
prevent confusion is a value that does not exist in your data. If it is a
text field I use the word All instead of -1. Here is the key point, you
have an or. The first part of the or statement is looking for a specific
value, the next is seeing if the parameter = this particular value
(table1.xxx = :parameter1 or :parameter1 = -1)
Note the different position the parameter is put (whether it is to the left
or right of the equal size).
Give it a try, it works great.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> Thanks for your response, but in oracle does -1 return everything for that
> column? I need All to actually return all? It looks like to me that -1
> would just return what ever text is associated to the value -1. Thanks
> again, I appreciate your help.
> "Antoon" wrote:
>> I'm not sure I understand completely. But here's what I do.
>> (I use oracle, hope you can translate)
>> I always add a "All" to my parameters list (suppose the parameter name is
>> parameter1):
>> Select value, label from parameters
>> union
>> Select -1, 'All' from dual
>> In the data-queries I than write:
>> Select ...
>> from table1
>> where
>> (table1.xxx = :parameter1 or :parameter1 = -1)
>> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
>> think)
>> "Eddie J" wrote:
>> > So I have created 6 or 7 drop down lists using seperate datasets for
>> > each
>> > drop down and the value field is an integer value and the label field
>> > is a
>> > text value. The dropdowns all are set to have a datatype of integer
>> > and
>> > allow null value is checked. I also have default value on all of the
>> > drop
>> > downs set to 0 which is the ALL value. Now the issue, how can I add
>> > them to
>> > the where clause in the data view screen? As soon as I add them to the
>> > where
>> > clause and ALL (0) is selected there is no way for me to select all
>> > values
>> > back. I don't want all the fields to be manditory, that's why I added
>> > the
>> > ALL(0) option. I know I could probably write a stored procedure to do
>> > this,
>> > but I was wondering if anyone else has found a way around the issue.
>> > Thanks,
>> > all help is greatly appreciated|||Bruce, Antoon,
Thank you so much for you help, this seems to be a great start, but I'm
still running into some issues. Here is the way it looks with 3 parameters
w/ 1 dropdown:
WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=@.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
but as soon as I add another dropdown to the where clause like:
And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
the dataview automatically changes the where clause to look like this:
WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=@.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
(pj.BrandManagerID = @.ABM)
OR
(pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
Which would never give me the results I'm look for. Any ideas? Thanks
again, I'm in learning mode with reporting services.
"Bruce L-C [MVP]" wrote:
> I too use this technique. It does not have to be -1, what it should be to
> prevent confusion is a value that does not exist in your data. If it is a
> text field I use the word All instead of -1. Here is the key point, you
> have an or. The first part of the or statement is looking for a specific
> value, the next is seeing if the parameter = this particular value
> (table1.xxx = :parameter1 or :parameter1 = -1)
> Note the different position the parameter is put (whether it is to the left
> or right of the equal size).
> Give it a try, it works great.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > Thanks for your response, but in oracle does -1 return everything for that
> > column? I need All to actually return all? It looks like to me that -1
> > would just return what ever text is associated to the value -1. Thanks
> > again, I appreciate your help.
> >
> > "Antoon" wrote:
> >
> >> I'm not sure I understand completely. But here's what I do.
> >> (I use oracle, hope you can translate)
> >>
> >> I always add a "All" to my parameters list (suppose the parameter name is
> >> parameter1):
> >> Select value, label from parameters
> >> union
> >> Select -1, 'All' from dual
> >>
> >> In the data-queries I than write:
> >> Select ...
> >> from table1
> >> where
> >> (table1.xxx = :parameter1 or :parameter1 = -1)
> >>
> >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> >> think)
> >>
> >> "Eddie J" wrote:
> >>
> >> > So I have created 6 or 7 drop down lists using seperate datasets for
> >> > each
> >> > drop down and the value field is an integer value and the label field
> >> > is a
> >> > text value. The dropdowns all are set to have a datatype of integer
> >> > and
> >> > allow null value is checked. I also have default value on all of the
> >> > drop
> >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> >> > them to
> >> > the where clause in the data view screen? As soon as I add them to the
> >> > where
> >> > clause and ALL (0) is selected there is no way for me to select all
> >> > values
> >> > back. I don't want all the fields to be manditory, that's why I added
> >> > the
> >> > ALL(0) option. I know I could probably write a stored procedure to do
> >> > this,
> >> > but I was wondering if anyone else has found a way around the issue.
> >> > Thanks,
> >> > all help is greatly appreciated
>
>|||try using "(pj.InitiationDate between @.StartDate AND (@.EndDate + 1))" instead
of the => and <= expression
--
"Everyone knows something you don't know"
"Eddie J" wrote:
> Bruce, Antoon,
> Thank you so much for you help, this seems to be a great start, but I'm
> still running into some issues. Here is the way it looks with 3 parameters
> w/ 1 dropdown:
> WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
> but as soon as I add another dropdown to the where clause like:
> And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
> the dataview automatically changes the where clause to look like this:
> WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
> (pj.BrandManagerID = @.ABM)
> OR
> (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
> AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
> Which would never give me the results I'm look for. Any ideas? Thanks
> again, I'm in learning mode with reporting services.
>
> "Bruce L-C [MVP]" wrote:
> > I too use this technique. It does not have to be -1, what it should be to
> > prevent confusion is a value that does not exist in your data. If it is a
> > text field I use the word All instead of -1. Here is the key point, you
> > have an or. The first part of the or statement is looking for a specific
> > value, the next is seeing if the parameter = this particular value
> >
> > (table1.xxx = :parameter1 or :parameter1 = -1)
> >
> > Note the different position the parameter is put (whether it is to the left
> > or right of the equal size).
> >
> > Give it a try, it works great.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> > news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > > Thanks for your response, but in oracle does -1 return everything for that
> > > column? I need All to actually return all? It looks like to me that -1
> > > would just return what ever text is associated to the value -1. Thanks
> > > again, I appreciate your help.
> > >
> > > "Antoon" wrote:
> > >
> > >> I'm not sure I understand completely. But here's what I do.
> > >> (I use oracle, hope you can translate)
> > >>
> > >> I always add a "All" to my parameters list (suppose the parameter name is
> > >> parameter1):
> > >> Select value, label from parameters
> > >> union
> > >> Select -1, 'All' from dual
> > >>
> > >> In the data-queries I than write:
> > >> Select ...
> > >> from table1
> > >> where
> > >> (table1.xxx = :parameter1 or :parameter1 = -1)
> > >>
> > >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> > >> think)
> > >>
> > >> "Eddie J" wrote:
> > >>
> > >> > So I have created 6 or 7 drop down lists using seperate datasets for
> > >> > each
> > >> > drop down and the value field is an integer value and the label field
> > >> > is a
> > >> > text value. The dropdowns all are set to have a datatype of integer
> > >> > and
> > >> > allow null value is checked. I also have default value on all of the
> > >> > drop
> > >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> > >> > them to
> > >> > the where clause in the data view screen? As soon as I add them to the
> > >> > where
> > >> > clause and ALL (0) is selected there is no way for me to select all
> > >> > values
> > >> > back. I don't want all the fields to be manditory, that's why I added
> > >> > the
> > >> > ALL(0) option. I know I could probably write a stored procedure to do
> > >> > this,
> > >> > but I was wondering if anyone else has found a way around the issue.
> > >> > Thanks,
> > >> > all help is greatly appreciated
> >
> >
> >|||David thanks for you help, but unfortunately it doesn't help? I believe the
engine in the background of the Dataview is re-working my sql query for some
reason. So this works fine (perfect):
(pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division =@.division OR @.Division = 0)
but as soon as I add this to the where clause
and (pj.BrandManagerID = @.ABM or @.ABM = 0) then engine reworks my query to
something that doesn't work:) Like this:
(pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division =@.division OR @.Division = 0) AND (pj.BrandManagerID = @.ABM) OR
(pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division =@.division OR @.Division = 0) AND (@.ABM = 0)
Any help is greatly appreciated! I am working on just creating a stored
procedure to do all this work, but it sure would be good to know if there is
a way to do it with out a stored procedure. Thanks in advance.
Eddie
"David Bienstock" wrote:
> try using "(pj.InitiationDate between @.StartDate AND (@.EndDate + 1))" instead
> of the => and <= expression
> --
> "Everyone knows something you don't know"
>
> "Eddie J" wrote:
> > Bruce, Antoon,
> > Thank you so much for you help, this seems to be a great start, but I'm
> > still running into some issues. Here is the way it looks with 3 parameters
> > w/ 1 dropdown:
> > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
> > but as soon as I add another dropdown to the where clause like:
> > And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
> > the dataview automatically changes the where clause to look like this:
> > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
> > (pj.BrandManagerID = @.ABM)
> > OR
> > (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
> > AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
> > Which would never give me the results I'm look for. Any ideas? Thanks
> > again, I'm in learning mode with reporting services.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I too use this technique. It does not have to be -1, what it should be to
> > > prevent confusion is a value that does not exist in your data. If it is a
> > > text field I use the word All instead of -1. Here is the key point, you
> > > have an or. The first part of the or statement is looking for a specific
> > > value, the next is seeing if the parameter = this particular value
> > >
> > > (table1.xxx = :parameter1 or :parameter1 = -1)
> > >
> > > Note the different position the parameter is put (whether it is to the left
> > > or right of the equal size).
> > >
> > > Give it a try, it works great.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> > > news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > > > Thanks for your response, but in oracle does -1 return everything for that
> > > > column? I need All to actually return all? It looks like to me that -1
> > > > would just return what ever text is associated to the value -1. Thanks
> > > > again, I appreciate your help.
> > > >
> > > > "Antoon" wrote:
> > > >
> > > >> I'm not sure I understand completely. But here's what I do.
> > > >> (I use oracle, hope you can translate)
> > > >>
> > > >> I always add a "All" to my parameters list (suppose the parameter name is
> > > >> parameter1):
> > > >> Select value, label from parameters
> > > >> union
> > > >> Select -1, 'All' from dual
> > > >>
> > > >> In the data-queries I than write:
> > > >> Select ...
> > > >> from table1
> > > >> where
> > > >> (table1.xxx = :parameter1 or :parameter1 = -1)
> > > >>
> > > >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> > > >> think)
> > > >>
> > > >> "Eddie J" wrote:
> > > >>
> > > >> > So I have created 6 or 7 drop down lists using seperate datasets for
> > > >> > each
> > > >> > drop down and the value field is an integer value and the label field
> > > >> > is a
> > > >> > text value. The dropdowns all are set to have a datatype of integer
> > > >> > and
> > > >> > allow null value is checked. I also have default value on all of the
> > > >> > drop
> > > >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> > > >> > them to
> > > >> > the where clause in the data view screen? As soon as I add them to the
> > > >> > where
> > > >> > clause and ALL (0) is selected there is no way for me to select all
> > > >> > values
> > > >> > back. I don't want all the fields to be manditory, that's why I added
> > > >> > the
> > > >> > ALL(0) option. I know I could probably write a stored procedure to do
> > > >> > this,
> > > >> > but I was wondering if anyone else has found a way around the issue.
> > > >> > Thanks,
> > > >> > all help is greatly appreciated
> > >
> > >
> > >|||I know what you're saying but I think if you try
(pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division =@.division OR @.Division = 0) it may not re-work it
"Eddie J" wrote:
> David thanks for you help, but unfortunately it doesn't help? I believe the
> engine in the background of the Dataview is re-working my sql query for some
> reason. So this works fine (perfect):
> (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => @.division OR @.Division = 0)
> but as soon as I add this to the where clause
> and (pj.BrandManagerID = @.ABM or @.ABM = 0) then engine reworks my query to
> something that doesn't work:) Like this:
> (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => @.division OR @.Division = 0) AND (pj.BrandManagerID = @.ABM) OR
> (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => @.division OR @.Division = 0) AND (@.ABM = 0)
> Any help is greatly appreciated! I am working on just creating a stored
> procedure to do all this work, but it sure would be good to know if there is
> a way to do it with out a stored procedure. Thanks in advance.
> Eddie
>
> "David Bienstock" wrote:
> > try using "(pj.InitiationDate between @.StartDate AND (@.EndDate + 1))" instead
> > of the => and <= expression
> > --
> > "Everyone knows something you don't know"
> >
> >
> > "Eddie J" wrote:
> >
> > > Bruce, Antoon,
> > > Thank you so much for you help, this seems to be a great start, but I'm
> > > still running into some issues. Here is the way it looks with 3 parameters
> > > w/ 1 dropdown:
> > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
> > > but as soon as I add another dropdown to the where clause like:
> > > And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
> > > the dataview automatically changes the where clause to look like this:
> > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
> > > (pj.BrandManagerID = @.ABM)
> > > OR
> > > (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
> > > AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
> > > Which would never give me the results I'm look for. Any ideas? Thanks
> > > again, I'm in learning mode with reporting services.
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > I too use this technique. It does not have to be -1, what it should be to
> > > > prevent confusion is a value that does not exist in your data. If it is a
> > > > text field I use the word All instead of -1. Here is the key point, you
> > > > have an or. The first part of the or statement is looking for a specific
> > > > value, the next is seeing if the parameter = this particular value
> > > >
> > > > (table1.xxx = :parameter1 or :parameter1 = -1)
> > > >
> > > > Note the different position the parameter is put (whether it is to the left
> > > > or right of the equal size).
> > > >
> > > > Give it a try, it works great.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> > > > news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > > > > Thanks for your response, but in oracle does -1 return everything for that
> > > > > column? I need All to actually return all? It looks like to me that -1
> > > > > would just return what ever text is associated to the value -1. Thanks
> > > > > again, I appreciate your help.
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > >> I'm not sure I understand completely. But here's what I do.
> > > > >> (I use oracle, hope you can translate)
> > > > >>
> > > > >> I always add a "All" to my parameters list (suppose the parameter name is
> > > > >> parameter1):
> > > > >> Select value, label from parameters
> > > > >> union
> > > > >> Select -1, 'All' from dual
> > > > >>
> > > > >> In the data-queries I than write:
> > > > >> Select ...
> > > > >> from table1
> > > > >> where
> > > > >> (table1.xxx = :parameter1 or :parameter1 = -1)
> > > > >>
> > > > >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> > > > >> think)
> > > > >>
> > > > >> "Eddie J" wrote:
> > > > >>
> > > > >> > So I have created 6 or 7 drop down lists using seperate datasets for
> > > > >> > each
> > > > >> > drop down and the value field is an integer value and the label field
> > > > >> > is a
> > > > >> > text value. The dropdowns all are set to have a datatype of integer
> > > > >> > and
> > > > >> > allow null value is checked. I also have default value on all of the
> > > > >> > drop
> > > > >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> > > > >> > them to
> > > > >> > the where clause in the data view screen? As soon as I add them to the
> > > > >> > where
> > > > >> > clause and ALL (0) is selected there is no way for me to select all
> > > > >> > values
> > > > >> > back. I don't want all the fields to be manditory, that's why I added
> > > > >> > the
> > > > >> > ALL(0) option. I know I could probably write a stored procedure to do
> > > > >> > this,
> > > > >> > but I was wondering if anyone else has found a way around the issue.
> > > > >> > Thanks,
> > > > >> > all help is greatly appreciated
> > > >
> > > >
> > > >|||David, I did try it but received the same results. Thanks for you help!
"David Bienstock" wrote:
> I know what you're saying but I think if you try
> (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => @.division OR @.Division = 0) it may not re-work it
> "Eddie J" wrote:
> > David thanks for you help, but unfortunately it doesn't help? I believe the
> > engine in the background of the Dataview is re-working my sql query for some
> > reason. So this works fine (perfect):
> > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > @.division OR @.Division = 0)
> > but as soon as I add this to the where clause
> > and (pj.BrandManagerID = @.ABM or @.ABM = 0) then engine reworks my query to
> > something that doesn't work:) Like this:
> >
> > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > @.division OR @.Division = 0) AND (pj.BrandManagerID = @.ABM) OR
> > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > @.division OR @.Division = 0) AND (@.ABM = 0)
> >
> > Any help is greatly appreciated! I am working on just creating a stored
> > procedure to do all this work, but it sure would be good to know if there is
> > a way to do it with out a stored procedure. Thanks in advance.
> > Eddie
> >
> >
> > "David Bienstock" wrote:
> >
> > > try using "(pj.InitiationDate between @.StartDate AND (@.EndDate + 1))" instead
> > > of the => and <= expression
> > > --
> > > "Everyone knows something you don't know"
> > >
> > >
> > > "Eddie J" wrote:
> > >
> > > > Bruce, Antoon,
> > > > Thank you so much for you help, this seems to be a great start, but I'm
> > > > still running into some issues. Here is the way it looks with 3 parameters
> > > > w/ 1 dropdown:
> > > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
> > > > but as soon as I add another dropdown to the where clause like:
> > > > And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
> > > > the dataview automatically changes the where clause to look like this:
> > > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
> > > > (pj.BrandManagerID = @.ABM)
> > > > OR
> > > > (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
> > > > AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
> > > > Which would never give me the results I'm look for. Any ideas? Thanks
> > > > again, I'm in learning mode with reporting services.
> > > >
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > I too use this technique. It does not have to be -1, what it should be to
> > > > > prevent confusion is a value that does not exist in your data. If it is a
> > > > > text field I use the word All instead of -1. Here is the key point, you
> > > > > have an or. The first part of the or statement is looking for a specific
> > > > > value, the next is seeing if the parameter = this particular value
> > > > >
> > > > > (table1.xxx = :parameter1 or :parameter1 = -1)
> > > > >
> > > > > Note the different position the parameter is put (whether it is to the left
> > > > > or right of the equal size).
> > > > >
> > > > > Give it a try, it works great.
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> > > > > news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > > > > > Thanks for your response, but in oracle does -1 return everything for that
> > > > > > column? I need All to actually return all? It looks like to me that -1
> > > > > > would just return what ever text is associated to the value -1. Thanks
> > > > > > again, I appreciate your help.
> > > > > >
> > > > > > "Antoon" wrote:
> > > > > >
> > > > > >> I'm not sure I understand completely. But here's what I do.
> > > > > >> (I use oracle, hope you can translate)
> > > > > >>
> > > > > >> I always add a "All" to my parameters list (suppose the parameter name is
> > > > > >> parameter1):
> > > > > >> Select value, label from parameters
> > > > > >> union
> > > > > >> Select -1, 'All' from dual
> > > > > >>
> > > > > >> In the data-queries I than write:
> > > > > >> Select ...
> > > > > >> from table1
> > > > > >> where
> > > > > >> (table1.xxx = :parameter1 or :parameter1 = -1)
> > > > > >>
> > > > > >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> > > > > >> think)
> > > > > >>
> > > > > >> "Eddie J" wrote:
> > > > > >>
> > > > > >> > So I have created 6 or 7 drop down lists using seperate datasets for
> > > > > >> > each
> > > > > >> > drop down and the value field is an integer value and the label field
> > > > > >> > is a
> > > > > >> > text value. The dropdowns all are set to have a datatype of integer
> > > > > >> > and
> > > > > >> > allow null value is checked. I also have default value on all of the
> > > > > >> > drop
> > > > > >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> > > > > >> > them to
> > > > > >> > the where clause in the data view screen? As soon as I add them to the
> > > > > >> > where
> > > > > >> > clause and ALL (0) is selected there is no way for me to select all
> > > > > >> > values
> > > > > >> > back. I don't want all the fields to be manditory, that's why I added
> > > > > >> > the
> > > > > >> > ALL(0) option. I know I could probably write a stored procedure to do
> > > > > >> > this,
> > > > > >> > but I was wondering if anyone else has found a way around the issue.
> > > > > >> > Thanks,
> > > > > >> > all help is greatly appreciated
> > > > >
> > > > >
> > > > >|||In the querie pannel there is an icon (two windows and a pencil), select it.
It will give you a plain text window to write your query and VS will not
interfere
"Eddie J" wrote:
> David, I did try it but received the same results. Thanks for you help!
> "David Bienstock" wrote:
> > I know what you're saying but I think if you try
> > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > @.division OR @.Division = 0) it may not re-work it
> >
> > "Eddie J" wrote:
> >
> > > David thanks for you help, but unfortunately it doesn't help? I believe the
> > > engine in the background of the Dataview is re-working my sql query for some
> > > reason. So this works fine (perfect):
> > > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > > @.division OR @.Division = 0)
> > > but as soon as I add this to the where clause
> > > and (pj.BrandManagerID = @.ABM or @.ABM = 0) then engine reworks my query to
> > > something that doesn't work:) Like this:
> > >
> > > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > > @.division OR @.Division = 0) AND (pj.BrandManagerID = @.ABM) OR
> > > (pj.InitiationDate BETWEEN @.StartDate AND @.EndDate + 1) AND (p.Division => > > @.division OR @.Division = 0) AND (@.ABM = 0)
> > >
> > > Any help is greatly appreciated! I am working on just creating a stored
> > > procedure to do all this work, but it sure would be good to know if there is
> > > a way to do it with out a stored procedure. Thanks in advance.
> > > Eddie
> > >
> > >
> > > "David Bienstock" wrote:
> > >
> > > > try using "(pj.InitiationDate between @.StartDate AND (@.EndDate + 1))" instead
> > > > of the => and <= expression
> > > > --
> > > > "Everyone knows something you don't know"
> > > >
> > > >
> > > > "Eddie J" wrote:
> > > >
> > > > > Bruce, Antoon,
> > > > > Thank you so much for you help, this seems to be a great start, but I'm
> > > > > still running into some issues. Here is the way it looks with 3 parameters
> > > > > w/ 1 dropdown:
> > > > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0)
> > > > > but as soon as I add another dropdown to the where clause like:
> > > > > And (pj.BrandManagerID = @.ABM OR @.ABM = 0)
> > > > > the dataview automatically changes the where clause to look like this:
> > > > > WHERE (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <=> > > > > @.EndDate + 1) AND (p.Division = @.division OR @.Division = 0) AND
> > > > > (pj.BrandManagerID = @.ABM)
> > > > > OR
> > > > > (pj.InitiationDate >= @.StartDate) AND (pj.InitiationDate <= @.EndDate + 1)
> > > > > AND (p.Division = @.division OR @.Division = 0) AND (@.ABM = 0)
> > > > > Which would never give me the results I'm look for. Any ideas? Thanks
> > > > > again, I'm in learning mode with reporting services.
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > I too use this technique. It does not have to be -1, what it should be to
> > > > > > prevent confusion is a value that does not exist in your data. If it is a
> > > > > > text field I use the word All instead of -1. Here is the key point, you
> > > > > > have an or. The first part of the or statement is looking for a specific
> > > > > > value, the next is seeing if the parameter = this particular value
> > > > > >
> > > > > > (table1.xxx = :parameter1 or :parameter1 = -1)
> > > > > >
> > > > > > Note the different position the parameter is put (whether it is to the left
> > > > > > or right of the equal size).
> > > > > >
> > > > > > Give it a try, it works great.
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Eddie J" <EddieJ@.discussions.microsoft.com> wrote in message
> > > > > > news:9DE8649E-B19F-4087-B90B-B0649FD2F13A@.microsoft.com...
> > > > > > > Thanks for your response, but in oracle does -1 return everything for that
> > > > > > > column? I need All to actually return all? It looks like to me that -1
> > > > > > > would just return what ever text is associated to the value -1. Thanks
> > > > > > > again, I appreciate your help.
> > > > > > >
> > > > > > > "Antoon" wrote:
> > > > > > >
> > > > > > >> I'm not sure I understand completely. But here's what I do.
> > > > > > >> (I use oracle, hope you can translate)
> > > > > > >>
> > > > > > >> I always add a "All" to my parameters list (suppose the parameter name is
> > > > > > >> parameter1):
> > > > > > >> Select value, label from parameters
> > > > > > >> union
> > > > > > >> Select -1, 'All' from dual
> > > > > > >>
> > > > > > >> In the data-queries I than write:
> > > > > > >> Select ...
> > > > > > >> from table1
> > > > > > >> where
> > > > > > >> (table1.xxx = :parameter1 or :parameter1 = -1)
> > > > > > >>
> > > > > > >> (oracle uses ":" to denote a parameter, in SQLServer that is a "?" I
> > > > > > >> think)
> > > > > > >>
> > > > > > >> "Eddie J" wrote:
> > > > > > >>
> > > > > > >> > So I have created 6 or 7 drop down lists using seperate datasets for
> > > > > > >> > each
> > > > > > >> > drop down and the value field is an integer value and the label field
> > > > > > >> > is a
> > > > > > >> > text value. The dropdowns all are set to have a datatype of integer
> > > > > > >> > and
> > > > > > >> > allow null value is checked. I also have default value on all of the
> > > > > > >> > drop
> > > > > > >> > downs set to 0 which is the ALL value. Now the issue, how can I add
> > > > > > >> > them to
> > > > > > >> > the where clause in the data view screen? As soon as I add them to the
> > > > > > >> > where
> > > > > > >> > clause and ALL (0) is selected there is no way for me to select all
> > > > > > >> > values
> > > > > > >> > back. I don't want all the fields to be manditory, that's why I added
> > > > > > >> > the
> > > > > > >> > ALL(0) option. I know I could probably write a stored procedure to do
> > > > > > >> > this,
> > > > > > >> > but I was wondering if anyone else has found a way around the issue.
> > > > > > >> > Thanks,
> > > > > > >> > all help is greatly appreciated
> > > > > >
> > > > > >
> > > > > >