> For the complete documentation index, see [llms.txt](https://docs.avonnicomponents.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.avonnicomponents.com/dynamic-components/components/pivot-table.md). # Pivot Table 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. ## Overview ### **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)
*** {% 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 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**
{% 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**
{% 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
{% 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.
{% 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
{% 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**
{% 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
{% 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 at each stage-period intersection. Grand totals appear at the bottom and right edge. Users can click headers to sort or reconfigure groupings.
*** ## Configuration To configure the Pivot Table, select it on the canvas. The **Edit Pivot Table** panel opens on the right with two tabs: **Properties** and **Interactions**. The sections below mirror the Properties tab. ### Data Source The Pivot Table requires a **Query** data source. Connect it to Salesforce records containing the data you want to analyze. * Click **Create Query** to define which object to query, 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. ### Data Mappings Once a Query data source is configured, use the **Data Mappings** section to define how your query fields drive the pivot table structure. There are three mapping areas: **Row Groups** Each row group field creates a set of row headers. Each unique value in the selected field becomes its own row. You can add a custom display label for any group by enabling **Custom Label**. **Limits:** Maximum of 3 grouping fields total across Row Groups and Column Groups 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). {% 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 %} **Column Groups** Each column group field creates a set of column headers. Each unique value creates its own column. Custom labels are also supported here. **Example:** Column group = `FiscalYear` (shows a separate column for each year: 2023, 2024, 2025). **Aggregations** Define the calculated values displayed in table cells where rows and columns intersect. For each aggregation, select a **Field** and a **Measure**. Available measures: **AVG**, **COUNT**, **COUNT\_DISTINCT**, **MAX**, **MIN**, **SUM**. You can add multiple aggregations to display several metrics side-by-side. Custom labels are supported. **Example:** Aggregation 1 = Field: `Amount`, Measure: `SUM`; Aggregation 2 = Field: `Id`, Measure: `COUNT`. You can also select one or more fields to expose as **user filters** in the Filter section (see below), so end-users can narrow the pivot table interactively without changing the underlying query. ### Content Control how totals, drill-down, and multiple calculations are presented. **Show Grand Total** — When enabled, adds a final row and column showing overall totals for all data. Enable for a quick dataset overview; disable to focus on group comparisons. **Show Subtotals** — When enabled, adds intermediate totals for each subgroup when using nested row or column groups. Only meaningful when more than one row group or more than one column group is defined. **Stacked Summaries** — Controls how multiple aggregations are displayed. When enabled, results for all aggregations appear together within the same cells for direct comparison. When disabled, each aggregation gets its own separate rows/columns. **Detail Rows** — When enabled, clicking any data cell opens a panel listing the underlying records that make up that cell's aggregated value. Useful when users need to inspect or audit the source records behind a summary number. **Detail Rows Actions** — When **Detail Rows** is enabled, you can add row-level action buttons that appear in the detail panel. Each action requires a **Label** and a **Name** (used to identify it in an interaction), and optionally an **Icon Name**, **Disabled**, and **Hidden** flag. Requires: **Detail Rows** enabled. **Enable Inline Edit** — When enabled, users can edit field values directly inside the Detail Rows panel. Edited values are saved back to the underlying Salesforce records. Requires: **Detail Rows** enabled. #### How Detail Rows Work * The user clicks any data cell (excluding subtotal and total cells). * A panel slides in below the table, filtered to match the row group and column group of the selected cell. For example, clicking the cell at `Proposal × Q3` shows only Opportunities where `StageName = 'Proposal'` AND the close date falls in Q3. * Each Aggregation field configured on the table appears as a column in the detail panel. The first column always shows the record name (or the default name field for the queried object). * The user closes the panel to return to the summary view, or clicks another cell to refilter the panel. #### How Inline Edit Works When **Enable Inline Edit** is on, every Aggregation column in the detail panel is editable. * Double-click a cell to open the inline editor for that field. * Edit one or many cells — pending changes are tracked in a draft state. * Click **Save** at the bottom of the panel to commit changes to Salesforce. * While saving, the table shows a loading state. Once the save resolves, the pivot table re-runs its query so the aggregated values reflect the new data. {% hint style="info" %} #### **Enable Inline Edit** is dependent on **Detail Rows** Turning Detail Rows off automatically disables inline editing in the Properties Panel. {% endhint %} {% hint style="warning" %} Inline edits write directly to the underlying Salesforce records. Standard field-level security, validation rules, and record sharing still apply — users who lack edit access on a field will see the cell as read-only or get an error toast on Save. {% endhint %} #### Setup Steps {% stepper %} {% step %} #### **Enable Detail Rows** * Select the **Pivot Table** on the canvas. * In the **Content** section of the Properties Panel, toggle **Detail Rows** on. {% endstep %} {% step %} #### **Enable Inline Edit (optional)** * In the same **Content** section, toggle **Enable Inline Edit** on. * *Note: This option only appears once Detail Rows is enabled.* {% endstep %} {% step %} #### **Choose Aggregation Fields That Make Sense to Edit** * Inline editing applies to the fields you set up under **Aggregations**. * Pick fields that users are expected to update from this view (e.g., `Amount`, `CloseDate`, `StageName`). Avoid using read-only fields like `Id` or formula fields — they will appear as read-only cells. {% endstep %} {% endstepper %} ### Header Add context and actions to your pivot table with a title, caption, avatar, and action buttons. | Setting | Description | | --------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **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"). Displayed above the title. | | **Avatar** | Optional image, fallback icon, or initials displayed in the header. Set an **Image** URL, a **Fallback Icon Name** (e.g., `standard:report`), or **Initials** (e.g., "SA"). Size and variant (Circle/Square) are available under advanced options. | | **Help Text** | Informational text displayed as a hoverable icon near the header to help users understand the data. | | **Is Joined** | When enabled, removes the bottom border and shadow so the header sits flush with an adjacent component. | | **Actions** | Add buttons to the header for common tasks like exporting or refreshing. Each action requires a **Label** and a **Name**, and optionally an **Icon Name**, **Title** (tooltip), **Disabled**, and **Hidden** flag. | | **Visible Actions Buttons** | Number of action buttons to display before the rest collapse into a dropdown overflow menu. | | **Hide Actions** | When enabled, temporarily hides all header 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. ### Filter The **Filter** section lets you expose interactive filter controls to end-users above the pivot table, driven by the fields you selected in Data Mappings. | Setting | Description | | ------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Display As Popover** | When enabled, the filter controls are displayed inside a popover instead of inline. | | **Use Record Picklist Values** | When enabled and filtering a picklist field, filter options are derived from values present in the records instead of the picklist's full defined value set. | | **Use Cascading Filter Values** | When enabled, selecting a filter dynamically narrows the options in the other filters to reflect only records matching the current selection. Overrides **Use Record Picklist Values**. | ### Set Component Visibility All components support conditional visibility — see [Component Visibility](/dynamic-components/core-concepts/component-visibility.md). ## Interactions [Interactions](/dynamic-components/component-builder/interactions.md) define what happens when users interact with the Pivot Table. Configure them from the **Interactions** tab of the Edit Pivot Table panel. ### **On Header Action Click** Triggered when a user clicks a button added to the header. Use the **Target Name** field to match the action's **Name** from the Header settings. **Common use — Export Data to CSV** 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
### **On Detail Rows Action Click** Triggered when a user clicks a row-level action button inside the Detail Rows panel. Use the **Target Name** field to match the action's **Name** from the Detail Rows Actions settings. Requires **Detail Rows** to be enabled. *** ## Specifications ### Output Attributes | Property | Type | Description | | ----------------------------- | ------ | ----------------------------------------------------------------- | | `clickedDetailRowsActionName` | string | — | | `clickedHeaderActionName` | string | — | | `groupByCube` | string | The current row/column grouping combination selected by the user. | ## Troubleshooting Common Issues | Problem | Cause | Fix | | ------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | The pivot table is empty even though the Data Source has records. | No row group or column group has been configured, or no aggregation has been added — the table needs at least one of each to render meaningful data. | In **Data Mappings**, add at least one **Row Group** or **Column Group**, then add an **Aggregation** with a numeric field and a Measure (SUM, AVG, COUNT, MIN, or MAX). | | A configured row or column group does not appear in the table. | The component caps grouping at 3 fields total across rows and columns. Any group beyond that limit is ignored. | Reduce row groups + column groups to 3 fields combined. | | Subtotals are not showing even though **Show Subtotals** is enabled. | Subtotals only render when you have more than one row group or more than one column group. With a single grouping level, there are no intermediate groups to subtotal. | Add a second row group (or column group), or rely on **Show Grand Total** for an overall total. | | Clicking a cell does nothing — the detail panel never opens. | **Detail Rows** is disabled in the Content section, or the user clicked a Grand Total / Subtotal cell (those are excluded from drill-down). | Enable **Detail Rows**, and click a regular data cell rather than a totals row. | | Detail Rows opens but cells cannot be edited. | **Enable Inline Edit** is off, or the field is a formula, system, or otherwise non-editable Salesforce field. | Turn on **Enable Inline Edit** in the Content section. For non-editable fields, replace them with editable counterparts in the **Aggregations** list. | | User clicks **Save** in the detail panel but the change does not stick. | Salesforce field-level security, a validation rule, or sharing rules blocked the write. The Pivot Table surfaces the error as a toast but does not alter the underlying behavior. | Verify the user's permissions on the field/object, check active validation rules, and confirm record-level sharing. Re-run the save once the cause is resolved. | | The Export button on the header does nothing when clicked. | No interaction has been wired to the **Header Action Click** trigger, or the action's **Target Name** does not match the action's **Name** in the header. | Open the **Interactions** tab → add **Header Action Click** → set Target Name to the header action's Name → add **Export To** as the Type. | | The pivot table works in the builder preview but renders empty on the Lightning Page. | The query relies on `{!RecordId}` or `{!$Component.RecordId}` and the page has no record context (e.g., it was placed on a Home page). | Use the Pivot Table on a Record Page where a record context exists, or remove the record-based filter and rely on absolute filter values (e.g., `CloseDate = THIS_YEAR`). | | Performance is slow or the table fails to load on large datasets. | Ungrouped queries can return tens of thousands of rows, and grouping on high-cardinality fields (e.g., `Account.Name` across the whole org) compounds the cost. | Add filters to the Data Source query to narrow the dataset (date ranges, owner, record type), and group by lower-cardinality fields like `Stage`, `Industry`, or `FiscalQuarter`. | --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## 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= ``` 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.