Hi All,
If anyone is willing and has the time, I have attached a report (Not 100% complete yet) which "duplicates" what NAV 2009 does to calculate inventory value.
I am doing it in Jet because I was to have a table come through where a user/auditor would be able to create a pivot table and view the report in the way they choose.
Global dimensions and location codes are still to be added.
The problem I am having is that a DB with +/- 10000 items and over 1 million value entries takes about 20mins in NAV to run including export to excel but my report takes about 30mins and does not even have all the needed info yet.
I need to be able to run the report adhoc so the scheduling in not the option I am looking for.
My challenge is this, can anyone assist me with speeding up this report. Please also bear in mind that with this particular DB that I am using, someone deleted an item number via the back door so the report must take blank item numbers into account.
Should this all be successful and I am able to finish this report so that it is useable in a production environment, I would be happy to make this available to the community if you feel it would be useful to you.
Kind Regards
Tom
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi Tom -
There are a number of factors that can affect the speed of a report.
These include (but are, by no means, limited to) such things as:
- the number of Jet functions
- the number of Excel calculations
- the version of Jet Essentials
- the version of Excel
- the speed of the connection to the data (including database type, version, and connection driver)
- the latency time of the connection to the data
- the number of inter-sheet references in the workbook
- having multiple NL(Rows) functions on a single line [or mutiple NL(Columns) functions in a single column]
- running a report with +Values
- running a report with the progress details expanded (when using Jet 2010 or higher)
- running a report with cells on an Options tab formatted as TEXT (when using Jet 2010 or higher)
- running a report with the Advanced Application setting Preserve Page Breaks enabled
- running a report with the Advanced Application setting Preserve Conditional Formatting in Excel 2010 or later (when using Excel 2010 or later)
- the sheer number of records in the database through which the report must search to accumulate the requested data
In your particular case (with ~10,000 Item records), your report would have 90,000 NL(Sum) functions - each of which has to examine over 1,000,000 records. Unfortunately, there is only so much you can do to speed up that process.
Here are two possibilities:
Since you report uses the NF() function, you would want to make sure that you have NF Function Optimization enabled.
You can try including SumIndexes in NAV to help NAV perform some of the calculations… thus freeing Excel from having to perform them.
I hope that helps.
-HP -
Jet Reports Historic Posts Thanks for the feedback,
I have checked all the settings you mentioned and those are fine.
I have checked my report in terms of the text etc and I must be honest I don't know what I am looking for there - is it just making sure the format of the cell is not Text?
NF is optimized
Have created additional keys in NAV for Jet to choose from and have tried manually forcing Jet to use various key combinations with SumIndexes and without and no joy.
I do appreciate the feedback incase I had missed anything but so far I think I have covered all bases.
If there are anymore suggestions out there I would be willing to try still.
Thanks
Tom
Please sign in to leave a comment.