Jet Reports is a flexible self serve reporting tool which allows us business users to create our own reports without asking IT to develop one for us. However, in some cases the reports can take longer to run than we would like. Here are some Tips for improving the performance of your Jet Reports.
Internet connection, infrastructure
Firstly we need to understand our data-source and learn about latency. Depending on the type of ERP system or database you are connecting to the speed of that connection can vary greatly. In general, a connection to an on-premise data-source will be a lot faster than a cloud data-source. On-premise connections usually connect directly to the database, whereas cloud connections need to connect using a different method such as webservices. If you are on-premise check if you are using webservice connection: if you are then you could create a SQL connection which will be a lot faster. If you are connected to a public cloud service such as Business Central, here are some things you can check:
- Make sure you have a good internet connection (Wired if possible) You can test your connection in the Jet data-source which will give you the response time. Test this 5 or 6 times and ignore the first couple (As they are doing extra things), then take an average of the last 3-4 tests. The results I get, from NZ to my Business Central (In Australia) is about 4,000 ms. If its a lot higher than this then you might want to talk to IT to see if they can do anything about that. Don’t be too alarmed by the high milliseconds for the test, as only a small proportion of this is taken when running queries. The video below explains this in more detail (About 5 minutes in)
- Consider using an Azure VM PC which is in the same datacenter as your Business Central instance. I have found jet reports performance in the VM 2-3 times faster than my local computer.
Other infrastructure tips:
- The more threads on your PC the faster your reports may run. IN Jet>Settings>App Settings> performance you will see an item “Multi-threaded calculation” this is a feature where jet will perform multiple processes at once if possible. For example, if you have 10 sheets which all insert rows, and you have 10 threads on your computer it will process all of theses at the same time, (as long as they are not dependent on each other). See view 48 minutes
- For large or complicated datasets you might want to consider data-warehousing and/or cubes. This is because data warehouse tables and cubes are designed for reporting and analysis, where your ERP is designed for recording data (Not reporting from it). For example in sales you have a sales invoice table and a sales credit memo table.. and a header table and a line table. In a data-warehouse you can combine data form many tables into one, create summary tables, create your own indexes and reduce lookups in reports. Obviously the best tool for creating this is Jet Analytics (Not biased of course!)
Indexes, Keys and flow-fields
Learn how your data is indexed. When jet is doing a query, the database will try to retrieve the information using an index in that table. If it cannot find an index then it will need to search all of the records to find the data it needs. If your report seems to take a long time on a single type of query that could indicate it isn’t using an index (Which is also called a key in NAV/BC) (See video 18 Min)
In NAV or BC try to use flow fields if possible. Flow fields are calculation ‘virtual’ fields in a table which are actually sums from another table. For Example in the GL account table the “Net Change” field is the sum of the amount in the G/L entry table. Flow-fields also have what are called Flow filters which are fields which can be filtered. For example the Net change field can be filtered by Dates, and dimensions etc. (See 15:30 in Video). The beauty of flow-fields is that they must be indexed, so if you are using a flow-fields, you know there is an index sitting behind it.
Report Design Tips (Video 21 Mins & 31 Mins)
- Try to avoid using too many sub-groupings in your report. The deeper your grouping levels are then the longer the report takes and this can become a bit exponential. Some alternatives you can explore are:
- Use NL Table and then pivot tables to summarise and group that.
- Create your own subheadings if the structure wont change a lot.
- Take advantage of Business Centrals Indentations where you can use conditional formatting instead of grouping
- Use NF Functions where you can
- Take advantage of flow-fields, indexes and Keys
- Use NP Eval where needed. (Video 32:30) – this mostly applies to volatile functions such as =Today() which are then used as parameters for the the jet functions. NP(Eval) will convert the function into a value when it starts running the report.
- Use Xlookup in some scenarios (33:30) I have had some reports which requires you to look up a table many times. Seethe video which takes you through the process.
- Using NL (Filter) and link= appropriately. You can see the article here
- Avoid bringing in unnecessary data. add filters if necessary
Keep a tidy Workbook
- Avoid large workbooks. Large workbooks will reduce jet reports performance, even if you don’t have more Jet formulas in them. This is because jet needs to search through all cells of the workbook to find any jet functions which need executing. So.. the larger the workbook the longer your reports will take.
- Remove any unnecessary tabs or sections of data you don’t need. If this is an old workbook you inherited, take a backup copy first and you can always revert to this if you delete something which was important.
- Consider breaking the report into smaller reports. I have seen many instances of people having all their reports inside of one workbook. (You can break these up into smaller reports, and then use Run in Background feature)
- Check for external workbook links Having external links will slow your reports so try to avoid these. If your links are broken then this will make your problem many times worse, as jet will be waiting for excel to resolve the link before moving on. I know many of you will use workbook links, but the best practice is to have all of your data in a database and use jet to retrieve that data rather than using links. (See video 39 Mins)
- Tidy up Name manager: (Formulas>Name manager) remove any range-names which have #value or names which refer to another workbook. (Video 42:00)
- Ensure there are no corrupt shadow sheets. Jet creates what we call shadow sheets to record data we need to make the report work (Such as the underlying formulas of a jet function). When you are in report mode these sheets will be created, and then used to move the report back into design mode after which time they will be removed. If we do something like cancel a report mid way through the run, these sheets could become corrupt. to learn more details on these see video 43:00 mins
- Keep Backup copies of your jet reports Always good practice, but I have another reason for doing this. I have seen instances jet report performance reducing, and I can see that the workbook size is a lot larger than it should be. However there is no evidence of this looking at the formulas in the workbook. The easiest way is to take the backed up report and use that. An alternative way is to copy/paste the data from each sheet into a fresh workbook.. but easier is better!
- One of the most important items for improving jet report performance is to ensure it is configured to use batch function execution. This is where jet will send many queries in one database request rather than send each request one by one. You can check this in Settings – App Settings _Performance. This can have a major impact on the runtime of your reports. Also you will need to make sure that you follow some design rules, as if they are not adhered to the batch function will not work (..and at the time of writing jet doesn’t tell you it isn’t operating, it just takes longer) You can see the kb article here
Use Scheduling and automation capabilities
- Run Reports in Background using the run in background feature. These reports do take longer to run as they wont use optimization, but you can carry on with your other works and a notification will appear then they are ready.
- Also check out the Jet scheduler if you haven’t already.
Please feel free to place any feedback.. I hope this helps improving the performance of your Jet Reports
Sign up for our educational webinar series here