Pivot Table

avonni-dd-pivot-table

The Avonni Data Driven Pivot Table displays aggregated records in a pivot table format, with groupable rows and columns, optional subtotals and grand totals, and a configurable header and filter section. Unlike the other data driven components, the pivot table cannot rely on the base component to run the query: the records must be aggregated server-side with a GROUP BY CUBE query before they can be displayed. This component therefore builds the aggregate query from the mapping, executes it, and feeds the pre-aggregated rows to the base component as static items.

Overview

Pivot Table is a data-driven Lightning Web Component that displays records aggregated into a cross-tab grid, with groupable rows and columns, configurable measures, and optional subtotals and grand totals.

Unlike the other Data Driven Components, the pivot table cannot let the base component run the query directly: the records must be aggregated server-side with a GROUP BY CUBE query before they can be displayed. The component builds that aggregate query from your query and mapping, executes it, and renders the pre-aggregated rows. It therefore runs in query mode only—there is no static items input.

Use Cases

• Sales pipeline analysis: Sum opportunity amounts by stage (rows) and type (columns) to see where revenue concentrates.
• Case volume reporting: Count cases by priority and origin to spot support hotspots.
• Revenue by region: Break down totals by territory and product family with subtotals per region.
• Forecast roll-ups: Show grand totals across all groups for a single headline number.
• Quota attainment: Average or sum a metric across two dimensions for managers reviewing team performance.

---

Use Case Examples

Example 1: Query mode

Scenario: Show opportunity revenue summed by stage (rows) and type (columns), with subtotals and a grand total, and let users edit the underlying detail rows.

<!-- opportunityPivot.html -->
<template>
    <avonni-dd-pivot-table
        query={opportunityQuery}
        mapping={opportunityMapping}
        filters={opportunityFilters}
        header-caption="Query mode demo"
        header-title="Opportunities by stage and type"
        detail-rows
        enable-inline-edit
        show-grand-total
        show-subtotals
        ongroupchange={handleGroupChange}
        onheaderactionclick={handleHeaderActionClick}
        onsave={handleSave}
    ></avonni-dd-pivot-table>
</template>

// opportunityPivot.js
import { LightningElement } from 'lwc';

export default class OpportunityPivot extends LightningElement {
    opportunityQuery = {
        objectApiName: 'Opportunity',
        orderBy: 'StageName ASC',
        limit: 200
    };

    // Rows by stage, columns by type, cells = SUM(Amount).
    opportunityMapping = {
        groupRows: [{ field: 'StageName' }],
        groupColumns: [{ field: 'Type' }],
        aggregations: [{ field: 'Amount', measure: 'SUM' }]
    };

    opportunityFilters = ['StageName', 'Type', 'Amount', 'CloseDate'];

    handleGroupChange(event) {
        const { groupRows, groupColumns } = event.detail.mapping || {};
    }

    handleHeaderActionClick(event) {
        const actionName = event.detail.name;
    }

    handleSave() {
        // Detail-row inline edits were saved.
    }
}

Result: A pivot grid with stages down the side, opportunity types across the top, summed amounts in each cell, subtotals where multiple groups apply, and a grand total row and column. Clicking a cell reveals editable detail rows.

Example 2: Query mode — case volume with a filter

Scenario: Count open cases by priority (rows) and origin (columns) for a support dashboard, restricting the query to non-closed cases.

<!-- caseVolumePivot.html -->
<template>
    <avonni-dd-pivot-table
        query={caseQuery}
        mapping={caseMapping}
        filters={caseFilters}
        header-title="Open cases by priority and origin"
        show-grand-total
        onerror={handleError}
    ></avonni-dd-pivot-table>
</template>

// caseVolumePivot.js
import { LightningElement } from 'lwc';

export default class CaseVolumePivot extends LightningElement {
    caseQuery = {
        objectApiName: 'Case',
        filter: "Status != 'Closed'",
        limit: 500
    };

    // Rows by priority, columns by origin, cells = COUNT(Id).
    caseMapping = {
        groupRows: [{ field: 'Priority' }],
        groupColumns: [{ field: 'Origin' }],
        aggregations: [{ field: 'Id', measure: 'COUNT' }]
    };

    caseFilters = ['Priority', 'Origin'];

    handleError(event) {
        const message = event.detail.message;
    }
}

Result: A grid counting open cases by priority and origin, with a grand total row and column summarizing the overall volume. The query filter keeps closed cases out of the aggregation.

---

Specifications

Attributes

Name	Description	Type	Default	Required
detail-rows	If true, detail rows are displayed when an aggregation cell is clicked.	Boolean	false
disable-header-actions	If true, the header actions are disabled.	Boolean	false
enable-inline-edit	If true, detail rows support inline editing.	Boolean	false
filters	Array of field API names that belong to the queried object. These fields will be displayed as user filters.	string[]	—
filters-attributes	Object defining the filters-specific attributes.	DdElementFiltersAttributes	—
header-actions	Array of actions to display at the top right of the header. On click on a header action, the headeractionclick event is fired.	DdElementAction[]	—
header-avatar	Avatar displayed at the top left of the header.	DdElementAvatar	—
header-caption	Header caption, displayed above the title.	String	—
header-help-text	If present, a help text icon is displayed next to the header title. On focus or hover on the icon, the header help text is displayed in a tooltip.	String	—
header-help-text-attributes	Object defining the help text-specific attributes.	DdElementHelpTextAttributes	—
header-is-joined	If true, the header's bottom border and shadow are removed so it sits flush with an adjacent component.	Boolean	false
header-title	Main title displayed in the header.	String	—
header-visible-actions-count	Number of header actions that appear as regular buttons. Remaining actions appear in a dropdown menu.	integer	—
hide-header-actions	If true, the header actions are hidden.	Boolean	false
mapping	Object defining the way the records returned by the query should be mapped to the pivot table groups (rows and columns) and aggregations (measures).	DdPivotTableMapping	—
query	Definition of the query used to retrieve the records to aggregate. The objectApiName and filter are used to build the GROUP BY CUBE query; the grouping and aggregation expressions come from the mapping.	DdElementQuery	—
refresh-emp	Object describing a platform event that should be subscribed to in order to refresh the component when an event is published.	DdElementRefreshEmp	—
search-attributes	Object defining the search-specific attributes.	DdElementSearchAttributes	—
search-fields	Array of field API names that can be used by the search box to filter the records. The fields must belong to the queried object, and they must be filterable.	string[]	—
show-grand-total	If true, a grand total row and column are displayed.	Boolean	false
show-subtotals	If true, subtotals are displayed. Only available when more than one groupable row or column is defined.	Boolean	false
side-panel-attributes	Object defining the side panel-specific attributes.	DdElementSidePanelAttributes	—
stacked-summaries	If true, cell values are stacked in the same row or column instead of being separated.	Boolean	false

Mapping

The mapping object describes how queried records are grouped and aggregated. It has three arrays:

Mapping Key	Type	Description
groupRows	Object[]	Fields that group the records into pivot rows. Each entry is { field, customLabel?, label? }. At least one is required.
groupColumns	Object[]	Fields that group the records into pivot columns. Each entry is { field, customLabel?, label? }.
aggregations	Object[]	Fields that are aggregated into the cell values. Each entry is { field, measure, customLabel?, label? }.

Each group entry properties:

Property	Type	Description
field	String	Required. API name of the field used as the grouping key.
customLabel	Boolean	If true, use label as the header instead of the field's default label.
label	String	Custom header shown when customLabel is true.

Each aggregation entry properties:

Property	Type	Description
field	String	Required. API name of the field whose values are aggregated.
measure	String	Aggregation function. Valid values: AVG, COUNT, COUNT_DISTINCT, MAX, MIN, SUM.
customLabel	Boolean	If true, use label as the measure header instead of the field's default label.
label	String	Custom header shown when customLabel is true.

Unlike text and timeline mappings, the pivot table mapping references fields by their API name (e.g. 'Amount'), not with the {{Record.FieldApiName}} merge syntax—because each value is grouped or aggregated, not rendered inline.

Methods

Name	Description	Argument Name	Argument Type	Argument Description
refresh	Refresh the query and the records displayed in the pivot table.

Custom Events

error

The event fired when an error occurs in the component.

The error event returns the following parameters.

Parameter	Type	Description
message	string	Message of the error.

The event properties are as follows.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

filter

The event fired when the user filters the records.

The filter event returns the following parameters.

Parameter	Type	Description
value	object	Object containing the filters applied by the user. Its keys correspond to the field API names of the selected filters. The values are arrays of strings, corresponding to the values selected for the filter.

The event properties are as follows.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

groupchange

The event fired when the user changes the pivot table grouping structure, by moving or removing a row or column group.

The groupchange event returns the following parameters.

Parameter	Type	Description
mapping	object	Updated mapping object, containing the new groupRows, groupColumns and aggregations.

The event properties are as follows.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

headeractionclick

The event fired when a header action is clicked.

The headeractionclick event returns the following parameters.

Parameter	Type	Description
name	string	Name of the action clicked.

The event properties are as follows.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

nbitemschange

The event fired when the number of items displayed in the component changes.

The nbitemschange event returns the following parameters.

Parameter	Type	Description
value	integer	Number of items displayed in the component.

The event properties are as follows.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

save

The event fired when the Save button is clicked during inline editing of the detail rows.

The save event doesn't return any parameters.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

error

The event fired when an error occurs in the component.

The error event returns the following parameters.

Parameter	Type	Description
message	string	Message of the error.

The event properties are as follows.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

filter

The event fired when the user filters the records.

The filter event returns the following parameters.

Parameter	Type	Description
value	object	Object containing the filters applied by the user. Its keys correspond to the field API names of the selected filters. The values are arrays of strings, corresponding to the values selected for the filter.

The event properties are as follows.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

groupchange

The event fired when the user changes the pivot table grouping structure, by moving or removing a row or column group.

The groupchange event returns the following parameters.

Parameter	Type	Description
mapping	object	Updated mapping object, containing the new groupRows, groupColumns and aggregations.

The event properties are as follows.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

headeractionclick

The event fired when a header action is clicked.

The headeractionclick event returns the following parameters.

Parameter	Type	Description
name	string	Name of the action clicked.

The event properties are as follows.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

nbitemschange

The event fired when the number of items displayed in the component changes.

The nbitemschange event returns the following parameters.

Parameter	Type	Description
value	integer	Number of items displayed in the component.

The event properties are as follows.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

save

The event fired when the Save button is clicked during inline editing of the detail rows.

The save event doesn't return any parameters.

Property	Value	Description
bubbles	false	This event does not bubble.
cancelable	false	This event has no default behavior that can be canceled. You can't call preventDefault() on this event.
composed	false	This event does not propagate outside of the component in which it was dispatched.

Styling Hooks

CSS Variable	Type	Default
--avonni-dd-pivot-table-header-actions-color-background	color	—
--avonni-dd-pivot-table-header-actions-color-background-active	color	—
--avonni-dd-pivot-table-header-actions-color-background-hover	color	—
--avonni-dd-pivot-table-header-actions-color-border	color	—
--avonni-dd-pivot-table-header-actions-color-border-active	color	—
--avonni-dd-pivot-table-header-actions-color-border-hover	color	—
--avonni-dd-pivot-table-header-actions-text-color	color	—
--avonni-dd-pivot-table-header-actions-text-color-active	color	—
--avonni-dd-pivot-table-header-actions-text-color-hover	color	—
--avonni-dd-pivot-table-header-caption-font-family	string	—
--avonni-dd-pivot-table-header-caption-font-size	dimension	—
--avonni-dd-pivot-table-header-caption-font-style	string	normal
--avonni-dd-pivot-table-header-caption-font-weight	number	400
--avonni-dd-pivot-table-header-caption-letter-spacing	string	—
--avonni-dd-pivot-table-header-caption-line-height	string	—
--avonni-dd-pivot-table-header-caption-text-color	color	#000000
--avonni-dd-pivot-table-header-color-background	color	—
--avonni-dd-pivot-table-header-color-border	color	—
--avonni-dd-pivot-table-header-color-border-bottom	color	#c9c9c9
--avonni-dd-pivot-table-header-icon-color-background	color	—
--avonni-dd-pivot-table-header-icon-color-foreground	color	—
--avonni-dd-pivot-table-header-icon-color-foreground-default	color	—
--avonni-dd-pivot-table-header-icon-radius-border	string	—
--avonni-dd-pivot-table-header-margin-block-end	dimension	—
--avonni-dd-pivot-table-header-radius-border	string	—
--avonni-dd-pivot-table-header-sizing-border	string	—
--avonni-dd-pivot-table-header-sizing-border-bottom	dimension	1px
--avonni-dd-pivot-table-header-spacing-block-end	dimension	0.75rem
--avonni-dd-pivot-table-header-spacing-block-start	dimension	0.75rem
--avonni-dd-pivot-table-header-spacing-inline-end	dimension	1rem
--avonni-dd-pivot-table-header-spacing-inline-start	dimension	1rem
--avonni-dd-pivot-table-header-styling-border	string	—
--avonni-dd-pivot-table-header-styling-border-bottom	string	solid
--avonni-dd-pivot-table-header-title-font-family	string	—
--avonni-dd-pivot-table-header-title-font-size	dimension	1rem
--avonni-dd-pivot-table-header-title-font-style	string	normal
--avonni-dd-pivot-table-header-title-font-weight	number	400
--avonni-dd-pivot-table-header-title-letter-spacing	string	—
--avonni-dd-pivot-table-header-title-line-height	number	1.25
--avonni-dd-pivot-table-header-title-text-color	color	#080707

Key Considerations

• Query-only: The pivot table has no static items input. It always builds and runs a GROUP BY CUBE aggregate query from query + mapping.
• Aggregation requirements: A SOQL aggregate query can only sort by grouped or aggregated fields—an orderBy referencing an ungrouped field is ignored. At least one groupRows entry with a valid field is required, or the component renders empty.
• Field API names, not merge fields: The mapping references fields directly by API name ('Amount'), unlike the {{Record.Field}} syntax used by other Data Driven Components.
• Subtotals need multiple groups: show-subtotals only has an effect when more than one group row or column is defined.
• Server-side load: Aggregation runs in Apex. Keep the query filter and limit tight so the aggregate query stays performant on large objects.
• Best Practice: Always define at least one groupRows entry and one aggregations measure—without them the component has nothing to aggregate and renders empty. Keep the query limit reasonable; the records are aggregated server-side, so a tight filter and limit keep the query fast.

---

Troubleshooting Common Issues

• Table is empty: Confirm query.objectApiName is set and mapping.groupRows contains at least one entry with a valid field, plus at least one aggregations measure.
• Subtotals not showing: show-subtotals requires more than one group row or column; with a single group dimension only grand totals apply.
• Query error event fired: A measure on an unsupported field type, an invalid orderBy, or a malformed filter can make the aggregate query invalid—listen for error and check event.detail.message.
• If issues persist: Contact our support team at [email protected] for assistance.