Summarizing Data
A major benefit to grouping data is that it lets you put summary data within the group footer and header. This is beneficial because when there are a lot of detail records, you don’t want the reader to have to get out a calculator to calculate sub-totals and averages of columns. You want the report to do this automatically.
Crystal Reports gives you a multitude of functions for adding summary calculations to a report. Table 3-1 shows a complete list of the summary functions available.
Table 3-1. Summary functions for groups.
| Function | Description |
|---|---|
| Average | Calculate the average value. (2) |
| Correlation | Calculate the correlation of two fields. (1) (2) |
| Count | Count the number of detail records. Fields with Null values are not included in the calculation. (3) |
| Covariance | Calculate the measure of the linear relation between paired variables. (1) |
| DisctinctCount | Calculate the number of unique values for that field. |
| Maximimum | Find the maximum value of all the fields. |
| Median | Return the middle value if all the fields were sorted. (1) |
| Minimum | Find the minimum value of all the fields. |
| Mode | Returns the value with the most duplicates. |
| Nth Largest | Finds the largest value of all the fields with a ranking of N. For example, if N were 6, it returns the sixth largest value. |
| Nth Most Frequent | Finds the Nth ranking field with the most duplicate values. For example, if N were 6, it returns the value with the 6th most duplicates. |
| Nth Smallest | Finds the smallest value of all the fields with a ranking of N. For example, if N were 6, it returns the sixth smallest value. |
| Pth Percentile | Returns the value for the specified percentile of the field. (2) |
| Pop Standard | Deviation |
| Calculates how much a field deviates from the mean value. (1) (2) | Pop Variance |
| Find the population variance of a set of values in a report. (1) | Sample Standard |
| Deviation | Returns the sample standard deviation for the field. (1) (2) |
| Sample Variance | Returns the sample variance for the field. (1) (2) |
| Sum | Returns the total of all the detail fields.(2) |
| Weighted Average | Returns the weighted average of all the detail fields. (2) |
Chart Notes:
(1) See a statistics book for detailed calculation information.
(2) Can only be used for numeric data.
(3) Null values can be included if you set them to return their default values. To do this, select the menu options File > Report Options. Then check the box for converting Null field values to their default.
Summary fields can be put in both the Group Header and Group Footer sections. It might seem strange that a summary field can be in the Group Header section since it is printed before the detail records are printed. But if you recall from Chapter 1, Crystal Reports uses a two-pass process to build the data printed on a report. The summary fields are calculated in the first pass and are already known before any of the records are printed. That’s why summary calculations can appear prior to printing the actual data.
To create a summary value for a group field, right-click on the field that you want to summarize and select Insert. The pop-up menu gives you the option of inserting a summary or a running total.
If Insert is missing from the pop-up menu, then the report object you clicked on is a second-pass formula and it can’t be summarized on. Please see Chapter 1 for more information about how the two-pass reporting process works.
Inserting a summary brings up the dialog box in Figure 3-25. The top dropdown box shows the field you had selected. Below that is a dropdown box that selects the summary function to perform. The third dropdown box tells where the field should go. If you put it in the report footer, it is a grand total. If you put it within an existing group, it is a sub-total. If there are no groups based upon the field you want to associate this summary field with, then click the Insert Group button and after you close the dialog box a new group will be created. Ideally, this won’t be necessary because you will plan out your report so that it already has all the necessary groups created.
Figure 3-25. The Insert Summary dialog box (Crystal Reports XI R2)
By default, sub-totals are automatically placed in the group footer. You can move this field to the group header if you want to keep all the summary information in the same section and printed before the detail rows.
New to Crystal Reports XI R2 is the ability to add a summary field to all groups simultaneously. If you look in Figure 3-25, in the middle of the dialog box is the checkbox called Add To All Group Levels. This option is only available with the R2 edition. If you have multiple groups in a report, checking this box saves you the time of having to open and re-open this dialog box to insert a summary field into every group footer section.
To change the summary function after it has been created, right-click on the summary field and select Edit Summary. This brings up a dialog box with a dropdown box of all the available summary functions. Select the one you want to change it to and click on the OK button.
If you move a summary field into a new group, it takes the new group as its parent and calculates the summary value for that group.
If you were really paying close attention earlier, you might have noticed that none of the summary functions in Table 3-1 calculate the percentage of a number. For example, if you want to show the percent sales that a sales person had in relation to the entire department, there is no way to do it. Of course, you could always do this calculation yourself using a custom formula, but you might think that with all the advanced functionality that Crystal Reports gives you that it would be a standard function. Well, it turns out that it is available. At the bottom of the Insert Summary dialog box is an option to show the summary as a percentage of another total.
Percentages are always calculated based on a total from an outside group. In other words, the percentage is calculated using an interior group value compared to a value outside of that group. For example, if you want to calculate the percentage of a value in a group, then this percentage will be based on the grand total amount because the grand total is outside of the group level. If you want to calculate the percentage of a value in sub-group, then it will either be based on the parent group or the grand total. You can’t calculate the percentage of a grand total value because it is already at the outer-most level and there is no outer number to calculate the percentage from.
