Hi there,
I've used Jet Reports for several years now and I'm starting to run into some issues that I haven't experienced much before.
Our business primary leases appartments and houses to private persons. I have created a report that collects the monthly turnover from these leases into an Excel report through Jet Reports. The problem is that if I make a list of all the leases that have been posted in MS Dynamics NAV the list is too long (approx. 5.000 lines each month) and Excel (or Jet?) crashes. I've tried using the NL(SUM) formula and although it works, I need several of these formulas (as in Leases per category etc.) and again Excel crashes after Jet has been running for approx. 20 minutes.
Has anyone experienced this problem before? Is there some kind of data "cap" on what Jet Reports can handle? Does anyone have some kind of ideas how I can retrieve a lot of data from MS Dynamics NAV through Jet Reports?
Thank you very much for any help.
Date
Votes
8 comments
-
Jet Reports Historic Posts Hi
Please share the report in design mode.
Best regards
Ravi-Hong Kong -
Jet Reports Historic Posts Hi,
Thanks for the response. Attached you'll find the file with the NL(SUM) functions in J44 up to N46. However there are more formulas like that in the file.
I hope you can find something to improve.
Cockpit Exploitatie NIEUW v150121.xlsx -
Jet Reports Historic Posts Hello Thanks for the file
Oooops what's language is it , I can access and use only English language in my machine.
Best regards
Ravi -
Jet Reports Historic Posts It's in dutch, but Excel will translate Excel functions from Dutch to English if you use English as the system language.
All Jet Functions will do the same.
The other text is just text and can be ignored.
The only other thing is that we use MS Dynamics in combination with a real estate specific database. So that's why there are "strange" tables and fields in the Jet Functions. But that should not be an issue. All these tables are part of our MS Dynamics Database.
Best regards,
Thijs -
Jet Reports Historic Posts Why do you use the NL(Filter) in J44?
I guess the global dimension 1 is available in Elementpost as well.
Is there a specific need to use Eenheidnr. in that formula?
If so, I would suggest to take out that NL(Filter), because you use it in M44 as well.
Now Jet needs to calculate it twice.
Not sure why you use the excel formula's to calculate the values from the other tabs.
You should be able to ask Jet to return just the needed value and leave all the data on the server.
My 2 cents :)
rmw -
Jet Reports Historic Posts Thanks for the reply.
The Filter is there because we make a difference between units of different types (the types are in the column E (hidden)). But it would be possible to use the same filter function for every row, that would cut some time of in running the report. I'll try that at least.
The problem with only using functions to extract the information directly from the database is that either they take way, way, way longer to calculate than a simple NL(Row) function in another tab. The other problem is that it is sometimes impossible to calculate the result directly: the other tabs help to combine data from different tables together (it's not possible to set the correct filters at once). I'll try using an NL(table) function to see if that helps. -
Jet Reports Historic Posts NL(Table) sure has some benefits over NL(Rows), but if NL(Sum) takes way way longer then NL(Rows) you need to look into the keys you have available for selecting within NAV.
Probably adding a key will speed things up.
I even had speed improvements by re-defining the order of the fields within one key….
rmw -
Jet Reports Historic Posts Hi,
You've got a few formulas in this report which are very expensive to process.
VLOOKUPS inside replicators take a lot of time. You might want to try this trick for a faster VLOOKUP
https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/
You also have several queries which have 'offsheet references' to named ranges. Having calculated values on one worksheet that are dependent on values on a different worksheet which are changing (due to the replicator) can be very time intensive.
Just as a test you might want to move your queries on your "Contracten" worksheet to the "Cockpit" worksheet & see if that helps.
Please sign in to leave a comment.