Upgrading Jet / Speed Issues

I'm currently on version 2013 of Jet Reports and looking to upgrade to a more up-to-date version while I have the chance. I've been doing some testing on v18 through to v20 and am finding that opening complex reports can take ages. One of our most complicated reports was still "calculating" after 30 minutes after which I cancelled.
This particular report has various NL(Rows) in multiple columns as it requires data from lots of different areas (Job, Job Ledger Entries, Billings, CVR tables, etc.). It also contains macros which pull data in from SQL into a couple of data tables, these are then used as lookups because Jet can't cope with the amount of data that it needs to scan (millions of rows).

I logged this with support and they say I should have no more than one NL(Row) in each row, which I think is quite extraordinary. How else do you extract data from more than one table on a single row?

It's strange that the later versions appear not to perform as well as the older versions. I'd appreciate any suggestions you may have regarding this.

Thanks

Paul

Bryan Robinson September 05, 2019 18:48

As a best practice, as Jet Support noted, you shouldn't have more than one rows replicator in the same row. Often times, people use the NL(Rows) function instead of the NL(First) function (which can pull data from other tables as long as you have something to filter on). It isn't listed on either of these articles but is taught that way in training.

https://support.jetglobal.com/hc/en-us/articles/218953198-Improving-Report-Performance

https://support.jetglobal.com/hc/en-us/articles/218954558-Jet-Reports-Best-Practices

Additionally, macros can mess with the Jet functionality and is not supported in Jet reports: https://support.jetglobal.com/hc/en-us/articles/219403847-Can-I-use-Excel-Macros-with-the-Jet-Excel-Add-in-

Perhaps the design of the report needs to be looked over because Jet typically doesn't have an issue scanning millions of rows.

Last thing you may want to look at is cell A1 and ensure that Auto+hide+values is there. In older versions of Jet, this was not auto populated and can cause issues during opening because without it, Jet will try to populate every Jet function with a sample value at open.

Upgrading Jet / Speed Issues

1 comment