Working with Crosstabs

About Crosstabs

Data Roles for a Crosstab

Specify Properties for a Crosstab

Managing Rows and Columns

Sort a Row or Column

Rearrange Rows and Columns

Resize Columns

Create a Hierarchy from a Crosstab

About Crosstabs

A crosstab displays the intersections of category values and measure values as text. If the crosstab contains measures, then each cell of the crosstab contains the aggregated measure values for a specific intersection of category values. If the crosstab does not contain measures, then each cell of the crosstab contains the frequency of an intersection of category values.

Data Roles for a Crosstab

The basic data roles for a crosstab are columns, rows, and measures. You can assign either a single hierarchy or any number of categories to each column and row role. If you assign measures to the crosstab, then the measure values are displayed in the cells of the crosstab. If you do not assign measures, then the frequency of each intersection of values is displayed in the cells of the crosstab.

Specify Properties for a Crosstab

On the Properties tab, you can specify the following options:

Name

specifies the name of the visualization.

Show missing labels as blanks

displays missing values as empty cells in the crosstab. By default, missing values are represented by a period (.) character.

Indented

selects the indented layout for the crosstab.

Show column subtotals

adds subtotals to each column for each node on the row axis after the first.

Note: For the indented layout, subtotals are always enabled.

Show column totals

adds totals to each column.

Note: Total values are aggregated based on the aggregations for each measure.

Show row subtotals

adds subtotals to each row for each node on the column axis after the first.

Note: For the indented layout, subtotals are always enabled.

Show row totals

adds totals to each row.

Note: Total values are aggregated based on the aggregations for each measure.

Totals Placement

specifies the location of totals and subtotals. Select Before to place the totals and subtotals before the axis headings. Select After to place the totals and subtotals after the axis headings.

Note: For the indented layout, totals are always placed before the axis headings.

Managing Rows and Columns

Sort a Row or Column

By default, the crosstab is sorted alphabetically by the values of the first category that you assign to the Rows role. To change the sorting, right-click on the heading for the row or column that you want to sort by, and then select Sort and select the sorting method.

If you apply sorting to a measure, then the sorting for the rows is applied as a secondary sort. The outer row values are sorted by their subtotals for the measure. To remove measure sorting, select a sort for any category.

Rearrange Rows and Columns

To rearrange your rows and columns, drag and drop the row headings and column headings.

Resize Columns

To resize a column, click, and then drag and drop the left or right edge of the column heading.

Create a Hierarchy from a Crosstab

Crosstabs enable you to create hierarchies from the categories on a crosstab axis. To create a hierarchy, right-click on a category heading, and then select Create Hierarchy. The categories are replaced with a new hierarchy.

The name of the new hierarchy is generated from the name of the outermost category, with the suffix “Hierarchy.”

Last updated: January 8, 2019