Using the Pivot Table Widget

The Pivot Table widget allows aggregating and configuring various combinations of data related to one entity.

In the widget:

  • Row group columns are used as "treeview",

  • Column groups are used as headers, and

  • Value columns are used as data in the cells.

Users can reorganize columns in the table by dragging them left or right by their header.

Pivot Table Widget

In addition, the widget sidebar displays the tabs:

  • Columns, which lists all the columns configured for the widget. They can be hidden by unticking the checkbox next to them. It differs from the option Hide this column which removes it altogether from the list. Users can look for columns using a search field above the list. They can also drag and reorganize them into the following sidebar lists:

    • Row Groups, which lists the columns used as row groups. In the widget configurator, said columns must have the option Enable Row group ticked. If they have Use as Default set to Row Group, it will be used as such if the saved widget state is not indicating otherwise. For more details, please refer to Section Understanding Widget State. The order in the list impacts how the widget rows are grouped.

    • Values, which requires the selected column in the widget configurator to have the options Enable Value ticked and Use as Default set to Value. The order in the list impacts how the columns are displayed.

    • Column Labels, which requires the selected column in the widget configurator to have the options Enable Column group ticked and Use as Default set to Column Group. The order in the list impacts how the column groups are displayed.

  • Filters, which provides a way to filter the values of each column.

The widget settings can be edited using a dedicated configurator.

Pivot Table Widget Configurator

The upper tabs allow configuring the:

  • Header, i.e. the widget top bar, by setting if it must be shown by ticking Show Header and if so, by inserting its Title and Icon.

  • Colors, by ticking Use Custom Colors, and setting the Header Color and Text Color.

  • Toolbar, beneath the widget header, by adding configurable action buttons. For more details, please refer to Section Using the Action API.

The main tab allows configuring the:

  • Properties of the widget, which are the:

    • Type, i.e. the entity to visualize.

    • Merge grouping columns by default option, which, if multiple columns are set as row groups, merges them into one called Group.

    • Hide aggregation header row if there's only one option:

      • If it is not set or if there is more than one active "value" column, an additional header row displays the column name.

      • If it is set and there is only one value column, the header is masked.

    • Sidebar parameters to Do not display, Display all, Display only filters, or Display only columns.

    • Close sidebar by default option.

  • Columns of the widget, which depend on the entity selected in Type. Users can reorganize them using the and buttons and use the menu that displays the options Add Column, Remove Column, Duplicate Column, and Reset Columns.

    The order impacts the display as columns listed higher are higher in the hierarchy of column and row groups. For each column, the following parameters can be set:

    • Column Header, i.e. the label for the column.

    • Field, i.e. the entity attribute for which to display values in the column.

    • Hide this Column, which can be turned on and off from the widget sidebar using the checkbox next to the column name.

    • Selector filter, which changes the column filter in the widget sidebar to a checkbox list.

    • Enable Row group, which allows using values of the attribute selected in Field as expandable row groups. Ticking this checkbox displays the parameter Used as Default, which can then be set to Row Group. This displays all the values from the columns listed below the selected one, on the left panel, as part of the row group in the widget. Said columns must have the options Enable Value ticked and Used as Default set to Row Group. The higher a column is listed in the left panel, the higher it is in the grouping hierarchy.

    • Enable Column group, which allows using values of the attribute selected in Field as column groups. Ticking this checkbox displays the parameter Used as Default, which can then be set to Column Group. This displays all the columns listed below the selected one, on the left panel, as part of the column group in the widget. Said columns must have the options Enable Value ticked and Used as Default set to Value. The higher a column is listed in the left panel, the higher it is in the grouping hierarchy.

    • Enable Value, which displays the column and its values in the widget. The Column Header can be hidden when it is the only column of a column group, using the option Hide aggregation header row if there's only one. Ticking the Enable Value checkbox displays the parameters:

      • Used as Default, which can then be set to Value to display the values of the Field attribute.

      • Aggregation option that can be set to COUNT, DISTINCT, MIN, MAX, AVG, and/or SUM depending on the value type. For more details, please refer to Section Understanding GraphQL Default Queries.

    • Used as Default, which is displayed if the options Enable Row group, Enable Column group, or Enable Value are ticked. It can be set to:

      • Not used, i.e. the Field attribute is not used to group values.

      • Row Group, i.e. the Field attribute is used to group rows of values from another lower column. If the column is the lowest, there is no grouping. The checkbox Enable Row group must be ticked.

      • Column Group, i.e. the Field attribute is used to group lower columns. If the column is the lowest, there is no grouping. The checkbox Enable Column group must be ticked.

      • Value, i.e. values of the Field attribute are displayed individually.

    • Value Format, which can be set to:

      • Date, using the format 10/3/2023, Oct. 3, 2023, October 3, 2023, or Thursday, October, 2023.

      • Time, using the format 3:50 PM, 3:50:55 PM, or 3:50:55 PM GMT+2.

      • Duration, using the format 8760:00:00 (hh:mm:ss), 525600 (Minutes), 8760 (Hours), 365 (Days), 52 (Weeks), 12 (Months), or 1 (Years).

      • Number, using the format 123,412%, 1,231 (Rounded value), 1,234.1, 1,234.12, 1,234.123, 1.234123e+3 (Scientific), 1.234e+3 (Scientific), 1.234K (thousands), 1K (thousands, rounded), 0.001M (millions), or 0M (millions, rounded).

      • Currency, using the format $ 1,234 (Rounded value), € 1,234 (Rounded value), 1,234 (Rounded value), $ 1,234.12 (Decimals), € 1,234.12 (Decimals) or 1,234.12 (Decimals).

      • Custom, which prompts the user to choose a formatter and a format, either ICU (Default), using the ICU message format, or Luxon (Date & Time), using the Luxon message format.