How to optimize the performance of crystal report?

[Former Member]

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

[former_member11696]

Please post one question per posting so it is easier for others to answer and follow along. There is a 1500 character limit and then formatting is removed to save space.

[Former Member]

I dont know why my question is coming like one whole paragraph though I used proper formatting to make it more understandable....

[Former Member]

1) 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.

2) 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.

3) 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.

1) 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.

2)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.

Edited by: Amrita Singh on May 12, 2009 12:26 PM

[Former Member]

/

• I believe the problem is occuring due to the length of the msg hence breaking the msg in 2 parts. */

3) 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

[Former Member]

Hi,

>>-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.

Sorry, I have replied to one of your question with rownum as the answer for you which you have already used and I do not understand when you can filter the records at the DB level using rownum then why are you looking for a way to do the same thing on report level? Does it not mean it is a threat/performance degrade factor?

If I am wrong then please make me correct, but I need the explaination.

Thanks

-Azhar

[Former Member]

For (3), you can eliminate the need for the subreport for the Time field grouping by using the CAST function in SQL to force the data type of the time field to be a string.

To minimize the data sent back to the report, I would create a subreport for the chart, and another subreport for the limited number of detail records. The chart subreport would be based on a database command that performs the summary of all of the records, so only the summary data is returned to the report. The detail subreport would use the database's syntax to limit the number of records returned. This is database dependant, but I've seen

SELECT TOP 5000 field1, field2,...

and

SELECT field1, field2, ...  FETCH FIRST 5000 ROWS ONLY

Combined, these should speed things up for you...

HTH,

Carl