Cross tab limitation for displayinf more than 65k rows

[jk2802]

Hi

I am using Crystal 10 and one the cross tab reports throws an exception "the number of rows or columns are too big: Try limiting the number of Unique group values" when the cross tab processes a large nof rows. For smaller data the report runs good.

Have you seen this kind of behaviour in Crystal and please share any work arounds you may have for this.

Thanks for your help.

 

[Madawc]

Is it possible to merge some of the smaller groups? Crosstab can be based on formula fields and I've written reports when older dates are merged to a single month or year.

Failing that, you could move half of the data to a subreport. Use Save-As to make a duplicate, then re-import as a subreport in the report footer. In the main report limit the data selected to a fixed value, and strt from that value in the subreport.

Also do you have to use crosstabs? You can have the same effect with group summary totals and I'd not expect there to be a limit on those.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jk2802]

Thank you for your thoughts.

The second approach seemed very much possible , by using a filter in 'Record Selection Formula' for restricting the records upto 30K. And then using a sub report for the rest.

But in the end the same exception was thrown, even though the nof records being processed was reduced.

Kind of very much stuck with cross tab on this : what I need is a one row data display of, say an investors contribution into several funds. With the query I am able to get multiple rows , say 5 records for an investor each with 20% contribution in 5 funds. With the cross tab I could show 5 Fund Names as Column Names and 20 % as their contribution.

Since the nof funds an investor can opt for remains dynamic , not sure about any other control could do this.

Really appreciate your help with this.

( I was really desperate for a way out - thats why posted the same in the Others forum as well )

 

[IanWaterman]

Another possibility is to group the report by Investor and then place the cross tab in the Investor group header.

Ian

 

[jk2802]

Ian,

Thanks for your reply.

I was having the crosstab in the header section with grouping on Investor.

But it looks like even if I am restricting the nof rows while reading the records, crytal ( not sure if this is possible ) identifies there are more than 65k records and the crosstab fails

 

[IanWaterman]

What is your data source and connection method.

Ian

 

[jk2802]

A Stored procedure is pulling the data from sybase 12.5 DB using an OleDbConnection with VB

 

[IanWaterman]

Does the report run when executed directly through Crystal rather than VB.

The error you are getting does not sound like a Crystal error, looks like it may be your VB application.

Ian

 

[jk2802]

Actually the report gets executed in any case - but the data is not appearing in the report.

From Crystal the exception is thrown first and a blank section is shown where crosstab is placed, with VB this exception is not caught but a blank cross tab itself is generated.

Actually I am generating two different output layouts with the same report. One is with the crosstab and the other is straight forward data display. Even when the report is run for the second format ( which invloves no crosstab) this error is thrown. But data is generated on the report without any problems.

know this is weird , but this is how the crystal behaves now.

Appreciate your help.

 

[jk2802]

The FundName (FundA,FundB etc) is selected as the Column value and Investment% as the summary in the cross tab.

In the stored procedure result set I would be getting 5 records with InvestorID = 1 and for InvestorID = 2

InvestorID Name FundA FundB FundC FundD Fund E
1 abd 10% 20% 30% 30% 30%
2 efg 20% 20% 20% 20% 20%

Could this be achieved with out using a crosstab.