Jet best practices

Monday, March 28, 2016 - By Jon Gardner

making easier to use Jet Reports

Jet Reports has a fabulous Knowledgebase site – see http://kb.jetreports.com/ – but one of the articles I refer to often at the end of each training course relates to best practices; helping clients/trainees to have a much easier and enjoyable experience working with this amazing product.

All the below relate to speed – much as we love working with Excel it is inherently slow in terms of calculation speeds when put against the calculation speeds of say NAV or GP.

• Save Reports in Report Mode
  Please make sure that you have Auto+Hide+Lock (or Auto+Hide+Values) in Cell A1 of the report pages in your workbook. This will help to avoid calculation when the report is opened, thus speeding up the opening of the workbook.
• One workbook at a time
  Self-evident really but it’s amazing how often we all do it! Far better to work with just one Jet report open so that Excel does not attempt to calculate items from multiple workbooks simultaneously.
• Use correct file formats
  We’re assuming that you are using Excel 2007 or above…. So please ensure that reports are saved in the open xml format (.xlsx, .xlsm, .xltx, .xlsb) rather than the ‘old’ .xls format. If Excel opens in “Compatibility Mode” you are using an old file format – and there is a risk that Jet could behave erratically.
• Use the NAV SQL Connector
  We have nothing whatsoever against web services but if you have an on-premise SQL server and a NAV 2013..2016 database try setting up the “Dynamics NAV 2013 and later (SQL Server)” data source. It’s certainly faster; Jet have suggested between 50% and 70% faster.
• Use Cell Names to update report filters
  When creating the Options page for the report give all the filter fields in the Value column a name using the Name Box. Giving a cell a name (Eg. DFILTER) stores the value of that cell in virtual memory – and the name can be referenced in the filters on the report sheets in the workbook (=DFILTER).
• Eliminate invalid/redundant named ranges
  Remove or repair any named ranges especially if they are creating #REF! messages. Either edit all the formulas where the error messages appear or use the Name Manager functionality in the Excel Formulas ribbon bar.
• Use 32-bit Excel with 32-bit NAV C/Front
  For older versions of NAV (NAV 2009 R2 or earlier) use the 32-bit editions of Excel (and Jet). The older versions of NAV rely on the C/Front functionality for the communications, which comes as a 32-bit application only.
• Use Jet’s =NL(Sum) or =GL() instead of Excel’s =SUM() where possible
  This helps especially with large or multi-sheet workbooks where the calculation is effectively done by Jet/NAV rather than within Excel. Using Jet in this way is not only much quicker but will also provide the drilldown functionality in the totals.

This list is not exhaustive by any means; if you have any questions or comments, or tips of your own that you would like to share, please let us know below –