Hello all
Am I missing a trick or does there seem to be a drawback in using parameters
in Reporting services. They do not act the same way as for example a prompt
in business objects . When a user specifies a value in a prompt in BO this
will alter the Where clause of the sql behind the report- therefore this will
usually decrease the amount of time the report takes to run.
However I have just created a parameter in RS and far from decreasing the
amount of time the report takes to run it increases it. It seems that it
acts as a filter on the sql already generated. It is bringing back all
values before restricting it. Am i doing something wrong? or is a parameter
more like a filter than a condition? If so - how do i create a condition
that the user can fulfill? arrgghh!!! lol - gregI think you hit the nail on the head. Parameters in Rpt Services are like
filters. My understanding is that regardless of the parameter/filter
setting, all of the data is returned, then filtered after the fact.
Now that may ot be true if you use parameters in your SQL dataset itself.
If your dataset calls a stored proc that takes in parameters directly that
would clearly limit the resultset and return less data. It would have to.
Another nice benefit of using a stored proc with it's own parameters is that
if you define your dataset this way in the wizard, Rpt Services will
automatically recognize that your dataset has parameters and include them in
the RDL as report parameters. Might be worth a try.
sebring1130
"Greg" wrote:
> Hello all
> Am I missing a trick or does there seem to be a drawback in using parameters
> in Reporting services. They do not act the same way as for example a prompt
> in business objects . When a user specifies a value in a prompt in BO this
> will alter the Where clause of the sql behind the report- therefore this will
> usually decrease the amount of time the report takes to run.
> However I have just created a parameter in RS and far from decreasing the
> amount of time the report takes to run it increases it. It seems that it
> acts as a filter on the sql already generated. It is bringing back all
> values before restricting it. Am i doing something wrong? or is a parameter
> more like a filter than a condition? If so - how do i create a condition
> that the user can fulfill? arrgghh!!! lol - greg
>|||I'm looking at a SQL Profiler trace of RS executing a report and it is
sending a parameratized query to SQL via sp_executesql.
What is the relative time increase?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Tue, 9 Nov 2004 08:29:01 -0800, "Greg"
<Greg@.discussions.microsoft.com> wrote:
>Hello all
>Am I missing a trick or does there seem to be a drawback in using parameters
>in Reporting services. They do not act the same way as for example a prompt
>in business objects . When a user specifies a value in a prompt in BO this
>will alter the Where clause of the sql behind the report- therefore this will
>usually decrease the amount of time the report takes to run.
>However I have just created a parameter in RS and far from decreasing the
>amount of time the report takes to run it increases it. It seems that it
>acts as a filter on the sql already generated. It is bringing back all
>values before restricting it. Am i doing something wrong? or is a parameter
>more like a filter than a condition? If so - how do i create a condition
>that the user can fulfill? arrgghh!!! lol - greg|||No, you are definitely wrong here. I go against tables with between 1
million and 10 million rows and they are definitely not all coming back and
being filtered.
My guess is that a filter is being used, not a query parameter. Report
parameters are flexible, they can be used multiple ways. It is important to
understand the difference between a query parameter, a filter and a report
parameter. A report parameter can be used with either a filter or as a query
paramter. If the query string does not have a @.queryparamname (if SQL
Server) or a ? (oledb) then a filter is being used instead of query
parameters. The query parameter has to be defined and then mapped to the
report parameter. When a query parameter is defined RS automatically creates
the report parameter. If named parameters (going against SQL Server) then it
will name the report parameter the same way, otherwise it names it something
generic. I always rename the automatically created report parameter and then
remap the query parameters (click on the ..., go to the parameters tab).
Whether it is a stored procedure or not makes no difference. The only thing
is with a stored procedure RS will identify the stored procedure parameters
and create the report parameters for you. Otherwise you have to edit the sql
string and add the query parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sebring1130" <sebring1130@.discussions.microsoft.com> wrote in message
news:2F7303D3-E85C-4440-BC7F-ADF33BA5E2E4@.microsoft.com...
> I think you hit the nail on the head. Parameters in Rpt Services are like
> filters. My understanding is that regardless of the parameter/filter
> setting, all of the data is returned, then filtered after the fact.
> Now that may ot be true if you use parameters in your SQL dataset itself.
> If your dataset calls a stored proc that takes in parameters directly that
> would clearly limit the resultset and return less data. It would have to.
> Another nice benefit of using a stored proc with it's own parameters is
that
> if you define your dataset this way in the wizard, Rpt Services will
> automatically recognize that your dataset has parameters and include them
in
> the RDL as report parameters. Might be worth a try.
> sebring1130
>
> "Greg" wrote:
> > Hello all
> >
> > Am I missing a trick or does there seem to be a drawback in using
parameters
> > in Reporting services. They do not act the same way as for example a
prompt
> > in business objects . When a user specifies a value in a prompt in BO
this
> > will alter the Where clause of the sql behind the report- therefore this
will
> > usually decrease the amount of time the report takes to run.
> >
> > However I have just created a parameter in RS and far from decreasing
the
> > amount of time the report takes to run it increases it. It seems that
it
> > acts as a filter on the sql already generated. It is bringing back all
> > values before restricting it. Am i doing something wrong? or is a
parameter
> > more like a filter than a condition? If so - how do i create a
condition
> > that the user can fulfill? arrgghh!!! lol - greg
> >|||I guess I wasn't clear enough, but that is basically what I said. That
report parameters are only filters of the returned sql dataset - unless the
report parameters are also query parameters being passed to the SQL proc
"directly that would clearly limit the resultset and return less data." I
wasn't 100% sure about that last part ... thanks for clarifying.
sebring1130
"Bruce L-C [MVP]" wrote:
> No, you are definitely wrong here. I go against tables with between 1
> million and 10 million rows and they are definitely not all coming back and
> being filtered.
> My guess is that a filter is being used, not a query parameter. Report
> parameters are flexible, they can be used multiple ways. It is important to
> understand the difference between a query parameter, a filter and a report
> parameter. A report parameter can be used with either a filter or as a query
> paramter. If the query string does not have a @.queryparamname (if SQL
> Server) or a ? (oledb) then a filter is being used instead of query
> parameters. The query parameter has to be defined and then mapped to the
> report parameter. When a query parameter is defined RS automatically creates
> the report parameter. If named parameters (going against SQL Server) then it
> will name the report parameter the same way, otherwise it names it something
> generic. I always rename the automatically created report parameter and then
> remap the query parameters (click on the ..., go to the parameters tab).
> Whether it is a stored procedure or not makes no difference. The only thing
> is with a stored procedure RS will identify the stored procedure parameters
> and create the report parameters for you. Otherwise you have to edit the sql
> string and add the query parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "sebring1130" <sebring1130@.discussions.microsoft.com> wrote in message
> news:2F7303D3-E85C-4440-BC7F-ADF33BA5E2E4@.microsoft.com...
> > I think you hit the nail on the head. Parameters in Rpt Services are like
> > filters. My understanding is that regardless of the parameter/filter
> > setting, all of the data is returned, then filtered after the fact.
> >
> > Now that may ot be true if you use parameters in your SQL dataset itself.
> > If your dataset calls a stored proc that takes in parameters directly that
> > would clearly limit the resultset and return less data. It would have to.
> > Another nice benefit of using a stored proc with it's own parameters is
> that
> > if you define your dataset this way in the wizard, Rpt Services will
> > automatically recognize that your dataset has parameters and include them
> in
> > the RDL as report parameters. Might be worth a try.
> >
> > sebring1130
> >
> >
> > "Greg" wrote:
> >
> > > Hello all
> > >
> > > Am I missing a trick or does there seem to be a drawback in using
> parameters
> > > in Reporting services. They do not act the same way as for example a
> prompt
> > > in business objects . When a user specifies a value in a prompt in BO
> this
> > > will alter the Where clause of the sql behind the report- therefore this
> will
> > > usually decrease the amount of time the report takes to run.
> > >
> > > However I have just created a parameter in RS and far from decreasing
> the
> > > amount of time the report takes to run it increases it. It seems that
> it
> > > acts as a filter on the sql already generated. It is bringing back all
> > > values before restricting it. Am i doing something wrong? or is a
> parameter
> > > more like a filter than a condition? If so - how do i create a
> condition
> > > that the user can fulfill? arrgghh!!! lol - greg
> > >
>
>
