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
> > > > > >
> > > > > >
> > > > > >

Parameters from Dropdownlist

In SSRS 2000 how can I have the parameter filled in the drop downlist from
the database table? My query is: I want the city field filled in from the
column. Thanks in advance.
SELECT FNAME, LNAME, .EMAIL, .PHONE_NBR, CITY
FROM MR_MEMBER_1
WHERE (MEMBER_STATUS = 'A') AND (CITY = @.CITY)Go into you parameter and check the box that says "From Query" then choose
the dataset you want to use and then choose the from the value field list
the field that you want to run the parameter off of! Hope that helps...
"Shan Afzal" <Shan Afzal@.discussions.microsoft.com> wrote in message
news:89935558-78A2-441D-A410-A7C51E056189@.microsoft.com...
> In SSRS 2000 how can I have the parameter filled in the drop downlist from
> the database table? My query is: I want the city field filled in from
> the
> column. Thanks in advance.
> SELECT FNAME, LNAME, .EMAIL, .PHONE_NBR, CITY
> FROM MR_MEMBER_1
> WHERE (MEMBER_STATUS = 'A') AND (CITY = @.CITY)|||Go into you parameter and check the box that says "From Query" then choose
the dataset you want to use and then choose the from the value field list
the field that you want to run the parameter off of! Hope that helps...
"Shan Afzal" <Shan Afzal@.discussions.microsoft.com> wrote in message
news:89935558-78A2-441D-A410-A7C51E056189@.microsoft.com...
> In SSRS 2000 how can I have the parameter filled in the drop downlist from
> the database table? My query is: I want the city field filled in from
> the
> column. Thanks in advance.
> SELECT FNAME, LNAME, .EMAIL, .PHONE_NBR, CITY
> FROM MR_MEMBER_1
> WHERE (MEMBER_STATUS = 'A') AND (CITY = @.CITY)|||Ben, It gives me the follwoing error when I do what you explained.
The report parameter â'CITYâ' has a DefaultValue or a ValidValue that depends
on the report parameter â'CITYâ'. Forward dependencies are not valid.
Build complete -- 1 errors, 0 warnings
Thanks
"Ben Watts" wrote:
> Go into you parameter and check the box that says "From Query" then choose
> the dataset you want to use and then choose the from the value field list
> the field that you want to run the parameter off of! Hope that helps...
> "Shan Afzal" <Shan Afzal@.discussions.microsoft.com> wrote in message
> news:89935558-78A2-441D-A410-A7C51E056189@.microsoft.com...
> > In SSRS 2000 how can I have the parameter filled in the drop downlist from
> > the database table? My query is: I want the city field filled in from
> > the
> > column. Thanks in advance.
> >
> > SELECT FNAME, LNAME, .EMAIL, .PHONE_NBR, CITY
> > FROM MR_MEMBER_1
> > WHERE (MEMBER_STATUS = 'A') AND (CITY = @.CITY)
>
>|||You could try and create a different dataset that is exactly the same as
your current one and name it city parameter or something like that. Then
inside your report parameter for the values, change the dataset to the one
you just created and choose the field from that! Let me know.....
"Shan Afzal" <ShanAfzal@.discussions.microsoft.com> wrote in message
news:3A6CE6FD-1BEA-459C-B223-B9B3952782CE@.microsoft.com...
> Ben, It gives me the follwoing error when I do what you explained.
> The report parameter 'CITY' has a DefaultValue or a ValidValue that
> depends
> on the report parameter "CITY". Forward dependencies are not valid.
> Build complete -- 1 errors, 0 warnings
> Thanks
> "Ben Watts" wrote:
>> Go into you parameter and check the box that says "From Query" then
>> choose
>> the dataset you want to use and then choose the from the value field list
>> the field that you want to run the parameter off of! Hope that helps...
>> "Shan Afzal" <Shan Afzal@.discussions.microsoft.com> wrote in message
>> news:89935558-78A2-441D-A410-A7C51E056189@.microsoft.com...
>> > In SSRS 2000 how can I have the parameter filled in the drop downlist
>> > from
>> > the database table? My query is: I want the city field filled in from
>> > the
>> > column. Thanks in advance.
>> >
>> > SELECT FNAME, LNAME, .EMAIL, .PHONE_NBR, CITY
>> > FROM MR_MEMBER_1
>> > WHERE (MEMBER_STATUS = 'A') AND (CITY = @.CITY)
>>|||Got it Ben. Work like a champ. Thanks for your help.
"Ben Watts" wrote:
> You could try and create a different dataset that is exactly the same as
> your current one and name it city parameter or something like that. Then
> inside your report parameter for the values, change the dataset to the one
> you just created and choose the field from that! Let me know.....
> "Shan Afzal" <ShanAfzal@.discussions.microsoft.com> wrote in message
> news:3A6CE6FD-1BEA-459C-B223-B9B3952782CE@.microsoft.com...
> > Ben, It gives me the follwoing error when I do what you explained.
> >
> > The report parameter 'CITY' has a DefaultValue or a ValidValue that
> > depends
> > on the report parameter "CITY". Forward dependencies are not valid.
> > Build complete -- 1 errors, 0 warnings
> >
> > Thanks
> >
> > "Ben Watts" wrote:
> >
> >> Go into you parameter and check the box that says "From Query" then
> >> choose
> >> the dataset you want to use and then choose the from the value field list
> >> the field that you want to run the parameter off of! Hope that helps...
> >>
> >> "Shan Afzal" <Shan Afzal@.discussions.microsoft.com> wrote in message
> >> news:89935558-78A2-441D-A410-A7C51E056189@.microsoft.com...
> >> > In SSRS 2000 how can I have the parameter filled in the drop downlist
> >> > from
> >> > the database table? My query is: I want the city field filled in from
> >> > the
> >> > column. Thanks in advance.
> >> >
> >> > SELECT FNAME, LNAME, .EMAIL, .PHONE_NBR, CITY
> >> > FROM MR_MEMBER_1
> >> > WHERE (MEMBER_STATUS = 'A') AND (CITY = @.CITY)
> >>
> >>
> >>
>
>

parameters

Hello,

i am new to ssrs and am trying to generate a report with 4 parameters. 2 of which are dates. The other 2 are drop downlists. Now the report works fine when i enter all 4 parameters. But in some cases i want to leave one of the parameters unentered . It doesnt all me to do that. Gives a error saying i need to enter the parameter. How do u get aroud this issue ?

I have seperate dataset for this parameters list and am using a where clause in my main query.

Can anyone please help me out with this?

Thx

Ashish

the parameters which are not needed all times can be set as

1. under parameter properties check all ow null values option

2. take default value for parameter as null

hope these will solve u r problem

|||

the parameters which are not needed all times can be set as

1. under parameter properties check allow null values option

2. take default value for parameter as null

hope these will solve u r problem

|||

hello rakam,

Thx for the reply.

My problem still continues. i tried ur suggestion but it still prompts me to select that paramater when trying to view report.

Any other options with be appreciated.

Thx

Ashish

|||The only thing you should have to do is define a valid Default value in the parameter setup in Design view of the report. If you still have the problem after saving and redeploying the report, you may want to delete the report from the report server and then deploy it again. Sometimes the subtle updates don't post properly unless you delete first.|||Read this article http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1061210&SiteID=1

parameters

Hello,

i am new to ssrs and am trying to generate a report with 4 parameters. 2 of which are dates. The other 2 are drop downlists. Now the report works fine when i enter all 4 parameters. But in some cases i want to leave one of the parameters unentered . It doesnt all me to do that. Gives a error saying i need to enter the parameter. How do u get aroud this issue ?

I have seperate dataset for this parameters list and am using a where clause in my main query.

Can anyone please help me out with this?

Thx

Ashish

the parameters which are not needed all times can be set as

1. under parameter properties check all ow null values option

2. take default value for parameter as null

hope these will solve u r problem

|||

the parameters which are not needed all times can be set as

1. under parameter properties check allow null values option

2. take default value for parameter as null

hope these will solve u r problem

|||

hello rakam,

Thx for the reply.

My problem still continues. i tried ur suggestion but it still prompts me to select that paramater when trying to view report.

Any other options with be appreciated.

Thx

Ashish

|||The only thing you should have to do is define a valid Default value in the parameter setup in Design view of the report. If you still have the problem after saving and redeploying the report, you may want to delete the report from the report server and then deploy it again. Sometimes the subtle updates don't post properly unless you delete first.|||Read this article http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1061210&SiteID=1