Working with Crosstabs
Crosstabs have different data, layout, and format options than tables or charts. If you selected Crosstab when you created a view, the following section explains tasks specific to your crosstab development.
Using Fields in Crosstab
Fields can be added to crosstabs as row groups or column groups. Measures can be added to crosstab rows or columns as well, but all measures must be included in a crosstab as either a row or a column. That is, you can add one or more measures to the crosstab as columns, or add one or more measures to the crosstab as rows, but you cannot have one measure as a column and another as a row in the same crosstab.
Crosstab Rows and Columns
When creating a view for a crosstab-type view, keep in mind 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 a Move option from the context menu or press the cursor keys. Rearranging the groups may change the preview data in the editor.
Adding a Field or Measure to a Crosstab Group
-
In the Data Source Selection panel, click to select the field you want to add to the crosstab as a group. Use the CTRL-click to select multiple items.
-
Drag the selected item into the Columns or Rows box 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 appear in the crosstab below the Measures heading.
-
Measures included as columns appear in the crosstab to the right of the Measures heading.
You can right-click a measure in the crosstab to open a context menu that provides these options.
-
Change Summary Function
-
Change Data Format
-
Remove From Crosstab
-
Create Filter
-
Move Up or Move Down
Measures are arranged in cells. You may add any number of measures. All the measures appear together in every cell. To rearrange the measures, drag them in the measure label area.
Pivoting a CrossTab
You may pivot a crosstab in two ways:
Pivot the entire crosstab by clicking 
. The row and column groups switch places.
Pivot a single group:
-
To pivot a single group, right-click it and select Switch to Column Group.
-
To pivot a single column group, right-click it and select Switch to Row Group.
Note: Pivoting removes any custom sorting applied to headings in your crosstab. It does not affect column or row sorts.
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 to remove all groups except the selected one from the crosstab.
-
Exclude to remove this 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; you are unable to slice by both row groups and column groups at once. Compare slice to drill-through, drill to details, and filtering.
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 from the context menu. 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. Note that you are unable to change the summary function on custom fields 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.
Related Topics