Pivot Table

The Pivot Table is a widget that displays aggregated information based on two data dimensions.

Widget Settings

In addition to the common widget settings, this widget has unique configuration options.

Data Settings

Dimensions and Measures

You can add two types of dimensions to a pivot table: Rows and Columns. The following settings are available for both:

• Title
• Formula
• Width (in pixels or percentages)
• View
• Display (display condition)

You can also enable array nesting for the dimension, hide empty values, and assign click actions.

View settings differ between dimensions and measures.

Dimension View

This parameter controls how dimension values are colored in the table. Available options:

• No coloring
• Text coloring
• Cell background coloring

For dimensions with Integer or Float data types, when coloring is enabled, you can choose among By values, By rule, or By range. To define colors, click the gear icon in the style settings field. In the panel that appears, click + Rule.

From the dropdown list, select the value for which coloring is being configured. Color modes available: Solid or By rule.

Measure

Measures support standard settings as well as the additional options described below.

Measure View

The View parameter controls how measure values are colored in the table. Available options:

• Disabled (default)
• Text coloring
• Cell coloring
• Table coloring
• Table coloring by defined range
• Indicator with arrow (growth/decline)

When Table coloring by defined range is selected, minimum and maximum values for the View setting are set automatically:

• In Duration format: units of measurement appear as a hint in the range settings field
• In Number format: as the specified value

For aggregated indicators, chart settings are available:

• Minimum value (by default, the table’s minimum value is treated as 0%. Values at or below this threshold are considered 0%).
• Maximum value (by default, the table’s maximum value is treated as 100%. Values at or above this threshold are considered 100%).
• Units of measurement (for Duration format): days/hours/minutes/seconds

Color settings cannot be configured for dimensions. For measures (except in Disabled mode), the following color modes are available:

• Auto
• Gradient
• By rule (rules with Solid or Custom modes are allowed)
• Custom (by formula)

Formulas for dimensions and measures can be changed via a dropdown list. Detailed information about the formula editor is available on the corresponding page.

Sorting

As with other widgets, you can apply sorting to the pivot table.

Display Settings

Text Size

Adjusts the text size within widget rows.

Alignment

Allows you to set vertical alignment of cell content to either the top or center. This setting affects all types of content in cells—text, checkboxes, colored bars, etc.

Table Header Settings

• Header background: Auto / Solid
• Font weight: Normal / Bold
• Text color: Auto / Solid

Row Limit

Limits the number of displayed rows for the Row dimension.

Column Limit

Limits the number of displayed columns for the Column dimension.

Row Nesting

When enabled, a plus icon appears to the left of the row name; clicking it expands the dimension’s hierarchy.

Filtering Settings

General filtering settings apply to this widget.

Filters configured within the widget cannot be removed in Browse mode.

Browse Mode

Pagination is handled via a navigation panel in the bottom-right corner of the widget. This panel appears automatically when the number of rows or columns exceeds the limits set in Row limit or Column limit.

In Browse mode, you can search for specific values within a dimension directly in the table. The search field is located in the column header. You can find and select multiple values; once selected, a filter for these values is applied to the dashboard—just as with regular click-based filtering on column values. Search also works for hierarchical and nested dimensions in both rows and columns.

Widget Configuration in YAML

You can configure the widget not only in the view but also in the YAML editor. The table below lists the keys and values available for configuration. For details on using the value key, see Dimension and Measure Values in YAML. Supported data formats are described on the corresponding page. General widget parameters are covered in Widget Configuration in YAML.

Data Settings in YAML

Rows and Columns in YAML (keys: row and column)

To add a row or column to the widget, use the row or column block respectively. The following keys are used within these blocks.

Measures in YAML (key: measures)

Hierarchical Dimension (key: hierarchyDimensions)

Range in YAML (key: range)

Display Settings in YAML

Filtering Settings in YAML

Example of a Configured Widget in the YAML Editor

widgets:
  - key: pivot-table
    apiVersion: "17"
    type: PIVOT_TABLE
    settings:
      title: ""
      titleSize: 14
      titleColor:
        mode: AUTO
      titleWeight: NORMAL
      showMarkdown: false
      markdownMeasures: []
      markdownText: ""
      textSize: 12
      rowsLimit: 7
      columnsLimit: 7
      row:
        - name: Store
          value:
            mode: FORMULA
            formula: '"store"."Store"'
          format:
            value: STRING
            mode: BASE
          formatting:
            value: AUTO
            mode: BASE
          displayCondition:
            mode: DISABLED
          onClick: []
          width:
            value: null
            mode: PERCENT
          dbDataType: String
          displayMode: NONE
      column:
        - name: Quarter and Year
          value:
            mode: TEMPLATE
            tableName: sale
            columnName: Дата
            templateName: yearAndQuarter
          format:
            value: QUARTER_YEAR
            mode: BASE
          formatting:
            value: AUTO
            mode: BASE
          displayCondition:
            mode: DISABLED
          onClick: []
          dbDataType: Int64
          displayMode: NONE
      measures:
        - name: Sales Amount
          value:
            mode: TEMPLATE
            tableName: sale
            columnName: Sales Amount
            templateName: sum
          format:
            value: NUMBER
            mode: BASE
          formatting:
            value: AUTO
            mode: BASE
          displayCondition:
            mode: DISABLED
          color:
            mode: FORMULA
            formula: if(sum("sale"."Sales Amount") < 50, '#E51320', '#078936')
          width:
            value: null
            mode: PERCENT
          dbDataType: Int64
          displayMode: TEXT
        - name: Total sales
          value:
            mode: TEMPLATE
            tableName: sale
            columnName: Total sales
            templateName: sum
          format:
            value: NUMBER
            mode: BASE
          formatting:
            value: AUTO
            mode: BASE
          displayCondition:
            mode: DISABLED
          color:
            mode: RULE
            formula: "#{Rule}"
          width:
            value: null
            mode: PERCENT
          dbDataType: Float64
          displayMode: FILL
      filterMode: DEFAULT
      ignoreFilters: false
      stateName: null
      filters: []
      sorting: []
      paddings: 8
      markdownTextSize: 14
      verticalAlign: TOP
      headColor:
        mode: AUTO
      headWeight: NORMAL
      headTextColor:
        mode: AUTO
      rowNesting: false
    localApiVersion: "2"