The Jet 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 Jet users have found over the years that help both products work to their fullest potential.
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 Jet report, especially within Jet 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 Jet Reports. Hard-coding the values from those workbooks into your Jet report can significantly reduce processing times and eliminate potential errors.
One workbook at a time
It is best to work with one Jet 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 Jet report or using the Run in Background feature
Run in Background
Using Excel whenever a report is running (including through the Jet Scheduler) can create issues both in the Jet 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 Jet 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.1. A backup copy of your report that can be reverted back to in case of corruption.
2. 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).
3. A copy of the report for the Jet Scheduler to use which may have some design modifications to ensure compatibility with the Jet Scheduler.
Use 32-bit Excel with 32-bit NAV
For our customers using Excel 2010 or higher with Microsoft Dynamics NAV 2009 R2 or earlier, Jet Reports recommends the 32-bit editions of Microsoft Office.
C/Front (the NAV utility that handles the vast majority of communications between the Jet Excel add-in and Dynamics NAV) cannot be stored within 64-bit Excel because it is a 32-bit application. Utilizing 32-bit Excel and the Jet add-in eliminates the need to run an external process called “JetFrontServer” to store C/Front inside of and pass queries through, thus eliminating overhead and potential errors.
Use the NAV SQL connector
When using the Jet Excel add-in with Microsoft Dynamics NAV 2013 (or higher) and your database is located in an on-premises SQL Server we strongly recommend utilizing the “Dynamics NAV 2013 and later (SQL Server)” Data Source.
In most cases this Data Source type is 50-70% faster than the “Dynamics NAV 2013 and later (Web Services)” Data Source.
If your database is not on-premises, you may still be able to use the Jet Remote Data Source to provide fast access to your data.