Hi, we just upgraded from Jet Express to Jet Essentials.
I have several reports I created with Jet Express and noticed we were limited to around 45K records. I assumed that restriction would be removed when we upgraded the license, but I'm still running into the same limit.
For instance, we're pulling sales information from the Item Ledger Entry table and the report will refresh if I run it from 01/01/15..05/31/15. If I try to include June in this report, it fails (The pivot table 'PivotTable2' could not be refreshed. The following error was reported: 'Reference is not valid.'.).
If I refresh the report with overlapping dates it runs fine, so long as I don't grab to large a range of dates. So, it seems unlikely I have corrupt data.
I've noticed the number of records for any range of dates seems to max out around 45K. What am I missing?
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Thanks for your help. I've been e-mailing support on the side, and I think we have two issues. Web Services is one, but our parent company controls the server and is unlikely to allow us to physically move it. The other is my use of the NL function for my lookups. I'm self-taught on the free version, and tend to take a brute force approach anyway, so I'm probably pulling WAY too much data.
Time for training. I'd check this thread as RESOLVED. -
Jet Reports Historic Posts Hello -
There is no "record count limit" imposed upon Jet Express reports.
What this sounds like is that Excel may be bumping up against a memory limit.
Excel uses memory for two separate purposes:
- storing the workbook
- processing the calculations contained in that workbook
When using Excel 2007-2016 in "native" mode, the 32-bit editions of Excel are limited to 2GB of "Virtual Memory". So, if you are using a 32-bit edition, that may be the case.
While 64-bit editions do not have such a limit (their simply limited by the amount of available memory), even if you are using a 64-bit edition of Excel 2010-2016, if your instance is running in "Compatibility Mode" (http://kb.jetreports.com/article/AA-00586) you would still experience the same memory limitations.
To test if this might be the case, you could use Windows Task Manager (if you are familiar with it) to report the size of the Excel process while your report is running. While the "Working Set Memory" reported in Task Manager is a little different than the "Virtual Memory" space limit I mentioned, take a look at Task Manager's "Processes" tab and look at the amount of memory being used by the Excel processes. If that number approaches 1.1-1.6GB, you can certainly start to see issues.
Since your report was original designed with Jet Express, I assume it uses an NL(Table) function. How many fields are you returning for each record? Are there any that you don't need? (i.e., could you trim it down?)
From what you describe, you are also using a pivot table. That increases the memory demand on your report, as both the NL(Table) and Excel's pivot table take up their own memory. -
Jet Reports Historic Posts Excel isn't pulling more than 8.3 M while running the report.
The report returns 24 fields per record. Is that a lot? It's sales data, so I wanted to give our Sales group plenty of variables by which to slice the data (outside rep, inside rep, customer, parent customer, product sku, price, date, etc).
We're running Excel through Citrix. Could that be part of the issue? -
Jet Reports Historic Posts As long as you are following all the guidelines for using Essentials in a Citrix environment (http://kb.jetreports.com/article/AA-00736), I wouldn't see that as an issue.
What you may want to do is duplicate the issue while creating a log file (http://kb.jetreports.com/article/AA-00651) and then submit that and a copy of the report to the Jet Reports Technical Support staff (https://support.jetreports.com/).
They would then be able to examine the log to get additional details as to the issue being experienced.
Please sign in to leave a comment.