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.
Connecting to Your Data Source
The foundation of your Pivot Table is the Salesforce data it analyzes. You'll connect the component to your data using the Avonni Query Data Source.
Configure the Query
Click Create/Edit Query:
Select Object: Choose the primary Salesforce object containing the data (e.g.,
Opportunity
).Add Filters (Recommended): Use filters within the query to limit the dataset before pivoting, such as by date range or record type. This significantly improves performance.
Save Query: Save your query definition.
Defining the Pivot Structure
Once connected to data, define the Pivot Table's layout in the Properties Panel: which fields form rows, columns, and what calculations appear in the cells.
Group Rows
Purpose: Organize data vertically. Each unique value in the selected field(s) creates a row header.
Configuration
Find the Row Groups section in the Properties Panel.
Click "Add Row Group".
Select the Field from your Data Source to group by (e.g.,
Account.Industry
).
Nesting Rows: Add multiple row groups for hierarchies (e.g.,
Account.Industry
thenAccount.Name
). The order determines the nesting level.

Group Columns
Purpose: Organize data horizontally. Each unique value creates a column header.
Configuration:
Find the Column Groups section.
Click "Add Column Group".
Select the Field from your Data Source (e.g.,
Stage
).
Nesting Columns: Add multiple column groups for nested columns (e.g.,
FiscalYear
thenFiscalQuarter
). Order defines the hierarchy.

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).
Aggregations
Purpose: Define the calculated values displayed in the table cells where rows and columns intersect.
Configuration:
Find the Values or Aggregations section.
Click "Add Aggregation".
Field: Select the numeric or date field to calculate (e.g.,
Amount
).Measure (or Function): Choose the calculation (
SUM
,AVERAGE
,COUNT
,MIN
,MAX
).
Multiple Values: Add multiple aggregations (e.g., SUM of Amount and COUNT of Opportunities) to display several calculations.
Customizing Display Options
Control how totals and multiple calculations are presented.
Grand Totals
Property:
Show Grand Total
(Boolean - Checkbox)What it Does: Adds a final row and column showing overall totals for all data.
When to Use: Enable for a quick overview of the dataset; disable to focus on group comparisons.

Subtotals (for Nested Groups)
Property:
Show Subtotals
(Boolean - Checkbox)What it Does: Adds intermediate totals for each subgroup when you have nested row or column groups.
When to Use: Enable for context at each hierarchy level; disable to simplify the view. Note: Only applies if you have more than one row or column group.
Multiple Aggregations Display (Stacked Summaries)
Property:
Stacked Summaries
(Boolean - Checkbox)What it Does: Controls display when using multiple aggregations (e.g., SUM and AVERAGE).
Options:
Enabled (Checked): Displays results together within cells (stacked or side by side) for direct comparison.
Disabled (Unchecked): Displays each calculation in separate rows and columns, creating a larger table that may be clearer for different scales.
Example: Analyzing Opportunity Data
Let's illustrate by analyzing opportunities:
Data Source Query: Select
Account.Name
,StageName
,FiscalQuarter
,FiscalYear
,Amount
fromOpportunity
.Row Groups:
Account.Name
StageName
Column Groups:
FiscalYear
Values (Aggregations):
Field:
Amount
, Measure:Sum
Display Options:
Show Grand Total
: trueShow Subtotals
: falseStacked Summaries
: false
Result: This creates a Pivot Table showing total and average opportunity amounts per stage for each account, broken down by fiscal year and quarter, with subtotals and grand totals.

Best Practices & Considerations
Grouping Limits: Be aware that grouping by fields with a high number of unique values (e.g., individual record names in a large dataset) can impact 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?