Calculate in report or in stored procedure

I have a requirement for two reports, which are basically except that one
shows values, whereas the other shows percentages. I would like to code this
as one report that modifies its display behaviour based on a parameter that
is passed in ( value 'P' or 'V').
The format of the cells in the grid will be an expression, giving integer
for values and one decimal place for percenatges.
If the percentages version is requested I can do the percentage calculation
in one of two places...
1. in the report with an expression against the cell Value.
2. in the stored procedure so that percentages are returned.
Which is best practice? If ReportingServices is running on the same SQL
Server as the source database is there a preference for overall performance?
Thanks,
AndrewHello Andrew,
Calculating in Report will run the calculating works in the
ReportingService process.
My suggestion is that you could calculating in the stored procedure which
is faster.
Also for the performance consideration, you could use the Report Cache.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||On Jun 4, 10:28 am, Duke (AN247) <D...@.newsgroup.nospam> wrote:
> I have a requirement for two reports, which are basically except that one
> shows values, whereas the other shows percentages. I would like to code this
> as one report that modifies its display behaviour based on a parameter that
> is passed in ( value 'P' or 'V').
> The format of the cells in the grid will be an expression, giving integer
> for values and one decimal place for percenatges.
> If the percentages version is requested I can do the percentage calculation
> in one of two places...
> 1. in the report with an expression against the cell Value.
> 2. in the stored procedure so that percentages are returned.
> Which is best practice? If ReportingServices is running on the same SQL
> Server as the source database is there a preference for overall performance?
> Thanks,
> Andrew
My personal preference is to do as much as possible in the stored
procedure. As far as performance gain, it depends on the size of the
returned dataset to the report and how complicated the expressions are
in the report. That said, you should be able to use some fairly simple
expressions to achieve the results you are looking for. So you could
use an expression similar to this in the Format property:
=iif(Parameters!ParameterName.Value = "P", #,0.0%, #,0)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks. I'd been leaning towards doing as much in the sproc as possible which
ties in with your answers. So, I'll calculate the percentage in the sproc
and the format in the report.
Cheers,
Andrew|||On Jun 5, 6:03 am, Duke (AN247) <D...@.newsgroup.nospam> wrote:
> Thanks. I'd been leaning towards doing as much in the sproc as possible which
> ties in with your answers. So, I'll calculate the percentage in the sproc
> and the format in the report.
> Cheers,
> Andrew
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant