Creating a Cross-Tab Object

When you look at a cross-tab, it s tempting to think of it as an entire report unto itself, much as an Excel spreadsheet is entirely independent. In fact, a cross-tab is just an object that resides in an existing report section. Even when you choose the Cross-Tab Wizard from the Reports Gallery, Crystal Reports just creates a cross-tab object and puts it in the report header. You can create more than one cross-tab per report, if you wish. In fact, you can even copy an existing cross-tab and put it in several different sections of the same report. It s just an object, like a text object, map, or database field.

A cross-tab can exist by itself on a report (as evidenced by the Reports Gallery Cross-Tab Wizard), or it can be placed on a report that already contains fields in the details section, as well as one or more groups. The report can be completely functional in every respect before the cross-tab is added ”the cross-tab just gets dropped in.

The first step in creating a cross-tab is to ensure that the tables you ve chosen and linked for your report contain enough data to populate the cross-tab. If, for example, you want to look at order totals for the years 1995, 1996, 1997, and 1998 by state, make sure you choose tables that include the order amount, the year the order was placed, and the state of the customer who placed the order. While this may seem rather obvious, you may not have enough data to adequately populate your cross-tab if you don t think ahead carefully .

You may or may not want to use actual report fields as cross-tab fields. If the fields are already on the report, you can add them to the cross-tab object. Or if you ve added completely different fields to the report, you can still base the cross-tab on other fields that exist in the tables you chose when creating the report.

You can use the Cross-Tab Report Creation Wizard from the Report Gallery, or add a cross-tab to an existing report you ve already created. To begin with the Cross-Tab Report Creation Wizard, simply choose it from the list of Wizards in the Reports Gallery when creating a new report. In addition to the familiar Data, Link, Chart, and Record Selection dialogs seen in other report wizards, the Cross-Tab Wizard displays a Cross-Tab row/column dialog and a Grid Style dialog similar to those discussed in more detail later in the chapter.

If you ve already created another report using another Expert or the Blank Report option, you may insert a cross-tab object whenever you want. To create a cross-tab object, it s best to select the Design tab first. Although you can place a cross-tab on the report in the Preview tab, you may not be able to accurately tell where it s being placed. In the Design tab, there s no question. Click the Insert Cross-Tab button on the Insert Tools toolbar, or choose Insert Cross-Tab from the pull-down menus . The Cross-Tab Expert dialog box will appear, as shown in Figure 11-3.

Figure 11-3: The Cross-Tab Expert dialog box

The Cross-Tab Expert dialog box has three tabs: Cross-Tab, Style, and Customize Style. The Cross-Tab tab is used to define the database fields or formulas that make up the rows and columns of the cross-tab. The Style tab lets you choose a predefined formatting style for the grid on the cross-tab. And, the Customize Style tab displays a large number of custom formatting options to precisely control the appearance of the cross-tab.

The first step to creating a cross-tab is to define the fields that will make up the row, column, and summarized fields in the cross-tab. This is done with the Cross-Tab tab. Look through the Available Fields box to find the fields you want to use for the cross-tab s row and column fields. Just drag your chosen field from the Available Fields box and drop it on the Rows or Columns box. You can also select the field in the Available Fields box and click the right arrow button beside the Rows or Columns box. If you don t want to scroll the Available Fields box to find your field, you can click the Find Field button and type all or part of the field name in the resulting dialog box. The first field name containing that string will be highlighted in the Available Fields box.

In addition to identifying row and column fields, you ll need to choose the field you want summarized in each cell (the intersection of each row and column). This will typically be a number or currency field, such as Quantity Sold or Order Amount, but it doesn t have to be. If you choose a number field, the default summary type will be a sum ( subtotal ) of the field for each cell . If you choose a field with another data type, the default summary type is a count of the number of occurrences of the field for each row/column combination. Drag the field to be summarized from the Available Fields box to the Summarized Field box, or select the field and click the right arrow next to the Summarized Fields list. If you need a different summary type, use the Change Summary button, as discussed in more detail later in this chapter.

If you d like to use an existing formula for a row, column, or summarized field, just select the formula in the Available Fields box. If you d like to create a new formula or edit an existing formula before using it in the cross-tab, click the New Formula button or Edit Formula button, either of which launches the Formula Editor, where you can create or edit the formula. The formula will then appear in the Available Fields box; you can then drag it to the Rows, Columns, or Summarized Field box.

If you re not concerned initially about doing any customized formatting for your cross-tab object, you re ready to place it on the report (formatting options on the other two tabs of the Cross-Tab Expert dialog box are discussed later in the chapter). When you click OK on the Cross-Tab Expert dialog box, you are returned to the report, with a small object attached to your mouse cursor. You can drop the cross-tab object in the report header or footer, or in a group header or footer. Cross-tabs can t be placed in the details section or in a page header or footer ”you ll get a no-drop cursor (a circle with a line through it) if you try to position the cross-tab in these sections.

When you preview the report, Crystal Reports will cycle through report data several times to properly summarize the totals for all row and column combinations ”you may note some extra time required to do this. The cross-tab will then appear in the section where you placed it.

The section in which you place a cross-tab is critical in determining the data that the cross-tab will encompass. If you place a cross-tab in the report header or footer, only one occurrence of the cross-tab will appear on the report (remember, the report header and footer appear only once, at the beginning and end of the report, respectively). This cross-tab will encompass all the data on the report. If you place a cross-tab in a group header or footer, you get as many cross-tabs on your report as there are groups, each encompassing only data for that group.

Figure 11-4 shows a cross-tab created using the XTREME sample database included with Crystal Reports. This cross-tab includes product names as the rows, and cities as the columns. Notice that all cities in all states show up in the cross-tab.

Figure 11-4: Cross-tab in report header

Contrast this with Figure 11-5, which is the exact same cross-tab object that s just been moved to a state group footer. Now there will be a cross-tab on the report for every state group, but each cross-tab will contain data only for that particular group. If you choose to, you can copy the cross-tab object between a group header or footer to the report header or footer and actually see cross-tabs for individual groups, as well as an all-encompassing cross-tab for the whole report.

Figure 11-5: Cross-tab in group footer

Note  

If you create the cross-tab through the report creation Wizard, you will see no groups, even though the row and column fields of a cross-tab are treated as if they were grouped. If you want to move the cross-tab to a group level in this report, you will need to insert a group on the desired field before moving the cross-tab object to the group header or footer.

Editing an Existing Cross-Tab

After you create a cross-tab and drop it on your report, making changes to it is easy. You must first select the entire cross-tab object, not just one of its individual pieces, by clicking either the small white space in the upper-left corner of the cross-tab (above the first row and to the left of the first column) or one of the grid lines between cells . You ll know you ve selected the entire cross-tab if the status bar displays Cross-Tab: you can select the cross-tab in either the Design tab or the Preview tab.

After you select the cross-tab that you want to modify, choose Format Cross-Tab Expert from the pull-down menus, or right-click and choose Cross-Tab Expert from the pop-up menu. This simply redisplays the Cross-Tab Expert dialog box, allowing you to change row, column, or summarized fields on the Cross-Tab tab, or format cross-tab styles with the Style tab or the Customize Style tab.

You can also pivot the cross-tab, which simply refers to swapping the rows and columns around so that what used to be the row will now be the column, and vice versa. Choose Format Pivot Cross-Tab from the pull-down menus, or right-click the selected cross-tab object and choose Pivot Cross-Tab from the pop-up menu.

Creative Use of Grouping and Formulas

As discussed previously, Crystal Reports chooses a default calculation for the summarized field when it creates the cross-tab. If you choose a number or currency field for a summarized field, Crystal Reports will use the Sum function to subtotal the numbers in each cell. This typically is what you want for this type of field (for example, the total sales figure for Green Bikes in the USA). If you use any other type of field (string, date, Boolean, and so forth), Crystal Reports will default to the Count function to count the occurrences of the particular summarized field for each row/column combination.

You are, however, completely free to change the function that Crystal Reports assigns to the summarized field. If you want to see the average sales figure in each cell instead of the total figure, it s easy to change. In either the Design or Preview tab, click the object in the cell (the intersection of the row and column). The status bar will indicate that you ve selected Sum or Count of <summarized field>. Change the summary operation to any other available summary by choosing Edit Edit Summary from the pull-down menus or by right-clicking and choosing Edit Summary from the pop-up menu. This brings up the Edit Summary dialog box.

You can change the field that s summarized, if desired, or just the summary operation itself. If you ve used a nonnumeric field for the cross-tab summarized field, you can change the summary operation from Count to Minimum, Maximum, Distinct Count, or any other summary function that is available for nonnumeric fields. You can also choose to express the summary as a percentage of another total or summary. These options are similar to how you can change the summary operation with existing group summaries and subtotals, as discussed in Chapter 3. In fact, the cross-tab in essence groups database records for every row/column combination, creating the summary or subtotal field for each cross-tab group. Chapter 3 has more information on available summary functions and what they calculate.

Tip  

Although cross-tabs are typically used for numeric analysis, you may sometimes find them of value when using string fields. For example, by using Minimum or Maximum summary functions with string fields, you can display textual information within a cross-tab cell. This may be helpful for certain types of string-oriented cross-tabs, such as schedules.

Changing Cross-Tab Grouping

Because Crystal Reports uses a procedure to create cross-tabs that s similar to its procedure for creating groups, you have some of the same flexibility to change the way the cross-tab is organized. You can change a cross-tab group when first creating the cross-tab or when editing an existing cross-tab through the Cross-Tab Expert. In the Cross-Tab tab, select the row or column field you want to change, and then click the Group Options button. The Cross-Tab Group Options dialog box appears.

The Common tab allows you to change the field on which you ve grouped, as well as the order in which the group is displayed. For a non-date field, there are three order choices:

• Ascending Order Shows the cross-tab row or column in A to Z order.

• Descending Order Shows the cross-tab row or column in Z to A order.

• Specified Order Lets you create custom rows or columns, based on the contents of the database field you chose for the row or column. This works identically to Specified Order Grouping, discussed in Chapter 3.

On the Options tab, the Customize Group Name Field check box, radio buttons , and formula button work identically to the same options in the Change Group dialog box discussed in Chapter 3. You may customize the appearance of the text that displays in the row or column of the cross-tab with these options.

If the row or column field is a date field, time field, or date/time field, the Cross-Tab Group Options dialog box offers additional options that give you even greater flexibility, similar to creating report groups with similar fields.

The pull-down list under The column will be printed: gives you a choice of how often you want a new row or column to appear in the cross-tab. Choices include every day, every week, every two weeks, every hour , every minute, and so on. Again, these are identical to the date/time grouping choices discussed in Chapter 3.

The pull-down list under The value printed for the column will be: gives you two choices: the first date in the period and the last date in the period. If you choose the first-date option with, say, a quarterly date period for 2001, the cross-tab will show 1/2001, 4/2001, 7/2001, and 10/2001. If you choose the last-date option, the cross-tab will show the exact same data in the cells, but the dates will be 3/2001, 6/2001, 9/2001, and 12/2001.

Note  

Remember that cross-tab row and column settings have no relation to any existing report groups. Changes you make in the Cross Tab Expert will have no bearing on existing report grouping, and vice versa.

Using Formulas in Cross-Tabs

Even with the powerful grouping options and the ability to change the summary function used to calculate cell values, you may not always be able to display material in a cross-tab exactly the way you want using just the database fields in the Available Fields box. You are completely free to use formulas in your cross-tabs as a row, column, or summarized field. You can create the formulas in advance with the Formula Editor, or click the New Formula button right in the Cross-Tab Expert dialog box to display the Formula Editor. After you create the formula, it will appear in the Available Fields box under the Report Fields category. Simply drag it to the Rows, Columns, or Summarized Field box.

Caution  

Because cross-tabs are calculated during the first report pass (WhileReadingRecords), you cannot use second-pass formulas in cross-tabs. You can use only formulas that calculate during the first pass. However, you can base a chart or a map on a cross-tab, because cross-tabs are processed before charts and maps. See Chapter 5 for a discussion on report passes .