Hello,
i get the attached error message, when i run my Jet Report. I'm a very new Jet Reports user, so i don' t know, if/how i can change the Report to reduce Memory usage.
I have also attached the Report.
The result of the Report should be like the template.pdf. PHG means Product main Group, PG = Product Group, PUG = Product Sub Group, Pfam = Product Family. The Groups will be setup in the Item Tabelle. With the Item-Filters in Column C i get all item numbers and them i filtered the value entries with the numbers.
Thanks in advance.
Date
Votes
7 comments
-
Jet Reports Historic Posts Hi Daniel,
One way to decrease memory usage on this report would be to remove all the NL(Filter) functions and change the NL(Sum) functions to use Link= instead. So for example, take the NL(Filter) in C9 and the NL(Sum) function in I9. If you remove the NL(Filter) function, then you could modify the NL(Sum) function to look like this:=NL("Sum","Value Entry","Sales Amount (Actual)","Posting Date",$I$6,"Item Ledger Entry Type","Sale","Link=","Item","No","=Item No.","Product family",$G9)
Then you could basically do the same thing for all the rest of the NL(Sum) functions and completely remove the NL(Filter) functions in column C. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi fhilton,
i will change the the Report as you describe and will check the result
Thanks. -
Jet Reports Historic Posts Hi fhilton,
I get the attached error message after 1,5 hours!!!, when i run the Report. Do you have any further idea, how i can fix the Problem?
Regards -
Jet Reports Historic Posts So the problem here is that the Value Entry is a huge table. Under many circumstances, Jet can have NAV itself execute an NL(Sum) formula. However, in your case since you need to link to the item numbers from a specific product family, Jet has to manually spin through the Value Entry records for each sum and for each record open the Item table and set a filter to check whether the entry is in the item product family. If the Item Product Family field were added to the Value Entry, Jet wouldn't have to do this, so if you can make modifications to NAV, that could be an option for you. If you did do that, you would want to add a key to the Value Entry with the Posting Date, Item Ledger Entry Type, and Item Product Family with a sumindex for the Sales Amount (Actual). Then the sums could be performed by NAV much more quickly without using very much memory on the client side.
Assuming you can't do that, one thing you could try is actually creating a filter for the item numbers you need. Unfortunately, if any of the item product families contain too many item numbers then the filter will be too long and you will get a #VALUE. However, it's worth a try at least. Basically you could take your original report which used the NL(Filter) and change the NL(Filter) formulas to something like this:=NP("Join",NL("Filter","Item","No.","Product family","@@"&$G9),"|")
This way, all the item numbers in question will be joined into a filter string with | in between. Then Jet should be able to make NAV execute the sums without spinning through all the records. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts I have insert the 4 product Group fields in the value entry table as flowfields and change the Report, so i only use the value entry table now. It seems, that this way is not better. Maybe the flowfields are the reason?!
-
Jet Reports Historic Posts Hello,
i have now insert the 4 fields as "normal" fields in the value entry table and so use only one table in my Report. The report runs (only) 5 mins now (instead many hours), but then i get a error message again. What is the reason?
Thanks -
Jet Reports Historic Posts Hi,
It looks like Excel itself is running out of memory trying to run the report. The report is just too large. I'm assuming you are using a 32-bit version of Excel, in which case the Excel process size can reach a theoretical maximum of 2 GB. However, in my experience Excel starts to become unstable when the process size is somewhere between 1 GB and 1.5 GB and will throw this error if it continues to grow.
You could switch to a 64-bit version of Excel, assuming you are using Excel 2010 or 2013. This should solve your memory problem. If you are using NAV 2013, then 64-bit Excel shouldn't make a difference to your report performance. If you are using an earlier version of NAV, your report will run somewhat slower in 64-bit Excel than it does in 32-bit Excel. However, if the report in its current state is very important, then this may be the only alternative.
The other option to consider is splitting the report up into multiple reports. You could put each of your 4 sheets into a separate workbook and then hopefully each of them would be able to run to completion without running out of memory.
Regards,
Hughes
Please sign in to leave a comment.