I have been using the new JetReports version 10 for a while on my development PC, while the reporting server was still running the version 9. Last Friday, I upgraded the version 9 to the new one, and since Monday, I have been having problems with 2 of my reports, which were working fine with the old version.
Normally, they took about 10 minutes to run, and now it runs for more than an hour and stops with "out of memory" error message (it's using more than 1 gigabyte of memory).
I know the report is a big one:
column B: shows salesperson names that sell lens (44 names)
NL("rows","salesperson commission","salesperson name","item commission","lens","end date","''")
column C: shows salesperson codes under each salesperson name on column 1 (94 salesperson codes)
NL("rows","salesperson commission","salesperson code","item commission","lens","end date","''","salesperson name,B2)
column D: shows customer names on the sales summary table with salesperson code from col 2
NL("rows","sales summary","customer no.","salesperson code",C2,"posting date",np("datefilter","01/01/10","05/31/11"))
- the datefilter is dynamic based on jan 1 last year and last working day, since I have to get the sales comparison from last year (this will give me almost 3500 combinations of salesrep and customer)
column E to I: total net sales (Sales - Returns) for each combination of salesrep and customer, for the last 5 days
For the daily net sales, I use sales invoice line and sales cr.memo line to get the amount
column J: total net sales (Sales - Returns) for the last week
column K: total net sales (Sales - Returns) for the Month to Date
column L: total net sales (Sales - Returns) for the Last Year Month to Date
column M: total net sales (Sales - Returns) for the Last Year Month to Date
column N to Y: total net sales (Sales - Returns) for the Last 12 month
For the monthly net sales I use the sales summary table which has monthly values for sales and returns
Question: Does the version 10 uses more memory? If that is the case, I probably have to switch back to version 9.
Date
Votes
6 comments
-
Jet Reports Historic Posts Hi,
I don't believe Jet Reports 2010 should use more memory than Jet Reports 2009, nor should it be running for significantly longer than 2009. First, please check your server and make sure you're using the latest version of Jet Reports 2010 (10.1.11048.0). If not, then upgrade and test again.
I should note that the most likely cause for your problem I can think of is that in Jet Reports 2010 you are expanding the progress window details when running the report. Progress window details were removed from Jet Reports 2009 because they use more memory and cause reports to run much slower. They were added back by popular demand in Jet Reports 2010, but they can still be deleterious to report performance. This has been improved in the latest version of Jet Reports 2010 from earlier versions of 2010 although expanding the details even on the latest version still negatively affects report performance.
Regards,
Hughes -
Jet Reports Historic Posts I am using the latest version (10.1.11048.0) and I don't expand the detail (I am using the scheduler to run the report unattended).
For now, I temporarily solved my problem by splitting the report into two salesrep groups (17 minutes and 21 minutes), and combine them with VBA. I also changed the way I combine the reps and customer using NL("rows","sales summary",{"salesperson code","customer no."}).
I will try to run the report on another PC which has the older version of Jets, just for the sake of comparison. -
Jet Reports Historic Posts I just got the same problem with one of my monthly reports.
This is what I have on the report:
the first column (B) - 16 cells with nl("filter","item","no.","item category code","xx","product group code","yy")
with xx = 3 item category, and yy total of 16 product group code.
the next column (C) - work with value entry table to get the sum of cost amount(actual) for each combination of item cat. and product group:
nl("sum","value entry","cost amount(actual)","posting date",np("datefilter","06/01/11","06/30/11"),"item no.",$B7), where $B7 is one of the above filters.
I have been using this report since August 2010, and suddenly I got this Jet Reports error message:
An unexpected error has occurred: Exception of type 'System.OutofMemoryException' was thrown (see attachment for complete message).
I am sure this is nothing to do with the hardware I use since I tried to run this on a server with 64 GB ram and still got the same error.
Is this something to do with Jet Reports or with the database/indexing etc? -
Jet Reports Historic Posts It looks like Excel is running out of memory while searching the worksheet for NP(Eval) functions, but I'm guessing that is not what's actually causing the issue. Even though your server has 64 GB of ram, you're probably running a 32-bit version of Excel which means its memory usage maxes out at 2 GB (and typically Excel will crash or throw an OutOfMemoryException quite a bit before it reaches that limit in my experience). The NL(Filter) function is quite memory intensive since it is building arrays of data and spinning through them for each record of the function referring to it. Of course the Value Entry table is also typically very large, so I'm guessing you're just dealing with a very large amount of data and running out of memory.
One way you could probably get around this is to use Link= instead of NL(Filter). You can typically accomplish the same thing with Link= as you can with NL(Filter) unless you're using array operations such as NP(Difference) or NP(Union). I think in your case, you would do something like this:
Change the NL("Filter","Item","No.","Item category code","xx","Product group code","yy") to this:
=NL("Link","Item",,"No.","=item no.","Item category code","xx","Product group code","yy")
Then change your nl("sum","value entry","cost amount(actual)","posting date",np("datefilter","06/01/11","06/30/11"),"item no.",$B7) to this:
=NL("sum","value entry","cost amount(actual)","posting date",np("datefilter","06/01/11","06/30/11"),"Link=",$B7)
Make that change to all the NL(Filter) and NL(Sum) functions and your report should use less memory and hopefully no longer give you the error.
Regards,
Hughes -
Jet Reports Historic Posts Thanks for your reply, and I will try your suggestion about changing filter with link.
The rule of thumb for using link is when the the main table is smaller than the linked table, but I found that sometimes it's faster if you are using link even if the main table is bigger. Is there any other reason/logic as to when we should use link instead of filter?
Back to my problem, this morning I tried to run the report only with one line (One cell filter, one item category and one product group), and it still gave me the same error. My question is why it worked fine with the old version (7 and 9) for almost a year, and suddenly stopped working? -
Jet Reports Historic Posts When should you use link: almost always. Basically if the number of results being returned by NL(Filter) are very small (think 20 or less) then it is probably better to use NL(Filter). Also, if you need to do array calculations (NP(Union), NP(Intersect), or NP(Difference)).
As far as this report, I'm not sure why it's giving you the error now, but I'd actually guess the workbook has become corrupted somehow. Often, when a workbook gets corrupted, Excel thinks that the "used" range of each sheet is the entire sheet (16000 columns by 1 million rows). This could be causing Excel to run out of memory when Jet Reports is searching each sheet (for NP(Eval), NL(Rows), etc.). This can also cause the workbook file size to get very large. If the workbook is corrupted, you can usually fix it by saving the workbook as HTML (or XML) and then saving it back as an XLSX, or by copying the formulas to a new workbook. Does that help?
Regards,
Hughes
Please sign in to leave a comment.