Improving Report Performance

Overview

• If using Dynamics NAV, apply filters in your reports so that that they match a key that is already in your NAV Database.  Also, when applying sorts in lists, use a sort order that matches a Dynamics NAV key.
• When returning multiple pieces of detail about a record (e.g., Customer Name, No., and Address), if possible, use NL functions to retrieve record keys rather than unique values.  Then use the NF function to obtain field values from those record keys - this can help because an NF function does not have to make a request to the database, while an NL(First) function does.
• To retrieve a single piece of data that is in a row or column that already contains a replicator (i.e. the name of a customer), use an NL() function with First as the "What" parameter.  Generally we do not recommend multiple replicators on the same line.
• When filtering across tables, be sure to choose either NL("Filter") or "Link=" appropriately (see Filtering Based On Data from Another Table ).

• When using Excel 2007 or 2010, avoid making frequent off-sheet cell references (cell references to a different sheet within your workbook).  If you find yourself doing this, bring the values over to the current sheet using NP("Eval") and refer your Jet formulas to the copied values instead.

• Avoid using conditional hides to eliminate large amounts of data in a spreadsheet.  In many cases, using an appropriate filter from another table will eliminate the data you do not want to see.

• Avoid using the NL function for "Rows" or "Columns" to create a summary list against a large table (e.g., a list of customers who had transactions in 2018) because the Jet Excel add-in must examine every record to find the unique values.  If there is a table that holds unique values of the field you are interested in, it is sometimes better to use that table.  If necessary, you can conditionally hide rows and columns you do not want, or integrate a Link= or NL(Filter) parameter to filter on data in the transaction table.

• Avoid using volatile Excel function such as NOW(), TODAY(), RANDBETWEEN(), etc.  If you do use them, make sure they are "wrapped" inside an NP("Eval") function, e.g., =NP("Eval","=NOW()").  This will avoid constant recalculation.

• Avoid inserting manual page breaks into your reports.

• Avoid using Excel "Grouping" (also known as "Outline") functionality.

• We generally recommend against nesting multiple Jet functions in the same cell whenever possible.

• Eliminate any Named Ranges with errors.  Use Excel's "Name Manager" feature ( Formulas > Name Manager or CTRL-F3 ) to review Named Ranges in your report.  Eliminate or correct any Named Ranges with values of #REF!.