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, then 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. Remember that text fields aren’t allowed to be printed in a cross-tab cell. Instead you have to choose from a list of text compatible summary functions. 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.

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 then 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.

Crosstab 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.