Overview
A Velixo report's refresh speed depends on how much data it requests, how Velixo communicates with your ERP, and how often Excel recalculates the workbook. This article covers the settings and report-design techniques you can use to make reports refresh faster.
This article covers:
-
Report-design techniques that reduce how much Velixo and Excel have to process
-
Refresh settings that control how much data is retrieved
-
Connection and parallelism settings that control how Velixo communicates with your ERP
-
Additional settings for large Sage Intacct databases
Performance also depends on factors outside Velixo, including your network connection to the ERP, the ERP server's capacity, your version and edition of Excel, the amount of available RAM, and how many workbooks are open at once. The techniques below address what you can control from within Velixo and Excel.
Designing efficient reports
Use EXPAND functions instead of listing values individually
The Velixo EXPAND functions return many values from a single formula. For example, instead of entering one formula per account to list a 150-account chart of accounts, a single BC.EXPANDACCOUNTRANGE formula returns the entire range as a spill. Fewer formulas mean a smaller workbook that refreshes faster.
Use spill-aware functions
Velixo GL functions are spill-aware: they accept an Excel spill range (for example, C3#) as an argument and return a result that spills to match. Building on the chart-of-accounts example, after BC.EXPANDACCOUNTRANGE produces the list of accounts in a spill, a single BC.ACCOUNTNAME formula referencing that spill returns the name of every account at once. Two formulas replace hundreds, which reduces the processing load on both Excel and Velixo. See .
Avoid volatile Excel functions
Volatile Excel functions force Excel to recalculate the workbook more often than necessary, and each recalculation can trigger data retrieval from the ERP. Avoid these functions in reports where possible. The volatile Excel functions are:
-
NOW -
TODAY -
RAND -
RANDBETWEEN -
OFFSET -
INDIRECT -
INFO(depending on its arguments) -
CELL(depending on its arguments)
For TODAY and NOW, use the non-volatile Velixo equivalents TODAYNV and NOWNV instead.
To learn more, see the Microsoft documentation on volatile functions.
Limit the number of open workbooks
When Excel recalculates, it recalculates all open workbooks. Keeping several reports open at once can substantially slow down a refresh. Close reports you are not actively using.
Choosing the right refresh option
Use Smart Refresh
Smart Refresh retrieves only data that is new or changed in the ERP since the last refresh, so it is substantially faster than a full reload. The Refresh menu options Current Sheet, Entire Workbook, and All Open Workbooks all use Smart Refresh, and Velixo recommends them by default. Use Full Refresh (Slow) only when Smart Refresh cannot detect a change, for example after records are hard-deleted from the ERP or its data structure changes. See Refresh features (NX).
Refresh only what you need
In Velixo NX, prefer Refresh → Current Sheet or Entire Workbook over All Open Workbooks. Refreshing a narrower scope retrieves and recalculates less data.
Refresh on open instead of reloading
In Velixo NX → Options → Refresh Behavior, enabling Refresh data when opening the file performs a Smart Refresh when the workbook opens, pulling in only what is new rather than reloading everything.
Tuning how Velixo communicates with your ERP
The following settings are in Velixo NX → Options → Performance. The defaults suit most workbooks; adjust them when a report makes many data requests, or when you encounter timeouts or throttling. See Options for the full settings reference.
Maximum number of simultaneous ERP data requests in Business Central (default 4, range 1–32). Raising this fetches data from multiple Business Central endpoints in parallel, which can significantly reduce refresh times for workbooks with many independent queries. Setting it too high may cause throttling by the Business Central API, resulting in slower responses or transient errors; lower it if refreshes fail.
Maximum number of simultaneous functions to process. Controls how many functions Velixo evaluates at once. Adjust this to match the resources available on your computer.
Clearing the cache
Velixo caches retrieved ERP data to speed up subsequent refreshes. If a report behaves unexpectedly or refreshes slowly after ERP-side changes, select Velixo NX → Options → Clear all Velixo data caches to force the next refresh to rebuild the cache from scratch.