Breadcrumbs

Ways to Improve Performance (Classic)

Overview

Velixo Classic report's refresh speed depends on how much data it requests from your ERP and how often Excel recalculates the workbook. This article covers the settings and report-design techniques you can use to make Classic reports refresh faster.

This article covers:

  • Report-design techniques that reduce how much Velixo and Excel have to process

  • Choosing between Smart Refresh and Full Refresh

  • Filtering generic inquiries so the ERP returns less data

  • Speeding up formula removal and report distribution

If you use Velixo NX, see Ways to Improve Performance instead. NX offers refresh scopes and connection tuning settings that Classic does not have. For a feature-by-feature comparison, see Comparing Velixo NX and Velixo Classic.

Performance also depends on factors outside Velixo, including your network connection to the ERP, the ERP server's capacity, your version 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 arrays and cell ranges as arguments

Classic GL functions accept Excel array references and cell ranges for any argument, so a single formula can return results for many accounts at once. For example, if your report lists 150 account codes in A2:A151, one ACCOUNTNAME formula referencing that range returns the name of every account, instead of 150 separate formulas. Fewer formulas mean a smaller workbook and less processing for both Excel and Velixo. See Using arrays or cell references as parameters in GL functions.

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, use the non-volatile Velixo equivalent TODAYNV instead, available in Velixo Classic 7.1.150 and higher.

To learn more, see the Microsoft documentation on volatile functions.

Close workbooks you are not using

This matters more in Classic than in NX. Both Classic refresh options recalculate every open workbook, and Classic offers no way to narrow a refresh to the current worksheet or workbook. Keeping several reports open at once can substantially slow down a refresh, so close the reports you are not actively using.

Choosing the right refresh option

Classic offers two refresh options: Smart Refresh and Full Refresh.

Prefer Smart Refresh

Smart Refresh loads only data that is new or updated in the ERP since the last refresh, and it picks up changes to the chart of accounts and subaccounts. Because it retrieves only a subset of the data, it is substantially faster than a Full Refresh, and Velixo recommends it by default. See Refresh features (Classic).

Full Refresh ignores cached data and reloads everything associated with the Velixo functions in the workbook, so it takes longer. Use it only when Smart Refresh cannot detect a change, which happens when:

  • Records were hard-deleted from the ERP rather than deactivated

  • An SQL script changed records without updating their timestamps

  • Data access classes changed, or a customization with data schema changes was published

  • Custom fields were added

To keep Smart Refresh effective, deactivate records and revert transactions in your ERP instead of hard-deleting them.

Refresh on open instead of reloading

In the Velixo ribbon, open Options and enable Refresh data when opening the file. Velixo then performs a Smart Refresh when the workbook opens, pulling in only what is new rather than reloading everything. See Options (Classic).

Do not refresh while a VBA macro is running

Refreshing a report while a VBA script is running can trigger an error. Let the script finish before you refresh.

Filtering generic inquiries

Generic inquiries can return very large result sets. Filtering the data down to only what the report needs significantly reduces load time, because the filter is applied by Acumatica before the data is downloaded. Even though Acumatica's OData feed is fast, retrieving tens or hundreds of thousands of records is slow.

If you experience long load times for inquiries with a very large result set (for example, GL transactions or invoices), always supply a filter using the GIFILTER function in your GI formulas. See GIFILTER.

Smart Refresh for a generic inquiry requires key columns that uniquely identify each record. Until you configure them, only Full Refresh is available for that inquiry. For setup steps, see Setting up Smart Refresh for Generic Inquiries (Classic) and Generic Inquiries Options (Classic).

Speeding up formula removal and distribution

If report distribution with Excel (Remove All Formulas) or the Copy Without Formulas feature runs slowly, enable Use an alternative algorithm when removing all formulas in Options. Use this option only if you have experienced problems with those features, or if Velixo Technical Support directs you to. See Copy Workbook Without Formulas (Classic).