2.7 Working with Standard Crosstabs

Crosstabs have different data, layout, and format options than tables or charts.

2.7.1 Using Fields in Crosstabs

Fields can be added to crosstabs as row groups or column groups. Measures can be added to crosstab rows or columns. However, all measures must be included in a crosstab as either a row or a column. You can add one or more measures to the crosstab as columns or rows. However, you cannot have one measure as a column and another as a row in the same crosstab.

Crosstab Rows and Columns

While creating a crosstab view, ensure that row and column groups are arranged in hierarchies. Drag the group headings to rearrange the hierarchy. You can also right-click a heading and select the Move option or press the Arrow keys. Rearranging the groups might change the preview data in the editor.

To add a field or measure to a crosstab group:

  1. In the Data Source Selection panel, select the field that you want to add to the crosstab as a group.

  2. Drag the selected item into the Columns or Rows in the Layout Band.

Crosstab Measures

Measure labels are displayed in the crosstab based on their status as a row or column:

  • Measures included as rows are displayed in the crosstab below the Measures heading.

  • Measures included as columns are displayed in the crosstab to the right of the Measures heading.

Right-click a measure in the crosstab to perform the following:

  • Change Summary Function

  • Change Data Format

  • Remove From Crosstab

  • Create Filter

  • Move Up or Move Down

Measures are arranged in cells. You can add any number of measures. To rearrange the measures, drag them into the measure label area.

Slicing

The slice feature lets you keep or exclude group members in a crosstab. To slice, right-click a group member and select:

  • Keep Only: Removes all groups except the selected one from the crosstab.

  • Exclude: Removes the selected group from the crosstab.

Use Ctrl+click and Shift+click to select multiple groups to keep or exclude.

NOTE:You can select multiple row groups or multiple column groups. However, you cannot slice by both row groups and column groups at once.

For more information about working with filters, see Section 2.8, Using Filters and Input Controls.

Summarizing

All row and column groups are summarized automatically.

  • To turn off a group summary, right-click any heading in the group and select Delete Row Summary or Delete Column Summary.

    To reapply the summary, right-click the heading and select Add Row Summary or Add Column Summary.

    NOTE:The Delete Summary option is available only for the outermost group on either axis (either the outermost row group or the outermost column group).

  • To select the summary function and data format for a measure, right-click the measure label and select from the context menu. You cannot change the summary function on custom items that calculate percents (Percent of Total, Percent of Column Group Parent, and Percent of Row Group Parent).

  • The summary functions for numeric fields are Sum, Average, Maximum, Minimum, Distinct Count, and Count All. Distinct Count is the number of different items in the row or column; Count All is the total number of items. For example, if there are 3 widgets of type A and 3 widgets of type B, Distinct Count is 2 and Count All is 6.

Collapsing and Expanding Members

By default, the editor displays each row and column group of a crosstab in a collapsed state. You can view the totals for the group, but not the measures for its individual members.

To view measures for a group's members, right-click the group label and select Expand Members.

When a group's members are expanded, select Collapse Members to hide the measures. Collapsing an outer group also collapses its inner groups. The Expand Members and Collapse Members options are only available for outermost groups, or for inner groups nested in an expanded outer group.

When you collapse a group, its summary is automatically displayed. This prevents invalid crosstab layouts in which there is nothing to display for some totals if the summary has been deleted previously.

Sorting

By default, the rows and columns of a crosstab are sorted in alphabetical order of the group names.

To change crosstab sort:

  1. Right-click the heading that you want to sort your crosstab.

  2. In the context menu, select the sorting option to apply:

    • Sort Ascending

    • Sort Descending

      A blue dot displays in the context menu next to the currently applied sort option.

When the crosstab includes more than one row group or more than one column group, the inner groups are also sorted according to your selection. Only one measure can be used for sorting at a time. Changing the sort order for another measure resets all others to the default.

Resizing and Layout

Many of the layout and formatting options that are set manually in tables are set automatically in crosstabs. In particular, row and column sizes are fixed and no spacer is available.