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 where each stage and period intersect. 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
Example: Show Grand Total: true, Show Subtotals: false, Stacked Summaries: false displays overall totals without intermediate subtotals, with each calculation in its own section.
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
Best Practices & Considerations
Grouping Limits: Be aware that grouping by fields with many unique values (e.g., individual record names in a large dataset) can affect performance and readability.
Governor Limits: Complex queries or operations on massive datasets might approach Salesforce governor limits. Design efficiently.
Designing for Users: Select row and column groups and aggregations that directly address the business questions your users have. Keep the initial view focused.
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?