Pivot Table
Overview
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.
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.
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 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
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 Settings
Data Source
Connects the Pivot Table to Salesforce records containing the data you want to analyze.
Data Source
Must be set to Avonni Query Data Source
Query Configuration
Define which object to analyze, 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.
Row Groups
Define how data is organized vertically. Each unique value in the selected field creates its own row header.
Field
The field from your data source to group by (e.g., Account.Industry, StageName)
Limits: Maximum of 3 grouping fields total across rows and columns 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)
Groups Column
Define how data is organized horizontally. Each unique value creates its own column header.
Field
The field from your data source to group by (e.g., FiscalYear, Stage)
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).
Example: Column group = FiscalYear (shows a separate column for each year: 2023, 2024, 2025)
Aggregation
Define the calculated values displayed in table cells where rows and columns intersect
Field
Select the numeric or date field to calculate (e.g., Amount, Quantity)
Measure
Choose the calculation: SUM, AVERAGE, COUNT, MIN, or MAX
Multiple Aggregations
Add multiple calculations to display several metrics side-by-side (e.g., SUM of Amount and COUNT of records)
Example: Aggregation 1 = Field: Amount, Measure: SUM; Aggregation 2 = Field: Id, Measure: COUNT
Display Options
Control how totals and multiple calculations are presented.
Show Grand Total
Adds final row and column showing overall totals for all data
Enable for quick dataset overview; disable to focus on group comparisons
Show Subtotals
Adds intermediate totals for each subgroup when using nested row or column groups
Enable for context at each hierarchy level; disable to simplify the view. Only applies with multiple groups
Stacked Summaries
Controls display when using multiple aggregations
Enabled: Results appear together within cells for direct comparison. Disabled: Each calculation gets separate rows/columns for clearer separation
Detail Rows
When enabled, clicking a cell in the table opens a drill-down panel listing the underlying records that make up that cell's aggregated value.
Enable when users need to inspect, audit, or drill from a summary number to the actual records behind it. Disable for read-only dashboards where the summary is the final answer.
Enable Inline Edit
Lets users edit field values directly inside the Detail Rows panel. Available only when Detail Rows is enabled. Edited values are saved back to the underlying Salesforce records.
Enable when users should update records without leaving the pivot view (for example, correcting Amount, Stage, or CloseDate during a pipeline review). Leave off for analysts who only consume data.
Example: Show Grand Total: true, Show Subtotals: false, Stacked Summaries: false displays overall totals without intermediate subtotals, with each calculation in its own section.
Example: Detail Rows: true, Enable Inline Edit: true lets a sales manager click a cell showing $1.2M in the Proposal stage, see the 8 opportunities behind that number, and update Stage or Amount on the spot — the table refreshes automatically once the save completes.
Detail Rows & Inline Edit
Detail Rows turn the Pivot Table from a read-only summary into an explorable view. When enabled, every aggregated cell becomes clickable and opens a panel showing the raw records that produced the number.
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 × Q3shows only Opportunities whereStageName = '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
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 likeIdor 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.
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")
Avatar - Image
URL to an image displayed in the header (optional)
Avatar - Fallback Icon Name
SLDS icon to display when no avatar image is provided (e.g., standard:report)
Avatar - Initials
Text to display in the avatar circle when no image is available (e.g., "SA")
Help Text - Content
Informational text or guidance displayed near the header to help users understand the data
Is Joined
When enabled, removes spacing between the header and the table body for a more compact appearance
Actions
Add buttons to the header for common tasks like exporting, refreshing, or navigating to related records
Visible Actions Buttons
Number of action buttons to display before collapsing into a dropdown menu
Hide Actions
When enabled, temporarily hides all 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.
Interactions
The Interactions tab lets you configure responses to user clicks on header action buttons. When a user clicks an action button you've added to the header (like "Export" or "Refresh")
Common Interaction Patterns
Export Data to CSV
Let users download pivot table data:
Add a header action labeled "Export" with a download icon
In Interactions, select Header Action Click
Add Type → Export To
Choose the export format (CSV, Excel, etc.)
Configure any additional export settings as needed
Troubleshooting
The pivot table is empty even though the Data Source has records.
No row group or column group has been configured, or no aggregation has been added — the table needs at least one of each to render meaningful data.
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 in the table.
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 are 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. With a single grouping level, there are no intermediate groups to subtotal.
Add a second row group (or column group), or rely on Show Grand Total for an overall total.
Clicking a cell does nothing — 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.
User clicks Save in the detail panel but the change does not stick.
Salesforce field-level security, a validation rule, or sharing rules blocked the write. The Pivot Table surfaces the error as a toast but does not alter the underlying behavior.
Verify the user's permissions on the field/object, check active validation rules, and confirm record-level sharing. Re-run the save once the cause is resolved.
The Export button on the header 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 → add Export To as the Type.
The pivot table works in the builder preview but renders empty on the Lightning Page.
The query relies on {!RecordId} or {!$Component.RecordId} and the page has no record context (e.g., it was placed on a Home page).
Use the Pivot Table on a Record Page where a record context exists, or remove the record-based filter and rely on absolute filter 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.
In Summary
The Avonni Pivot Table component is a powerful tool for interactive data summarization and analysis directly on Lightning Pages. By effectively configuring the data source, row/column groupings, value aggregations, and display options, you can transform raw Salesforce data into actionable business insights.
Last updated
Was this helpful?