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.

1

Select the Pivot Table Component

Drag the component on the canvas.

2

Choose Data Source Type

In the Properties Panel, set the Data Source to Avonni Query Data Source.

3

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

  1. Find the Row Groups section in the Properties Panel.

  2. Click "Add Row Group".

  3. 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.

Group Columns

• Purpose: Organize data horizontally. Each unique value creates a column header.

• Configuration:

  1. Find the Column Groups section.

  2. Click "Add Column Group".

  3. 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.

Aggregations

• Purpose: Define the calculated values displayed in the table cells where rows and columns intersect.

• Configuration:

  1. Find the Values or Aggregations section.

  2. Click "Add Aggregation".

  3. Field: Select the numeric or date field to calculate (e.g., Amount).

  4. 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 from Opportunity.

• Row Groups:

  1. Account.Name

  2. StageName

• Column Groups:

  1. FiscalYear

• Values (Aggregations):

  1. 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.

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.