Pivot Table

The Avonni Pivot Table component transforms how you analyze Salesforce data within your Dynamic Components. Go beyond static lists by interactively summarizing and exploring large datasets directly on your Lightning Pages. Uncover key business insights visually by dynamically grouping rows and columns and applying calculations.

Overview

Key Capabilities

Summarize & Aggregate: Condense data with calculations (SUM, AVG, COUNT, MIN, MAX).
Dynamic Grouping: Group data interactively by dragging fields to row and column areas.
Interactive Exploration: Allow users to easily pivot, reconfigure the view, sort, and potentially filter the summarized data.
Uncover Insights: Reveal trends, patterns, and comparisons hidden within raw Salesforce data.

See it in a real project

Revenue cube without a report: Closed won revenue by region and quarter, totals on both axes.

Quick Start Tutorial

Analyze Opportunities by Stage and Quarter

Build a working pivot table that shows how much revenue sits in each opportunity stage, broken down by fiscal quarter. Since this uses Salesforce's standard Opportunity object, you can follow along in any org that has opportunity data.

Prerequisites: You'll need Opportunities with the Amount and StageName fields filled in. Your org also needs FiscalYear and FiscalQuarter fields enabled (check Setup → Company Information → Fiscal Year settings)

Create a New Component

Start by creating a new Dynamic Component that will house your pivot table. This establishes the container where you'll build your analysis.

Open the Avonni Dynamic Components app from the App Launcher
Click New Component
Name your component (e.g., "Opportunity Analysis")
Leave Target Page Object blank (not needed for this example)

Add the Pivot Table Element

Now add the Pivot Table component itself. This is the element that will display your interactive data analysis.

From the Components list on the left, find Pivot Table
Drag the Pivot Table component onto your canvas
The Pivot Table appears on your canvas, ready for configuration in the Properties Panel

Configure the Data Source

Connect the pivot table to your Salesforce data. This query determines which Opportunity records will be analyzed and summarized

Click Create Query from the Data Source section.
For Object, select Opportunity from the list
Under Filters, add: Stage not equal "$Global Constant.Null" AND Amount > 0 (optional but recommended for cleaner data)
Click Save

Define Groups Row

Set up how data will be organized vertically. Each unique stage name will become its own row in the table.

Find the Data Mappings section in the Properties Panel
Click Add Group Row
For Field, select StageName

Define Groups Column

Set up how data will be organized horizontally. Each fiscal year (or quarter) will become its own column.

Find the Data Mappings section in the Properties Panel
Click Add Column Group
For Field, select Fiscal (or FiscalYear if that's the exact field name in your org)
Optionally add a second column group for FiscalQuarter to nest quarters under years

Configure Aggregations

Define what calculations appear where rows and columns intersect. This determines what numbers users actually see in each cell.

Find the Aggregations section
Click Add Aggregation
For Field, select Amount
For Measure, select SUM
Optionally add a second aggregation with Measure set to COUNT to see both total amounts and opportunity counts.

Set Display Options

Control whether the table shows subtotals and grand totals, making it easier to see overall trends alongside detailed breakdowns

Find the Content section
Enable Show Grand Total to see overall totals at the bottom and right edge
Enable Show Subtotals if you added multiple row or column groups (otherwise, leave disabled)
For Stacked Summaries, leave disabled to display each calculation in separate rows/columns

Configure the Header (Optional)

Add context and functionality to your pivot table with a descriptive title and action buttons users can click.

Find the Header section in the Properties Panel
For Title, enter "Opportunity Pipeline Analysis"
For Caption, enter "Group by stage and fiscal period"
Under Actions, click Add Actions
Set Label to "Export"
Choose an icon (e.g., utility:download)
Click Done

Add Export Interaction (Optional)

Make the Export button functional so users can download the pivot table data.

Click the Interactions tab in the Properties Panel
Select Header Action Click as the trigger
Click Add Header Action Click → select Export To as Type
Set the options based on your needs

Save and Activate

Deploy your pivot table to a Lightning Page so users can start exploring the data

Click Save to save your component
Go to Setup → Lightning App Builder
Edit an existing Home Page or App Page (or create a new one)
Drag your AX - Dynamic Components onto the page canvas
Check Display as Card
Click Save and Activate the page

What You Should See

A header titled "Opportunity Pipeline Analysis" with the caption "Group by stage and fiscal period" and an Export button. Below that, a table with opportunity stages listed vertically (Prospecting, Qualification, Proposal, Closed Won, etc.) and fiscal periods across the top. Cells show the sum of opportunity amounts at each stage-period intersection. Grand totals appear at the bottom and right edge. Users can click headers to sort or reconfigure groupings.

Configuration

To configure the Pivot Table, select it on the canvas. The Edit Pivot Table panel opens on the right with three tabs: Properties, Interactions, and Style. The sections below mirror the Properties tab.

Data Source

The Pivot Table requires a Query data source. Connect it to Salesforce records containing the data you want to analyze.

Click Create Query to define which object to query, apply filters to limit the dataset (recommended for performance), and set sorting.

Example: Query Opportunity records filtered by CloseDate = THIS_YEAR and Amount > 0, ordered by CloseDate ascending.

Data Mapping

Once a Query data source is configured, use the Data Mappings section to define how your query fields drive the pivot table structure. There are three mapping areas:

Row Groups

Each row group field creates a set of row headers. Each unique value in the selected field becomes its own row. You can add a custom display label for any group by enabling Custom Label.

When the selected Field is a date or date/time field, two extra settings appear:

Group — buckets the dates instead of using each distinct value: Year, Quarter, Month, Week, Day, Year-Quarter, Year-Month, or Year-Week.
Display Full Month Label — spells the month out ("January") instead of abbreviating it. Requires Group = Month or Year-Month.

Limits: Maximum of 3 grouping fields total across Row Groups and Column Groups combined (e.g., 2 row groups + 1 column group, or 1 row group + 2 column groups).

Example: First row group = Account.Industry, second row group = Account.Name (shows industries with specific accounts nested beneath).

Note: Maximum 3 Grouping Fields

The Pivot Table component allows a maximum of three fields combined for Row Groups and Column Groups. (For instance, you can use 2 fields for rows and 1 for columns, but not 2 for rows and 2 for columns).

Column Groups

Each column group field creates a set of column headers. Each unique value creates its own column. Custom labels are also supported here, as are Group and Display Full Month Label on a date or date/time field.

Example: Column group = FiscalYear (shows a separate column for each year: 2023, 2024, 2025).

Aggregations

Define the calculated values displayed in table cells where rows and columns intersect. For each aggregation, select a Field and a Measure. Available measures: AVG, COUNT, COUNT_DISTINCT, MAX, MIN, SUM. You can add multiple aggregations to display several metrics side-by-side. Custom labels are supported.

Example: Aggregation 1 = Field: Amount, Measure: SUM; Aggregation 2 = Field: Id, Measure: COUNT.

Filter Fields

In the Data Mappings section, select which fields are available to end-users as filters. Only the fields you select here will appear in the filter menu.

Content

Control how totals, drill-down, and multiple calculations are presented.

Show Grand Total — When enabled, adds a final row and column showing overall totals for all data. Enable for a quick dataset overview; disable to focus on group comparisons.

Show Subtotals — When enabled, adds intermediate totals for each subgroup when using nested row or column groups. Only meaningful when more than one row group or more than one column group is defined.

Stacked Summaries — Controls how multiple aggregations are displayed. When enabled, results for all aggregations appear together within the same cells for direct comparison. When disabled, each aggregation gets its own separate rows/columns.

Detail Rows — When enabled, clicking any data cell opens a panel listing the underlying records that make up that cell's aggregated value. Useful when users need to inspect or audit the source records behind a summary number.

Detail Rows Actions — When Detail Rows is enabled, you can add row-level action buttons that appear in the detail panel. Each action requires a Label and a Name (used to identify it in an interaction), and optionally an Icon Name, Disabled, and Hidden flag. Requires: Detail Rows enabled.

Enable Inline Edit — When enabled, users can edit field values directly inside the Detail Rows panel. Edited values are saved back to the underlying Salesforce records. Requires: Detail Rows enabled.

How Detail Rows Work

The user clicks any data cell (excluding subtotal and total cells).
A panel slides in below the table, filtered to match the row group and column group of the selected cell. For example, clicking the cell at Proposal × Q3 shows only Opportunities where StageName = 'Proposal' AND the close date falls in Q3.
Each Aggregation field configured on the table appears as a column in the detail panel. The first column always shows the record name (or the default name field for the queried object).
The user closes the panel to return to the summary view, or clicks another cell to refilter the panel.

How Inline Edit Works

When Enable Inline Edit is on, every Aggregation column in the detail panel is editable.

Double-click a cell to open the inline editor for that field.
Edit one or many cells — pending changes are tracked in a draft state.
Click Save at the bottom of the panel to commit changes to Salesforce.
While saving, the table shows a loading state. Once the save resolves, the pivot table re-runs its query so the aggregated values reflect the new data.

Enable Inline Edit is dependent on Detail Rows

Turning Detail Rows off automatically disables inline editing in the Properties Panel.

Inline edits write directly to the underlying Salesforce records. Standard field-level security, validation rules, and record sharing still apply — users who lack edit access on a field will see the cell as read-only or get an error toast on Save.

Setup Steps

Enable Detail Rows

Select the Pivot Table on the canvas.
In the Content section of the Properties Panel, toggle Detail Rows on.

Enable Inline Edit (optional)

In the same Content section, toggle Enable Inline Edit on.
Note: This option only appears once Detail Rows is enabled.

Choose Aggregation Fields That Make Sense to Edit

Inline editing applies to the fields you set up under Aggregations.
Pick fields that users are expected to update from this view (e.g., Amount, CloseDate, StageName). Avoid using read-only fields like Id or formula fields — they will appear as read-only cells.

Header

Add context and actions to your pivot table with a title, caption, avatar, and action buttons.

Setting

Description

Title

Main heading displayed above the pivot table (e.g., "Sales Performance Dashboard").

Caption

Descriptive subtitle providing additional context (e.g., "Year-to-date analysis by region and product"). Displayed above the title.

Avatar

Optional image, fallback icon, or initials displayed in the header. Set an Image URL, a Fallback Icon Name (e.g., standard:report), or Initials (e.g., "SA"). Size and variant (Circle/Square) are available under advanced options.

Help Text

Informational text displayed as a hoverable icon near the header to help users understand the data.

Is Joined

When enabled, removes the bottom border and shadow so the header sits flush with an adjacent component.

Actions

Add buttons to the header for common tasks like exporting or refreshing. Each action requires a Label and a Name, and optionally an Icon Name, Title (tooltip), Disabled, and Hidden flag.

Visible Actions Buttons

Number of action buttons to display before the rest collapse into a dropdown overflow menu.

Hide Actions

When enabled, temporarily hides all header action buttons from view.

Disable Actions

When enabled, action buttons appear but cannot be clicked.

Example: Title: "Quarterly Pipeline Analysis", Caption: "Filtered by open opportunities", Actions: Export button with download icon.

Filter

The Filter section controls how the filter menu is presented to end-users. Filter fields are enabled in the Data Mappings section.

Setting

Description

Display As Popover

When enabled, the filter controls are displayed inside a popover instead of inline.

Filter Operators

Each filter in the menu carries a mode selector — tooltipped Filter mode — that sets how its value is matched. There is nothing to configure in the builder: the operators on offer follow the field's type, and users pick one as they filter.

Text, picklist, multi-select picklist and lookup filters — Equals (default), Not Equals, Empty, Not Empty.
Number, currency, percent, date, date/time and time filters — Between (default), Not Between, Equals, Not Equals, Less Than, Less or Equal, Greater Than, Greater or Equal, Empty, Not Empty.
Checkbox filters — no Filter mode selector; they always match on equality.

Selecting Empty or Not Empty hides the value input, since neither operator takes a value.

No Results Message

The No Results Message section controls what the Pivot Table shows when its data source returns no records.

Setting

Description

No Results Message

The message to display when no records are returned.

Hide No Results Image

If enabled, the no results illustration is hidden.

Hide No Results Message

If enabled, the no results message is hidden.

Set Component Visibility

All components support conditional visibility — see Component Visibility.

Interactions

Interactions define what happens when users interact with the Pivot Table. Configure them from the Interactions tab of the Edit Pivot Table panel.

Header Action Click

Fires when a user clicks a button in the component header. Use the targetName output to identify which header action was clicked and branch your flow logic accordingly.

Detail Rows Action Click

Fires when a user clicks a row-level action button inside the Detail Rows panel. Use the targetName output to identify which action was clicked. Requires Detail Rows to be enabled in the Content section.

Output Variables

The Pivot Table exposes these output variables you can reference elsewhere on the page after the user interacts with it.

Header Actions

Fires when a user clicks a button in the component header.

Output variable

Type

What it returns

Clicked Header Action - Name

Text (String)

The name of the header action button the user clicked.

Example: Add an "Export" action in the Header section, then use Clicked Header Action - Name in an interaction to trigger an Export To action only when its value matches "export".

Detail Rows Actions

Fires when a user clicks a row-level action button inside the Detail Rows panel. Requires Detail Rows to be enabled.

Output variable

Type

What it returns

Clicked Detail Rows Action - Name

Text (String)

The name of the Detail Rows action button the user clicked.

Example: Add a "View Record" action in the Detail Rows Actions list, then use Clicked Detail Rows Action - Name to navigate to the record's page when its value matches "view-record".

Others

Output variable

Type

What it returns

Group By Cube

Text (String)

The current row/column grouping combination selected by the user.

Troubleshooting Common Issues

Pivot table is empty despite records in the Data Source — No row group, column group, or aggregation has been configured. In Data Mappings, add at least one Row Group or Column Group, then add an Aggregation with a numeric field and a Measure (SUM, AVG, COUNT, MIN, or MAX).
A configured row or column group does not appear — The component caps grouping at 3 fields total across rows and columns; any group beyond that limit is ignored. Reduce row groups + column groups to 3 fields combined.
Subtotals not showing even though Show Subtotals is enabled — Subtotals only render when you have more than one row group or more than one column group. Add a second row group (or column group), or rely on Show Grand Total for an overall total.
Clicking a cell does nothing and the detail panel never opens — Detail Rows is disabled in the Content section, or the user clicked a Grand Total / Subtotal cell (those are excluded from drill-down). Enable Detail Rows and click a regular data cell rather than a totals row.
Detail Rows opens but cells cannot be edited — Enable Inline Edit is off, or the field is a formula, system, or otherwise non-editable Salesforce field. Turn on Enable Inline Edit in the Content section; for non-editable fields, replace them with editable counterparts in the Aggregations list.
Save in the detail panel does not stick — Salesforce field-level security, a validation rule, or sharing rules blocked the write. Verify the user's permissions on the field/object, check active validation rules, and confirm record-level sharing, then re-run the save.
Export button does nothing when clicked — No interaction has been wired to the Header Action Click trigger, or the action's Target Name does not match the action's Name in the header. Open the Interactions tab, add Header Action Click, set Target Name to the header action's Name, and add Export To as the Type.
Pivot table works in builder preview but renders empty on the Lightning Page — The query relies on {!RecordId} and the page has no record context (e.g., placed on a Home page). Use the Pivot Table on a Record Page, or replace the record-based filter with absolute values (e.g., CloseDate = THIS_YEAR).
Performance is slow or the table fails to load on large datasets — Ungrouped queries can return tens of thousands of rows, and grouping on high-cardinality fields (e.g., Account.Name across the whole org) compounds the cost. Add filters to the Data Source query to narrow the dataset (date ranges, owner, record type) and group by lower-cardinality fields like Stage, Industry, or FiscalQuarter.

Styling

Configure the Pivot Table's appearance from the Style tab of the Edit Pivot Table panel.

Controls the outer spacing around the component.

Top / Right / Bottom / Left: Adjust the space on each side.

Controls the inner spacing between the component's content and its border.

Top / Right / Bottom / Left: Adjust the inner spacing on each side.

Controls the component's dimensions.

Width / Height: Set fixed dimensions.
Min Width / Max Width / Min Height / Max Height: Constrain the size within bounds.
Overflow: Control how content that exceeds the bounds is handled.

Customizes the border surrounding the component.

Color / Size / Style / Radius: Set the border color, thickness, style, and corner rounding.

Styles the Pivot Table header, its border, title, caption, and avatar.

Header: Background Color; Padding Top / Right / Bottom / Left; Margin Bottom.
Border: Color / Size / Style / Radius; Bottom Border Color / Size / Style (Is Joined).
Title: Color; Font Size / Font Style / Font Weight.
Caption: Color; Font Size / Font Style / Font Weight.
Avatar: Background Color / Foreground Color; Border Radius.

Last updated 2 days ago

Was this helpful?