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

In Crystal Reports (CR) version 8.5 and newer, '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'

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

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?

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.

PERFORM GROUPING ON SERVER SIDE GROUP BY SUM SHOW SQL QUERY REPORT OPTION Crystal Reports SQL-type databases SQL Query Server-side grouping , c2009635 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Crystal Reports 2008 V1 ; SAP Crystal Reports 10.0 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 8.0 ; SAP Crystal Reports 9.0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2