Cross-Tabs

Chapter 12 - Analyzing Data Visually
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

A cross-tabulation (cross-tab) object is a two-dimensional grid that helps users quickly analyze data by comparing two variables against each other. Cross-tabulation is a popular basic bivariate and multivariate statistical process whose goal is to identify interdependency between two variables. The business question being asked when a cross-tab is used is whether there is a relationship between the two variables being compared.

Cross-tabs can compute frequency counts and summary statistics for pairs of variables. The intersection cell values are always numerical. As an example, consider that a cross-tab could present data showing how gender and salaries are related. Or in the case of the VistaNations company, a cross-tab can help answer the question of whether there is an interdependency between five-star resorts and the regions in which the resorts are located. The business question answered by a cross-tab involves comparing one parameter to another: gender versus salary and five-star status versus region location. Reports can contain as many cross-tabs as you need.

The row and column format of a cross-tab lets a user see large quantities of summarized data in an easy-to-digest spreadsheet-like fashion, with numerical values displayed in row/column intersecting cells. The row and column data are the parameters to the grid, e.g., five-star status and region location. The results of the comparison are presented in tabular format, and the intersection of a row and a column answers a question that involves both parameters. Figure 12.29 depicts row and column data intersecting, with a numerical value in their intersection; this cross-tab shows the average purchase price by resort in Canada.

Business Question: Create a report that compares the average unit purchase price of resorts in the Canadian provinces.

Figure 12.29. Cross-tab example

The intersection cell of a cross-tab displays summary data. The cross-tab grid can therefore be placed in any header or footer section in a report. When you drop the cross-tab into place, it will be outlined with a gray border; position the top-left corner where you want the grid to be placed. Since each section has access to different information, the data displayed by a cross-tab changes based on the section into which it is placed.

Warning 

A cross-tab cannot be placed in the Details section. The Details section contains individual rows of data retrieved from a database; no summarization is allowed in this section.

Creating a Cross-Tab

The process of creating a cross-tab object begins with thinking about and identifying the three or more fields that will participate in the cross-tab:

• Field for the column headings

• Field for the row headings

• A summary data item relating the column field to the row field

A report may consist entirely of a cross-tab object or it can also contain the detail that supports the summarization. To add a cross-tab object, choose Insert > Cross-Tab or click the Insert Cross-Tab toolbar icon. This opens the Cross-Tab Expert, shown in Figure 12.30.

Figure 12.30. Cross-Tab Expert

Configuring Data Options

The goal of setting options for a cross-tab is to choose appropriate rows and columns first and then select a field to summarize that is related to the chosen rows and columns. The Cross-Tab Expert takes the familiar approach of listing values on the left that you choose from and areas on the right to which the chosen fields are added. On the right, three distinct areas are provided to hold fields from the Available Fields list: Columns, Rows, and Summarized Fields. The arrow buttons to the left of these areas let you copy fields to or remove fields from the grid or drag a field from the list on the left and drop it into one of the areas on the right. This Available Fields list contains all report fields, all fields in the attached data source, and all formula fields.

Tip 

In Crystal Reports 9, you can use memo fields in a cross-tab grid by first creating a formula in the Formula Workshop, setting the formula’s value to the memo field, and then using the formula field in the cross-tab. For display purposes, the formula containing the memo field should make use of string functions such as LEFT, MID, and RIGHT to control which text in the field displays in the cross-tab or use an array subscripting technique to display parts of the field (e.g., {Database.MemoField} [27 to 41]).

For the cross-tab being created in Figure 12.30, resort codes will be used as row data, and resort countries with a subcategory of StateProvince will appear as columns. In the intersecting cells, the average purchase price for the resort will be displayed. The intersecting cell can display any of the following and can display multiple values at the same time:

• A single numerical value

• Multiple numerical values

• Percentage value

Above each of the row, column, and summary areas, an up and down arrow pair appears. Use these buttons to reorder the way fields are categorized and subcategorized in the cross-tab. For instance, you could select the Resorts.Country option in the Columns area and use the up and down arrows to position the Country field below the StateProvince field.

Tip 

The cells of the cross-tab can be resized by selecting the field in the cell and resizing just as you would any other field in a Crystal report. It is the field size that controls the size of the cross-tab cell.

Below each of the row, column, and summary areas, a Group Options or Change Summary button is visible. When you select the row, column, or summary data item, the associated button becomes active. For the row and column data, the Group Options button opens a Cross-Tab Group Options dialog. From the dialog, you can control the sort and grouping order for the data in the row or column; it can be sorted in ascending, descending, or specified (custom) order. The Change Summary button affects the data in the intersecting cell and allows you to choose a different summary method from the full range of those available in Crystal. Refer to Chapter 6, “Summarizing Information,” Table 6.1, for a complete list and description of the summary methods available in Crystal Reports.

Styling a Cross-Tab

Crystal Reports 9 makes it easy to create a great-looking cross-tab. Built-in styles give you quick and slick reports, while customized styles give you formatting control down to a very detailed level.

Built-In Styles

The Styles tab contains a list of built-in styles that can spruce up your cross-tabs with color schemes, font combinations, and cell border variations. From the Original style (simple grid) to the Custom style (any combination of formatting features you can think of), you can quickly give the cross-tab a professional look. Figure 12.31 shows the Basic - Gray Scale style (after all, this is a black and white book!).

Figure 12.31. Cross-Tab Styles

The Custom style at the bottom of this list can be used to completely define a cross-tabs style. To use it, select the Custom option and then switch to the Customize Style tab.

Customizing a Style

The Customize Style tab, shown in Figure 12.32, uses the style specified on the Style tab as a starting point, and from there, you can change the look of just about everything.

Figure 12.32. Customizing a style

To customize options for a row or column, first select it in the appropriate area on the top half of the screen. Then use the options in the Group Options area to modify the look and behavior of the selected row or column. All options are not available at all times; the selected row determines the available options. The customizations you can make for rows and columns include the following:

• Suppress a subtotal

• Suppress a label

• Assign an alias to a row or column that can be used in a formula

• Change the background color of each row independently of the other rows and columns

For the data values in the Summarized Fields area, you can choose to display text labels for the summary values. The text for the labels can be edited like any text object in Design mode. When more than one field is being summarized, you can determine how the values display inside the intersection cell: Horizontal (side-by-side) mode or Vertical (one below the other) mode. If there is only one summary value, this option is disabled.

The Grid Options area contains settings that affect the cross-tab as a whole. The following options can be enabled or disabled using the associated check box, and any combination of these options is allowed:

• Show Cell Margins

• Indent Row Labels (and set the indent size in inches)

• Repeat Row Labels

• Keep Columns Together

• Column Totals On Top

• Row Totals On Left

• Suppress Empty Rows

• Suppress Empty Columns

• Suppress Row Grand Totals

• Suppress Column Grand Totals

The Format Grid Lines button opens the dialog shown in Figure 12.33. Here, you can control the visibility, color, style, and width of each grid line in the cross-tab.

Figure 12.33. Formatting grid lines

Customizing a Cross-Tab

After creating a cross-tab grid and placing it in the report, you can continue to customize all aspects of the grid as well as perform additional tasks on it. With the cross-tab grid selected, the Format menu provides access to the Format Cross-Tab option, the Cross-Tab Expert, and the Pivot Cross-Tab option. Right-clicking a selected cross-tab provides additional choices, as shown in Figure 12.34. From here you can select Insert Chart or Insert Map to directly add a chart or map to the report based on the summarized data in the cross-tab. You can also use the Group Sort Expert option to limit the data reported in the cross-tab to the Top N, Bottom N, Top Percentage, or Bottom Percentage values.

Figure 12.34. Cross-tab options

The Cross-Tab Expert can be reopened at any time to display the dialog originally used to create the cross-tab. You can change all or any of the original settings.

Tip 

When you right-click a cross-tab to display its submenu of options, be sure to right-click the upper-left corner of the cross-tab and not in any of the individual cells. Clicking an individual cell will display a formatting menu specific to the field in the cell.

Pivoting a Cross-Tab

A cross-tab in a report can be pivoted to swap the row and column axes. This is helpful if, after previewing the cross-tab, you decide that the descriptions and data would fit more successfully on the screen by converting wide columns to rows. To pivot a cross-tab, select the cross-tab and use the Pivot Cross-Tab menu command shown in Figure 12.34.

Interactive Analysis with Cross-Tabs

When viewed in Preview mode in Crystal Reports, a cross-tab is an active in-place editing object. You can select any of the column headings and drag and drop it to a different column heading location in order to analyze the data in different ways. This gives the user the ability to think through a problem interactively by comparing one value against another in real time. When a column title is selected, dragging it to a new location converts the mouse pointer to an icon that resembles a piece of paper. Figure 12.35 shows the cross-tab columns repositioned from their original locations, shown in Figure 12.29.

Figure 12.35. Cross-tab after interaction

Note 

Data cells cannot be repositioned to column locations; selecting a data cell and attempting to move it will result in repositioning the entire cross-tab object.

Formatting a Cross-Tab

Basic formatting options for any report object are set with the Format Editor. For a cross-tab, the options include setting tool tip text, conditional controls over suppressing the cross-tab, and adding hyperlinks. To open the Format Editor for the cross-tab, select the cross-tab on the page and then choose Format > Format Cross-Tab, or right-click the selected cross-tab to directly choose Format Cross-Tab from the submenu. The Format Editor contains tabs that allow you to format the current object of focus (the chart) for page and layout issues. As with other objects in Crystal, the universal Format Editor can be used to format the cross-tab with options such as conditionally suppressing based on a formula, drawing a border around the cross-tab, and adding a hyperlink to another destination. Programmers who write in languages like Java or use the .NET software platform can manipulate a cross-tab object in code using the unique object name that is set in the Format Editor; CrossTab1 is the default and it can be changed.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback