Skip to Content
0
Former Member
May 12, 2009 at 09:36 AM

How to optimize the performance of crystal report?

452 Views

Hi,

-I have to design a crystal report with best possible optimization. Optimization is main concern since report will run against 1-2 million data set. Though I am using parameter to fetch only the required data, required data can go till 1 million records.

-Based on the input passed by the user I have to group the data in report. And for each selected parameter the Detail section I am printing is different. For example:-If user selects store then detail section is different and if user select Host then detail section will be different.

-Report can be grouped by Time Field also. So to full fill this requirement I would have to create a sub report since other parameters are of string type and can be used in one formula to get parameter based grouping in report. However If I try to return Time filed from the same formula I get the errors the "Return type should be of String typeu201D. This forces me to create sub report for Time based grouping. If user selects Time Field to be grouped on, all the information in the main report gets suppressed and only the sub report gets printed.

If user select store, Host and User in parameter to be grouped on, sub report gets suppressed.

Now with the above mentioned points I tried to optimize the report in following way.

-Printing 1 million records in the report does not make sense; hence we wanted to show the summary of all the records in chart section but wanted to print just 5000 records in detailed section. Suppressing detailed section after 5000 records does not help much since suppressing just saves the time in printing and does not limit the number of records to be fetched from the DB.I have a subreport also so it fetches the data 2 times from the DB hence makes the performance of the report worse.

To solve this problem I used command object and put the charts in the subreport and detail in main report.

In main report's Command Object I limited the number to records to be fetched from the DB to 5000 using rownum<5000 but in subreport's Command Object I did not set any limit in the query but I am doing all my aggregation in SQL which means do summary operation in DB and get only summarized data from DB.

-To solve section problem I am using Template object (new feature added in CR 2008).In which I am returning the field based on the "Group By" parameter selected by user.

-For time Field I have created two sub reports, one for chart and other one for details in the same way described in point one(Printing 1 million recordsu2026u2026).

After implementing these points my crystal reports performance improved drastically. The report that was taking 24 minute to come back now taking only 2 minutes.

However I want my report to come back with one minute. It returns if I remove the sub reports for Time Based Grouping but I can not do so.

My questions here are,

-Can I stop Subreport from fetching the data from DB if itu2019s suppressed?

-I believe using Conditional Template Object is a better option rather than having multiple detailed sections to print the data for a selected Group. However any suggestion here to improve the performance will be appreciable.

-since crystal report does not provide any option to limit the number of records to be fetched from DB, I am forced to use command object with rownum in where condition.

Please let me know about other option(s) to get this done...If there is any.

I am using Crystal report 2008.And we have developed our application the use JRC to export crystal report in PDF.

Regards,

Amrita

Edited by: Amrita Singh on May 12, 2009 11:36 AM