Modifying the Cross-Tab Properties
After getting the empty cross-tab object on the report, you need to modify its properties so that it knows what data to display and how to display it. Right-click on the cross-tab object and select Cross-Tab Expert. This opens the Cross-Tab Expert shown in Figure 13-4.
Figure 13-4. The Cross-Tab Expert dialog box.
The Cross-Tab Expert shown in Figure 13-4 is what you’ll see when you right-click on an existing cross-tab object and select Cross-Tab Expert. It is very similar, but not exact, to the Cross-Tab Expert dialog box you see when creating a new report using the Cross-Tab Report Wizard. The new report wizard dialog box is missing a few key buttons. The New Formula and Edit Formula buttons are not available. Secondly, the Group Options and Change Summary buttons are missing. The Change Summary button lets you show a percentage of the overall total in the summary field. The dialog box presented by the new report wizard box only lets you choose from the basic summary functions. The Group Options button lets you customize the sorting order of the groups. Thus, if you want to create new formulas, have advanced summaries, or customize the groups, you have to wait till the wizard is finished and go back and edit the cross-tab. I’ve found that when I need this type of functionality in the cross-tab object, it is easier to just create a new blank report and add the cross-tab object afterwards. By adding the cross-tab object later, I can first create whatever formulas I need as well as know that the Cross-Tab Expert will have its full functionality available.
The dialog box in Figure 13-4 has three tabs: Cross-Tab, Style, and Customize Style. The Cross-Tab tab, discussed next, sets the fields shown on the cross-tab object. The Style tab lets you apply a predefined style to the cross-tab. The last tab, Customize Style, lets you specify (in great detail) the formatting of each of the cross-tab elements.
The first tab, Cross-Tab, has an Available Fields area and three primary input areas: Rows, Columns, and Summarized Fields. The Available Fields area shows you all the fields that can be used within the cross-tab object. Add fields from this list into the appropriate sections to the right. Do this either by dragging and dropping the fields, or by selecting a field and clicking one of the arrow buttons. As we saw in other dialog boxes, you can preview the data in a field by selecting that field and clicking the Browse Data button.
All three input areas require you to add a minimum of one field before the cross-tab is functional. For example, you can’t specify fields for the Rows and Summary Fields windows and not put a field in the Columns window. You can also add multiple fields to each window to make the sub-groups.
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 by clicking on the Change Summary button.
When changing the summary function for a numeric field, you can choose from all the summary functions in Crystal Reports. Summarizing on a text field is more restrictive because 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 13-1.
Table 13-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. After you save and close this dialog box, the 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 is added to the report just like any other formula and it can be placed anywhere on your report.
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.
