Chapter 3 - Sorting and Grouping
Being able to sort records in either ascending or descending order is a fundamental reporting skill. Sorting makes it easy for a user to quickly find a particular piece of data buried within a large report. This chapter has ten tutorials that quickly get you up to speed on every aspect of sorting and grouping data.
Become a Crystal Reports expert with the authoritative resource available.
The tuturials and tips in this book will take your skills to the next level.Buy at
This is an excerpt from the book Crystal Reports Encyclopedia.
Click to read more chapter excerpts.
Summarizing Report 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 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 where 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 would return the sixth largest value. |
| Nth Most Frequent | Finds the Nth ranking field with the most duplicate values. For example, if N were 6, it would return 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 would return 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 Stanadard Deviation |
Return the sample standard deviation for the field. (1) (2) |
| Sample Variance | Return the sample variance for the field. (1) (2) |
| Sum | Return the total of all the detail fields.(2) |
| Weighted Average | Return the weighted average of all the detail fields. (2) |
Chart Notes:
- See a statistics book for detailed calculation information.
- Can only be used for numeric data.
- 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.
| Note |
|---|
| Summary fields can be put in both the Group Header or 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 Multi-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 fields can appear in the header as well as the footer sections. |
To read all my books online, click here for the Crystal Reports ebooks.