Grouping on the Database Server

Another powerful feature of SQL database servers is server-side processing, or server-based grouping. Server-based grouping refers to the ability of the SQL server to perform aggregate calculations on groups of records, returning only subtotals, summaries, and other aggregates to the client. If you are creating a summary report (very often the case), this server capability can significantly decrease the amount of data being passed over the network. Also, the summary calculations can be performed on the server rather than on the client.

Consider the following simple Sales by Region report:

This is the beginning of a report that simply shows all U.S.A. customers, grouped by region, with a customer count and sales subtotal for each group . Because the details section is shown, the database server will need to send all customers in the U.S.A. to Crystal Reports. Crystal Reports is actually calculating the subtotal and count for each group on the client machine as the report processes. This is confirmed by looking at the SQL query for this report:

Notice that this fairly simple SQL statement just selects database fields and limits records to the U.S.A. The only server feature used here that helps with grouping is the ORDER BY clause, which presorts the result set into Region order before sending it to the client.

To create a summary report, simply hide the details section by right-clicking in the gray details area in the left of the Design tab, and choose Hide (Drill-Down OK) from the pop- up menu. The report will now show just the summary data, with no individual customers appearing on the report.

However, if you look at the SQL query, even after refreshing the report, you ll notice that it hasn t changed, because server-based grouping is turned off by default. The server is still sending all customers in the U.S.A. to Crystal Reports, and Crystal Reports still has to cycle through the individual customer records to calculate the customer count and sales subtotals. Database fields in the details section are hidden, but the database returns them anyway.

Enabling Server-Based Grouping

There are several ways to turn on server-based grouping:

• To turn it on for the current report only, choose Database Perform Grouping on Server from the pull-down menus. You can also choose File Report Options from the pull-down menus and check Perform Grouping on Server. And, you can choose the same option when you right-click in the Available Data Sources area of the Database Expert and choose Options from the pop-up menu.

• To turn it on for all new reports you create from this point forward, choose File Options from the pull-down menus and check Perform Grouping on Server on the Database tab.

Once this option is enabled, the report should look the same. There will be some significant changes to the SQL query, however.

You will see that the SELECT clause now includes only the group field (in this case, Region) and two aggregate functions that perform the sum and count functions right on the database server before data is sent to the client. You will also see a new GROUP BY clause that you have not seen before. This new SQL function tells the server to group database records and perform the summary calculations all on the server. Only the summary totals will be sent to the client.

What s Required to Use Server-Based Grouping

Your report must meet certain criteria and be designed in a certain way to use server-based grouping. When you think about the way the server groups and summarizes records, you ll begin to realize why your report must meet these requirements, shown in Table 16-1.

Table 16-1: Server-Based Grouping Requirements

Requirement	Reasoning
There must be at least one group on the report.	For the GROUP BY clause to be added to the SQL query, there needs to be at least one group defined on the report.
The details section must be hidden.	If the details section is visible, no grouping will occur on the server, because the server must send down individual database records to show in the details section.
Include only the group fields or summary fields in group headers and footers.	If you include any other database fields (or formula fields that contain anything other than group fields or summary fields), the server will have to send detail data to the client to properly display the report.
Don't group or base record selection on Crystal Reports formulas.	Because most Crystal Reports formulas can't be converted to SQL, they must be calculated on the client before the report can select or group them. Because of this requirement, the server has to send detail records to the client. This is another reason you may want to use SQL expressions as an alternative to formulas, as SQL expressions can still be used for server-based grouping.
Running totals must be based on summary fields.	If running totals (described in Chapter 5) are based on detail fields, Crystal Reports needs the detail data to calculate them as the report processes. This prevents grouping from being done on the server.
The report cannot contain Average or Distinct Count summaries.	Crystal Reports can't convert these particular functions to SQL. Therefore, the grouping will be performed by the client if these functions are used.
Report groups can be sorted only in ascending or descending order. Specified-order grouping won't work.	Since Crystal Reports bases specified-order grouping on its own internal logic, it needs detail records to properly evaluate what specified groups to place them in. This requires detail records to be sent by the server.

Effects of Drill-Down

When Crystal Reports is performing its own grouping, the concept of report drill-down is fairly straightforward. Even though the details section or lower-level group headers and footers aren t being shown, Crystal Reports is still processing them and storing the data they create. When a viewer double-clicks to drill down on a group, Crystal Reports simply opens a new drill-down tab and displays the data it had previously not displayed.

However, when you enable server-side grouping, there is no detail data to display when a viewer drills down ”only the summary data has been sent to the client! Crystal Reports still enables drill-down, however; it just sends another query to the SQL server whenever a viewer drills down. This second query requests just the detail data for the group that was drilled into by adding additional criteria to the WHERE clause. This method provides the benefits of server-based grouping, while still allowing the powerful interactivity of drill-down. The price a viewer pays is the additional time the SQL server may take to process the drill- down query.

Look at the following example. Notice that a viewer has drilled down on a particular region. A new drill-down tab appears, showing detail data for that region. Notice the new SQL query that was created on the fly.

You can view drill-down SQL queries by choosing Database Show SQL Query when viewing the drill-down tab. If you return to the main Preview tab and show the query, you ll once again notice the GROUP BY clause.