Improve Report Performance

BC Cloud Only - this article only applies to Reporting Intelligence for BC Cloud

Creating reports with the Reporting Intelligence 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).
  • 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 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 Reporting Intelligence 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.

    Reporting Intelligence 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 Reporting Intelligence functions.

    When reversing the signs of Reporting Intelligence functions, avoid multiplying by -1 and instead use the method shown in the Reverse Signs for Values on Reporting Intelligence 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.

Improve Report Design Performance

The Enable Go To Design Mode - No Calc option enhances performance during report design. With this option, you switch to Design Mode where you can work with your Reporting Intelligence formulas without triggering calculations or data source connections. Unlike standard Design Mode, which retrieves a first row preview automatically, this mode skips all calculations to maximize performance. This allows you to add or edit formulas freely without waiting for data retrieval You can activate the Enable Go To Design Mode – No Calc option in Reporting Intelligence > Settings > App Settings > Performance. When you activate the option, the Design Mode behavior described will be default.

When you add new formulas while in this mode they display the #PendingRun indicator. This means that the formula is valid but hasn’t been calculated yet.

To calculate all formulas and retrieve actual data, you need to complete your formula edits and click Run. The software will calculate all the formulas and display the results.

Was this article helpful?

We're sorry to hear that.