Ways to Improve Performance
Performance improvement tips
There are a variety of factors that can impact the performance of a Velixo report, including:
the speed of the connection to Acumatica
the speed of the server on which Acumatica sits
the design of the report
whether or not the report contains volatile Excel functions
the version and edition of Excel
the amount or RAM available
the speed of the computer
the number of other reports that are currently open
etc.
Here are few suggestions that might help you:
Tip 1 - Use GI filters
If using generic inquires (which can return *a lot* of data), be sure to filter the data down to just what is actually needed. While Acumatica's OData feed is quite fast, if you try to retrieve 10s or 100s of thousands of records, it is still going to take a while.
Tip 2 - Smart Refresh
When using (non-grouped) generic inquiries, it is best to ensure that the default refresh method is configured for Smart Refresh (where only new data is retrieved from the database) rather than Full Refresh (which brings in all data which matches the specified filters).
Tip 3 - List functions
Use of the Velixo LIST functions can really be beneficial. These functions (such as EXPANDACCOUNTRANGE) return multiple values. For example, rather than listing our entire chart of accounts in our report (e.g., ~150 values), we can use one function to return the entire CoA. This makes our workbooks smaller, and they refresh faster.
Tip 4 - Use arrays
Along those same line, using Velixo's "array aware" capabilities can really speed things up. Let's consider the CoA example. WE have one functions that returns the entire CoA. We can then use one function (ACCOUNTNAME) to refer to the *entire* array and it will return the name of every account in our CoA. In this way, we have entered two functions (instead of 300) to get that same information. This can save a lot of processing by Excel and Velixo.
Tip 5 - Avoid volatile functions
Workbooks containing volatile Excel functions will cause Excel to recalculate the workbook more often. Typically, we don't notice this because most workbooks are fairly static - there's not much changing. In a report, however, recalculating can cause a delay as data is retrieved for Acumatica.
Tip 6 - Limit open workbooks
Keep in mind that when Excel recalculates, it recalculates *all* open workbooks. Thus, having multiple workbooks (especially reports) open can have a substantial impact on the performance of a report.