When you add a field to the Summarized Fields window, by default it assigns one of two different summary functions to the field. If the field is numeric, then the Sum() function is used. If the field is text, the Count() function is used. You can change the default summary function after it has been added to the Summary Fields window by clicking on the Change Summary button.
When changing the summary function for a numeric field, all the summary functions in Crystal Reports are available. Summarizing on a text field is more restrictive. You have to choose from a list of text compatible summary functions because not every function works with a text field. For example, it isn’t possible to calculate the average value of a text field. However, you can determine the 5th largest item of all available items and print that. The text compatible functions that you can choose from are in Table 11-1.
Table 11-1. Summary functions available for text fields.
| Function Name |
|---|
| Count() |
| DistinctCount() |
| Nth Largest() |
| Nth Smallest() |
| Nth Most Frequent() |
| Minimum() |
| Maximum() |
| Mode() |
Summary functions are typically based on fields from a data source. But there are times when you need to summarize a custom formula field. You can select a formula that already exists or you can create a new formula from the Cross-tab Expert dialog box. When you click the New Formula button, it brings up the Formula Editor dialog box. This is the same dialog box discussed in Chapter 7. After you save and close this dialog box, that formula is added to the list of available fields at the bottom of the dialog box. You can drag and drop the formula field into the Summary Fields window. Although this formula was created via the Cross-tab Expert, it will now be listed along with all the other formulas in your report. Thus, it can be placed on your report just like any other report object.
Cross-tab summary functions can’t perform many tasks that you take for granted with other types of reports. For example, it is a common error to attempt to devise formulas for a cross-tab report that calculates a value depending upon the value of another cell in the cross-tab or the sum of a group of cells in another row or column. You expect this to be possible because it is easy to do for a standard report. But a cross-tab report can’t do this because it is built around the premise that each summary value is calculated independently of the other cells. Cross-tab summary fields are calculated during the report’s first-pass and there is no mechanism to reference the value of any other field in the cross-tab. You can try to get creative and write a formula to emulate these tasks, but you will find that each formula relies upon having information about the other fields in the cross-tab. Thus, it won’t succeed. The tasks that a cross-tab report can’t perform are as follows: calculating second-pass formulas, calculating running sums, calculating percentages of the subtotal/grand total and sorting rows according to the row totals.
Once you are finished adding the fields, click the OK button. The cross-tab object is added to your report and you are put back at the report designer.
Adding additional summary fields to the cross-tab object results in the values being placed in the same cell. Each is stacked vertically on top of the other. The first field added is placed at the top. You also have the option of placing them side-by-side by going to the Customize Style tab and selecting the Vertical option.
Adding additional grouping fields for either the rows or columns creates a sub-group format. This is very similar to a standard report that uses multiple groups. The first field becomes the outermost group and the remaining fields are grouped based upon the subset of data. If you preview the report and find that the fields are not in the proper order, you can rearrange them by opening the Cross-tab Expert again and using the mouse to drag and drop the fields to the correct position in the list.
Figure 11-5 shows a cross-tab object in design mode that has two grouping fields in the row and two summary fields.
Figure 11-5. The cross-tab object using multiple fields.
The large, left-most block in the cross-tab object represents the outermost group. The innermost group is represented by the two thinner blocks just to the right of it. Both groups have a subtotal field associated with them. This is similar to a standard multi-group report because when it prints there will be a group footer showing the subtotal for the group. The two summary fields are visually represented by the multiple fields filled with the number five. The top-most field is the first summary field that was added to the Summary Fields window. Running the report generates the output shown in Figure 11-6.
Figure 11-6. The output of the cross-tab report using multiple fields.
Question: My data is a range of numbers between 1 and 100. I want my cross-tab to group the data in intervals of 10. How can I do that?
Answer: This can be accomplished by creating a formula that returns the grouping names you want to display and using that formula in the cross-tab object. Here is an example formula using Basic syntax:
