Most Excel formulas of Invantive Control for Excel have an
ExecutionHint parameter. The behavior of a formula can be changed via the
ExecutionHint parameter. The hint is often used when figuring out why an Excel formula has an unexpected outcome.
ExecutionHint looks like this in the Excel function wizard:
You can specify no, one or multiple execution hints. If more than one hint is specified, they must be separated by a comma (",").
The following hints can be used:
ALL_ROWS: retrieve all data for a query, even if only one data row is needed.
VERBOSEERR: show the cause of
VERBOSENA: show the cause of
#N/A(English Excel version) instead of this result.
SHOWPROGRESS: when evaluating an Excel formula, repeatedly show a window with intermediate numbers and dates that collectively constitute the outcome.
VERBOSE: equal to
ASYNC: currently no meaning.
The use of
SHOWPROGRESS via the
ExecutionHint parameter causes formulas that process data from a data platform to produce a final result (such as a balance sheet position) to display, upon evaluation, a window containing the underlying temporary figures and data:
Many Excel functions reuse the data to increase performance. This progress window is only shown when the data is freshly retrieved. Log on again to empty this so-called “cache” and force display of the progress window.
The specification of
ALL_ROWS via the
ExecutionHint parameter can sometimes lead to performance gains when many of the same Excel formulas are used, such as when retrieving the name of a customer in Exact Online. If the data is requested thousands of times for a different customer each time, it is cheaper to retrieve all the data at once with the first formula that is being evaluated.
The use of
ALL_ROWS is superfluous for the financial Exact Online formulas such as balance sheet and P&L amounts. These are already processed in an advanced and fast way.
You can check through the table
UDFMeasuremenets@InvantiveControl how the data is processed by the Excel formulas. See Analyze Performance of Invantive's Excel Formulas for more information.
If an Invantive Excel formula has outcome
#N/A, then specify a hint “VERBOSENA” or “VERBOSE” in the formula via the parameter
ExecutionHint. The cause of the outcome will then be shown as outcome of the formula. In the image below, the top formula returns a #N/B because the Invantive Control model is not present. The lower formula uses “VERBOSENA” to find out the cause. The result of the formula then explains the cause: “#NA: No model and open connection.”:
More tips on analyzing
#N/A can be found in Hoe los ik een #N/B of #N/A op in Invantive Control for Excel? (Dutch only).
If an Invantive Excel formula has #Err as result, then specify a hint “VERBOSEERR” or “VERBOSE” in the formula via the parameter
ExecutionHint. The cause will then be shown as an outcome.
VERBOSE is identical in effect to specifying both