Crosstabs

Crosstabs summarize data across multiple sets of related fields and are a more compact representation than a table.

Columns and Rows specify properties
Cells contain the summarized measurements.

Format Options

Fields can be added as
- Row groups
- Column groups
- Measures.
To add a field:
- Drag it into the crosstab
- Click it in the list of fields and select an option from the context menu.
  
  You can add fields more than once (ex. you can add a field as both a measure and row group).
Row and column groups are arranged in hierarchies. To rearrange the hierarchy:
- Drag the group headings.
- Click a heading and select a Move option from the context menu.
- Use the cursor keys.
  
  Rearranging the groups may change the preview data in the editor.
When the crosstab includes more than one row group or more than one column group
- Click the arrow in each heading cell to collapse the inner group.
- Click the arrow again to expand the inner group once more.
  
  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 the summary has been deleted previously.
To pivot the crosstab, click . The row and column groups will switch places.
Measures are arranged in cells. You can add any number of measures. All the measures appear together in every cell. To rearrange the measures, you can:
- Drag them in the top-most row of cells.
- Click a measure in the top-most row and select a Move option from the context menu.
- Press the up and down arrow keys on your keyboard.
All row and column groups are summarized automatically:
- To turn off a group summary:
  - Click any heading in the group
  - Select Delete Row Summary or Delete Column Summary from the context menu.
- To select the summary function and data format for a measure
  - Click an instance of that measure in the top-most row
  - Select from the context menu.
- The summary functions for numeric fields are:
  - Maximum
  - Minimum
  - Average
  - Sum
  - Distinct Count (number of different items in the row or column)
  - Count All (total number of items)
On the Crosstab tab, by default, the editor only displays a smaller, sample set of the data in your crosstab. Click to view the full set of data.
Many of the layout and formatting options set manually in tables are set automatically in crosstabs. In particular, row and column sizes are fixed.

Ad Hoc Editor’s Standard Crosstab Layout

Common tasks when working with Ad Hoc crosstabs include:

Component	Description
Columns area	Drag dimensions and measures from the Data Selection panel to this area to create column groups. Drag fields to change the order of the groups. Measures must all be in the same area; you cannot have measures in the Columns and Row areas at the same time.
Rows area	Drag dimensions and measures from the Data Selection panel to this area to create row groups. Drag fields to change the order of the groups. Measures must all be in the same area; you cannot have measures in the Columns and Row areas at the same time.
Row and column group labels	Displays the name of each field used for grouping. Right-click the group labels to use the context menu. When no row groups are defined, the words Row Group indicate this vertical region.
Row and column group values	Heading cells that show the group values. When there is more than one level of grouping, use the icons on the outer groups to expand or collapse the inner groups. Right-click a group value to exclude it or to keep only that value from among all group values of the same level.
Sorting controls	An icon beside a label shows the current sorting. Right-click a label to apply or change sorting. You can sort on multiple groups, but only one measure; sorting on a measure will reset all other measure columns to Don’t Sort.
Measure labels	Display the name of each measure in the crosstab. Right-click the measure label to change the summary function or data format of the measure.
Measures	Measures show an aggregated value in each cell of the crosstab, as well as row and column totals for each level of grouping. Click on a measure value to open an Ad Hoc table view in a new window showing the individual values that make up the aggregated value.

Common tasks when working with Ad Hoc crosstabs include:

Set the granularity of date groups	When you select a date field as a group, you can specify the granularity of the group values. Right-click the group label, select Change Grouping and select Year, Quarter, Month, or Day.
Pivot a single group	To pivot any group from row to column or vice-versa, select the group by clicking its label, then drag it to the other area. You can also right-click the group label and select Move to Column Group for row groups or Move to Row Group for column groups.
Pivot entire crosstab	To pivot all row groups to column groups and vice-versa at the same time, click .
Filtering	Right-click a group label and select Create Filter to slicing (Keep Only).
Keep Only	Slice out a single group by right-clicking its group value and selecting Keep Only. Use Ctrl-click to select multiple members to keep.
Exclude	Remove a group value from any group by right-clicking it and selecting Exclude. Use Ctrl-click to select multiple members to exclude.
Summaries (Totals)	By default, the crosstab includes grand totals of all row groups, shown in a Totals row at the bottom, and of all column groups, shown in a Totals column to the right. To toggle the row totals, right-click the left-most row group and select Delete Row Summary or Add Row Summary. To toggle the column totals, right-click the top-most column group and select Delete Column Summary or Add Column Summary. You cannot hide the inner totals of an expanded row or column group.
Change Summary Functions	Right-click a measure label and click Change Summary Function to select a summary function. For example, you may want to display an average rather than a total.
Column group limits	In some cases, the editor prompts you to confirm that you want it to return large amounts of data. When the number of column groups exceeds a configurable limit, the editor displays an ellipsis (...); its ToolTip indicates the number of remaining groups. Click the ellipsis to display them. For more information on configuration.
Sorting	Crosstabs support multiple levels of sorting: Right-click Measures to sort measure groups by label. Right-click a row or group label to sort its values. When your selection contains inner groups, they are also sorted. Right-click an inmost column header to sort the column values. Sorting can only be set for one column at a time.
Filter top or bottom N values	Filter the numeric data shown in a crosstab to show only the rows with the top or bottom N values, where N is a number that you specify. For example, you can filter a crosstab to display only the top 10 values in a column. Right-click the header you want to use for filtering and select one of these options: Filter Top N Values Filter Bottom N Values Don't Filter Values Enter the number of values you want to show in the crosstab and then select whether to apply the filter across all row groups. You can also choose to show an aggregate of the unranked values in the crosstab.
Drill to detail	Click a measure to drill to open an Ad Hoc table that displays a summarized column for each measure in your crosstab; the table is filtered by the group values for the measure you clicked. The original crosstab and the summarized table operate independently.
Merge and unmerge cells	By default, the Ad Hoc editor merges cells containing the same data into a larger, single cell to make the crosstab data easier to read. To display all of the individual cells in the crosstab instead of merged cells, hover over and select Unmerge crosstab cells. To merge the crosstab cells, hover over and select Merge crosstab cells.