Greetings, after spending quite a bit of time to get a multi-year Actual and Budget report setup and formatted, the report takes an excess amount of time to run. The details required for the report include at least three years of actuals and two years of budget, with columns for projecting the current year-end balances as well a column for entering adjustments to next year's proposed budget all broken down by various income and expense groupings. I was pleased to have the report work, but using row replicator for my groupings expands the rows so much that the report takes so long to run that it is not really useable. I need to report on 25 active companies in Navigator and we don't consolidate so all reporting is done via JET reports. I considered trying to build a new report using table/report builder/wizard but have not used them yet and unsure how to bring in all the necessary companies, tables etc and not sure how much time it would take to put together such a complex report. Any advice on how to set up a new report similar to the one attached using Table/Report builder/wizard etc would be appreciated.
Budget Report - Multi Year Budget Review vs 1.04 to JET.xlsx
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Greetings Hughes,
After implementing all of your suggestions and adding addtional filtering in Columns C and D to restrict the number of rows replicted, the report runs in under 20 minutes for 4 companies. This is acceptable and works fine. Thanks for all your assistance with the report. Bob T -
Jet Reports Historic Posts Hi,
This really isn't the type of report that is possible to create using a tool like the Table Builder, Report Builder, etc.
I don't really know how many rows are being replicated on the report, but I'm guessing it's a lot and that consequently there are a LOT of NL(Sum) functions on this report. If you are using NAV 2013, then I'm guessing this is going to be quite slow and even if you are using an earlier version of NAV it may be pretty slow.
One thing that may help is that I notice you are doing a lot of ISERR checks and then nesting your NL(Sum) formulas inside IF statements to check for earlier errors. This is going to make things a lot slower. So a better way to solve this is to use @@ before the cell reference in your nested NL(Rows) and your NL(Sum) functions so that they handle blanks better. For example, your NL(Rows=2) function in C61 could return a blank, so your NL(Rows) function in E62 needs to be able to handle that circumstance without returning a #VALUE. So you could modify it to this:=nl("Rows","Version Line Worksheet","Budgeting Code",0,$F$4,"Budgeting Code",Budget,"Department Code","@@"&$D62,"Fund No.",Fund)
This should return a blank instead of a #VALUE, assuming you don't have blank department codes. Then your NL(Sum) functions such as the one in K62 would look like this:=nl("Sum","G/L Entry",$F$7,"Transaction Type",K$14,0,$F$4,"Posting Date",K$8,"G/L Account No.",$G62,"Budgeting Code","@@"&$H62,"Department Code","@@"&$D62,"Fund No.",$F$10)
This should mimic the logic of your current IF functions. Of course you can also do something similar to the NL(First) functions in I62 and J62.
Once you have made these changes to your NL(Rows), NL(Sum), and NL(First) functions, there is actually an optimization called Batch Function Optimization in the latest version of Jet 2013 which should kick in to optimize the evaluation of these functions a little bit more which should also help.
The other thing you should look into is whether there are appropriate keys with sum indexes in your NAV database for the G/L Entry for your functions. For example, in the case of the function I just mentioned, you would want a key on the G/L Entry with the Transaction Type, Posting Date, G/L Account No., Budgeting Code, Department Code, and Fund No. fields on the key with a sum index for the Amount field. Of course, some of those may be advanced dimensions, in which case this won't work, but if they are Global Dimensions, then you can use the Global Dimension 1 Code and Global Dimension 2 Code fields which should work. If you can do that, then Jet can use the sum indexes to perform the sums which is much much faster.
Anyway, I hope some of this helps. Good luck!
Regards,
Hughes -
Jet Reports Historic Posts Thanks Hughes,
I have updated the file with the "@@"& which cleaned up many of the formulas which are now easier to write and to read! Thanks for that tip!
You mentioned "I notice you are doing a lot of ISERR checks." Is there a clean up on that as well?
Would it work to swamp out the NL(Sum for GL(Balance? As I understand it the GL function did not work with Navigator in the past but it does now. Is that worth pursuing as well? If this will pull the balance based on the filters given I would think that would save time since Excel would not have to do the calculations.
With the changes suggested, other than the KEY, on one company my report finished in 24 minutes which is an improvement!
Thanks for all the suggestions to this point as they have proved very helpful.
Regards, Bob T -
Jet Reports Historic Posts Bob,
As far as the ISERR checks, I think if you use @@ for the filters then most of those won't be relevant anymore since those formulas will no longer return #VALUE's. Is that not the case? Mainly my recommendation was that it's better for performance for the formulas to return blanks than #VALUEs. There is definitely a performance penalty when Jet returns a #VALUE rather than just a blank. So if you use the @@ filters then your formulas return blank instead of #VALUE and then you don't need to do an ISERR check generally speaking.
As far as the NL(Sum) vs. GL, they should perform basically the same. Maybe you would save a tiny anount of time parsing the functions, but it won't be noticable when running the report, and the actual database query will be exactly the same in either case.
I'm glad the changes improved the runtime!
Regards,
Hughes
Please sign in to leave a comment.