Hello,
I have attached the report I'm working on and gives me some headaches, mostly because of it's running time.
So, the report sums up quantities from the last version of sales orders archive and groups them by customer, on each sheet. I optimized as much as I could think of the formulas, but when I run the report I can see the following behavior:
- as you can see, the example report has 3 sheets / customers: CARACTERES, EDIMETA and SAS FITER
- when I run the report and follow the Details, when it gets to EDIMETA's transactions first it sums the quantities for CARACTERES and then it gets to EDIMETA
- when it gets to SAS FITER's transactions, first it sums the quantities for CARACTERES, then the ones for EDIMETA and then for SAS FITER.
So, for each new sheet per customer it goes thru the other sheets and makes the calculations over and over again, expanding my running time.
Is this a normal behavior for the NL(Sheets) function? Or am I doing something wrong?
Thanks,
Elena
Date
Votes
4 comments
-
Jet Reports Historic Posts Quick observations:
Put the frontpage C12 =TODAY() function on Options sheet within NP("Eval").
Then refer to it from C11.
TODAY is a volatile function and could be your problem of the constant re-calculations.
C12 = NP("Eval";"=Local(TODAY())")
C11 = DATE(YEAR(C12);1;1)
On the reports sheet cell D2 put the + within "+Sell-to Customer Name".
It is good you using NP("Eval";"='Options!….'") better is to use a named range.
So on the options sheet give the cell a name for example "OptionItem" then on the reports sheet refer to it with NP("Eval";"=OptionItem")
That way if you move around cells on the options sheet your report does not break. -
Jet Reports Historic Posts Hi Sebastian,
Thanks for the quick reply.
Following your observations I changed TODAY() with NP(Eval) on the Options sheet.
I tried your other recommendation with the "+Sell-to Customer Name", but I get a #VALUE error - it doesn't recognize "+Sell-to Customer Name" as a valid field. So I changed the formula for D2 = NL("Sheets";"Sales Header Archive";"Sell-to Customer Name";"+Sell-to Customer Name";"*";"Filters=";$F$4:$G$8).
Unfortunately, the behavior with calculating all the previous customers for each sheet it's still there.
Elena -
Jet Reports Historic Posts Hi Elena,
The behavior you're seeing is normal. What happens is that between replicator expansion on each sheet, Jet tells Excel to calculate the formulas in the report (this is required due to cell dependencies and functions which due not get calculated during replicator expansion). When this happens, Excel will sometimes calculate functions on other sheets. However, you should know that any Jet functions that have already been calculated as part of the report run will have their values cached, so they should not take much time at all to calculate. Also, replicators on each sheet will only get expanded once.
Actually the thing that is probably adding the most time to your report run is having the details expanded on the Jet progress window. Having the details expanded during the report run can add significantly to the run time of the report, so you should leave them closed unless there is a specific reason to expand them.
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
This is what I wanted to know - if this is the normal behavior or if I need to drastically improve my approach on this report.
I only expanded the details on the Jet progress window because I needed to see why the calculations on my third customer (with only one line to show) is taking so much time.
Thank you,
Elena
Please sign in to leave a comment.