Skip to main content

Configuration

You can configure Pivot appearance and functionality via the corresponding API, namely, you can configure the Pivot table elements and the configuration panel. The available parameters will allow you to:

  • define the structure of the Pivot table and how data is aggregated via the config property
  • change the table configuration on the fly via the render-table event
  • configure the look of the Pivot table via the tableShape property
  • configure the look and behavior of the Pivot columns via the columnShape property
  • configure the look and behavior of headers in the Pivot table via the headerShape property
  • control the visibility of the configuration panel via the configPanel property
  • apply the desired locale via the setLocale() method (see the Localization section)
  • load data and fields via the corresponding data and fields properties
  • define how data should be modified before it's applied via the predicates property
  • define custom mathematical methods for data aggregation via the methods property
  • control the maximum limit for the number of rows and columns in the final dataset via the limits property

All instructions about working with data see here: Working with data

You can configure and/or customize the following elements of the Pivot table:

  • columns and rows
  • headers and footers
  • cells
  • the table sizes

Resizing the table​

You can change the size of the table rows and columns, header and footer using the tableShape property.

The next sizes are applied by default:

const sizes = {
    rowHeight: 34,
    headerHeight: 30,
    footerHeight: 30,
    columnWidth: 150
};

Example:

const table = new pivot.Pivot("#root", {
    fields,
    data,
    tableShape: {
        sizes: {
            rowHeight: 44,
            headerHeight: 60,
            footerHeight: 30,
            columnWidth: 170
        }
    },
    config: {
        rows: ["studio", "genre"],
        columns: [],
        values: [
            {
                field: "title",
                method: "count"
            },
            {
                field: "score",
                method: "max"
            }
        ]
    }
});
info

To set the width of specific column(s), apply the width parameter of the columnShape property.

Autosizing columns to content​

The widget allows setting the minimum width value for all columns and it also enables sizing for the table data only, the table header or combined auto sizing. To configure all these autosizing settings, you should apply the autoWidth parameter of the columnShape property.

All parameters of autoWidth are optional and for detailed description of each parameter refer to the columnShape property.

  • use the columns parameter to define if the width of columns should be calculated automatically and which columns will be affected
  • use the auto parameter to adjust the width to the header or cell content (or both)
  • use maxRows to specify how many data rows will be applied to detect the size of a column; by default 20 rows are used

If firstOnly is set to true (default), each field of the same data is analyzed only once to calculate the column width. In case of multiple columns based on the same data (e.g., the oil field with the count operation and the oil field with the sum operation), only data in the first one will be analyzed and the others will inherit this width.

Example:

const table = new pivot.Pivot("#root", {
    fields,
    data,
    config: {
        rows: ["studio", "genre"],
        columns: [],
        values: [
            {
                field: "title",
                method: "count"
            },
            {
                field: "score",
                method: "max"
            }
        ]
    },
    columnShape: {
        autoWidth: {
            // calculate column width for these fields
            columns: {
                studio: true,
                genre: true,
                title: true,
                score: true
            },
            // analyze all fields
            firstOnly: false
        }
    }
});

Applying templates to cells​

Adding templates via tableShape​

To set a template to cells, use the templates parameter of the tableShape property. It's an object where each key is a field id and the value is a function that returns a string. All columns based on the specified field will have the related template applied.

In the example below we apply the template to the state cells to show the combined name of a state (the full name and abbreviation).

const states = {
  "California": "CA",
  "Colorado": "CO",
  "Connecticut": "CT",
  "Florida": "FL",
// other values,
};

const table = new pivot.Pivot("#root", {
    tableShape: {
        templates: {
            // set a template to customize values of "state" cells
            state: v => v+ ` (${states[v]})`,
        }
    },
    fields,
    data,
    config: {
        rows: ["state", "product_type"],
        columns: [],
        values: [
            {
                field: "profit",
                method: "sum"
            },
            {
                field: "sales",
                method: "sum"
            },
            // other values
        ],
    },
    fields,
});

Adding a template via the template helper​

You can insert HTML content to table cells via the pivot.template helper by defining a template as a cell property of the column object. You need to apply the template right before the table renders, which is done by intercepting the render-table event using the api.intercept() method.

The example shows how you can add icons (star or flag icon) to body cells based on their field (id, user_score):

function cellTemplate(value, method, row, column) {
    const field = column.fields ? column.fields[row.$level] : column.field;

    if (field === "id") {
        return idTemplate(value);
    }

    if (field === "user_score") {
        return scoreTemplate(value);
    }

    return value;
}

function idTemplate(value) {
    const name = value?.toString().split("-")[0];
    return `<span className="cell-id flag-${name}"></span> ${value}`;
}

function scoreTemplate(value) {
    return `<i className="cell-score wxi-star"></i> ${value}`;
}

widget.api.intercept("render-table", ({ config: tableConfig }) => {
    tableConfig.columns = tableConfig.columns.map((c) => {
        if (c.area === "rows") {
            // Apply a template to column cells from the "rows" area
            c.cell = pivot.template(({ value, method, row, column }) => cellTemplate(value, method, row, column));
        }
        return c;
    });
});

Applying templates to headers​

Adding templates via headerShape​

To define the format of text in headers, apply the template parameter of the headerShape property. The parameter is the function that:

  • takes the field id, label and sublabel (the name of a method if any is applied)
  • returns the processed value

A default template is as follows: template: (label, id, subLabel) => label + (subLabel ? (${subLabel}) : ""). By default, for the fields applied as values the label and method are shown (e.g., Oil(count)). If no other template is applied to columns, the value of the label parameter is displayed. If any predicates template is applied, it will override the template of the headerShape property.

In the example below for the values fields the header will display the label, the method name (subLabel) and converts the result to lowercase (e.g., profit (sum)):

new pivot.Pivot("#pivot", {
    data,
    headerShape: {
        // a custom template for header text 
        template: (label, id, subLabel) => (label + (subLabel ? ` (${subLabel})` : "")).toLowerCase(),
        },
    config: {
        rows: ["state", "product_type"],
        columns: [],
        values: [
            {
                field: "profit",
                method: "sum"
            },
            {
                field: "sales",
                method: "sum"
            },
            // other values
        ],
    },
    fields,
});

Adding templates via the template helper​

You can insert HTML content to header cells via the pivot.template helper by defining a template as a cell property of the header cell object. You need to apply the template right before the table renders, which is done by intercepting the render-table event using the api.intercept() method.

The example below shows how to add icons to:

  • the header labels based on the field name (for example, if the field is "id", it adds the globe icon next to the header value)
  • the column headers based on the value (colored arrow indicators are added)
function rowsHeaderTemplate(value, field) {
    let icon = "";
    if (field === "id") icon = "<i className='icon wxi-earth'></i>";
    if (field === "user_score") icon = "<i className='icon wxi-star'></i>";
    return `${value} ${icon}`;
}

function statusTemplate(value) {
    let icon = "";
    if (value === "Up") icon = "<i style='color:green' className='icon wxi-arrow-up'></i>";
    if (value === "Down") icon = "<i style='color:red' className='icon wxi-arrow-down'></i>";
    return `${value} ${icon}`;
}

widget.api.intercept("render-table", ({ config: tableConfig }) => {
    tableConfig.columns = tableConfig.columns.map((c) => {
        if (c.area === "rows") {
            // Apply a template to the first header row of the columns from the "rows" area
            c.header[0].cell = pivot.template(({ value, field }) => rowsHeaderTemplate(value, field));
        } else {
            // For header cells that display values from the "status" field
            const headerCell = c.header.find((h) => h.field === "status");
            if (headerCell) {
                headerCell.cell = pivot.template(({ value }) => statusTemplate(value));
            }
        }
        return c;
    });
});

Making columns collapsible​

It's possible to collapse/expand columns that are under one header. To make columns collapsible, use the value of the collapsible parameter of the headerShape property by setting it to true.

const table = new pivot.Pivot("#root", {
    fields,
    data,
    headerShape: {
        collapsible: true,
    },
    config: {
        rows: ["studio", "genre"],
        columns: [],
        values: [
            {
                field: "title",
                method: "count"
            },
            {
                field: "score",
                method: "max"
            }
        ]
    }
});

Freezing columns​

The widget allows freezing columns on the left or right side, which makes the columns static and visible while scrolling. To freeze columns, apply the split parameter of the tableShape property by setting the value of the left or right parameter to true. More details with examples, see below.

Freezing columns on the left​

The number of columns that are split is equal to the number of the rows fields that are defined in the config property. In the tree mode only one column gets frozen regardless of the number of the rows fields that are defined. In the sample below, 1 column is fixed initially on the left, which is equal to the number of fields defined for the "rows" area.

const table = new pivot.Pivot("#root", {
    fields,
    data,
    config: {
        rows: ["studio"],
        columns: ["genre"],
        values: [
            {
                field: "title",
                method: "count"
            },
            {
                field: "score",
                method: "max"
            }
        ]
    },
    tableShape: { 
        split: {left: true } 
    }
});

You can also apply a custom split using the render-table event. It's not recommended to split columns with colspans.

In the sample below all columns from the "rows" area and first 4 columns from the "values" area are fixed initially. The number of columns that are split depends on the number of the rows and values fields that are defined via the config property.

const table = new pivot.Pivot("#root", {
    fields,
    data,
    config: {
        rows: ["continent", "name"],
        columns: ["year"],
        values: [
            {
                field: "oil",
                method: "sum"
            },
            {
                field: "oil",
                method: "count"
            }
        ]
    }
});
table.api.on("render-table", (tableConfig) => {
    const config = api.getState().config;

    tableConfig.split = {
        left: config.rows.length + config.values.length * 2
    };
});

Freezing columns on the right​

The right parameter of the tableShape property allows fixing total columns on the right.

const widget = new pivot.Pivot("#pivot", {
    fields,
    data: dataset,
    tableShape:{
        split: {right: true},
        totalColumn: true,
    },
    config:  {
        rows: ["hobbies"],
        columns: ["relationship_status"],
        values: [
            {
                field: "age",
                method: "min"
            },
            {
                field: "age",
                method: "max"
            }
        ]
    }
});

To fix custom columns on the right, you need to apply the table API via the render-table event. It's not recommended to split columns with colspans. In the sample below, 2 columns on the right are fixed initially.

const widget = new pivot.Pivot("#pivot", {
    fields,
    data: dataset,
    config:  {
        rows: ["hobbies"],
        columns: ["relationship_status"],
        values: [
            {
                field: "age",
                method: "min"
            },
            {
                field: "age",
                method: "max"
            }
        ]
    }
});

widget.api.on("render-table", ({ config: tableConfig }) => {
    const { config } = widget.api.getState();
    tableConfig.split = {
        right: config.values.length,
    }
})

Sorting in columns​

The sorting functionality is enabled by default. A user can click the column's header to sort data. To disable/enable sorting, apply the sort parameter of the columnShape property. In the example below we disable sorting.

const table = new pivot.Pivot("#root", {
    fields,
    data,
    config: {
        rows: ["studio", "genre"],
        columns: [],
        values: [
            {
                field: "title",
                method: "count"
            },
            {
                field: "score",
                method: "max"
            }
        ]
    },
    columnShape: {
        sort: false
    }
});

For more information about sorting data, refer to Sorting data.

Enabling the tree mode​

The widget allows presenting data in a hierarchical format with expandable rows. To switch to the tree mode, apply the tree parameter of the tableShape property by setting its value to true (default is false). To specify the parent row, put its name first in the rows array of the config property.

const table = new pivot.Pivot("#root", {
    tableShape: {
        tree: true
    },
    fields,
    data: dataset,
    config: {
        rows: ["studio", "genre"],
        values: [
            {
                field: "title",
                method: "count"
            },
            {
                field: "score",
                method: "max"
            },
            {
                field: "episodes",
                method: "count"
            },
            {
                field: "rank",
                method: "min"
            },
            {
                field: "members",
                method: "max"
            }
        ]
    }
});

Expanding/collapsing all rows​

To expand/collapse all rows, the tree mode should be enabled via the tableShape property and you should use the close-row and open-row events of the Table widget getting access to its API via the getTable method.

The example below shows how to expand/collapse all data rows with the button click in the table tree mode.

const table = new pivot.Pivot("#root", {
    tableShape: {
        tree: true
    },
    fields,
    data: dataset,
    config: {
        rows: ["type", "studio"],
        columns: [],
        values: [
            {
                field: "score",
                method: "max"
            },
            {
                field: "rank",
                method: "min"
            },
            {
                field: "members",
                method: "sum"
            },
            {
                field: "episodes",
                method: "count"
            }
        ]
    }
});

const api = table.api;
const table = api.getTable();
// setting all table branches closed on the table config update
api.intercept("render-table", (ev) => {
    ev.config.data.forEach((r) => (r.open = false));

    // returning "false" here will prevent the table from rendering
    // return false;
});

function openAll() {
    table.exec("open-row", { id: 0, nested: true });
}

function closeAll() {
    table.exec("close-row", { id: 0, nested: true });
}

const openAllButton = document.createElement("button");
openAllButton.addEventListener("click", openAll);
openAllButton.textContent = "Open all";

const closeAllButton = document.createElement("button");
closeAllButton.addEventListener("click", closeAll);
closeAllButton.textContent = "Close all";

document.body.appendChild(openAllButton);
document.body.appendChild(closeAllButton);

Changing text orientation in headers​

To change text orientation from default horizontal to vertical, use the headerShape property and set its vertical parameter to true.

const table = new pivot.Pivot("#root", {
    fields,
    data,
    headerShape: {
        vertical: true
    },
    config: {
        rows: ["studio"],
        columns: ["type"],
        values: [
            {
                field: "title",
                method: "count"
            },
            {
                field: "score",
                method: "max"
            }
        ]
    }
});

Controlling visibility of Configuration panel​

The Configuration panel is displayed by default. The widget provides the default functionality that allows controlling the visibility of the Configuration panel with the button click. It's made possible via the configPanel property or show-config-panel event.

Hiding Configuration panel​

To hide the panel, set the value of the configPanel property to false.

// the configuration panel is hidden on init
const table = new pivot.Pivot("#root", {
    fields,
    data: dataset,
    configPanel: false,
    config: {
        rows: ["hobbies"],
        columns: ["relationship_status"],
        values: [
            {
                field: "age",
                method: "min"
            },
            {
                field: "age",
                method: "max"
            }
        ]
    }
});

You can also trigger the show-config-panel event with the api.exec() method, and set the mode parameter to false.

const table = new pivot.Pivot("#root", {
    fields,
    data,
    config: {
        rows: ["studio", "genre"],
        columns: [],
        values: [
            {
                field: "title",
                method: "count"
            },
            {
                field: "score",
                method: "max"
            }
        ]
    }
});
//hide the configuration panel
table.api.exec("show-config-panel", {
    mode: false
});

Disabling the default toggling functionality​

You can block toggling the visibility of the Configuration panel on the button click via the api.intercept() method (by listening to the show-config-panel event and returning false).

Example:

const table = new pivot.Pivot("#root", {
    fields,
    data,
    config: {
        rows: ["studio", "genre"],
        columns: [],
        values: [
            {
                field: "title",
                method: "count"
            },
            {
                field: "score",
                method: "max"
            }
        ]
    }
});

table.api.intercept("show-config-panel", () => {
    return false;
});

You can also control the visibility of the Configuration panel using the showConfigPanel() method.

Actions with fields in the panel​

In the Configuration panel it's possible to perform the next operations with fields:

Related samples: