Hi,
Do you know if there is any (significant) difference when it comes to performance (as in shorter running time) between using NL(Rows) with Excel totaling or NL(SUM)?
Thanks,
Elena
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi Elena -
There are a couple of ways to look at that question.
One way: "Is there a difference [when using an NL(Rows) function] between allowing Excel to perform totaling or using the NL(Sum) function to do the totaling?"
But what I think you are asking is: "Can I get better performance using a single NL(Sum) to total up a number of values or is it faster to use the NL(Rows) function to return the values and have Excel sum them up?"
With smaller numbers of records in the database, any difference would probably be minuscule.
As a general rule however, think of it this way:
Using the NL(Rows) function, Essentials has to ask the database for a list of the values. The database server has to get those and send them to Essentials. Essentials then needs to expand the rows within Excel to accommodate the number of values returned and place those values in the new rows. Excel can then sum up those values.
When using the NL(Sum) function, Essentials asks the database for that sum. The database server performs the calculation and returns the single value, which is then placed in the Excel cell.
Databases tend to perform such calculations *very* quickly. Thus, the general rule is that the NL(Sum) would be faster.
As an example, I referenced about 132K records from one of my tables in a Dynamics NAV database - using NL(Rows) to return a single field and the Excel SUM() function to add them up. With that many records, this took about 4 times longer than simply using the NL(Sum) function.
Jet Reports is continuously looking at ways to optimize the performance of each product. The upcoming release of Jet Essentials 2016 includes a Web Portal that can run the reports without Excel, at all - Excel is simply used to view the results. Early testing shows that this is actually faster than the current release of Essentials. More research is being done to look into how this type of functionality can be evolved.
Best,
HP -
Jet Reports Historic Posts What HPDeskJet said.
I just did a Sales report that includes weight, gross revenue, average price per pound and average price per foot (we buy material by the pound and sell it by the foot). I use NL("Sum") functions and it's a pretty quick report. I can't imagine trying to pull every sales line for all of 2015 and then summing in Excel. My report would be a monster and IT would beat me to death when I tried to e-mail it out to multiple people.
So, it's not only a matter of calculation speed, it's also a matter of file size. Let the server do the heavy mental lifting. Use the spreadsheet to display stuff. -
Jet Reports Historic Posts Thank you for your response. That is what I also thought, but…
In my reports case it turned out that NL(Rows) was the fastest way to go :)
The NL(SUM) had a lot of filters from other tables than the base one. This really slowed down my report…
I created a new sheet for my calculations, added a 4 criteria grouping, then conditional Excel totaling and it works great!
Thank you!
Please sign in to leave a comment.