It's probably my "fault" for having too many if, then statements. :)
But, are there any good ways to reduce runtime for a report?
I have a Consolidating Trial Balance report that is taking more than eight minutes to generate upon clicking "Run".
Like I said, I do have many "copied" if, then statements.
Also, it's pulling data from multiple companies and performing other calculations.
I'm simply wondering if there is something that I can fix, prior to showing a report to an end user.
Thank you!
My first message has been pending for almost a week, lets try again
There are quite a few things that can impact runtimes of reports. You mentioned that you have lots of IF statements, if your Jet functions are within the IF statements, that will have a massive impact on runtime. If you put a Jet function within an Excel function, Jet is unable to batch the queries and must run them one by one. Think about if there is a way to avoid putting the Jet function in an IF statement (perhaps the Jet query is in one cell and the IF statement looks at the result of the Jet query from another cell, then hide what you don't want to see)
Another common mistake I have seen in financial reports is multiplying by -1 to reverse a sign. This is another thing to avoid (performing math on a Jet function) for the same reason as nesting Jet functions in Excel functions. It is always best to put a - in front of the Jet function so =GL(.....)*-1 becomes =-GL(....). Jet will be able to batch the query and Excel will revere the sign after the result is populated.
Other than that, without seeing the report in question to make recommendations specific to your report, that would be the two main things I have seen. You may benefit from working with a consultant, look at some recent posts with replies to find consultants
