My scheduled Table Report ran last night, but the Excel output file is corrupted. We are analyzing Item sales to current inventory levels for re-ordering. The process took 12 hours (usually takes 9). The file was output as Values to save space. The output size of this corrupted file was 39mb, and the earlier 25mb file was readable. The output log does not indicate any errors.
Are there practical limits to the size/complexity of the Table being output?
Should I try using SUMIF or smilar in the Table to group by month (we only need to view the monthly sales)?
Some facts-our last readable Table (26mb) has:
~249,000 rows (line item of each item, each sale)
Four TABLE calculations (IF on Date for last 90 days, TEXT reformatting of the Date for use in the Pivot columns)
Summary Pivot Table (with additional calculations-this could be moved to a separate sheet and reading the Table in an external location)
Any suggestions as to how to trim the fat and get a readable file (in just a few hours)?
Date
Votes
3 comments
-
Jet Reports Historic Posts Official comment Ok, I have answered my own inquiry with a little help from my friends (Hughes and this forum…)
I was unecessarily using a Table, displaying all ~300,000 records and calculating on them. About 1 million calculations-it's no wonder it took so long.
I now use a Report, using the Rows function and Sum functions to display the totals for each item. Only 1,400 lines (already summed) need to be displayed, and my remaining calculations can be done mostly with simple Excel formulas in Design mode. I actually pulled one of the "Report Player" sample reports, and used that as a starting point. It even gave me columns for monthly date ranges.
The initial report took 9 hours to run. Current configuration takes 39 seconds! :shock: Additionally, I used the same framework for a related report that only took two hours to complete (no need to reinvent the wheel). The Boss is happy, and I am glad to be using Jet reports. -
Jet Reports Historic Posts Hi,
My guess here is that Excel is running out of memory. If you're using 32-bit Excel 2010 (or Excel 2007 or earlier which are all 32-bit) then Excel has a max process size of 2 GB but in practice it usually starts getting unstable around 1 GB - 1.5 GB. The size of report you're talking about could easily get that big or bigger. If you're using 64-bit Excel 2010 then theoretically the process could get much larger, but in practice if you use up all available system RAM then it will probably start getting unstable or just REALLY slow. So my guess is that probably your report got so big that Excel stopped working correctly before it finished which is why it got corrupted.
In practical terms, the way to solve this is to narrow down your data. You could narrow down the date range for the data (or split the report into 2 smaller reports which each get half the time period). You could leave out columns you don't often need to look at. Date fields in NL(Table) should always get formatted as dates in Excel (at least if you are on the latest version of Jet), so a TEXT column should be unnecessary I would think. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
Good suggestions-I suspected I was taxing the limits of Excel. I will see about implementing a "divide-and-conquer" strategy for managing the data. I didn't remember that the data is static in the past (doh! :oops: ). I can probably archive that, and draw on it with a pasted link into the pivot table.
On reflection, a couple of the formulae can probably put in or alongside the Pivot table.
Thanks for your help! I'll try to report back on my progress.
-John
Please sign in to leave a comment.