Creating reports with the Jet Excel add-in is a highly customizable process. Over time, we have identified certain best practices that can help you make your reports as efficient as possible.  The following guidelines may help to improve the run-time of your reports.

• If using Dynamics NAV or Business Central, apply filters in your reports so that they match a key that is already in your NAV/BC Database.  Also, when applying sorts in lists, use a sort order that matches a NAV/BC 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 replicator functions 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, for example =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!.

  

• Reorganize filter order to generate better indexes.

  Jet Reports creates indexes that match the order of the field filters specified in an NL() function. For example, C5: =NL("Rows","Dimension Value",,"Dimension Code","Project","Code",Project,"Fund Filter",$B$3)

  D5: =NF($C5,"Code")

  E5: NL("Sum","G/L Entry","Amount","Posting Date","1/1/2024..12/31/2024","Project Code","@@"&$D5,"G/L Account No.", "10000..19999")

  In the example above, the NL("Sum") filter order is: Posting Date, Project Code, G/L Account No.

  This is an inefficient index because the Posting Date is a wide range, whereas the Project Code has high cardinality and is specified as a single value (different for each replicated row).

  If you rearrange the order of the filters to: Project Code, Posting Date, G/L Account No., we will create a much more useful index for more performant reporting. For example, E5: NL("Sum","G/L Entry","Amount","Project Code","@@"&$D5,"Posting Date","1/1/2024..12/31/2024","G/L Account No.", "10000..19999")

• Use the proper method for reversing the sign of Jet functions.

  When reversing the signs of Jet functions, avoid multiplying by -1 and instead use the method shown in the Reverse Signs for Values on Jet Function Results article.

  Incorrect: =GL(“Balance”,”10000”,”1/1/2025”,”1/31/2025”)*-1

  Correct: =-GL(“Balance”,”10000”,”1/1/2025”,”1/31/2025”)

• Use replication rather than hard-coded reports to take advantage of Batch Function Execution Optimization. For example, Best Practices for GL Reports.