# 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)

<figure><img src="https://2532358799-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FODPvvv7Cx9Z9RECLn3oV%2Fuploads%2Fb0xuq1esDTAgmp2GkxeZ%2F2025-12-05_15-24-55.png?alt=media&#x26;token=3dd97579-518c-4dcb-8e9f-c3601525fc8f" alt=""><figcaption></figcaption></figure>

***

{% stepper %}
{% step %}

#### **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)
  {% endstep %}

{% step %}

#### **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
  {% endstep %}

{% step %}

#### **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 sectio&#x6E;**.**
* 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**

<figure><img src="https://2532358799-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FODPvvv7Cx9Z9RECLn3oV%2Fuploads%2FCcljIWKs80tGjBpO4IqZ%2F2025-12-05_15-06-26.png?alt=media&#x26;token=3afce7c2-73a4-4d5f-a202-bcfd7551a27c" alt="" width="563"><figcaption></figcaption></figure>
{% endstep %}

{% step %}

#### **Define Groups Row**

Set up how data will be organized vertically. Each unique stage name will become its own row in the table.

* Find the **Data Mappings** section in the Properties Panel
* Click **Add Group Row**
* For **Field**, select **StageName**

<figure><img src="https://2532358799-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FODPvvv7Cx9Z9RECLn3oV%2Fuploads%2FfciJ4YrsDUEPqVoP61zJ%2F2025-12-05_15-11-36.png?alt=media&#x26;token=8527f9c2-cec7-4e2a-95d3-ed6e5439bea8" alt="" width="324"><figcaption></figcaption></figure>
{% endstep %}

{% step %}

#### **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

<figure><img src="https://2532358799-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FODPvvv7Cx9Z9RECLn3oV%2Fuploads%2F3f0AHJKYbPGIkNvIQCYC%2F2025-12-05_15-12-14.png?alt=media&#x26;token=66a7c256-aac2-46c3-802e-f7f21a376d60" alt="" width="327"><figcaption></figcaption></figure>
{% endstep %}

{% step %}

#### **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.

<figure><img src="https://2532358799-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FODPvvv7Cx9Z9RECLn3oV%2Fuploads%2FA85hyKYXI4eoYmobEGSZ%2F2025-12-05_15-14-37.png?alt=media&#x26;token=f9c77d5d-4a07-46a1-9bf0-8850aa355074" alt="" width="375"><figcaption></figcaption></figure>
{% endstep %}

{% step %}

#### **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

<figure><img src="https://2532358799-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FODPvvv7Cx9Z9RECLn3oV%2Fuploads%2FNBSy6GPdaiqwUr4duoxK%2F2025-12-05_15-15-44.png?alt=media&#x26;token=56261feb-7447-4b53-84ee-e0f77bb4d91a" alt="" width="483"><figcaption></figcaption></figure>
{% endstep %}

{% step %}

#### **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**

<figure><img src="https://2532358799-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FODPvvv7Cx9Z9RECLn3oV%2Fuploads%2Foc9veQkXytEFBB9zGMEE%2F2025-12-05_15-17-12.png?alt=media&#x26;token=ea6493cb-fc75-41bd-82a2-06531bfa7a03" alt=""><figcaption></figcaption></figure>
{% endstep %}

{% step %}

#### **Add Export Interaction** (Optional)

Make the Export button functional so users can download the pivot table data.

* Click the **Interactions** tab in the Properties Panel
* Select **Header Action Click** as the trigger
* Click **Add Header Action Click** → select **Export To** as Type
* Set the options based on your needs

<figure><img src="https://2532358799-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FODPvvv7Cx9Z9RECLn3oV%2Fuploads%2FagCRx3CRAVm2xtHxF0hg%2F2025-12-05_15-19-23.png?alt=media&#x26;token=1671f924-e4d3-4110-8b29-bcd7efea4c61" alt="" width="563"><figcaption></figcaption></figure>
{% endstep %}

{% step %}

#### **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
  {% endstep %}
  {% endstepper %}

### **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.

<figure><img src="https://2532358799-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FODPvvv7Cx9Z9RECLn3oV%2Fuploads%2Fb0xuq1esDTAgmp2GkxeZ%2F2025-12-05_15-24-55.png?alt=media&#x26;token=3dd97579-518c-4dcb-8e9f-c3601525fc8f" alt=""><figcaption></figcaption></figure>

***

## Configuration Settings

### Data Source

Connects the Pivot Table to Salesforce records containing the data you want to analyze.

<table><thead><tr><th width="216.3876953125">Setting</th><th>Description</th></tr></thead><tbody><tr><td><strong>Data Source</strong></td><td>Must be set to <strong>Avonni Query Data Source</strong></td></tr><tr><td><strong>Query Configuration</strong></td><td>Define which object to analyze, apply filters to limit the dataset (recommended for performance), and set sorting</td></tr></tbody></table>

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

<table><thead><tr><th width="217.4912109375">Setting</th><th>Description</th></tr></thead><tbody><tr><td><strong>Field</strong></td><td>The field from your data source to group by (e.g., <code>Account.Industry</code>, <code>StageName</code>)</td></tr></tbody></table>

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

<table><thead><tr><th width="236.0166015625">Setting</th><th>Description</th></tr></thead><tbody><tr><td><strong>Field</strong></td><td>The field from your data source to group by (e.g., <code>FiscalYear</code>, <code>Stage</code>)</td></tr></tbody></table>

{% hint style="warning" %}

## 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).
{% endhint %}

**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

<table><thead><tr><th width="155.30859375">Setting</th><th>Description</th></tr></thead><tbody><tr><td><strong>Field</strong></td><td>Select the numeric or date field to calculate (e.g., <code>Amount</code>, <code>Quantity</code>)</td></tr><tr><td><strong>Measure</strong></td><td>Choose the calculation: <strong>SUM</strong>, <strong>AVERAGE</strong>, <strong>COUNT</strong>, <strong>MIN</strong>, or <strong>MAX</strong></td></tr><tr><td><strong>Multiple Aggregations</strong></td><td>Add multiple calculations to display several metrics side-by-side (e.g., SUM of Amount and COUNT of records)</td></tr></tbody></table>

**Example:** Aggregation 1 = Field: `Amount`, Measure: `SUM`; Aggregation 2 = Field: `Id`, Measure: `COUNT`

### Display Options

Control how totals and multiple calculations are presented.

<table><thead><tr><th width="187.173828125">Setting</th><th>Description</th><th>When to Use</th></tr></thead><tbody><tr><td><strong>Show Grand Total</strong></td><td>Adds final row and column showing overall totals for all data</td><td>Enable for quick dataset overview; disable to focus on group comparisons</td></tr><tr><td><strong>Show Subtotals</strong></td><td>Adds intermediate totals for each subgroup when using nested row or column groups</td><td>Enable for context at each hierarchy level; disable to simplify the view. Only applies with multiple groups</td></tr><tr><td><strong>Stacked Summaries</strong></td><td>Controls display when using multiple aggregations</td><td><strong>Enabled:</strong> Results appear together within cells for direct comparison. <strong>Disabled:</strong> Each calculation gets separate rows/columns for clearer separation</td></tr></tbody></table>

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

<table><thead><tr><th width="225.7919921875">Setting</th><th>Description</th></tr></thead><tbody><tr><td><strong>Title</strong></td><td>Main heading displayed above the pivot table (e.g., "Sales Performance Dashboard")</td></tr><tr><td><strong>Caption</strong></td><td>Descriptive subtitle providing additional context (e.g., "Year-to-date analysis by region and product")</td></tr><tr><td><strong>Avatar - Image</strong></td><td>URL to an image displayed in the header (optional)</td></tr><tr><td><strong>Avatar - Fallback Icon Name</strong></td><td>SLDS icon to display when no avatar image is provided (e.g., <code>standard:report</code>)</td></tr><tr><td><strong>Avatar - Initials</strong></td><td>Text to display in the avatar circle when no image is available (e.g., "SA")</td></tr><tr><td><strong>Help Text - Content</strong></td><td>Informational text or guidance displayed near the header to help users understand the data</td></tr><tr><td><strong>Is Joined</strong></td><td>When enabled, removes spacing between the header and the table body for a more compact appearance</td></tr><tr><td><strong>Actions</strong></td><td>Add buttons to the header for common tasks like exporting, refreshing, or navigating to related records</td></tr><tr><td><strong>Visible Actions Buttons</strong></td><td>Number of action buttons to display before collapsing into a dropdown menu</td></tr><tr><td><strong>Hide Actions</strong></td><td>When enabled, temporarily hides all action buttons from view</td></tr><tr><td><strong>Disable Actions</strong></td><td>When enabled, action buttons appear but cannot be clicked</td></tr></tbody></table>

**Example:** Title: "Quarterly Pipeline Analysis", Caption: "Filtered by open opportunities", Actions: Export button with download icon.

***

## Interactions

The [**Interactions tab**](https://docs.avonnicomponents.com/dynamic-components/component-builder/interactions) 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:

1. Add a header action labeled "Export" with a download icon
2. In Interactions, select **Header Action Click**
3. Add Type → **Export To**
4. Choose the export format (CSV, Excel, etc.)
5. Configure any additional export settings as needed

<figure><img src="https://2532358799-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FODPvvv7Cx9Z9RECLn3oV%2Fuploads%2FagCRx3CRAVm2xtHxF0hg%2F2025-12-05_15-19-23.png?alt=media&#x26;token=1671f924-e4d3-4110-8b29-bcd7efea4c61" alt="" width="563"><figcaption></figcaption></figure>

***

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.avonnicomponents.com/dynamic-components/components/pivot-table.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.