Best Practices to Keep in Mind When Using Jet Reports
Drawing on data from Microsoft Excel workbooks, users can now easily and accurately generate various reports that help speed up decision making. The coming together of the Jet Excel add-in and Microsoft Excel has boosted users’ capacity to work on existing data without need of an IT specialist to develop code or programming to achieve the same result.
To make the best use of this innovation, however, there are a few tips that should be followed.
Install the Right Product
Jet Global offers an excellent range of highly beneficial products, but depending on your needs you will likely not need all of them. There has been some confusion with similarly named offerings like Jet Reports Financials and Jet Reports. These are very different, so ensure you pick the right option to avoid interferences in your attempt to generate the desired report.
Update Your Excel
Jet Reports works best with Microsoft Excel 2007 or higher. This allows for files to be saved in open XML format. Using an older version means that your files will likely be saved in the older XLS format that is emulated. Accessing data in this format will mean opening the file in ‘Compatibility Mode’. This can make it very difficult for Jet Reports to be generated at all.
Match 32 Bit MS Office to 32 Bit NAV
Jet Reports is a 32-bit add-in so for those who have installed Office 2010 or higher it is good to check if you chose the 32-bit or 64-bit versions. A mismatch may require running ‘JetFrontServer’ that will allow for adequate channelling of communication and instructions.
No External Links
There is huge potential for errors when linking to external workbooks while working on a Jet Report. This is particularly risky when working within the replicator functions of the report, i.e. rows, columns, and sheets. To prevent errors in both Excel and Jet Reports should the workbook being linked to be opened, edited or deleted, the values being accessed should be hard-coded. This will also help to speed up the processing time.
Access One Workbook at a Time
To avoid confusion as a result of Excel accessing and calculating values from multiple workbooks being open at the same time, we recommend opening only the required workbook at a time and closing all others. Alternatively, you can make use of the ‘Run in Background’ function to allow you to utilise the active Excel workbook while other reports are still running.
Apply Report Mode
When saving reports, be sure to do so in Report Mode. This will make opening up of the workbook faster as less processing time is taken to recalculate values each time. To do this simply apply Auto+Hide+Values in cell A1 of the sheet in the workbook.
Make Extra Copies of Reports
Having extra copies of reports generated can help it be more widely used while still preserving its integrity. We advise that you keep a copy of the original report for purposes of reference and in case there is subsequent corruption of other copies.
Another copy should be set aside for other users on the team to work with as ongoing data is updated. This should be saved in Report Mode+Values. If utilising Jet Scheduler, another copy should be arranged for this, especially if there are design customisations.
Switch to SQL Server
For those using Microsoft Dynamics NAV 2013, data will be stored on the SQL Server. To provide direct and faster access to this data, we recommend switching to the ‘Dynamics NAV 2013 and later (SQL Server)’ Data Source, from the ‘Dynamics NAV 2013 and later (Web Services)’ Data Source. This can cut processing time by more than half.
Save Emailed Reports
Before accessing files or reports that have been emailed, we highly recommend saving them to your computer or network first. Opening them directly will result in read-only versions of the workbook. If you want to update, edit or otherwise work on the report it must download it first.
Correct or Delete Erroneous Name Ranges
Make use of Excel’s Name Manager to correct or eliminate any name ranges with values depicted as #REF. This error reference can badly affect the presentation of a report. You can find the Name Manager tool under the Formulas tab.
If you’re looking to get more from your experiences with Jet Reports, contact Dynavics today. We’re official Jet Global partners and we’d be more happy to discuss your requirements. We can even offer bespoke Jet Reports training sessions to help you and your team get more from this set of powerful business reporting tools.
Start Your Business Transformation
Give us a call to discuss your Microsoft Dynamics 365 requirements
01276 583 024
Get Insights Delivered to Your Inbox
Dynavics will never share your data with a third party.