Report Parameter help

In am trying to create a report using a stored procedure having two
parameters. The Avalilable values for the parameters come from queries. The
user while running the report may not select one of the parameters i.e. leave
it blank. When I leave one of the parameters blank the report does not run, I
have to select some value for both parameters for report to run.
Can Someone let me know how to run a report without passing a value in
parameter.
Thanks
AnoopHi Anoop
You will always have to pass the parameter to the report because the
stored procedure is expecting it and won't run unless it has it.
The way round this is to pass a null value and allow for it in your
stored procedure.
Wherever you use the parameter in the stored procedure, you could use
the following syntax:
(@.QueueId IS NULL OR QueueId = @.QueueId)
eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
This would mean that if the @.QueueId parameter is null, it will ignore
it and return all the queues.
Hope this helps?
Regards
Dave|||Hi Dave,
Thanks for the response.
I am already handling NULLs in the Stored procedure. The problem that I am
having is that my report parameters are populated using queries which do not
have null, also user may not select all report parameters. When user does not
select a parameter the report does not pass NULL to sp infact it does not run
at all. Is there a way around this.
Thanks and Regards,
Anoop
"Bungle" wrote:
> Hi Anoop
> You will always have to pass the parameter to the report because the
> stored procedure is expecting it and won't run unless it has it.
> The way round this is to pass a null value and allow for it in your
> stored procedure.
> Wherever you use the parameter in the stored procedure, you could use
> the following syntax:
> (@.QueueId IS NULL OR QueueId = @.QueueId)
> eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> This would mean that if the @.QueueId parameter is null, it will ignore
> it and return all the queues.
> Hope this helps?
> Regards
> Dave
>|||Unless you allow null or blank in the report, or have a default value , the
user MUST select a value for each parameter or the report will not run...
If your issue is that you wish to account for Null or blanks, then the prior
respondant's method is commonly used, although it may perform badly..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anoop" <Anoop@.discussions.microsoft.com> wrote in message
news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> Hi Dave,
> Thanks for the response.
> I am already handling NULLs in the Stored procedure. The problem that I am
> having is that my report parameters are populated using queries which do
not
> have null, also user may not select all report parameters. When user does
not
> select a parameter the report does not pass NULL to sp infact it does not
run
> at all. Is there a way around this.
> Thanks and Regards,
> Anoop
>
> "Bungle" wrote:
> > Hi Anoop
> >
> > You will always have to pass the parameter to the report because the
> > stored procedure is expecting it and won't run unless it has it.
> >
> > The way round this is to pass a null value and allow for it in your
> > stored procedure.
> >
> > Wherever you use the parameter in the stored procedure, you could use
> > the following syntax:
> >
> > (@.QueueId IS NULL OR QueueId = @.QueueId)
> >
> > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> >
> > This would mean that if the @.QueueId parameter is null, it will ignore
> > it and return all the queues.
> >
> > Hope this helps?
> >
> > Regards
> >
> > Dave
> >
> >|||I think I cannot do what I am trying to do through reporting services as each
parameter must be supplied with a value for a report to run.
Thanks a lot for your help.
"Wayne Snyder" wrote:
> Unless you allow null or blank in the report, or have a default value , the
> user MUST select a value for each parameter or the report will not run...
> If your issue is that you wish to account for Null or blanks, then the prior
> respondant's method is commonly used, although it may perform badly..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> > Hi Dave,
> > Thanks for the response.
> >
> > I am already handling NULLs in the Stored procedure. The problem that I am
> > having is that my report parameters are populated using queries which do
> not
> > have null, also user may not select all report parameters. When user does
> not
> > select a parameter the report does not pass NULL to sp infact it does not
> run
> > at all. Is there a way around this.
> >
> > Thanks and Regards,
> >
> > Anoop
> >
> >
> > "Bungle" wrote:
> >
> > > Hi Anoop
> > >
> > > You will always have to pass the parameter to the report because the
> > > stored procedure is expecting it and won't run unless it has it.
> > >
> > > The way round this is to pass a null value and allow for it in your
> > > stored procedure.
> > >
> > > Wherever you use the parameter in the stored procedure, you could use
> > > the following syntax:
> > >
> > > (@.QueueId IS NULL OR QueueId = @.QueueId)
> > >
> > > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> > >
> > > This would mean that if the @.QueueId parameter is null, it will ignore
> > > it and return all the queues.
> > >
> > > Hope this helps?
> > >
> > > Regards
> > >
> > > Dave
> > >
> > >
>
>|||Can you modify the query that loads the parameters to add another line like
'None' that has a value of NULL? This would be similar to the method used to
add an 'All' selection.
Actually, I may need to try this myself for 1 of my reports.
Neil
"Anoop" wrote:
> I think I cannot do what I am trying to do through reporting services as each
> parameter must be supplied with a value for a report to run.
> Thanks a lot for your help.
> "Wayne Snyder" wrote:
> > Unless you allow null or blank in the report, or have a default value , the
> > user MUST select a value for each parameter or the report will not run...
> >
> > If your issue is that you wish to account for Null or blanks, then the prior
> > respondant's method is commonly used, although it may perform badly..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> > news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> > > Hi Dave,
> > > Thanks for the response.
> > >
> > > I am already handling NULLs in the Stored procedure. The problem that I am
> > > having is that my report parameters are populated using queries which do
> > not
> > > have null, also user may not select all report parameters. When user does
> > not
> > > select a parameter the report does not pass NULL to sp infact it does not
> > run
> > > at all. Is there a way around this.
> > >
> > > Thanks and Regards,
> > >
> > > Anoop
> > >
> > >
> > > "Bungle" wrote:
> > >
> > > > Hi Anoop
> > > >
> > > > You will always have to pass the parameter to the report because the
> > > > stored procedure is expecting it and won't run unless it has it.
> > > >
> > > > The way round this is to pass a null value and allow for it in your
> > > > stored procedure.
> > > >
> > > > Wherever you use the parameter in the stored procedure, you could use
> > > > the following syntax:
> > > >
> > > > (@.QueueId IS NULL OR QueueId = @.QueueId)
> > > >
> > > > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> > > >
> > > > This would mean that if the @.QueueId parameter is null, it will ignore
> > > > it and return all the queues.
> > > >
> > > > Hope this helps?
> > > >
> > > > Regards
> > > >
> > > > Dave
> > > >
> > > >
> >
> >
> >|||Hi,
I will try to be as clear as possible, excuse my english
What you will need to do is for the SP's that are used to generate the
parameter list add a value that will mean "All Possible value" like so:
create procedure <ParamList>
AS
declare @.paramtable TABLE (ordersequence TINYINT,
id INT,
VALUE VARCHAR(100))
insert into @.paramtable values (1,0,"All Possible values")
insert into @.paramtable
select 1,id,value
from table1
select id,value
from @.paramtable
order by ordersequence,value
Then you will need to setup your parameter in RS and define the default
value of this parameter to 0.
You will also need to modify your report SP to manage the case of the "All
Possible values" param value...
Something like this should work
Create Procedure <Report>
(
@.ParamValue INT
)
AS
select col1,col2,col3,col4
from table1
where col1 = case when @.ParamValue = 0 then col1
else @.ParamValue
end
HTH,
Aiwa
"Anoop" wrote:
> In am trying to create a report using a stored procedure having two
> parameters. The Avalilable values for the parameters come from queries. The
> user while running the report may not select one of the parameters i.e. leave
> it blank. When I leave one of the parameters blank the report does not run, I
> have to select some value for both parameters for report to run.
> Can Someone let me know how to run a report without passing a value in
> parameter.
> Thanks
> Anoop|||Hi,
Thanks for your help.
As I have 10 parameters for my report. I have now created tables which have
0 or blank as a value. These tables are used to populate the Lists for report
parameters. It is a long process but it is working.
Hope there was a simpler solution. Anyways..
Thanks
Anoop
"Aiwa" wrote:
> Hi,
> I will try to be as clear as possible, excuse my english
> What you will need to do is for the SP's that are used to generate the
> parameter list add a value that will mean "All Possible value" like so:
> create procedure <ParamList>
> AS
> declare @.paramtable TABLE (ordersequence TINYINT,
> id INT,
> VALUE VARCHAR(100))
> insert into @.paramtable values (1,0,"All Possible values")
> insert into @.paramtable
> select 1,id,value
> from table1
> select id,value
> from @.paramtable
> order by ordersequence,value
>
> Then you will need to setup your parameter in RS and define the default
> value of this parameter to 0.
> You will also need to modify your report SP to manage the case of the "All
> Possible values" param value...
> Something like this should work
> Create Procedure <Report>
> (
> @.ParamValue INT
> )
> AS
> select col1,col2,col3,col4
> from table1
> where col1 = case when @.ParamValue = 0 then col1
> else @.ParamValue
> end
> HTH,
> Aiwa
> "Anoop" wrote:
> > In am trying to create a report using a stored procedure having two
> > parameters. The Avalilable values for the parameters come from queries. The
> > user while running the report may not select one of the parameters i.e. leave
> > it blank. When I leave one of the parameters blank the report does not run, I
> > have to select some value for both parameters for report to run.
> >
> > Can Someone let me know how to run a report without passing a value in
> > parameter.
> >
> > Thanks
> > Anoopsql