cancel
Showing results for 
Search instead for 
Did you mean: 

The number of rows or columns exceeds its limit 65535

Former Member
0 Kudos

CRXI, ODBC database connection

My report uses a SQL statement command as the datasource. My SQL statement returns 30k records total. My crosstab is in a group header and no group has more than 10k records of this 30k. So technically each crosstab instance only has hit a max of 10k. I have only 1 column of summarized data. Yet my report returns this message. I can play around with different restrictions on the data (different where clauses) and can get the message to not appear. However I really can't tell how Crystal thinks I am exceeding this 65535 limit.

Does anyone have any insight into how Crystal is counting the number of rows or why I may be receiving this message?

Thanks.

Kurt

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Nope, I sure don't... " I have Never heard of that one "

Former Member
0 Kudos

Just tried it, I was able to pull over 457,000 records "In a Cross-Tab". But, I am on MS Sql 2000 for this DB

Former Member
0 Kudos

It appears that the 65535 does not truly mean that many records. My crosstab has 'Rows' defined on 9 different fields. If I remove one of the rows my crosstab executes without an error. Crystal is still reading the 30k records. My output is fewer records because there is one less field to make the data unique (basically one less group by value ala SQL).

I'll see if I can figure out how Crystal is computing the 65535. Perhaps it is multiplying the number of crosstab row fields by something.

BTW, the complete error message is:

The number of rows or columns is too big. Try limiting the number of unique group values. Details: The number of rows or columns exceeds it limit, 65535.

Former Member
0 Kudos

Here is more detail about the crosstab.

I have 9 Row fields defined in the crosstab expert (R1, R2, R3, R4, R5, R6, R7, R8, R9). Crystal will not repeat the row values on every row. This makes the report difficult to read unless you use gridlines (which I don't) and the report is not very Excel friendly in that format. As a result, my R1 field is actually a formula equal to R2R3R4R5R6R7R8+R9. I 'supress the label' for R1 and then the row values are repeated on every row, because R1 is unique for every row. If I remove R9 from the R1 formula I do not get the error. Leaving R9 in the formula produces the error.

It still does not make sense because there are only 30k records. The unique combination of R2R3R4R5R6R7R8+R9 on 30k records cannot be more than 30k (or 65535).

If I remove R1 as a crosstab row I no longer receive the error, however, row values are not repeated on every row. So I can eliminate the R1 formula row and my error if I can come up with another trick for repeating the row values on every row.

Answers (4)

Answers (4)

Former Member
0 Kudos

Just for snicks and grins, how about creating a few more formulas

Form1 R2R3R4+R5

Form2 R6R7R8+R9

Form3 {@Form1}+{@Form2}

Then use the Form3 in the Crosstab, just to see if the error goes away.

Former Member
0 Kudos

Unfortunately that did not eliminate the error.

0 Kudos

Hi Kurt,

I'm wondering if the error is from the ODBC driver and not from CR and the crosstab.

As a test open your report up and click on Database, Set Location and expand the New driver list and you should see Btrieve. If you don't see it then close everything down and go to Add/Remove Programs and modify the CR install and expand Database drivers and select Btrieve.

This is a our native Pervasive driver, still called Btieve for legacy purposes, before Pervasive purchase Btrieve. Select the File.ddf as the source and then if there are any field name mapping issues our mapping UI should pop up. Fix up the report, now click on Database and then Verify Database.

Once you have verified it all works in the designer then Save As a new report name and test it in your app now.

Thank you

Don

Former Member
0 Kudos

Thanks, Don.

However my report is based on an SQL command or stored procedure. I don't think I am able to use the Btrieve DDFs for this type of reporting, am I?

Former Member
0 Kudos

Sorry, did not mean version I see you said CRXI, what is your release?

I am on 11.5.12.1838 (Which I am farily sure is SP6 (The last of the updates for this product)

Former Member
0 Kudos

No selecting a different printer makes no difference.

You must be using XI R2. I believe I am still on XI, 11.0.0.2543. I worry about updating because I have my product out at many client sites and am concerned that if I change my development version that the client runtime may behave differently. And I don't want to reinstall client runtime files at all client locations for all client workstations...Of course, a more current release may fix my issue. Although a search of my error message on the SAP website did not turn up many hits.

Former Member
0 Kudos

I understand that concern, most of my users get there reports from a web service connection, so, as long as that still works I can upgrade all I want. I remember there were many changes in SP2, SP3, and just a few things in 4, 5, 6. Do you have a test

machine, that you could fully patch (There is a trick to that as well) and see if the problem goes away. Any how, that is an odd error, I have not been able to duplicate it, I have a DB with 1.5 million records, and CR will pull them all if I ask it too.) What about changing the ODBC driver? Perhaps there is a newer one of those available ?

Former Member
0 Kudos

Oh, one other question, what type of SQL is this, I have, multiple sources for my SQL, Microsoft 2003, 2005, 2008, Sybase, and a few others, each one has it's own set of querks.

Former Member
0 Kudos

Ah, but do you have Pervasive PSQL (ver 10)?

Former Member
0 Kudos

Just to clarify, this error occurs only with a crosstab. Are you able to pull 1.5 million records into a crosstab? Crystal documentation clearly states the 65535 limit for a crosstab (at least my version). But as far as I can tell, I am no where near that limit.

Former Member
0 Kudos

Complete wild question, does it make any difference what type of printer you have selected?

Also what version of CR are you on?

former_member292966
Active Contributor
0 Kudos

Hi Kurt,

When does this error happen? I usually see this when exporting to Excel because that would be the row limit in Excel.

To see what Crystal's doing, make a copy of this report and remove the crosstab. Drop a field into the Detail section and run the report.

How many records does Crystal say it returns in the Record Count when you preview the report? If it shows more than 30,000 records then possibly look at your joins.

If you are exporting to Excel, sometimes an extra row is created for each row in the Crosstab. Go into the Crosstab Expert and in the Customize Style tab. At the bottom, remove the check from Show Cell Margins. This will remove the extra spacing that creates the extra rows and columns between your fields.

Good luck,

Brian

Former Member
0 Kudos

The error occurs when I preview the report. It has nothing to do with exporting to Excel.

I removed the crosstab as you suggested and Crystal is showing that it is reporting the 30k records only. So still perplexed why the crosstab thinks there are more.

Thank for your suggestion.

Kurt