Overview
Jet Reports includes the Enable Batch Function Execution option.
Background
Certain Jet Reports 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 Jet Reports, if 20 rows were expanded and there were 3 additional Jet 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 Jet Reports expands certain NL(Rows) functions, it first removes the qualifying Jet functions from the same line (saving a “token” with information about each function that is removed).
After the NL(Rows) function is expanded, Jet Reports 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 Jet functions were removed, Jet Reports 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 Jet 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 Jet functions which will be optimized are: NL(First) , NL(Last) , NL([blank]) , NL([number]) , NL(Sum) , GL() , and NF() ...
| Function | Examples |
|---|---|
| NL(First) | =NL("First","Customer","Name","No.",B4) =NL(,"Customer","Name","No.", B4) |
| NL(Last) | =NL("Last","Cust. Ledger Entry","Entry No.","Customer No.",B4) |
| NL([number]) | =NL(1,"Customer","Name","No.",B4) =NL(2,"Cust. Ledger Entry","Entry No.","Customer No.",B4) |
| NL(Sum) | =NL("Sum","G/L Entry","G/L Account No.",G5) |
| GL() | =GL("Balance",G5) |
| NF() | =NF(B5,"Name") =NF(H5,"Account No.") |
Guidelines for Optimizing Functions
- The Jet 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 Jet 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.
| Optmized Example |
|---|
| =-NL(,"G/L Entry","Amount","Entry No.","@@"&$C3,"Posting Date",$C$2) |
| Non-Optimized Examples | Why? |
|---|---|
| = NL(,"G/L Entry","Amount","Entry No.","@@"&$C3,"Posting Date", NP(“DateFilter",$C$2,$C$3) ) |
nested function |
| =NL(,"G/L Entry","Amount","Entry No.","@@"&$C3,"Posting Date", $C$2:$C$5 ) | multi-cell range |
| =NL(,"G/L Entry","Amount","Entry No.","@@"&$C3,"Posting Date", $C$2+1 ) |
math |
| =NL(,"G/L Entry","Amount","Entry No.","@@"&$C3,"Posting Date", {“1/1/2012","1/2/2012"} ) |
array |
| =NL(,"G/L Entry","Amount","Entry No.","@@"&$C3,"Posting Date",$C$2) * -1 | math after function |
- The Jet functions being optimized must all directly reference either the NL(Rows) function itself or another Jet function being optimized. A Jet function which references 2 other Jet functions being optimized will not be optimized (and neither will any Jet 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 Jet function must not return #VALUE! in order to be optimized.
Note: Optimization does *not* apply to the GL("Rows") function for Dynamics GP.
Comments