Execution hints of Invantive Control Excel-formulas

Wiki (en)
1

Go to Dutch version

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.

The ExecutionHint looks like this in the Excel function wizard:

ExecutionHint
ExecutionHint709×380 5.18 KB

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 #Err instead of #Err.
  • VERBOSENA: show the cause of #N/B (Dutch) and #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 VERBOSEERR,VERBOSENA.
  • ASYNC: currently no meaning.

SHOWPROGRESS

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:

SHOWPROGRESS
SHOWPROGRESS1055×473 21 KB

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.

ALL_ROWS

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.

VERBOSENA

If an Invantive Excel formula has outcome #N/B or #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.”:

image

More tips on analyzing #N/B and #N/A can be found in Hoe los ik een #N/B of #N/A op in Invantive Control for Excel? (Dutch only).

VERBOSEERR

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

The hint VERBOSE is identical in effect to specifying both VERBOSENA and VERBOSEERR.