Batch Function Execution Optimization

Overview

Reporting Intelligence includes the Enable Batch Function Execution option.

Background

Certain Reporting Intelligence functions are referred to as "Replicating" functions.

The most common of these is the NL("Rows") function.  This function makes copies of itself (and everything else on that row) as it expands through the data it returns.

In earlier versions of Reporting Intelligence, if 20 rows were expanded and there were 3 additional Reporting Intelligence functions [e.g, NF() functions] on each row, this would result in 60 additional queries being sent to the database.

"Batch Function Optimization" can help improve your report's performance in situations like this.

How does Batch Function Optimization Work?

Before Reporting Intelligence expands certain NL(Rows) functions, it first removes the qualifying Reporting Intelligence functions from the same line (saving a “token” with information about each function that is removed).

After the NL(Rows) function is expanded, Reporting Intelligence goes through those tokens and executes all the queries needed for each one - as a single batch.

So, to use our earlier example, if 20 rows were expanded and 3 Reporting Intelligence functions were removed, Reporting Intelligence will send just 3 queries into the data source, rather than 60 queries.

In the case of NF() functions, the optimization is even better (as long as no flow filters are applied) since only 1 query will be sent for all NF functions referencing the same key.

For Which Data Sources Does Optimization Work?

While all data source types get some boost from this process, the NAV Web Service Data Source (NAV 2013 and later) is currently optimized to take full advantage of the benefits.

Which Functions are Optimized?

Only Reporting Intelligence functions within the inner-most nested NL(Rows) function are optimized. An NL(Rows) function is "inner-most" when there are no nested NL(Rows) functions inside it.

The Reporting Intelligence functions which will be optimized are: NL(First) , NL(Last) , NL([blank]) , NL([number]) , NL(Sum) , GL() , and NF() ...

Guidelines for Optimizing Functions

• The Reporting Intelligence function cannot be inside another function or have any Excel math done on it in the cell. The one exception to this rule is that a Reporting Intelligence function can have a negative sign in front of it and it will still be optimized.
• Arguments in queries must be simple for optimization to work - either single values or single cell references. Complex arguments (such as nested functions, cell ranges, math, concatenation, etc.) will all cause a function not to be optimized. Oftentimes, the math or the embedded function can be moved to another cell and be referenced there, instead.
• If the argument is a cell reference...
• it cannot reference an NL(Filter) function.
• a cell reference argument is allowed to have “@@”& in front of it.
• named ranges must reference a single cell.

• The Reporting Intelligence functions being optimized must all directly reference either the NL(Rows) function itself or another Reporting Intelligence function being optimized. A Reporting Intelligence function which references 2 other Reporting Intelligence functions being optimized will not be optimized (and neither will any Reporting Intelligence function referencing it).
• Replicators that replicate less than 5 rows will not be optimized since the overhead involved in the optimization could take longer than the time saved by doing it.
• The Reporting Intelligence function must not return #VALUE! in order to be optimized.