Mike Dinnis said:
I'm sure this has been posted before but I can't find where...
Is it better to do calculations in a query or a report? I'm anticipating
producing a grid of figures for a year to display in a report and want to
know if it's better to do each field calc in the report or in the query?
It depends.
If you need to sort on the result of a calculation, then it needs to be in the query.
If you need running calculations or any calculation that needs to reference other
*rows* as opposed to other *fields* then you are better off doing that in the Report.
If you are using a server back end like SQL Server you have to weigh the pros/cons of
doing the calculation on the server versus doing it locally.
There was a time when "All processing on the server" was always the best route
because the servers were so much more powerful. Today, depending on your company's
server replacement interval, there will often be many clients connecting to the
server that are significantly more powerful than the server is. What is still very
important though is to do as much on the server as possible that limits the amount of
data returned because network bandwidth is still precious. Once your server returns
'n' rows it could very well be a toss-up whether the server or client PC can do
calculations on those rows faster than the other.
In the end, as long as the result is correct and you are satisfied with how quickly
the report runs, then it shouldn't make much difference.
