Reporting Intelligence Best Practices
Overview
The Reporting Intelligence Excel add-in is a powerful reporting tool that takes advantage of another powerful tool that so many business users already know... Microsoft Excel.
Together, the two provide almost unlimited capabilities.
There are certain practices that work better than others.
Here are a few things that Reporting Intelligence users have found over the years that help both products work to their fullest potential.
Row and Column reserved for a report
When designing your report with the Reporting Intelligence Excel add-in, please remember that row 1 and column A on each worksheet are reserved for Reporting Intelligence Keywords
Including other data such as Excel functions (including IF functions), text, etc. in these two areas can interfere with the functionality of your report, make the report run much slower, and even make it impossible to return the report back to Design mode.
Save reports in Report Mode
Save reports in Report Mode with Auto+Hide+Values present in cell A1 of any sheet in the workbook. This will help to avoid calculation when the report is opened, thus speeding up the process of opening the workbook.
Avoid external links
If possible, do not link to external workbooks from within a Reporting Intelligence report, especially within Reporting Intelligence replicator functions [ NL("Rows"), NL("Columns"), and NL("Sheets") ]. These functions will have to query the external workbook(s) numerous (sometimes thousands of) times when the report is refreshed.
If the workbook is currently open, has moved, or has been deleted, this can produce errors within Excel and Reporting Intelligence. Hard-coding the values from those workbooks into your report can significantly reduce processing times and eliminate potential errors.
One workbook at a time
It is best to work with one report open at a time to ensure that Excel does not attempt to calculate items from multiple workbooks simultaneously. We suggest either closing other active Excel workbooks before attempting to refresh a report or using the Run in Background feature
Run in Background
Using Excel whenever a report is running (including through the Reporting Intelligence Scheduler) can create issues both in the report and the other workbooks. Using the Run in Background feature allows you to continue to use Excel while other reports are running.
Save Outlook attachments before using
When opening a report that was received via email, save the file to your local PC or a network location first. We do not recommend opening files directly from email as this will create temporary, read-only copies of the workbooks.
Use Excel's latest formats
When utilizing Microsoft Excel 2007 or higher, ensure that all files are saved in open xml format (.xlsx, .xlsm, .xltx, .xlsb). Files saved as Excel's older format (.xls) will open in a separate “Compatibility Mode” instance of Excel (which is emulated). Using Excel in the emulated "Compatibility Mode" can cause the Reporting Intelligence add-in to behave erratically.
In addition, Excel is much more limited when used in Compatibility Mode. This can make large workbooks difficult or impossible to work with.
Eliminate invalid named ranges
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!.
Keep multiple copies
We recommend storing multiple copies of your reports to be used for various functions.
A backup copy of your report that can be reverted back to in case of corruption.
A copy of the report for users to run on an ad-hoc basis which is saved in Report Mode with +Values (see "Save reports in Report Mode" above).
A copy of the report for the Reporting Intelligence Scheduler to use which may have some design modifications to ensure compatibility with the Reporting Intelligence Scheduler.