Pivot Table

Pivot Table cross-tabulates a value (metric) against rows and columns (dimensions) and displays the result as a two-dimensional table.

Pivot Table

Pivot Table

Specification

Field roles

Pivot Table treats rows and columns as dimensions and the value as a metric, and shows the aggregated result as a table.

Item	Role	Notes
Rows	Dimension	One or more.
Columns	Dimension	Optional.
Value	Metric	Supports numeric and string metrics.

You can control the order with row sort / column sort. See Filter, sort, and limit for details.

Tips

Drill down

Pivot Table can display rows hierarchically and let you collapse them.

Set this up as follows.

1. Set multiple row fields.
   • In the chart settings, set 2 or more fields for Rows, such as a major category and a minor category.
   • Example: state (major category) → city (minor category).
2. Switch to tree display.
   • In advanced settings > Layout > Default hierarchy type, choose Tree.
     • Grid: the conventional table format.
     • Tree: displays a hierarchical structure. You can expand and collapse it using the toggle icon.
3. Set the initial state to collapsed (optional).
   • Checking Collapse by default collapses all levels of the hierarchy on initial display.

Additional notes:

• This can only be set when there are 2 or more row fields.
• Report viewers can freely expand and collapse the hierarchy.

Differences by processing method

Pivot Table shows different results depending on whether in-memory processing was used or SQL-based processing was used.

• With in-memory processing
  • There's no limit on the number of rows or columns, and all aggregated data can be displayed.
• With SQL-based processing
  • When both rows and columns are specified, each axis has a unique item limit. Excess items are grouped into (Others).
  • The default is 30 for each axis. You can change Max row items / Max column items in the chart's Basic settings, or in Explorer Pivot conditions.
  • (Max row items + 1) × (Max column items + 1) must be 1,000 or less. A combination that exceeds this limit is an error.
  • If you set only one of Max row items / Max column items, the unspecified side is treated as the maximum that fits when checking this product.
  • If the specified value is large, that product can still exceed the limit. For example, 700 on one axis with the other unset is an error.
  • When only rows or columns are specified, the specified axis displays up to 990 items.

See Limits for the chart rendering limit.