Limitation in processing # of records in Crystal Reports XI

[Nansat]

I am running a huge sales report ( over 50 million records) using Crystal Report XI and while reading the records, the Crystal reports is abruptly closing with out displaying anything.

is there any limitation on no of records that can get processed in Crystal Reports XI version?

 

[synapsevampire]

50 million is an insane number of rows to process in any client front end.

I suggest that you get some help, either a dba or a coder to help you preprocess the data in pass through SQL, Views or SPs to supply the information rewquired for the report.

Would be interesting to hear a person try to defend that they need to look at all 50 million rows in a report as opposed to some sort of aggregates...

-k

 

[Nansat]

If I got you correctly, you are suggesting me to have metadata layer... am I right?

and obviously 50 million records, were grouped and aggregated on the report. so the report doesnt print 50 million as such.

 

[synapsevampire]

It does attempt to use 50 million rows, which is an insane number of rows for any client to process.

Metadata is the most oft misused term in IT, what I'm suggesting is that you construct the query to perform the aggregates on the database.

If you don't have access to the database to create Views/SPs, then use the Add Command function listed under the data source to pass your own SQL.

-k

 

[IdoMillet]

You may be able to rely on aggregation by the server by using the Crystal option of "Perform Grouping on Server":

Here's the text from:

http://technicalsupport.businessobj...sliceId=&dialogID=1554409&stateId=1 0 1558047

______________________________________________

How does the 'Perform Grouping on Server' option work?

KBase Article ID:c2009635

Article refers to:Crystal Reports 8.5
Symptom

In Crystal Reports (CR) 8.5, 'Perform Grouping on Server' is an option available when you set up your report.

LOCATE THE 'PERFORM GROUPING ON SERVER' OPTION BEFORE CREATING A REPORT

1. In Crystal Reports, from the 'File' menu, select 'Options'

2. In the Options dialog box, select the 'Database' tab.

3. Under the 'Advanced Options' heading, the 'Perform Grouping on Server' option is listed.

LOCATE THE 'PERFORM GROUPING ON SERVER' OPTION IN AN EXISTING REPORT

1. In Crystal Reports, from the 'File' menu, select 'Report Options'.

2. In the Report Options dialog box, 'Perform Grouping on Server' option is listed.

==========

NOTE:

If you customize report settings from the Options dialog box, this will affect new reports.

If you customize report settings from the Report Options dialog box, this will affect the currently open report only.

==========

If the 'Perform Grouping on Server' option is selected, how does it affect your report?

Resolution

When the 'Perform Grouping on Server' option is selected, this allows a report to request the database server to return data that is already grouped and aggregated.

When the 'Perform Grouping on Server' option is not selected, Crystal Reports returns detailed records that still need to be grouped and summarized once they are received by the report.

EXAMPLE:

Use the example of a report that is grouped by the Customer_ID field and has a subtotal for that group in the Amount field. The only fields displayed on the report are the Customer_ID field and the subtotal in the Amount field.

If the 'Perform Grouping on Server' option is not selected, the SQL query of the report looks like the following example. This query causes the detailed customer records to be returned by the server that will then have to be grouped and totaled by CR once it receives the data.

EXAMPLE OF SQL QUERY WHEN 'PERFORM GROUPING ON SERVER' OPTION IS NOT SELECTED

SELECT

"TableA"."Customer_ID",

"TableA"."Amount"

FROM

"TableA" TableA

ORDER BY

"TableA". "Customer_ID" ASC

The SQL query of the report looks like the following example if 'Perform Grouping on Server' option is selected, and the other requirements for this option are met. (See further below for more information on the other requirements)

EXAMPLE OF SQL QUERY WHEN 'PERFORM GROUPING ON SERVER' OPTION IS SELECTED

SELECT

"TableA"."Customer_ID",

SUM ("TableA"."Amount")

FROM

"TableA" TableA

GROUP BY

"TableA". "Customer_ID"

ORDER BY

"TableA". "Customer_ID" ASC

TWO MAIN DIFFERENCES IN THE ABOVE EXAMPLES OF SQL QUERY

1. The second query example specifies a GROUP BY clause which will cause the database server to only return a single row per unique Customer_ID.

2. The second query example specifies the SUM of the Amount field in the SELECT clause that will cause the database server to perform the aggregation and only return the sum value.

The two main differences result in the second query returning fewer records. This allows for faster report processing especially for reports that normally return large result sets.

REPORT REQUIREMENTS TO USE 'PERFORM GROUPING ON SERVER' OPTION IN CRYSTAL REPORTS

1. From the menu 'File' | 'Report Options', select 'Perform Grouping on Server' so there is a checkmark beside this option.

2. In the Details section, format by selecting 'Suppress (No Drill-Down)' or Hide (Drill-Down OK)'.

==========

NOTE:

Hide the Details section for a report where the user is allowed to drill down to the details of the group.

When the user drills down to the details of the group, this will cause Crystal Reports to request the detail records from the database.

==========

3. In the report, create at least one group. This is required to generate the GROUP BY clause in the SQL query.

4. In the report, display only fields that are summary values and group fields. SQL syntax dictates that the only fields that you can list in the SELECT clause of a query that has a GROUP BY clause are fields that were used in the GROUP BY clause or aggregate values like SUM, COUNT, MAX, MIN, etc.

If all the above requirements are met, the report will generate the optimized type of SQL query.
___________________________________________________

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com