What is a pivot table?

A pivot table summarizes, groups, and sorts the data in a report. Rearranging the columns pivots the data, displaying results in different ways.

A pivot table has two column types:

  • Dimension columns. Dimension columns sort values. A column usually has many rows with identical dimension values, which form a hierarchy. Dimensions can also be called hierarchies.
  • Measure columns. Measure columns aggregate values. The measure can be a numeric or a string class. For numeric measures, the aggregation can be an average of the measure values, the sum of the measure values, the maximum or minimum of the measure values, or another mathematical function. For string measures, the aggregation is a count of the number of values.

A pivot table has one row type, a fact row. The cells of a row are either dimension values or measures, depending on the column type.

For example, a table has the dimension columns "Shipping Country" and "Product Category", and the measure column "Unit price". After sorting the data, the rows display as shown in Figure 1. "Shipping Country" and "Product Category" form a hierarchy or tree where a country branch has sub branches for its products. The innermost dimension ("Product Category") contains the values for the measure ("Unit price").

Figure: Pivot Table

This figure shows the output of a pivot table with Shipping Country and Product Category as dimensions and Unit price as a measure.

A pivot table is typically drawn as a table; however, other charting options (such as a bar or pie chart) are available.

In Genero Studio, a pivot table is constructed from four elements:
  • The PIVOTTABLE element represents the table itself and contains all other elements.
  • The columns are represented by HIERARCHY elements (for a dimension column) or MEASURE elements.
  • MEASURE elements are grouped by FACT elements, which represent the rows.

For example, Figure 2 shows a table with two dimensions (HIERARCHY elements) and one measure (MEASURE elements). This pivot table would display as in Figure 1.

Figure: Pivot table in the Structure View

This figure shows a Report Structure. The Page Root has one visible child: a Pivot Table. The Pivot Table node has three chidren: a Dimension for shipcountry, a Dimension for catid, and a Fact. The Fact node has one child: a Measure for unitprice.

Static versus dynamic pivot tables

A static pivot table uses predefined dimensions and measures when creating the report, whereas a dynamic pivot table determines the dimensions and measures at runtime. See Sample pivot table reports.

Pivot tables compared to business graphs

Table 1 compares pivot tables with business graphs.

Table 1. Comparing chart objects and pivot tables
Element Type Number of dimensions (hierarchies) Number of measures Number of aggregation groups Aggregation functions Sorting options
MAPCHART One (specified by the key attribute) One (specified by the value attribute) One (values with the same key value are summarized) One (sum) By key, value, and input order
CATEGORY CHART Two (specified by the key and categoryKey attributes) One (specified by the value attribute) One (values with the same key + categoryKey value combination are summarized) One (sum) By keys, value, and input order
XYCHART None Two (specified by the x and y attributes) None None None
PIVOTTABLE N (specified by HIERARCHY elements) N (specified by MEASURE elements) N (Aggregation can be performed on all dimensions) Many (sum, count, average, maximum, minimum, and more) Input order and any combination of measures