on 06-30-2010 4:22 PM
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
Nope, I sure don't... " I have Never heard of that one "
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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 ?
Complete wild question, does it make any difference what type of printer you have selected?
Also what version of CR are you on?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.