React Data GridPivot Result Columns

Version 36.2.0

Enterprise

The grid generates pivot result columns to display the aggregated values for each unique permutation of pivot values.

index.tsx

"use client";

import React, {
  useCallback,
  useMemo,
  useRef,
  useState,
  StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import {
  AutoGroupColumnDef,
  ClientSideRowModelModule,
  ColDef,
  ColGroupDef,
  GridApi,
  GridOptions,
  ModuleRegistry,
  enableDevValidations,
} from "ag-grid-community";
import {
  ColumnMenuModule,
  ColumnsToolPanelModule,
  ContextMenuModule,
  PivotModule,
} from "ag-grid-enterprise";
import { IOlympicData } from "./interfaces";
import { useFetchJson } from "./useFetchJson";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

const modules = [
  ClientSideRowModelModule,
  ColumnsToolPanelModule,
  ColumnMenuModule,
  ContextMenuModule,
  PivotModule,
];

const GridExample = () => {
  const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
  const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);

  const [columnDefs, setColumnDefs] = useState<ColDef[]>([
    { field: "country", rowGroup: true },
    { field: "sport", pivot: true },
    { field: "gold", aggFunc: "sum" },
    { field: "silver", aggFunc: "sum" },
    { field: "bronze", aggFunc: "sum" },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      flex: 1,
      minWidth: 130,
    };
  }, []);
  const autoGroupColumnDef = useMemo<AutoGroupColumnDef>(() => {
    return {
      minWidth: 200,
    };
  }, []);

  const { data, loading } = useFetchJson<IOlympicData>(
    "https://www.ag-grid.com/example-assets/olympic-winners.json",
  );

  return (
    <AgGridProvider modules={modules}>
      <div style={containerStyle}>
        <div
          style={{ display: "flex", flexDirection: "column", height: "100%" }}
        >
          <div style={gridStyle}>
            <AgGridReact<IOlympicData>
              rowData={data}
              loading={loading}
              columnDefs={columnDefs}
              defaultColDef={defaultColDef}
              autoGroupColumnDef={autoGroupColumnDef}
              pivotMode={true}
            />
          </div>
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

interfaces.ts


export interface IOlympicData {
    athlete: string,
    age: number,
    country: string,
    year: number,
    date: string,
    sport: string,
    gold: number,
    silver: number,
    bronze: number,
    total: number
}

useFetchJson.tsx

import { useState, useEffect } from 'react';

/**
 * Fetch example Json data
 * Not recommended for production use!
 */
export const useFetchJson = <T,>(url:string, limit?: number) => {
    const [data, setData] = useState<T[]>();
    const [loading, setLoading] = useState(true);
    useEffect(() => {
        // StrictMode runs this effect twice: drop the superseded run's response rather than applying both.
        let cancelled = false;
        const fetchData = async () => {
            setLoading(true);

            // Note error handling is omitted here for brevity
            const response = await fetch(url);
            const json = await response.json();
            const data = limit ? json.slice(0, limit) : json;
            if (cancelled) {
                return;
            }
            setData(data);
            setLoading(false);
        };
        fetchData();
        return () => {
            cancelled = true;
        };
    }, [url, limit]);
    return { data, loading };
};

Column Definitions Copy Link

Pivot Result Columns inherit Column Definitions from the value column that they were created from. It is also possible to extend this definition further to specifically customise pivot result columns using the processPivotResultColDef grid option.

ProcessPivotResultColDef

Callback for the mutation of the generated pivot result column definitions

PivotModule

In the example below, the Gold column has cellStyle: { backgroundColor: '#f2e287' } applied, this is then inherited by the pivot result columns, causing all of the sum(Gold) columns to have a gold background. Note that the Silver column does not have this background so neither do the sum(Silver) columns.

The grid option processPivotResultColDef is then also used, which sets the text colour of all the pivot result columns to #2f73ff.

index.tsx

"use client";

import React, {
  useCallback,
  useMemo,
  useRef,
  useState,
  StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import {
  AutoGroupColumnDef,
  CellStyleModule,
  ClientSideRowModelModule,
  ColDef,
  ColGroupDef,
  GridApi,
  GridOptions,
  ModuleRegistry,
  ProcessPivotResultColDef,
  enableDevValidations,
} from "ag-grid-community";
import {
  ColumnMenuModule,
  ColumnsToolPanelModule,
  ContextMenuModule,
  PivotModule,
} from "ag-grid-enterprise";
import { IOlympicData } from "./interfaces";
import { useFetchJson } from "./useFetchJson";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

const modules = [
  CellStyleModule,
  ClientSideRowModelModule,
  ColumnsToolPanelModule,
  ColumnMenuModule,
  ContextMenuModule,
  PivotModule,
];

const GridExample = () => {
  const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
  const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);

  const [columnDefs, setColumnDefs] = useState<ColDef[]>([
    { field: "country", rowGroup: true },
    { field: "sport", pivot: true },
    {
      field: "gold",
      aggFunc: "sum",
      cellStyle: { backgroundColor: "#f2e287" },
    },
    { field: "silver", aggFunc: "sum", cellStyle: {} },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      flex: 1,
      minWidth: 130,
    };
  }, []);
  const autoGroupColumnDef = useMemo<AutoGroupColumnDef>(() => {
    return {
      minWidth: 200,
    };
  }, []);
  const processPivotResultColDef = useCallback((colDef) => {
    if (typeof colDef.cellStyle === "object") {
      colDef.cellStyle.color = "#2f73ff";
    }
  }, []);

  const { data, loading } = useFetchJson<IOlympicData>(
    "https://www.ag-grid.com/example-assets/olympic-winners.json",
  );

  return (
    <AgGridProvider modules={modules}>
      <div style={containerStyle}>
        <div
          style={{ display: "flex", flexDirection: "column", height: "100%" }}
        >
          <div style={gridStyle}>
            <AgGridReact<IOlympicData>
              rowData={data}
              loading={loading}
              columnDefs={columnDefs}
              defaultColDef={defaultColDef}
              autoGroupColumnDef={autoGroupColumnDef}
              pivotMode={true}
              processPivotResultColDef={processPivotResultColDef}
            />
          </div>
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

interfaces.ts


export interface IOlympicData {
    athlete: string,
    age: number,
    country: string,
    year: number,
    date: string,
    sport: string,
    gold: number,
    silver: number,
    bronze: number,
    total: number
}

useFetchJson.tsx

import { useState, useEffect } from 'react';

/**
 * Fetch example Json data
 * Not recommended for production use!
 */
export const useFetchJson = <T,>(url:string, limit?: number) => {
    const [data, setData] = useState<T[]>();
    const [loading, setLoading] = useState(true);
    useEffect(() => {
        // StrictMode runs this effect twice: drop the superseded run's response rather than applying both.
        let cancelled = false;
        const fetchData = async () => {
            setLoading(true);

            // Note error handling is omitted here for brevity
            const response = await fetch(url);
            const json = await response.json();
            const data = limit ? json.slice(0, limit) : json;
            if (cancelled) {
                return;
            }
            setData(data);
            setLoading(false);
        };
        fetchData();
        return () => {
            cancelled = true;
        };
    }, [url, limit]);
    return { data, loading };
};

This uses the following configuration to both inherit and modify column definitions on the pivot result columns:

const [columnDefs, setColumnDefs] = useState([
    // ...other column definitions
    { field: 'gold', aggFunc: 'sum', cellStyle: { backgroundColor: '#f2e287' } },
    { field: 'silver', aggFunc: 'sum', cellStyle: {} },
]);
const pivotMode = true;
const processPivotResultColDef = useCallback((colDef) => {
    colDef.cellStyle.color = '#2f73ff'; // the params are mutated directly, not returned
}, []);

<AgGridReact
    columnDefs={columnDefs}
    pivotMode={pivotMode}
    processPivotResultColDef={processPivotResultColDef}
/>

Filtering Copy Link

When pivot mode is enabled, you can Filter on the pivot result columns by setting the filter attribute on your value column.

index.tsx

"use client";

import React, {
  useCallback,
  useMemo,
  useRef,
  useState,
  StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import "./styles.css";
import {
  AutoGroupColumnDef,
  ClientSideRowModelModule,
  ColDef,
  ColGroupDef,
  GridApi,
  GridOptions,
  ModuleRegistry,
  NumberFilterModule,
  enableDevValidations,
} from "ag-grid-community";
import {
  ColumnMenuModule,
  ContextMenuModule,
  FiltersToolPanelModule,
  PivotModule,
  SetFilterModule,
} from "ag-grid-enterprise";
import { IOlympicData } from "./interfaces";
import { useFetchJson } from "./useFetchJson";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

const modules = [
  ClientSideRowModelModule,
  FiltersToolPanelModule,
  ColumnMenuModule,
  ContextMenuModule,
  PivotModule,
  SetFilterModule,
  NumberFilterModule,
];

const GridExample = () => {
  const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
  const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);

  const [columnDefs, setColumnDefs] = useState<ColDef[]>([
    { field: "country", rowGroup: true },
    { field: "athlete", rowGroup: true },
    { field: "year", pivot: true },
    { field: "gold", aggFunc: "sum", filter: "agNumberColumnFilter" },
    { field: "silver", aggFunc: "sum", filter: "agNumberColumnFilter" },
    { field: "bronze", aggFunc: "sum", filter: "agNumberColumnFilter" },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      flex: 1,
      minWidth: 130,
      floatingFilter: true,
    };
  }, []);
  const autoGroupColumnDef = useMemo<AutoGroupColumnDef>(() => {
    return {
      minWidth: 200,
    };
  }, []);

  const { data, loading } = useFetchJson<IOlympicData>(
    "https://www.ag-grid.com/example-assets/small-olympic-winners.json",
  );

  return (
    <AgGridProvider modules={modules}>
      <div style={containerStyle}>
        <div style={gridStyle}>
          <AgGridReact<IOlympicData>
            rowData={data}
            loading={loading}
            columnDefs={columnDefs}
            defaultColDef={defaultColDef}
            autoGroupColumnDef={autoGroupColumnDef}
            pivotMode={true}
          />
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

styles.css

.test-container {
    height: 100%;
    display: flex;
    flex-direction: column;
}

.test-header {
    font-family: Verdana, Geneva, Tahoma, sans-serif;
    font-size: 13px;
    margin-bottom: 5px;
}

interfaces.ts


export interface IOlympicData {
    athlete: string,
    age: number,
    country: string,
    year: number,
    date: string,
    sport: string,
    gold: number,
    silver: number,
    bronze: number,
    total: number
}

useFetchJson.tsx

import { useState, useEffect } from 'react';

/**
 * Fetch example Json data
 * Not recommended for production use!
 */
export const useFetchJson = <T,>(url:string, limit?: number) => {
    const [data, setData] = useState<T[]>();
    const [loading, setLoading] = useState(true);
    useEffect(() => {
        // StrictMode runs this effect twice: drop the superseded run's response rather than applying both.
        let cancelled = false;
        const fetchData = async () => {
            setLoading(true);

            // Note error handling is omitted here for brevity
            const response = await fetch(url);
            const json = await response.json();
            const data = limit ? json.slice(0, limit) : json;
            if (cancelled) {
                return;
            }
            setData(data);
            setLoading(false);
        };
        fetchData();
        return () => {
            cancelled = true;
        };
    }, [url, limit]);
    return { data, loading };
};

As pivot values are all aggregates, filtering out rows will not re-aggregate the parent, group and grand total rows. Refer to Filtering Aggregated Values for more information.

Pivot result columns inherit the properties of the value column from which they are generated. However, setting filter: true will instead default to a Number Filter in the case of a pivot result column. The Set Filter cannot be used for filtering pivot result columns.

Best Practices Copy Link

Limiting Column Generation Copy Link

When pivoting, changes in data, aggregation or pivot columns can cause the number of generated columns to scale exponentially. This can cause performance issues such as long delays in rendering, and often the resulting view would be unmanageable for the user.

To prevent this from happening, you can set the pivotMaxGeneratedColumns option. When the grid generates a number of pivot columns exceeding this value, it halts column generation, clears the view, and fires the onPivotMaxColumnsExceeded event to allow your application to intervene.

index.tsx

("use client");

import React, {
  useCallback,
  useMemo,
  useRef,
  useState,
  StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import {
  AutoGroupColumnDef,
  ClientSideRowModelModule,
  ColDef,
  ColGroupDef,
  GridApi,
  GridOptions,
  ModuleRegistry,
  SideBarDef,
  enableDevValidations,
} from "ag-grid-community";
import {
  ColumnsToolPanelModule,
  FiltersToolPanelModule,
  PivotModule,
  SideBarModule,
} from "ag-grid-enterprise";
import { IOlympicData } from "./interfaces";
import { useFetchJson } from "./useFetchJson";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

const modules = [
  ClientSideRowModelModule,
  SideBarModule,
  ColumnsToolPanelModule,
  FiltersToolPanelModule,
  PivotModule,
];

const GridExample = () => {
  const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
  const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);

  const [columnDefs, setColumnDefs] = useState<ColDef[]>([
    { field: "country", rowGroup: true, enableRowGroup: true },
    { field: "athlete", enablePivot: true },
    { field: "year", enablePivot: true },
    { field: "sport", enablePivot: true },
    { field: "gold", aggFunc: "sum" },
    { field: "silver", aggFunc: "sum" },
    { field: "bronze", aggFunc: "sum" },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      flex: 1,
      minWidth: 130,
    };
  }, []);
  const autoGroupColumnDef = useMemo<AutoGroupColumnDef>(() => {
    return {
      minWidth: 200,
    };
  }, []);

  const { data, loading } = useFetchJson<IOlympicData>(
    "https://www.ag-grid.com/example-assets/olympic-winners.json",
  );

  const onPivotMaxColumnsExceeded = useCallback(() => {
    console.warn(
      "The limit of 1000 generated columns has been exceeded. Either remove pivot or aggregations from some columns or increase the limit.",
    );
  }, []);

  return (
    <AgGridProvider modules={modules}>
      <div style={containerStyle}>
        <div style={gridStyle}>
          <AgGridReact<IOlympicData>
            rowData={data}
            loading={loading}
            columnDefs={columnDefs}
            defaultColDef={defaultColDef}
            autoGroupColumnDef={autoGroupColumnDef}
            pivotMode={true}
            sideBar={"columns"}
            pivotMaxGeneratedColumns={1000}
            onPivotMaxColumnsExceeded={onPivotMaxColumnsExceeded}
          />
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

interfaces.ts


export interface IOlympicData {
    athlete: string,
    age: number,
    country: string,
    year: number,
    date: string,
    sport: string,
    gold: number,
    silver: number,
    bronze: number,
    total: number
}

useFetchJson.tsx

import { useState, useEffect } from 'react';

/**
 * Fetch example Json data
 * Not recommended for production use!
 */
export const useFetchJson = <T,>(url:string, limit?: number) => {
    const [data, setData] = useState<T[]>();
    const [loading, setLoading] = useState(true);
    useEffect(() => {
        // StrictMode runs this effect twice: drop the superseded run's response rather than applying both.
        let cancelled = false;
        const fetchData = async () => {
            setLoading(true);

            // Note error handling is omitted here for brevity
            const response = await fetch(url);
            const json = await response.json();
            const data = limit ? json.slice(0, limit) : json;
            if (cancelled) {
                return;
            }
            setData(data);
            setLoading(false);
        };
        fetchData();
        return () => {
            cancelled = true;
        };
    }, [url, limit]);
    return { data, loading };
};

In the example above, pivoting by the Athlete column will instead trigger the pivotMaxColumnsExceeded event, which logs an error in the browser console.

The example above demonstrates the following configuration:

const pivotMode = true;
const pivotMaxGeneratedColumns = 1000;
const onPivotMaxColumnsExceeded = () => {
    console.error(
        'The limit of 1000 generated columns has been exceeded. Either remove pivot or aggregations from some columns or increase the limit.'
    );
};

<AgGridReact
    pivotMode={pivotMode}
    pivotMaxGeneratedColumns={pivotMaxGeneratedColumns}
    onPivotMaxColumnsExceeded={onPivotMaxColumnsExceeded}
/>

Getting Started

AI Features

Layout & Styling

Charting

Core Features

Advanced Features

React Data GridPivot Result Columns

Column Definitions Copy Link

Filtering Copy Link

Best Practices Copy Link

Limiting Column Generation Copy Link