Options

Created by Damien Zwillinger, Modified on Thu, 24 Oct at 2:00 PM by Harry Lewis

Several options are available to control certain features or the behavior of Velixo NX based on your preference, such as:

Refresh behavior
Account balance sign
Drilldown sheet destination
Transaction drilldown presentation
Blank rows handling in "Hide Zero Rows"
Generic inquiries options
- Refresh Mode
- Key columns
Missing identifier behavior
Options for large Sage Intacct databases
Maximum number of operations per API request in Sage Intacct
Maximum number of simultaneous functions to process
Advanced Options

By clicking the Options button on the Velixo NX ribbon...

... the Velixo NX side panel is opened, allowing you to update the options that determine how Velixo NX operates:

(note: not all options are available for all ERP connection types)

Refresh behavior

Refresh data when opening the file: When checked, a Smart Refresh will be performed when the workbook is opened (drawing in any data that is new in the ERP since the last refresh of the Velixo functions).

Refresh external data sources and PivotTables when refreshing this workbook: When checked, this setting will automatically refresh both PivotTables and non-Velixo external data sources when using the Velixo NX Refresh feature.

Account balance sign

(applies to Acumatica, Cegid, Haufe 360, Jamis Prime, and MYOB connections)

Controls the way the balances of the liability and income GL accounts are represented in your worksheets. The following options are available:

Normal (Default value): The credit balances for liability and income accounts are displayed as positive numbers, and the debit balances are displayed as negative numbers. In this mode, the total balance of all asset and expense accounts is equal to the total balance of all liability and income accounts (and Assets + Expenses = Liabilities + Income)

Reversed: The credit balances of liability and income accounts are displayed as negative numbers, and the debit balances are displayed as positive numbers. In this mode, the total balance of all accounts is equal to zero (i.e., Assets + Expenses + Liabilities + Income = 0)

This setting only impacts functions returning data from the GL module (e.g., AccountBeginningBalance, AccountTurnover, AccountEndingBalance). Data from other modules (e.g., Project Module) is not affected.

Drilldown sheet destination

Controls whether drilldown sheets are created in the current workbook or if they are created in a separate workbook.

Transaction drilldown presentation

(applies to Sage Intacct connections)

Controls whether transaction drilldown into Sage Intacct directs to the default Summary (GLENTRY) screen or the Detail (GLDETAIL) screen.

Blank rows handling in "Hide Zero Rows"

Controls how blank rows/columns should be handled after hiding zero rows/columns in a report. There are three possible settings:

Preserve all blank rows (default value): Rows/columns with blank values will remain visible in the report
Hide all blank rows: Blanks will be treated the same as zeros and those rows/columns will be hidden
Hide consecutive blank rows, keep at most one: All but the first of consecutive blank rows/columns will be hidden; only the first will be remain visible in the report

Generic inquiries options

(applies to Acumatica, Cegid, Haufe 360, Jamis Prime, and MYOB connections)

If your report contains Velixo GI functions, the Options window includes a setting section for each Generic Inquiry referenced from within the report:

Using the edit ("pencil") icon, the side panel displays the options for the specific generic inquiry:

Refresh Mode

Allows you to control how much data is retrieved from the database. There are two options for Refresh Mode: Smart Refresh and Full Refresh.

Key columns

Key columns are those fields which uniquely identify each record in the Generic Inquiry. An example of a unique field for a list of GL accounts would be the AccountCode. Another example for invoice document lines would be the combination of Document Type, Invoice Number, and Line Number.

NOTE: Typically, a Description field or an Account Class would not suffice as a key field (since there are many duplicate values in tables that contain such fields). Doing so would result in an error message when using GI functions with that Generic Inquiry.

Also note that there is no universally applicable unique field, and a set of keys would typically differ across Generic Inquiries. For example, AccountCode can guarantee uniqueness in the chart of accounts, but would not suffice as a key in a list of GL transactions, where multiple lines can contain the same account.

When used with Full Refresh, Velixo NX does not require the configuration of key fields for use with the GI function. Keys are necessary only if we want to use Smart Refresh and/or if we want to use the GILOOKUP function.

Note: The ERP only supports the use of Smart Refresh with those generic inquiries that do not contain binary or date fields which include the words "modif" or "stamp" (e.g., LastModifiedDateTime, LastModified, tstamp, timestamp).  For inquiries that do include such fields, only the option for Full Refresh will be available.

Important: ... If a generic inquiry (which has been used within the report) subsequently becomes unavailable (by being removed from either the ERP or its OData feed), a notification icon will appear to the left and the edit icon will be removed from the right of the Generic Inquiries Options section...

... and Excel will display a #VALUE! message wherever that inquiry is referenced within the workbook:

Missing identifier behavior

This setting determines if Velixo functions will return zero if one or more of the specified values are not found in the current tenant. This option is useful when creating reports that consolidate data from multiple tenants with different configuration. You must refresh your report after changing this option.

Note: When using the Sage Intacct ERP and referencing an existing account group to which no accounts have been assigned, the Velixo functions will ALWAYS return zero, regardless of the setting of this option.

Options for large Sage Intacct databases

(applies to Sage Intacct connections)

Use asynchronous data retrieval. If unchecked, required data will be retrieved from Sage Intacct all at one time (regardless of how long it takes). For very large datasets, this can sometimes take long enough that the Sage Intacct server will discontinue the data request. Enabling this option will retrieve the data in several smaller subsets. This will take some extra time but will allow the data to be retrieved.

Only load balances for accounts specified in the workbook. If unchecked, all Sage Intacct account balances will be retrieved and cached. This speeds subsequent retrieval of data. For very large charts of accounts, this can take more time than is desired. By enabling this option, balances will be retrieved for ONLY those accounts specifically listed in the workbook.

Maximum number of operations per API request in Sage Intacct

(applies to Sage Intacct connections)

Maximum number of simultaneous functions to process

This setting configures the maximum number of functions that Velixo will attempt to evaluate at any one time. This can be adjusted to accommodate the resources available on your system.

Advanced Options

The Forget all connection credentials setting allows you to require that new credentials be entered for all connections in the workbook.

Clear all Velixo data caches provides the same functionality as clicking the Clear Cache button on the Velixo NX ribbon button but does so for all connections at one time.