Hi-

I have a crystal reports application that processes multiple crystal report objects every day (usually 25-50) and has for the last 5 years. I use CR 11.5 (on Windows XP) but also have the program in CR 2008 (on Windows 7) for overflow processing. They both use a sql server 2008 r2 database.

Anyways, the reports are very complex, and range from 50 to 80 pages of statistical analyses, and may process 1k to 10k rows of data. I have processed up to 150,000 rows of data and it'll take 10-15 hours to do that! The client has now asked us to process 300,000 rows of data and the report does not seem able to and I can't figure out why. It loads all of the records in fine, but at some point in processing, it seems to just stall. I get the message "Crystal reports has stopped working error message" and it happens in CR 11.5, CR 2008 and on both XP and 7 machines (I have tried 32 and 64 bit).

Do you think that Crystal is running out of system resources? Is there some natural limitations to processing a report? I can't tell if this is hardware or CR related. Any suggestions are welcome.

Thank you.

It is a good possibility. I know there are programs that will monitor the resources. If it did not take so long to process. It would be a good experiment to see how many rows it would process (200,000?). Do you have a machine with the maximum amount memory installed in it and with a large hard drive with lots of free space. And of course, no other applications running.

I hope this helps.

Some things that you can look at that might help the problem:

1. Turn on both "Use Indexes or Server for Speed" and "Perform Grouping on Server" in the report options. This will push some of the processing down to the database server. You then have to make sure that you're not using Crystal formulas in your selection criteria or groups as they will cause Crystal to pull the entire data set into memory to do the filtering and grouping there.

2. Delete all of the Crystal Temp files - look for filenames in the format ~*.rpt and delete them. Crystal does a lot of swapping to disk and is NOT very good at cleaning up after itself when reports fail like this.

3. If you're using linked tables in your report, look at using a command and doing some of your calculations in the query instead of in your report. This can be a good solution if your SQL skills are good. Another option is to create a stored procedure that will do all or most of your calculations and just use the report to show the data provided by the stored proc.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

Thanks for your replies.

@kray4660: I have tried running it on a new machine that has good specs (8 gb ram, dual 500 gb hard drives raid0 (don't remember the speed but good), windows 7 ult; icore 7 third gen, etc...), but it hasn't made a difference. I know that the report can load 300k records, just can't process them. I haven't tried processing 200k, but I might try soon. And yes, I never have any apps running when I run CR since it hogs all system resources. I have thought about trying 16 gb and SSD; but since CR really can't utilize a 64 bit environment, I figured it wasn't worth the trouble or $$.

@Dell:

1) I already had "Use Indexes or Server for Speed" checked and I just checked "Perform Grouping on Server" which was not checked and am rerunning the report (although the report does not have groups so I'm not sure this will matter). I'll know by tomorrow AM if anything changed. I have no selection criteria within the report. There are three parameters in the report; two are passed by the stored procedure saying which two data sets to pull, and a third just says whether it needs to be formatted for different export options.

2) I don't see any of these temp files.

3) I'm using a sql stored procedure, since pulling the correct data is quite complex, certain data has weights applied to it before it is passed to CR. For this complex and lengthy report, I don't think it's an option to just spit the numbers into CR from the sproc. I have done that for smaller reports (10 pages) and it is extremely time consuming and difficult.

So, I'm rerunning the report after a fresh reboot and the one option changed per number 1 above and will post the result tomorrow. Any more thoughts are welcome.

Thanks!

Update: No change. I still received the same error message.

Have you tried running the stored proc for all 300,000 rows in the database? How long does it take and does it complete?

In your report, are you linking to any other tables in addition to the stored proc or are you just using the sp?

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

Hi Dell,

The sproc takes 27 seconds to spit out the records. And no, the report does not link to tables directly, just the data retrieved by the sproc.

Harlan

Are you using Page N of M in the report? This dramatically slows down report processing because Crystal has to render all of the pages as part of its processing. Also, how are you delivering the report? Is it being exported? If so, what format is it being exported to?

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

Hi Dell,

Yes, I use Page N of M. I can try removing that and try processing again.

I deliver the report two ways, once in PDF and once through Excel.

You can't deliver 300K rows in Excel - the version of Excel that Crystal reports to will only take up to 256K rows. To get the 300K rows you'll have to either export to CSV which can be opened in Excel or upgrade to Crystal 2011.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

Hi Dell,

This is a summary report, all of it is narrative and summary objects (crosstabs and charts) so we don't use the detail section at all. So, there aren't any problems exporting to Excel. However, I'm just trying to run the report directly in CR and I can't do that. It bugs out at some point after loading the records and while it's processing them, so far before it's exported.

Ok, removing Page N or M didn't work. I realized I'd been running SP3 so I just upgraded SP4 (don't know what was fixed in that) and am going to try running it through that. I also am downloading trial for CR 2011 to see if that bugs out with the large N.

Nope, CR 2011 and 2008 SP4 didn't work.

Since this is a summary that needs @300,000 rows, Crystal is doing a lot of processing in memory. Would it be possible to create another stored proc that rolls up the data - that way the database is doing the work instead of Crystal.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com