All of us know that Excel has a limit of 65K rows.
So when you see that message "There is not enough room for rows"
how do you do to overcome that issue? redesign the report or use some sort of formulas to get around it? :idea:
Date
Votes
16 comments
-
Jet Reports Historic Posts Yes,
But if it isn't possible or you can't find it right away, you can also use the "limit=" in the NL function:
put "Limit=" in filterfield and the record limit in filter.
So your NL only returns, lets say, 20000 records.
I do this and just make a note on top of the page: a maximum of 5000 rows will be returned
Another solution in some cases is finding a way to divide the data in groups (ex: product group, country, posting group,…) and split them into seperate sheets. -
Jet Reports Historic Posts First thing that comes to my mind is what sort of report would have more than 65000 rows that would be benificial to users? If the report is used as an interface or system extract, sure. But I would be surprised if somebody has a need to look at a report with that level of detail. I could be wrong…
I would think a report using some subtotals and summing would be presenting more useful information. If not, then the only other option as I see it are those Jan suggested. -
Jet Reports Historic Posts mark, I understand your statement, but I've met these limits also. For example when you need to calculate a factor in entry lines per item. (some price x some quantity)
You can't get all of these values through a simple sum, so you need all the lines and count the totals later… -
Jet Reports Historic Posts Ok, I see what you mean. Similar thing to calculating total cost if all you have a lines with quantity and price. You could group by price though and sum the quantity and do the calculation that way:
B1=nl("Rows","Sales Invoice Line","Price",…)
B2=nl("Sum","Sales Invoice Line","Quantity","Price",B1,….) (where '…' is the same as B1)
B3=B1*B2
A simple example, and it may not apply to all circumstances or situations (and makes troubleshooting a little difficult). Not sure about performance either. But I do understand where you are coming from. -
Jet Reports Historic Posts Group by price proved to be very slow with us and not really intresting, we are in the retail business and items change selling price very quickly (3% discount, 5c discount,…)
And when using a group by price, we had to group on something else afterwards, which was not pleasant.
When playing somewhat with the "limit=" filter and diffirent sheets, it is possible to make a second sheet and generate the rest of the data there (even make 3 or more…) and then have a "dashboard"-style sheet. -
Jet Reports Historic Posts I had suspected the performance would be fairly painful.
I guess at the end of the day Excel is the biggest limitation to Jet Reports. I'm looking forward to when we upgrade to new versions of Excel (we're on 2003) where you can get access to may more rows. -
Jet Reports Historic Posts Sorry to dissappoint you mate, following piece of the v7.1 release notes:
Known Limitations:
Reports can only contain up to 256 Columns and 65536 Rows.
Excel 2007 memory limitations prevent us from taking advantage of the increased Row/Column upper bounds. -
Jet Reports Historic Posts For some reason I'm not surprised.
Does that mean that a single "Rows" formula can not replicate higher than that number, or is it total for the remote?
Could you have multiple Rows forumlas under each other, each individually not exceeding 65K but combined being larger? For example:
B2=nl("Rows","Sales Invoice Line",,"Posting Date","010105..010107") - returns 60,000 rows
B3=nl("Rows","Sales Invoice Line",,"Posting Date","010107..010109") - returns 60,000 rows
When completed B3 would be starting on B60,001 - does that work? -
Jet Reports Historic Posts Never tested it, because, we have excel 2007 but amongst our customers it is not yet common…
-
Jet Reports Historic Posts interesting postings from Jan and Markl.
Well seriously, most ppl who ask about the 65K rows limit are usually the IT ppl. they will talk about that limitation and then discredit JetReports based on that!
When some1 ask me about that Limitation, i will ask them the question as what MarkL had said "what sort of report would have more than 65000 rows that would be benificial to users? If the report is used as an interface or system extract, sure. But I would be surprised if somebody has a need to look at a report with that level of detail."
then i provide a solution like grouping the data by Country, Dimension code or posting groups in sheets. well certainly people can analyse better and thing are more organise.
but having said that, that Excel row limitation still remain a thorn in closing deals and IT people will still talk about it despite a good reason and a solution given.
Therefore i'm here to find out what the forumers in here will do so as to further support my case. -
Jet Reports Historic Posts If it helps I'm an IT person (database adminstrator) and I don't believe the 65000 limit negatively impacts the usability of the product, as there are a number of ways to work around it, as you and Jan have mentioned.
You will probably find most IT people would be pushing for a larger product, such as SQL Reporting Services (which there are valid arguments for that as well). SQL Reporting Services requires much more expert knowledge - you need to be able to write SQL (more or less) to pull any data out, whereas with Jet, anybody can do it, which I think the number one reason IT people aren't comfortable with Jet - it gives the End User too much control. -
Jet Reports Historic Posts Thanks for the compliment and I hope you can put this to good use!
-
Jet Reports Historic Posts just a thought…
is it possible for Jet or excel to place data on another worksheet automatically once the current sheet has reached the 65k limit? -
Jet Reports Historic Posts If it helps I'm an IT person (database adminstrator) and I don't believe the 65000 limit negatively impacts the usability of the product, as there are a number of ways to work around it, as you and Jan have mentioned.
You will probably find most IT people would be pushing for a larger product, such as SQL Reporting Services (which there are valid arguments for that as well). SQL Reporting Services requires much more expert knowledge - you need to be able to write SQL (more or less) to pull any data out, whereas with Jet, anybody can do it, which I think the number one reason IT people aren't comfortable with Jet - it gives the End User too much control.
This is very true, it "scares" them in this way: end users are going to make things, everybody happy, till something goes wrong…
With smaller customers we often only sell jet, while with larger, we sell jet and reporting services (on cubes).
For the smaller companies, performance isn't that big an issue, so jet meets more than their needs. For larger companies, Jet isn't really the cost in a NAV implementation (1 - 2% of total cost), while it gives a great reporting tool.is it possible for Jet or excel to place data on another worksheet automatically once the current sheet has reached the 65k limit?
LegacySubaru
Depends on what you are trying to reach.
If all you want is an actual "list" of some large table (some entry table), you could use the "limit=" as stated before. Since you're using excel you can play with that number as much as you like (ex with the Row() function, which returns your current row, allowing you to easely calculate how much space you have left). I think you can be very creative on that part…
This allows you to stop in time quite easy, to continue where you left off it's a bit of a mess I'm affraid.
The best I can come up with: count the number of records you've filtered, substract the number of records you already displayed and you have the number of records that you still need.
You could then use the "limit=" again on the same filters, but sorted backwards (so you have the last items…) and make a NL("Filter")…
I know it's not pretty, but its a start… the stopping mechanism works fine for me. -
Jet Reports Historic Posts just a thought…
is it possible for Jet or excel to place data on another worksheet automatically once the current sheet has reached the 65k limit?
I imagine it is possible if you used some complicated If statements. Somehow paste the ID of the final row into the next sheet and use that cell reference in a filter. -
Jet Reports Historic Posts just a thought…
is it possible for Jet or excel to place data on another worksheet automatically once the current sheet has reached the 65k limit?
I imagine it is possible if you used some complicated If statements. Somehow paste the ID of the final row into the next sheet and use that cell reference in a filter.
Like I said, that would enable you to continue your data (if you can use that Id), but it wouldn't enable you to "create" sheets automaticly where needed…
Please sign in to leave a comment.