4 Techniques to Optimize Jet Reports for Faster Run Times
Oh, if only there were a set of steps that, if followed, would guarantee a fast Jet Report.
Given the nuances and intricacies in the design of many reports, combined with other factors, like the more complex database structure of BC compared to NAV, the size of your data, and your network or internet speed, it can feel like there are an infinite number of things to try to tweak. Or you may feel you don’t know where to start. Or you may simply want to throw your hands up in defeat and frustration.
Why is this report taking 45 minutes to run? I get it! And you are not alone.
There are many things to consider when trying to speed up a Jet Report. During the course of my 10 years (as of 2025) helping clients design, troubleshoot, and optimize Jet Reports, I have seen many slow reports and found that it is more of an art than anything else. What works for one report doesn’t always work for another.
But there is hope. Some tried-and-true best practices are worth adding to your tool belt.
I want to start with the caveat that some Jet Reports are just slow. I have worked with clients to improve speeds, and I have seen reports go from 45-minute run times to completing in 2.5 minutes. (See case study below.) But that doesn’t always happen. Sometimes, you are dealing with a complex report with a lot of data, and it still takes 40 minutes, even after we have done everything we can do.
If that is the case, we can still look at how we can help you. Maybe, it is setting your report up to run on the Jet Scheduler or uploading it to the Jet Hub, so it doesn’t tie up your Excel while it runs. Maybe we need to adjust what you have selected in your Jet settings. That’s nothing to sneeze at, by the way. I have seen one setting change make the difference between a report finishing in under 2 minutes vs not finishing after 1 hour.
Whatever the case, we can always look at your unique situation and make recommendations.
Regarding the report I mentioned above, in which we reduced the run time from 45 minutes to 2.5 minutes, we did four things. Note that we did not change any report filters, increase network speed, or anything else. We only edited the Jet formulas in the report. What were those four things?
I’m so glad you asked…
Optimization technique #1: Add filters you didn’t think you needed
Have you ever created a report on posted sales invoices, and you need to bring in data from both the header and the lines? You might have an NL(Rows) formula to list records from the invoice lines and then an NL(First) to grab header information.
Let’s say cell C2 has 01/01/2022..01/31/2022, and D4 has:
=NL(“Rows”, ”Sales Invoice Line”,,”Posting Date”,$C$2)
This will generate a record key for each of the invoice lines in January of 2022.
Let’s say E4 has:
=NF(D4, “Document No.”)
Then, let’s say you want to return the Salesperson Code in F4:
=NL(“First”, “Sales Invoice Header”, ” Salesperson Code”, ” No.”, E4).
This would look like this in Design Mode:
With these formulas:
Since the unique identifier of each record of the [Sales Invoice Header] table is “No.,” that will suffice.
However, the Jet query still has to get all the records from the [Sales Invoice Header] table, because it doesn’t know in advance of running which records will actually be needed in your report.
So…add a filter that is not necessary to get the correct data, and, Voilà!, the report runs much faster. Instead use:
=NL(“First”, “Sales Invoice Header”, “Salesperson Code”, “No.”,E4, “Posting Date”,$C$2)
This may not improve the speed much on a report returning under, say, 100 records, but it will make a big difference with larger reports. As your data set increases, this will yield an increasing benefit in reduced speed.
Don’t underestimate this. I have seen reports go from several minutes to a few seconds with this one change alone.
Optimization technique #2: NP(Eval)
When I went through Jet Reports training, it was emphasized always to make an Options page, and to put all filters, both hard-coded (unchanging filters) and report options (filters users select at run time), on the Options page. It was also emphasized to bring the filter field names and their values over to the report page using NP(Eval). This, I was taught, would increase efficiency.
I religiously wrote reports this way, and then, as I started working with clients and their reports, I saw many folks did not do this. They either linked to the options page directly using Excel links or didn’t bother to have an options page at all. Some had never heard of NP(Eval).
And for the most part, their reports ran just fine.
However, using NP(Eval) to link to other sheets is the best practice and can speed up your reports. Like the above example, this has a bigger effect on larger reports.
What is NP(Eval)? In my training sessions, I like to describe it sort of like Paste Values.
Basically, you wrap an Excel formula inside of an NP(Eval), and that formula only evaluates once at run time and doesn’t evaluate again until the report is run again. (Normally, each Excel and Jet formula runs repeatedly as the report runs.)
By the way, NP(Eval) is a great way to add time stamps to your Jet Reports. You can enter the following and format it as date/time:
=NP(“eval”,”=now()”)
So..
I was planning to include an entire section here with instructions and examples on NP(Eval), and I realized this topic is better for a live training or a separate article. But at least you have now had an introduction to this potential time saver in your reports.
Optimization technique #3: Use record keys and NFs whenever returning more than 1 field from the same table
Remember above, when we returned the [Salesperson Code] from [Sales Invoice Header]? Suppose we also wanted to return [Sales Invoice Header].[Currency Code].
One way to do this would be to write 2 NL functions, one for Salesperson Code and one for Currency Code:
=NL(“First”, “Sales Invoice Header”,” Salesperson Code”,” No.”,E4,” Posting Date”,$C$2)
And
=NL(“First”, “Sales Invoice Header”,” Currency Code”,” No.”,E4,” Posting Date”,$C$2)
Note: To see why we added the Posting Date filter, see optimization technique #1, above.
However, each NL function is a query to the database. This would query the database twice for each record returned. If you have additional fields from [Sales Invoice Header], each will ping the database again for each record.
Instead, you can write an NL(First) to return a record key, which is only 1 query, and then pull as many fields from each record as you like. Each additional field won’t impact performance the way it would if it were pulled using NL(First) for each.
=NL(“First”, “Sales Invoice Header”,, ”No.”,E4,”Posting Date”,$C$2)
Optimization technique #4: Don’t use NL(Rows) when you could use NL(First)
While working on the report in this case study, I noticed that there was an NL(Rows) formula pointing to the Customer table, returning the [Customer].[City] field for each record.
There was only one customer per record, so whether NL(First) or NL(Rows) were used, the resulting value in the report would be the same.
However, if NL(Rows) is used, Jet returns the first value and then keeps looking for additional values. It doesn’t know that there is only a single match.
That is my theory, anyhow. I haven’t verified this with Jet Reports, but the report results seemed to verify it:
My client and I had applied the other three optimization techniques, and the report ran in 23 minutes. We had already cut the run time by almost 50%.
But..
This additional change alone took the report down from 23 minutes to 2.5 minutes!
The moral of this story? Don’t use NL(Rows) where NL(First) will suffice!
Conclusion
Again, for a particular report I worked on with a client, the four techniques above reduced the speed from 45 minutes to under 2.5 minutes. That’s under 5% of the original run time!
Those were the techniques needed for this report. There is almost an endless number of things that could be tweaked or adjusted to improve report speed.
To detail everything out in a single article would be almost impossible. Hopefully, the above has given you some helpful tips and things to consider.
Contact ArcherPoint by Cherry Bekaert if you would like to learn more or have us review your specific reports to make recommendations. We would be happy to hear from you! In the meantime, we wish you happy Jet Reporting!