Pivot Table
Last updated
Was this helpful?
Last updated
Was this helpful?
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.
Use the Pivot Table component to condense large amounts of information into a meaningful, interactive summary table.
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.
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.
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.
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.
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 then Account.Name). The order determines the nesting level.
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 then FiscalQuarter). Order defines the hierarchy.
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).
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.
Control how totals and multiple calculations are presented.
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.
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.
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.
Let's illustrate by analyzing opportunities:
Data Source Query: Select Account.Name, StageName, FiscalQuarter, FiscalYear, Amount from Opportunity.
Row Groups:
Account.Name
StageName
Column Groups:
FiscalYear
Values (Aggregations):
Field: Amount, Measure: Sum
Display Options:
Show Grand Total: true
Show Subtotals: false
Stacked 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.
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.
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
