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 hide empty values and assign click actions to dimensions.

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.

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 is applied to the entire dashboard—just as if you had clicked those values directly in the column.

Widget Configuration in YAML

You can configure the widget not only visually 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

- key: PIVOT_TABLE
  apiVersion: "16"
  type: PIVOT_TABLE
  settings:
    displayCondition:
      mode: DISABLED
    hideZeroValues: false
    textSize: 12
    ignoreFilters: false
    column:
      - displayCondition:
          mode: DISABLED
        name: Hierarchy
        hierarchyDimensions:
          - dbDataType: DATE
            name: Quarter and Year
            format: DATE
            value:
              mode: FORMULA
              formula: if(defaultValueOfArgumentType("sale"."Date") = "sale"."Date", 0,
                toYear("sale"."Date") * 10 + toQuarter("sale"."Date"))
            formatting: AUTO
          - hideEmptyValues: true
            dbDataType: DATE
            name: Month and Year
            format: MONTH_YEAR
            value:
              mode: TEMPLATE
              templateName: yearAndMonth
              dbType: CH
              tableName: sale
              columnName: Date
            formatting: AUTO
        onClick: []
    filters: []
    filterMode: DEFAULT
    measures:
      - displayCondition:
          mode: DISABLED
        color:
          mode: FORMULA
          formula: If(sum("sale"."Sales Quantity") < 50, '#E51320', '')
        formattingTemplate: ""
        dbDataType: INTEGER
        name: Sales Quantity
        format: NUMBER
        value:
          mode: FORMULA
          formula: sum("sale"."Sales Quantity")
        formatting: "#,##x"
        displaySign: TEXT
      - displayCondition:
          mode: DISABLED
        color:
          mode: GRADIENT
          endValue: "#e51320"
          startValue: "#ffffff"
        formattingTemplate: ""
        dbDataType: FLOAT
        name: Sales Amount
        format: NUMBER
        value:
          mode: FORMULA
          formula: sum("sale"."Sales Amount")
        formatting: "#,##x"
        displaySign: FILL
    sorting:
      - name: Hierarchy
        value:
          mode: IN_WIDGET
          index: 0
          group: column
        direction: ASC
      - name: Sales Amount
        value:
          mode: IN_WIDGET
          index: 1
          group: measures
        direction: DESC
    row:
      - displayCondition:
          mode: DISABLED
        hideEmptyValues: true
        dbDataType: STRING
        name: Store
        format: STRING
        value:
          mode: FORMULA
          formula: '"store"."Store"'
        formatting: AUTO
        onClick: []
    title: ""
    titleSize: 14
    stateName: null
    rowsLimit: 15
    columnsLimit: 50
    titleColor:
      mode: AUTO
    titleWeight: NORMAL
    showMarkdown: false
    markdownMeasures: []
    markdownText: ""