Reporting Intelligence add-in and Excel Array Formulas

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

Overview

Excel array formulas ( those created with CTRL+SHIFT+ENTER, not to be confused with the Excel arrays returned by some Reporting Intelligence formulas ) do not work with a Reporting Intelligence workbook with +VALUES or +LOCK if they are placed on a worksheet that has Reporting Intelligence formulas.

The behavior will be that the first time you run the report report, it will work.  Subsequent runs, however, will not work.

Explanation

When a report is run, all the formulas on pages containing +VALUES or +LOCK are copied to a secure location.  When the report is re-run (or returned to Design mode), the formulas are copied back to the report sheet and the CTRL+SHIFT+ENTER part of the formula  (what makes it an array formula) cannot occur as Excel does not provide a method to automatically detect or recreate this type of formula.

As a result, you cannot use Excel array formulas on sheets with Reporting Intelligence functions.  You should be able to put Excel array formulas on sheets that do not contain Reporting Intelligence functions, because formulas are not copied for those sheets.

Was this article helpful?

We're sorry to hear that.