Crosstab Axes and Measures Selection

This section describes the process of populating the columns and measures in a crosstab. To create a crosstab using the selected universe, click the Crosstab Mode button on the Layout tab. The Selected Items field displays the crosstab layout structure, as shown below.

 

   Figure 224: Crosstab Layout Structure

Within the crosstab you can set between one and seven items as crosstab axes. These items can be applied to either the horizontal or vertical axes but the total number of items on the two axes must not exceed seven. Each item placed on the horizontal axis becomes a crosstab column. Each item placed on the vertical axis becomes a crosstab row.

Within the report you must define between one and 15 measures. Each measure adds a value to a column and row. Measure can be applied to both rows and columns in the crosstab. They add values, using functions including sum and count, based on the data in the rows and columns.

Selecting Axes

Expand the universe, as described in Universe Selection and Navigation on page 347.

To create a column, select and drag an item or a value from the Select Folder and Items: field to the Selected Items: field and drop the item over the area labelled Drag items here to define horizontal axis. To create additional columns drop new items next to the existing columns. As the cursor moves over the existing columns a small triangle indicates where the new column will be placed when the item is dropped.

To create a row, select and drag an item or a value from the Select Folder and Items: field to the Selected Items: field and drop the item over the area labelled Vertical axis. To create additional rows drop new items next to the existing rows. As the cursor moves over the existing row a small triangle indicates where the new row will be placed when the item is dropped.

You can select multiple items to drag and drop by holding the CTRL key whilst clicking the items.

You can change the order of the rows by dragging them to a new position. The small triangle indicates where the row will be placed when it is dropped.

Selecting Measures

Expand the universe, as described in Universe Selection and Navigation on page 347.

To add a measure to a crosstab, select and drag an item or a value from the Select Folder and Items: field to the Selected Items: field and drop the item over the area labelled Drag items here to define measures. You can create up to 15 measures in your report.

If the item you drag contains a numeric value, the measure defaults to Sum. If it contains a non-numeric values, the measure defaults to Count.

To change the summary function:

1.   Right-click the measure and select Summary from the popup menu. The Select Summary Function window is displayed, as shown below.

 

   Figure 225: Select Summary Function Window for a Crosstab

2.   Select a function from the Summary Function drop-down list. The functions are listed in the following table.

Table 69: Summary Functions Description

Summary Function

Description

Sum

Adds together the numeric values of each item in the row or column.

Count

Counts the number of items in the row or column.

Maximum

Returns the value of the highest value item in the row or column.

Minimum

Returns the value of the lowest value item in the row or column.

Average

Returns the average value of all items in the row or column.

The Select Summary Function window checks that the selected function can be applied to the data type when you click the OK button. If you have selected an unavailable function a warning message is displayed and the function returns to the default value for the data type.

3.   The Vertical Summary drop-down list is only available when you select the function Sum or Count. You can choose to display the measure below the column in one of the formats described in the following table.

Table 70: Vertical Summary Options

Vertical Summary Option

Description

None

No measure is applied to the column.

Sum only

Displays the result of the sum or count function as a value.

Percent only

Displays the result of the sum or count function as a percentage.

Sum and Percent

Displays the result of the sum or count function as a value and a percentage.

4.   The Horizontal Summary drop-down list is only available when you select the function Sum or Count. You can choose to display the measure next to the column in one of the formats described in the following table.

Table 71: Horizontal Summary Options

Vertical Summary Option

Description

None

No measure is applied to the column.

Sum only

Displays the result of the sum or count function as a value.

Percent only

Displays the result of the sum or count function as a percentage.

5.   The Crosstab has a grand total. Enter a title in the Grand Total Label field.

6.   Click OK to apply the changes and close the Select Summary Function window.