Syncfusion React Pivot Table

Implementing PivotView in React

The Syncfusion React PivotView component enables powerful data analysis and reporting capabilities. It allows users to organize, analyze, and summarize multidimensional data through interactive pivot tables with features like grouping, filtering, aggregation, drill-down analysis, and export functionality.

⚠️ Important: Always verify API class names, properties, and method signatures by consulting the reference files in this skill (references/*.md). These are maintained with verified, working examples. Do not assume API details from other sources.

⚠️ Security Warning: Data Source Validation

CRITICAL SECURITY NOTICE: When implementing pivot tables, always use trusted data sources. Never fetch or bind data from untrusted or user-provided URLs without proper validation and sanitization.

Security Best Practices:

1. Use Local Data: Prefer local, in-memory data sources for maximum security 2. Validate Remote Sources: Only connect to authenticated and authorized API endpoints under your control 3. Sanitize User Input: Never allow users to specify arbitrary URLs or data sources 4. Implement Authentication: Always use authentication headers and secure API endpoints 5. Content Validation: Validate and sanitize all data received from external sources before binding 6. Use HTTPS: Always use HTTPS for remote data connections 7. Rate Limiting: Implement rate limiting on API endpoints to prevent abuse

Security Risks:

• Indirect Prompt Injection: Untrusted third-party data can contain malicious content that manipulates AI agent behavior
• Data Exfiltration: Malicious data sources could attempt to extract sensitive information
• Code Injection: Untrusted data may contain scripts or harmful content

Recommended Approach:

✅ DO: Use controlled, authenticated backend APIs ✅ DO: Implement server-side data validation ✅ DO: Use environment variables for API endpoints ✅ DO: Whitelist allowed data sources

❌ DON'T: Accept user-provided URLs ❌ DON'T: Bind to public, untrusted endpoints ❌ DON'T: Skip data validation and sanitization ❌ DON'T: Use HTTP for sensitive data

When to Use This Skill

Use this skill when you need to:

• Create interactive pivot tables for data analysis
• Set up data aggregation and summarization
• Build dynamic reporting dashboards
• Configure row/column grouping with hierarchies
• Implement data filtering and sorting
• Add drill-down/drill-through capabilities
• Export reports to Excel or PDF
• Optimize performance for large datasets
• Style and format pivot table display
• Persist pivot table state across sessions

Navigation Guide

Setup

📄 Read: references/getting-started.md

• Installation, package setup, basic component creation, CSS imports, NextJS integration

📄 Read (optional): references/nextjs-integration.md

• Next.js App Router and Pages Router setup, dynamic imports, SSR considerations, API routes, deployment

Data Management

📄 Read: references/data-binding.md

• JSON data binding, CSV data binding, remote data binding, field mapping, DataManager setup

📄 Read: references/connecting-to-databases.md

• Database connections, Web API setup, SQL Server, MySQL, PostgreSQL, MongoDB, Oracle, Elasticsearch, Snowflake

📄 Read: references/connecting-to-data-source.md

• OLAP connections, server-side engine configuration, real-time data updates

Visualization & Interaction

📄 Read: references/pivot-chart.md

• Integrating pivot charts, chart types, visualization options, chart customization

📄 Read: references/classic-layout.md

• Classic layout configuration, layout switching, UI control options

📄 Read: references/drill.md

• Drill-down and drill-through functionality, editing, navigation controls, event handling

Data Organization & Display

📄 Read: references/field-list.md

• In-built field list (popup), stand-alone field list (fixed), field list modes, UI interactions, configuration options

📄 Read: references/grouping-bar.md

• Grouping bar configuration, drag-and-drop operations, filter/sort/remove icons, fields panel, value type icon, exclusion options

📄 Read: references/data-shaping.md

• Aggregation functions, calculated fields (defining and editing), summary customization options

📄 Read: references/aggregation.md

• 25+ aggregation types (Sum, Average, Count, etc.), advanced aggregations, base field configuration, runtime modification

📄 Read: references/calculated-field.md

• Interactive calculated field dialog, formula syntax and operators, code-based methods, format settings

📄 Read: references/grouping.md

• Data grouping feature, number grouping with ranges, date grouping with hierarchies, programmatic configuration, ungrouping

📄 Read: references/data-formatting.md

• Number formatting, conditional formatting, cell styling, tooltip customization

📄 Read: references/grid-customization.md

• Cell templates, row heights, column widths, text alignment, column resizing and reordering, dimension configuration

📄 Read: references/row-and-column.md

• Cell template customization, dimension configuration, row height, column width, resizing, reordering, text alignment

📄 Read: references/filtering-and-sorting.md

• Member filtering, label filtering, value filtering, sorting operations, filter customization

📄 Read separately if needed:

• references/member-filtering.md - Select/exclude specific members, interactive filter dialogs
• references/label-filtering.md - Filter by text patterns, date ranges, numeric ranges
• references/value-filtering.md - Filter by aggregated values, Top/Bottom N, percentage filtering
• references/value-sorting.md - Sort by measure values, ranking analysis, multi-level sorting

📄 Read: references/show-hide-totals.md

• Grand totals display, group totals configuration, subtotal calculation options

📄 Read: references/state-persistence.md

• Save and load pivot table state, persistence configuration, state management patterns

📄 Read: references/toolbar.md

• Toolbar controls, button customization, toolbar events and actions

📄 Read: references/tooltip.md

• Tooltip configuration, custom tooltip templates, tooltip formatting

📄 Read: references/hyperlink.md

• Hyperlink configuration, cell linking, navigation setup, link customization

Navigation & Analysis

📄 Read: references/drill.md

• Drill-down and drill-through functionality, hierarchical data exploration, editing capabilities

📄 Read separately if needed:

• references/drill-down.md - Drill-down specific features
• references/drill-through.md - Drill-through and raw data exploration

Export & Output

📄 Read: references/export.md

• Excel export, PDF export, export configurations, custom export handlers

📄 Read separately if needed:

• references/excel-export.md - Excel export details
• references/pdf-export.md - PDF export details

📄 Read: references/print.md

• Pivot table printing, chart printing, print configurations, browser compatibility

Advanced & Performance

📄 Read: references/defer-update.md

• Deferred layout updates, Apply button, batch field operations, performance optimization for large datasets

📄 Read: references/performance.md

• Virtual scrolling, paging configuration, performance best practices, data compression

📄 Read separately if needed:

• references/virtual-scrolling.md - Efficient rendering of 100K+ rows, virtual scroll settings, responsive optimization

Quick Start

/* App.css */
@import '../node_modules/@syncfusion/ej2-base/styles/tailwind3.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/tailwind3.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/tailwind3.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/tailwind3.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/tailwind3.css';
@import '../node_modules/@syncfusion/ej2-lists/styles/tailwind3.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/tailwind3.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/tailwind3.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/tailwind3.css';
@import '../node_modules/@syncfusion/ej2-calendars/styles/tailwind3.css';
@import '../node_modules/@syncfusion/ej2-react-pivotview/styles/tailwind3.css';

import { PivotViewComponent, Inject, GroupingBar, FieldList, IDataSet } from '@syncfusion/ej2-react-pivotview';
import './App.css';

const pivotData: IDataSet[] = [
  { Country: 'USA', Product: 'Laptops', Sales: 5000, Year: 2020 },
  { Country: 'USA', Product: 'Mobiles', Sales: 3000, Year: 2020 },
  { Country: 'Canada', Product: 'Laptops', Sales: 2500, Year: 2020 },
  { Country: 'Canada', Product: 'Mobiles', Sales: 1500, Year: 2020 }
];

function cellTemplate(props: any): JSX.Element {
  return (
    <span style={{ fontWeight: 'bold', color: '#333' }}>
      {props.cellInfo?.value}
    </span>
  );
}

function App() {
  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: pivotData as IDataSet[],
    rows: [{ name: 'Country' }],
    columns: [{ name: 'Product' }],
    values: [{ name: 'Sales', caption: 'Total Sales' }],
    showGrandTotals: true
  };

  return (
    <PivotViewComponent
      id="pivotview"
      dataSourceSettings={dataSourceSettings}
      cellTemplate={cellTemplate}
      showGroupingBar={true}
      showFieldList={true}
      height={350}
    >
      <Inject services={[GroupingBar, FieldList]} />
    </PivotViewComponent>
  );
}

export default App;

Common Patterns

Pattern 1: Basic Pivot Table with Row and Column Fields

const dataSourceSettings: DataSourceSettingsModel = {
  dataSource: data as IDataSet[],
  rows: [{ name: 'Country' }, { name: 'Region' }],
  columns: [{ name: 'Year' }, { name: 'Quarter' }],
  values: [{ name: 'Sales', caption: 'Total Sales' }]
};

Pattern 2: Adding Filters and Aggregation

const dataSourceSettings: DataSourceSettingsModel = {
  dataSource: data as IDataSet[],
  rows: [{ name: 'Country' }],
  columns: [{ name: 'Product' }],
  values: [
    { name: 'Sales', type: 'Sum' },
    { name: 'Quantity', type: 'Avg' }
  ],
  filters: [{ name: 'Year' }]
};

Pattern 3: Calculated Fields

import { PivotViewComponent, Inject, CalculatedField, FieldList } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

const dataSourceSettings: DataSourceSettingsModel = {
  dataSource: data as IDataSet[],
  rows: [{ name: 'Country' }],
  columns: [{ name: 'Product' }],
  values: [
    { name: 'Sold', caption: 'Units Sold' },
    { name: 'Amount', caption: 'Amount' },
    { name: 'Total', caption: 'Total', type: 'CalculatedField' }  // ← Must add to values
  ],
  calculatedFieldSettings: [
    {
      name: 'Total',
      formula: '"Sum(Amount)"+"Sum(Sold)"'  // ← Formula with aggregation functions
    }
  ]
};

Pattern 4: Cell Template with Conditional Formatting

function cellTemplate(props: any): JSX.Element {
  const value = props.cellInfo?.value;
  const isTotal = props.cellInfo?.isGrandTotal || props.cellInfo?.isDeserialized;
  
  const getStyle = () => {
    const numValue = parseFloat(value);
    if (numValue > 4000) return { color: '#4CAF50', fontWeight: 'bold' }; // Green for high values
    if (numValue < 1000) return { color: '#f44336', fontWeight: 'bold' }; // Red for low values
    return { color: '#333' };
  };

  return (
    <span style={getStyle()}>
      {isTotal ? <strong>{value}</strong> : value}
    </span>
  );
}

const dataSourceSettings: DataSourceSettingsModel = {
  dataSource: data as IDataSet[],
  rows: [{ name: 'Country' }],
  columns: [{ name: 'Product' }],
  values: [{ name: 'Sales' }]
};

Key Props & Configuration

GridSettings Configuration

• rowHeight: Specifies row height in pixels
• columnWidth: Sets default column width
• allowResizing: Enables column/row resizing
• allowReordering: Allows drag-and-drop field reordering
• allowTextWrap: Enables text wrapping in cells
• columnRender: Event handler for column customization (e.g., text alignment)

Related Skills

• Syncfusion React Grid - For general data grid functionality
• React Data Binding - For data source management patterns

Aggregation in Syncfusion React Pivot Table

Overview

Aggregation is a powerful feature in the Syncfusion React Pivot Table component that allows end users to perform calculations on groups of values, specifically for value fields placed in the value axis. This feature is applicable only for relational data sources (not for OLAP data sources).

By default, values in the pivot table are combined by summing them. However, the component supports 23+ different aggregation types to provide comprehensive data analysis capabilities.

Supported Aggregation Types

The Syncfusion React Pivot Table supports the following aggregation types:

Field Type Support

Important Notes:

• Numeric fields support all aggregation types listed above, except CalculatedField.
• Non-numeric fields (string, date, datetime, boolean, etc.) support only Count and DistinctCount aggregation types.
• By default, numeric fields use Sum aggregation, while non-numeric fields use Count aggregation.

Setting Aggregation Type

Using the type Property

You can set the aggregation type for each value field using the type property in the values array within dataSourceSettings:

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    filters: [],
    drilledMembers: [{ name: 'Country', items: ['France'] }],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold', type: 'Sum' }, 
      { name: 'Amount', caption: 'Sold Amount', type: 'Avg' }
    ]
  }
  
  let pivotObj: PivotViewComponent;
  
  return (
    <PivotViewComponent  
      ref={(d: PivotViewComponent) => pivotObj = d} 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings}>
    </PivotViewComponent>
  );
}

export default App;

Using Comparison Aggregations with baseField and baseItem

For aggregation types like DifferenceFrom and PercentageOfDifferenceFrom, you can specify a base field and base item for comparison:

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    filters: [],
    drilledMembers: [{ name: 'Country', items: ['France'] }],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { 
        name: 'Sold', 
        caption: 'Units Sold', 
        type: 'DifferenceFrom', 
        baseField: 'Year', 
        baseItem: 'FY 2018' 
      }, 
      { 
        name: 'Amount', 
        caption: 'Sold Amount', 
        type: 'Sum' 
      }
    ]
  }
  
  let pivotObj: PivotViewComponent;
  
  return (
    <PivotViewComponent  
      ref={(d: PivotViewComponent) => pivotObj = d} 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings}>
    </PivotViewComponent>
  );
}

export default App;

Key Properties for Comparison Aggregations:

• `type`: Sets the aggregate type of the field (e.g., 'DifferenceFrom', 'PercentageOfDifferenceFrom')
• `baseField`: Specifies the field to use as a comparison base
• `baseItem`: Specifies the member/item within the base field to compare against

Using baseField for Percentage Aggregations

For the PercentageOfParentTotal aggregation type, use the baseField property to specify the field:

values: [
  { 
    name: 'Amount', 
    caption: 'Sold Amount', 
    type: 'PercentageOfParentTotal', 
    baseField: 'Country' 
  }
]

Modifying Aggregation at Runtime

End users can dynamically modify aggregation types through the UI in two ways:

1. Through Grouping Bar

Value fields in the grouping bar include a dropdown icon that allows users to select different aggregation types:

import { GroupingBar, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }
    ]
  }
  
  let pivotObj: PivotViewComponent;
  
  return (
    <PivotViewComponent  
      ref={(d: PivotViewComponent) => pivotObj = d} 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings} 
      showGroupingBar={true}>
      <Inject services={[GroupingBar]}/>
    </PivotViewComponent>
  );
}

export default App;

2. Through Field List

Value fields in the field list also include a dropdown icon for changing aggregation types:

import { FieldList, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }
    ]
  }
  
  let pivotObj: PivotViewComponent;
  
  return (
    <PivotViewComponent  
      ref={(d: PivotViewComponent) => pivotObj = d} 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings} 
      showFieldList={true}>
      <Inject services={[FieldList]}/>
    </PivotViewComponent>
  );
}

export default App;

Customizing Aggregation Dropdown

Showing Specific Aggregation Types

You can customize the dropdown menu to display only specific aggregation types using the aggregateTypes property:

import { GroupingBar, FieldList, Inject, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let dataSourceSettings: DataSourceSettingsModel = {
    dataSource: pivotData as IDataSet[],
    drilledMembers: [{ name: 'Country', items: ['France'] }],
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount', type: 'Sum' }
    ],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    filters: [],
  }
  
  let pivotObj: PivotViewComponent;
  let pivotAggregateTypes: any = ['DistinctCount', 'Avg', 'Product'];
  
  return (
    <PivotViewComponent  
      ref={(d: PivotViewComponent) => pivotObj = d} 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings} 
      showFieldList={true} 
      showGroupingBar={true} 
      aggregateTypes={pivotAggregateTypes}>
      <Inject services={[GroupingBar, FieldList]}/>
    </PivotViewComponent>
  );
}

export default App;

UI Customization Options

Hiding Aggregation Type from Button Text

By default, value field buttons display both the field name and aggregation type (e.g., "Sum of Units Sold"). To display only the field name:

import { IDataSet, PivotViewComponent, Inject, GroupingBar } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    filters: [],
    drilledMembers: [{ name: 'Country', items: ['France'] }],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount', type: 'Sum' }
    ],
    showAggregationOnValueField: false
  }
  
  let pivotObj: PivotViewComponent;
  
  return (
    <PivotViewComponent  
      ref={(d: PivotViewComponent) => pivotObj = d} 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings} 
      showGroupingBar={true}>
      <Inject services={[GroupingBar]}/>
    </PivotViewComponent>
  );
}

export default App;

Hiding Aggregation Type Icon from Grouping Bar

To hide the dropdown icon for changing aggregation types in the grouping bar:

import { GroupingBar, GroupingBarSettings, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let groupingSettings: GroupingBarSettings = {
    showValueTypeIcon: false
  } as GroupingBarSettings;

  let dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    filters: [],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }
    ]
  }
  
  let pivotObj: PivotViewComponent;
  
  return (
    <PivotViewComponent  
      ref={(d: PivotViewComponent) => pivotObj = d} 
      id='PivotView' 
      height={350} 
      groupingBarSettings={groupingSettings} 
      dataSourceSettings={dataSourceSettings} 
      showGroupingBar={true}>
      <Inject services={[GroupingBar]}/>
    </PivotViewComponent>
  );
}

export default App;

Note: The aggregation type icon can only be hidden in the Grouping Bar, not in the Field List.

Events

AggregateCellInfo Event

The aggregateCellInfo event triggers each time a value cell is rendered, allowing you to override cell values or skip formatting:

import { PivotViewComponent, GroupingBar, Inject, IDataSet } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let dataSourceSettings: DataSourceSettingsModel = {
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    drilledMembers: [{ name: 'Country', items: ['France'] }],
    formatSettings: [{ name: 'Amount', format: 'C2' }],
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }
    ],
    rows: [{ name: 'Country' }, { name: 'Products' }],
  };
  
  function aggregateCell(args: any) {
    // Override cell value or skip formatting
    args.skipFormatting = true;
  }
  
  let pivotObj: PivotViewComponent;
  
  return (
    <PivotViewComponent 
      id='PivotView' 
      ref={(d: PivotViewComponent) => pivotObj = d} 
      height={350} 
      aggregateCellInfo={aggregateCell.bind(this)} 
      dataSourceSettings={dataSourceSettings} 
      showGroupingBar={true}>
      <Inject services={[GroupingBar]} />
    </PivotViewComponent>
  );
}

export default App;

Event Parameters:

• fieldName: Current cell's field name
• row: Current cell's row value
• column: Current cell's column value
• value: Value of current cell
• cellSets: Raw data for the aggregated value cell
• rowCellType: Row cell type value
• columnCellType: Column cell type value
• aggregateType: Aggregate type of the cell
• skipFormatting: Boolean property to skip formatting if applied

ActionBegin Event

Triggered when a user initiates an aggregation type change:

import { FieldList, GroupingBar, IDataSet, Inject, PivotViewComponent, PivotActionBeginEventArgs } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let dataSourceSettings: DataSourceSettingsModel = {
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }
    ],
    filters: [],
  }
  
  let pivotObj: PivotViewComponent;
  
  function actionBegin(args: PivotActionBeginEventArgs): void {
    if (args.actionName == 'Aggregate field') {
      // Cancel the action if needed
      args.cancel = true;
    }
  }
  
  return (
    <PivotViewComponent  
      ref={(d: PivotViewComponent) => pivotObj = d} 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings} 
      actionBegin={actionBegin.bind(this)} 
      showFieldList={true} 
      showGroupingBar={true}>
      <Inject services={[FieldList, GroupingBar]} />
    </PivotViewComponent>
  );
}

export default App;

Event Parameters:

• dataSourceSettings: Current data source settings
• actionName: Name of the current action (e.g., "Aggregate field")
• fieldInfo: Information about the selected value field
• cancel: Allows restricting the current action

ActionComplete Event

Triggered when an aggregation type change is completed:

import { FieldList, GroupingBar, IDataSet, Inject, PivotViewComponent, PivotActionCompleteEventArgs } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let dataSourceSettings: DataSourceSettingsModel = {
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }
    ],
    filters: [],
  }
  
  let pivotObj: PivotViewComponent;

  function actionComplete(args: PivotActionCompleteEventArgs): void {
    if (args.actionName == 'Field aggregated') {
      // Handle post-aggregation logic
    }
  }
  
  return (
    <PivotViewComponent  
      ref={(d: PivotViewComponent) => pivotObj = d} 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings} 
      actionComplete={actionComplete.bind(this)} 
      showFieldList={true} 
      showGroupingBar={true}>
      <Inject services={[FieldList, GroupingBar]} />
    </PivotViewComponent>
  );
}

export default App;

Event Parameters:

• dataSourceSettings: Current data source settings
• actionName: Name of completed action (e.g., "Field aggregated")
• fieldInfo: Information about the selected value field
• actionInfo: Unique information about the current UI action

ActionFailure Event

Triggered when an aggregation action fails:

import { FieldList, GroupingBar, IDataSet, Inject, PivotViewComponent, PivotActionFailureEventArgs } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  let dataSourceSettings: DataSourceSettingsModel = {
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }
    ],
    filters: [],
  }
  
  let pivotObj: PivotViewComponent;
  
  function actionFailure(args: PivotActionFailureEventArgs): void {
    if (args.actionName == 'Aggregate field') {
      // Handle failure
      console.error('Aggregation failed:', args.errorInfo);
    }
  }
  
  return (
    <PivotViewComponent  
      ref={(d: PivotViewComponent) => pivotObj = d} 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings} 
      actionFailure={actionFailure.bind(this)} 
      showFieldList={true} 
      showGroupingBar={true}>
      <Inject services={[FieldList, GroupingBar]} />
    </PivotViewComponent>
  );
}

export default App;

Event Parameters:

• actionName: Name of the failed action
• errorInfo: Detailed error information

Best Practices

Choosing the Right Aggregation Type

1. Basic Analysis:

• Use Sum for totaling values (sales, quantities, etc.)
• Use Count to count records or occurrences
• Use Avg for average calculations (average sales, average scores, etc.)

2. Statistical Analysis:

• Use Min and Max for range analysis
• Use Median for central tendency with outliers
• Use PopulationStDev or SampleStDev for variability analysis
• Use PopulationVar or SampleVar for variance calculations

3. Comparative Analysis:

• Use DifferenceFrom to compare against a baseline value
• Use PercentageOfDifferenceFrom for percentage change analysis
• Use RunningTotals for cumulative analysis

4. Percentage Analysis:

• Use PercentageOfGrandTotal for overall contribution
• Use PercentageOfColumnTotal for column-wise contribution
• Use PercentageOfRowTotal for row-wise contribution
• Use PercentageOfParentTotal for hierarchical percentage analysis

5. Unique Values:

• Use DistinctCount to count unique values
• Use Product for multiplicative calculations

Performance Considerations

1. Default Aggregations: Let the component use default aggregations (Sum for numeric, Count for non-numeric) when possible for optimal performance.

2. Complex Aggregations: Aggregations like DifferenceFrom and percentage-based aggregations require additional calculations. Use them judiciously on large datasets.

3. Multiple Aggregations: When displaying multiple aggregations of the same field, consider the performance impact on large datasets.

Field Type Compatibility

Always ensure you're using aggregation types compatible with your field types:

• Numeric fields only: Sum, Product, Min, Max, Avg, Median, Index, statistical aggregations, running totals, and all comparison/percentage aggregations
• All field types: Count, DistinctCount
• Special case: CalculatedField (for custom calculations)

User Experience

1. Limit Aggregation Options: Use the aggregateTypes property to show only relevant aggregation types for your use case.

2. Hide Unnecessary UI Elements: Use showAggregationOnValueField and showValueTypeIcon properties to simplify the interface when aggregation changes aren't needed.

3. Provide Clear Labels: Use the caption property to provide user-friendly names for value fields.

4. Handle Events: Implement actionBegin, actionComplete, and actionFailure events to provide feedback and handle errors gracefully.

Calculated Field Reference

Overview

The calculated field feature enables users to create custom value fields using mathematical formulas and existing fields from their data source. Users can perform complex calculations with basic arithmetic operators and seamlessly integrate these custom fields into their pivot table for enhanced data visualization and reporting.

Enabling Calculated Field

To enable the calculated field functionality, set the `allowCalculatedField` property to true. Once enabled, a "CALCULATED FIELD" button appears in the Field List UI. Clicking this button opens the calculated field dialog, where users can create and manage custom fields using an intuitive interface.

Module Injection

To use the calculated field feature, you must inject the CalculatedField module into the pivot table:

import { CalculatedField } from '@syncfusion/ej2-react-pivotview';

<Inject services={[CalculatedField]} />

Creating Calculated Fields

Users can create calculated fields in two convenient ways:

1. Interactive Method: Using the Built-in Dialog

The built-in dialog is accessible from the Field List UI. Click the "CALCULATED FIELD" button to open the calculated field dialog, where users can create and manage custom fields using an intuitive interface.

2. Code-Based Method: Using calculatedFieldSettings Property

You can define calculated fields programmatically using the `calculatedFieldSettings` property. This approach is ideal for pre-configuring specific calculations.

Properties

name

`name`: Specifies a unique name for the calculated field.

formula

`formula`: Defines the mathematical expression using existing field names and arithmetic operators.

formatSettings

`formatSettings`: Configures the number format for displaying calculated results.

Formula Syntax

Formulas use field names and arithmetic operators. Field names must be enclosed in double quotes when referenced in formulas:

formula: '"Sum(Amount)"+"Sum(Sold)"'

Supported Operators and Functions

Basic Arithmetic Operators

• + – addition operator

  Syntax: X + Y

• - – subtraction operator

  Syntax: X - Y

• * – multiplication operator

  Syntax: X * Y

• / – division operator

  Syntax: X / Y

• ^ – power operator

  Syntax: X^2

Comparison Operators

• < - less than operator

  Syntax: X < Y

• <= – less than or equal operator

  Syntax: X <= Y

• > – greater than operator

  Syntax: X > Y

• >= – greater than or equal operator

  Syntax: X >= Y

• == – equal operator

  Syntax: X == Y

• != – not equal operator

  Syntax: X != Y

Logical Operators

• | – OR operator

  Syntax: X | Y

• & – AND operator

  Syntax: X & Y

• ? – conditional operator

  Syntax: condition ? then : else

Built-in Functions

• isNaN – function that checks if the value is not a number

  Syntax: isNaN(value)

• !isNaN – function that checks if the value is a number

  Syntax: !isNaN(value)

• abs – function that returns the absolute value of a number

  Syntax: abs(number)

• min – function that returns the minimum value

  Syntax: min(number1, number2)

• max – function that returns the maximum value

  Syntax: max(number1, number2)

Note: You can also use JavaScript Math object properties and methods directly in formulas.

Adding Calculated Fields to Values Array

Important: The calculated field feature applies only to value fields. By default, calculated fields created programmatically are added to the field list and calculated field dialog UI. To display a calculated field in the pivot table UI, it must be added to the `values` property.

values: [
  { name: 'Sold', caption: 'Units Sold' },
  { name: 'Amount', caption: 'Sold Amount' },
  { name: 'Total', caption: 'Total Units', type: 'CalculatedField' }
]

Calculated Field Dialog UI

Opening the Dialog Programmatically

You can display the calculated field dialog by calling the `createCalculatedFieldDialog` method when an external button is clicked:

function btnClick(): void {
  pivotObj.calculatedFieldModule.createCalculatedFieldDialog();
}

Dialog Operations

The calculated field dialog provides several operations:

1. Create New Calculated Field: Enter a field name, build a formula, and set format options 2. Edit Existing Field: Click the edit icon next to a calculated field to modify it 3. Rename Field: Change the name of an existing calculated field 4. Delete Field: Remove a calculated field from the configuration 5. Reuse Formulas: Drag and drop existing calculated fields into the formula section

Editing and Deleting Calculated Fields

Editing through Field List and Grouping Bar

You can easily modify existing calculated fields using the built-in edit option available in both the field list and grouping bar:

1. Locate the calculated field button in either the field list or the grouping bar 2. Click the Edit icon next to the calculated field name 3. The calculated field dialog opens, displaying the current settings 4. Make changes to the field name, formula, or format as needed 5. Click OK to apply the changes

Renaming an Existing Calculated Field

To rename a calculated field:

1. Locate the calculated field button in either the field list or the grouping bar 2. Click the Edit icon next to the calculated field name 3. The calculated field dialog opens, displaying the current field name in the text box at the top 4. Replace the existing name with your preferred name 5. Click OK to save the new name

Editing Formula

To edit an existing calculated field formula:

1. Open the calculated field dialog 2. Select the calculated field you want to edit from the list 3. Click the Edit icon next to the selected field 4. The existing formula appears in a multiline text box at the bottom of the dialog 5. Update the formula according to your requirements 6. Click OK to save your changes

The pivot table will automatically refresh to reflect the updated calculations.

Reusing an Existing Formula

To reuse an existing formula in a new calculated field:

1. Open the calculated field dialog to create a new field 2. Locate the existing calculated field whose formula you want to reuse 3. Drag the existing calculated field from the tree view 4. Drop it into the Formula section 5. The formula from the existing field is automatically added to your new calculated field 6. Modify the formula further if needed, or use it as is 7. Click OK to create the new calculated field

Complete Code Examples

Example 1: Basic Calculated Field with Field List

import { CalculatedField, FieldList, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {

  let dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    filters: [],
    drilledMembers: [{ name: 'Country', items: ['France'] }],
    formatSettings: [{ name: 'Amount', format: 'C0' }, { name: 'Total', format: 'C2' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }, 
      { name: 'Total', caption: 'Total Units', type: 'CalculatedField' }
    ],
    calculatedFieldSettings: [{ name: 'Total', formula: '"Sum(Amount)"+"Sum(Sold)"' }]
  }
  let pivotObj: PivotViewComponent;
  
  return (
    <PivotViewComponent  
      ref={ (d: PivotViewComponent) => pivotObj = d } 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings} 
      allowCalculatedField={true} 
      showFieldList={true}>
      <Inject services={[CalculatedField, FieldList]}/> 
    </PivotViewComponent>
  );
};

export default App;

Example 2: Opening Calculated Field Dialog with Button

import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { CalculatedField, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {

  let dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    filters: [],
    drilledMembers: [{ name: 'Country', items: ['France'] }],
    formatSettings: [{ name: 'Amount', format: 'C0' }, { name: 'Total', format: 'C2' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }, 
      { name: 'Total', caption: 'Total Units', type: 'CalculatedField' }
    ],
    calculatedFieldSettings: [{ name: 'Total', formula: '"Sum(Amount)"+"Sum(Sold)"' }]
  }
  let pivotObj: PivotViewComponent;
  
  return (
    <div>
      <div className="col-md-9"> 
        <PivotViewComponent  
          ref={ (d: PivotViewComponent) => pivotObj = d } 
          id='PivotView' 
          height={350} 
          dataSourceSettings={dataSourceSettings} 
          allowCalculatedField={true}>
          <Inject services={[CalculatedField]}/> 
        </PivotViewComponent>
      </div>
      <div className='col-lg-3 property-section'>
        <ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>
          Calculated Field
        </ButtonComponent>
      </div>
    </div>
  );

  function btnClick(): void {
    pivotObj.calculatedFieldModule.createCalculatedFieldDialog();
  }
};

export default App;

Example 3: Complex Formula with Math Functions

import { CalculatedField, FieldList, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {

  let dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    filters: [],
    drilledMembers: [{ name: 'Country', items: ['France'] }],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }, 
      { name: 'Total', caption: 'Total Units', type: 'CalculatedField' }
    ],
    calculatedFieldSettings: [
      { 
        name: 'Total', 
        formula: 'Math.round("Sum(Amount)") > abs("Sum(Sold)") ? min("Sum(Amount)", "Sum(Sold)") : Math.sqrt("Sum(Sold)")'
      }
    ]
  }
  let pivotObj: PivotViewComponent;
  
  return (
    <PivotViewComponent  
      ref={ (d: PivotViewComponent) => pivotObj = d } 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings} 
      allowCalculatedField={true} 
      showFieldList={true}>
      <Inject services={[CalculatedField, FieldList]}/> 
    </PivotViewComponent>
  );
};

export default App;

Format Settings for Calculated Fields

Formatting Through Code

To format calculated field values in your code, use the `formatSettings` property:

formatSettings: [
  { name: 'Amount', format: 'C0' }, 
  { name: 'Total', format: 'C2' }
]

For more information about supported number formats, refer to the number formatting documentation.

Formatting Through User Interface

To apply formatting to calculated field values via the user interface, use the built-in "Format" dropdown available in the calculated field dialog. This dropdown provides the following predefined format options:

• Standard - Displays numbers in their basic numeric form
• Currency - Displays numbers as currency values
• Percent - Displays numbers as percentage values
• Custom - Allows you to specify a custom format pattern
• None - Applies no formatting to the values

Note: By default, None is selected in the dropdown.

Applying Custom Formatting

For specific formatting requirements, select the Custom option from the "Format" dropdown. This allows you to enter custom format patterns that meet your exact display needs.

Best Practices for Formula Creation

1. Use Descriptive Names: Choose clear, meaningful names for calculated fields that indicate their purpose

2. Enclose Field Names: Always enclose field names in double quotes within formulas (e.g., "Sum(Amount)")

3. Test Formulas: Verify your formulas with sample data before deploying to production

4. Apply Formatting: Always apply appropriate format settings to ensure calculated values display correctly

5. Use Math Functions: Leverage JavaScript Math object methods for complex calculations

6. Handle Edge Cases: Consider using conditional operators and validation functions (isNaN, abs) to handle edge cases

7. Document Complex Formulas: Add comments or documentation for complex formulas to aid maintenance

8. Optimize Performance: Keep formulas simple and efficient for better performance with large datasets

9. Reuse Formulas: Use the drag-and-drop feature to reuse existing formulas and maintain consistency

10. Validate Before Saving: Use the calculatedFieldCreate event to validate formulas before they are applied

Events

calculatedFieldCreate Event

The `calculatedFieldCreate` event enables you to validate and manage calculated field details before they are applied to the pivot table. This event is triggered when the "OK" button is clicked to close the calculated field dialog.

Event Parameters:

• `calculatedField`: Contains the calculated field information (new or existing) that was entered in the dialog
• `calculatedFieldSettings`: Provides access to the current calculated field settings
• `cancel`: Boolean property to prevent the dialog changes from being applied
• `dataSourceSettings`: Contains the current data source configuration
• `fieldName`: Specifies the name of the field being created or updated

Example:

import { CalculatedField, FieldList, IDataSet, Inject, PivotViewComponent, CalculatedFieldCreateEventArgs } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {

  let dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    filters: [],
    drilledMembers: [{ name: 'Country', items: ['France'] }],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [
      { name: 'Sold', caption: 'Units Sold' }, 
      { name: 'Amount', caption: 'Sold Amount' }, 
      { name: 'Total', caption: 'Total Units', type: 'CalculatedField' }
    ],
    calculatedFieldSettings: [
      { 
        name: 'Total', 
        formula: 'Math.round("Sum(Amount)") > abs("Sum(Sold)") ? min("Sum(Amount)", "Sum(Sold)") : Math.sqrt("Sum(Sold)")'
      }
    ]
  }
  let pivotObj: PivotViewComponent;
  
  return (
    <PivotViewComponent  
      ref={ (d: PivotViewComponent) => pivotObj = d } 
      id='PivotView' 
      height={350} 
      dataSourceSettings={dataSourceSettings} 
      allowCalculatedField={true} 
      showFieldList={true} 
      calculatedFieldCreate={calculatedFieldCreate.bind(this)}>
      <Inject services={[CalculatedField, FieldList]}/> 
    </PivotViewComponent>
  );

  function calculatedFieldCreate(args: CalculatedFieldCreateEventArgs) {
    if(args.calculatedField.formatString === '') {
      args.cancel = true;
    }
  }
};

export default App;

actionBegin Event

The `actionBegin` event allows you to control and monitor calculated field operations before they are executed. This event is triggered when users interact with calculated field functionality.

Event Parameters:

• `dataSourceSettings`: Current data source configuration
• `actionName`: Identifies the specific action being performed
• `fieldInfo`: Information about the selected field (when applicable)
• `cancel`: Boolean to prevent the action from proceeding

Action Names:

Example:

function actionBegin(args: PivotActionBeginEventArgs): void {
  if (args.actionName == 'Open calculated field dialog') {
    args.cancel = true;
  }
}

actionComplete Event

The `actionComplete` event enables you to track when calculated field operations are successfully completed.

Event Parameters:

• `dataSourceSettings`: Updated data source configuration after the operation
• `actionName`: Identifies the completed action
• `fieldInfo`: Information about the selected field (when applicable)
• `actionInfo`: Detailed information about the completed action

Action Names:

Example:

function actionComplete(args: PivotActionCompleteEventArgs): void {
  if (args.actionName == 'Calculated field applied') {
    // Triggers when the calculated field is applied
  }
}

actionFailure Event

The `actionFailure` event is triggered when a UI action fails to produce the expected result.

Event Parameters:

• `actionName`: Name of the failed action
• `errorInfo`: Error information about the failed action

Action Names:

Example:

function actionFailure(args: PivotActionFailureEventArgs): void {
  if (args.actionName == 'Open calculated field dialog') {
    // Handle the failure
  }
}

Sample Data Source

export let pivotData: object[] = [
  { 'In_Stock': 34, 'Sold': 51, 'Amount': 383, 'Country': 'France', 'Product_Categories': 'Accessories', 'Products': 'Bottles and Cages', 'Order_Source': 'Retail Outlets', 'Year': 'FY 2015', 'Quarter': 'Q1' },
  { 'In_Stock': 4, 'Sold': 423, 'Amount': 3595.5, 'Country': 'France', 'Product_Categories': 'Accessories', 'Products': 'Cleaners', 'Order_Source': 'Sales Person', 'Year': 'FY 2016', 'Quarter': 'Q1' },
  { 'In_Stock': 11, 'Sold': 19, 'Amount': 85.5, 'Country': 'France', 'Product_Categories': 'Bikes', 'Products': 'Touring Bikes', 'Order_Source': 'Retail Outlets', 'Year': 'FY 2017', 'Quarter': 'Q4' },
  { 'In_Stock': 10, 'Sold': 64, 'Amount': 320, 'Country': 'France', 'Product_Categories': 'Bikes', 'Products': 'Mountain Bikes', 'Order_Source': 'Sales Person', 'Year': 'FY 2018', 'Quarter': 'Q4' },
  { 'In_Stock': 2, 'Sold': 141, 'Amount': 1692, 'Country': 'France', 'Product_Categories': 'Clothing', 'Products': 'Jerseys', 'Order_Source': 'Sales Person', 'Year': 'FY 2015', 'Quarter': 'Q1' },
  // Additional data rows...
];

Classic Layout Configuration

Overview

The Classic layout (also called Tabular layout) in Syncfusion React PivotView provides a traditional spreadsheet-like presentation where row fields are displayed side by side in separate columns rather than hierarchically nested. This layout enhances readability for complex data with multiple row hierarchies and is compatible only with relational data sources.

Key Characteristic: Fields in the row axis appear as separate columns instead of nested hierarchies, making the data easier to interpret.

Enabling Tabular/Classic Layout

To enable the classic layout, set the layout property in gridSettings to `'Tabular'`:

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

const dataSourceSettings: DataSourceSettingsModel = {
  dataSource: pivotData as IDataSet[],
  rows: [
    { name: 'Country', caption: 'Country' },
    { name: 'Region', caption: 'Region' }
  ],
  columns: [{ name: 'Year' }, { name: 'Quarter' }],
  values: [{ name: 'Sales', type: 'Sum' }]
};

const gridSettings = {
  layout: 'Tabular'  // ← Enables classic/tabular layout
};

<PivotViewComponent
  dataSourceSettings={dataSourceSettings}
  gridSettings={gridSettings}
  height={400}
/>

Layout Comparison: Tabular vs Compact

Dynamic Layout Toggle

function PivotLayoutToggle() {
  const [layout, setLayout] = React.useState('Compact');

  return (
    <>
      <button onClick={() => setLayout(layout === 'Compact' ? 'Tabular' : 'Compact')}>
        Switch to {layout === 'Compact' ? 'Tabular' : 'Compact'} Layout
      </button>
      <PivotViewComponent
        dataSourceSettings={dataSourceSettings}
        gridSettings={{ layout: layout }}
        height={400}
      />
    </>
  );
}

UI Configuration Options

Field List Visibility

// Enable field list with popup mode (built-in to PivotViewComponent)
<PivotViewComponent
  showFieldList={true}  // Shows field list as popup
/>

// OR use standalone PivotFieldListComponent with renderMode
import { PivotFieldListComponent } from '@syncfusion/ej2-react-pivotview';

<PivotFieldListComponent
  renderMode="Fixed"  // ← 'Fixed' for Standalone, 'Popup' for dialog
  dataSourceSettings={dataSourceSettings}
/>

Grouping Bar Configuration

The grouping bar allows users to dynamically drag and drop fields:

<PivotViewComponent
  showGroupingBar={true}
  // With tabular layout
  gridSettings={{ layout: 'Tabular' }}
/>

Row and Column Header Customization

const gridSettings = {
  layout: 'Tabular',        // ← Tabular layout
  rowHeight: 35,            // Adjust row height
  columnHeight: 35,         // Adjust column header height
  allowResizing: true,      // Allow column resizing
  allowSelection: true,     // Allow cell selection
  allowTextWrap: true       // Enable text wrapping
};

<PivotViewComponent 
  dataSourceSettings={dataSourceSettings}
  gridSettings={gridSettings}
/>

Complete Tabular Layout Example

import { PivotViewComponent, Inject, FieldList, GroupingBar, IDataSet } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import React from 'react';

function TabularLayoutPivot() {
  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: sampleData as IDataSet[],
    rows: [
      { name: 'Country', caption: 'Country' },
      { name: 'Region', caption: 'Region' }
    ],
    columns: [
      { name: 'Year', caption: 'Year' },
      { name: 'Quarter', caption: 'Quarter' }
    ],
    values: [
      { name: 'Sales', type: 'Sum', caption: 'Total Sales', format: 'C2' }
    ]
  };

  const gridSettings = {
    layout: 'Tabular'  // ← KEY: Enable tabular/classic layout
  };

  return (
    <PivotViewComponent
      id="tabular-layout"
      dataSourceSettings={dataSourceSettings}
      gridSettings={gridSettings}
      showGroupingBar={true}
      showFieldList={true}
      height={400}
    >
      <Inject services={[FieldList, GroupingBar]} />
    </PivotViewComponent>
  );
}

Practical Example: Sales Analysis with Tabular Layout

function SalesAnalysisPivot() {
  const gridSettings = {
    layout: 'Tabular',
    rowHeight: 30,
    columnHeight: 25,
    allowResizing: true,
    allowSelection: true
  };

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: sampleData as IDataSet[],
    rows: [
      { name: 'Product_Categories', caption: 'Category' },
      { name: 'Products', caption: 'Product' },
      { name: 'Order_Source', caption: 'Source' }
    ],
    columns: [
      { name: 'Year', caption: 'Year' },
      { name: 'Quarter', caption: 'Quarter' }
    ],
    values: [
      { name: 'Sold', type: 'Sum', caption: 'Units Sold' },
      { name: 'Amount', type: 'Sum', caption: 'Sales Amount', format: 'C0' }
    ],
    formatSettings: [
      { name: 'Amount', format: 'C0' },
      { name: 'Sold', format: 'N0' }
    ]
  };

  return (
    <PivotViewComponent
      id="tabular-layout"
      dataSourceSettings={dataSourceSettings}
      gridSettings={gridSettings}
      showGroupingBar={true}
      showFieldList={true}
      height={400}
    >
      <Inject services={[FieldList, GroupingBar]} />
    </PivotViewComponent>
  );
}

Connecting to Data Sources

⚠️ SECURITY NOTICE

All remote connections MUST use authenticated, configuration-based endpoints. Never hardcode server URLs or use untrusted data sources.

✅ Required Security Controls:

• Environment-based configuration (.env files)
• Authentication and authorization
• HTTPS/SSL for all remote connections
• Input validation and sanitization
• Enterprise-managed servers only

Table of Contents

• Server-Side Pivot Engine
• OLAP Connections

Server-Side Pivot Engine

The server-side pivot engine processes large datasets (100K+ records) on the server, sending only viewport data to the client. This approach performs operations such as aggregation, filtering, sorting, and grouping on the server, reducing network traffic and improving Pivot Table rendering performance when working with large data sets.

Overview

When to use: Large datasets (100K+ records), complex aggregations, or when you need to process data on a dedicated backend.

Benefits:

• Processes data server-side, minimizing network bandwidth usage
• Only viewport-required data is transmitted to the client
• Works efficiently with virtual scrolling and paging
• Supports all existing Pivot Table features

Quick Setup Steps

1. Download Server-Side Application: Get the ASP.NET Core-based PivotController application from GitHub 2. Install Dependencies: The Syncfusion.Pivot.Engine library automatically downloads from nuget.org 3. Run Backend: Start the PivotController application in Visual Studio (typically runs on https://localhost:44350/api/pivot/post) 4. Configure Client: Set the Pivot Table's mode to Server and point to the backend URL

Configuring Server-Side Mode

⚠️ SECURITY: Use environment variables for server URLs. See security notice at top of document.

Step 1 — Create `.env` file:

REACT_APP_PIVOT_SERVICE_URL=https://your-server.com/api/pivot/post

Step 2 — Configure with environment variable:

import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

function ServerSidePivot() {
  const dataSourceSettings: DataSourceSettingsModel = {
    url: process.env.REACT_APP_PIVOT_SERVICE_URL,  // ← Environment-based URL
    mode: 'Server',  // ← Enable server-side mode
    rows: [{ name: 'ProductID', caption: 'Product ID' }],
    columns: [{ name: 'Year', caption: 'Production Year' }],
    values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Price', caption: 'Sold Amount' }],
    formatSettings: [
      { name: 'Price', format: 'C' }
    ]
  };

  return (
    <PivotViewComponent
      id="pivot-server"
      dataSourceSettings={dataSourceSettings}
      height={500}
    />
  );
}

Supported Data Sources

The server-side Pivot Engine supports multiple data source types:

• Collection: List or IEnumerable objects
• JSON: From local *.json files or remote sources
• CSV: From local or remote CSV files
• DataTable: Direct database access
• Dynamic: Runtime-generated data structures

Each data source is processed through the server's pivot engine, which handles all aggregation and filtering operations.

Backend Configuration (ASP.NET Core)

The server requires a controller to handle pivot requests:

[ApiController]
[Route("api/pivot")]
public class PivotController : ControllerBase
{
  private readonly PivotEngine<DataSource.PivotViewData> PivotEngine = new();

  [HttpPost("post")]
  public async Task<IActionResult> Post([FromBody] PivotViewDataOption request)
  {
    try
    {
      // Load data from CSV, JSON, or database
      var data = new DataSource.PivotViewData().GetVirtualData();
      
      // Process pivot using Syncfusion engine
      var pivotResult = PivotEngine.GetData(request, data);
      
      return Ok(pivotResult);
    }
    catch (Exception ex)
    {
      return BadRequest(new { error = ex.Message });
    }
  }
}

OLAP Connections

OLAP (Online Analytical Processing) enables analysis of multidimensional data using OLAP cubes. The Pivot Table supports connections to OLAP servers like Microsoft SQL Server Analysis Services (SSAS).

Overview

When to use: Enterprise data warehouses, multidimensional analysis, complex hierarchical data structures, or when using existing OLAP cube infrastructure.

Key Characteristics:

• Connects to OLAP cubes (e.g., Adventure Works)
• Supports hierarchies, measures, and calculated members
• Enables powerful multidimensional analysis
• Provides automatic sorting and filtering on hierarchical data

Configuring OLAP Data Source

⚠️ SECURITY: Use enterprise OLAP servers with authentication. See security notice at top of document.

Step 1 — Create `.env` file:

REACT_APP_OLAP_URL=https://your-olap-server.com/olap/msmdpump.dll

Step 2 — Configure OLAP with environment variable:

import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';

function OlapPivot() {
  const dataSourceSettings: DataSourceSettingsModel = {
    catalog: 'Adventure Works DW 2008 SE',  // ← OLAP Catalog
    cube: 'Adventure Works',                 // ← OLAP Cube
    providerType: 'SSAS',                    // ← Provider type
    enableSorting: true,
    localeIdentifier: 1033,                  // ← Locale (1033 = English)
    url: process.env.REACT_APP_OLAP_URL,     // ← Environment-based URL
    rows: [
      { name: '[Customer].[Customer Geography]', caption: 'Customer Geography' }
    ],
    columns: [
      { name: '[Product].[Product Categories]', caption: 'Product Categories' },
      { name: '[Measures]', caption: 'Measures' }
    ],
    values: [
      { name: '[Measures].[Customer Count]', caption: 'Customer Count' },
      { name: '[Measures].[Internet Sales Amount]', caption: 'Internet Sales Amount' }
    ],
    filters: [
      { name: '[Date].[Fiscal]', caption: 'Date Fiscal' }
    ]
  };

  return (
    <PivotViewComponent
      id="pivot-olap"
      dataSourceSettings={dataSourceSettings}
      height={500}
      showGroupingBar={true}
    />
  );
}

OLAP Cube Elements

OLAP cubes organize data using these hierarchical structures:

• Hierarchies: Organize members in parent-child relationships (e.g., Year → Quarter → Month)
• Measures: Numeric values for analysis (e.g., Sales Amount, Quantity Sold)
• Dimensions: Collections of related hierarchies (e.g., Product, Customer, Date)
• Calculated Members: Custom measures created from other measures
• Named Sets: Predefined collections of members (e.g., Top 10 Products)

When configuring rows, columns, and values, use the unique names of these elements exactly as they appear in your OLAP cube.

Applying Formatting to OLAP Values

Format OLAP value fields using the same format codes as relational data:

const dataSourceSettings: DataSourceSettingsModel = {
  // ... OLAP configuration ...
  formatSettings: [
    { name: '[Measures].[Internet Sales Amount]', format: 'C0' },  // ← Currency
    { name: '[Measures].[Discount Pct]', format: 'P2' }            // ← Percentage
  ]
};

Common OLAP Format Codes

Backend Setup (ASP.NET Core Example)

The server-side Pivot Engine package handles data processing:

// Backend: PivotController.cs
[ApiController]
[Route("api/pivot")]
public class PivotController : ControllerBase
{
  [HttpPost("post")]
  public async Task<IActionResult> Post([FromBody] PivotViewDataOption request)
  {
    try
    {
      // Load data (CSV, JSON, database, etc.)
      var data = GetCsvData("sales-data.csv");
      
      // Process pivot using Syncfusion engine
      var helper = new PivotViewHelper();
      var pivotResult = helper.GetPivotData(request, data);
      
      return Ok(pivotResult);
    }
    catch (Exception ex)
    {
      return BadRequest(new { error = ex.Message });
    }
  }

  private List<dynamic> GetCsvData(string filePath)
  {
    var data = new List<dynamic>();
    using (var reader = new StreamReader(filePath))
    using (var csv = new CsvReader(reader))
    {
      foreach (var record in csv.GetRecords<dynamic>())
      {
        data.Add(record);
      }
    }
    return data;
  }
}

Data Sources Supported by Server Engine

• CSV: sales-data.csv
• JSON: sales-analysis.json or dynamic JSON arrays
• Database Tables: Direct SQL queries
• Collections: IEnumerable/List<T>

Performance Benefits

• 100K records: ~500ms initial load vs. 5s client-side
• Memory: Server processes data (client only stores viewport)
• Bandwidth: Only viewport data transmitted (~50-200 rows)
• Virtual Scrolling: Seamless scrolling of millions of records

Enabling Virtual Scrolling with Server Mode

const dataSourceSettings: DataSourceSettingsModel = {
  url: process.env.REACT_APP_PIVOT_SERVICE_URL,  // ← Environment-based URL
  mode: 'Server'
};

<PivotViewComponent
  dataSourceSettings={dataSourceSettings}
  enableVirtualization={true}  // ← Virtual scrolling
  height={500}
  width="100%"
/>

OLAP Connections

OLAP (Online Analytical Processing) enables analysis using pre-aggregated dimensional data from OLAP servers like SQL Server Analysis Services (SSAS).

Overview

OLAP Characteristics:

• Pre-calculated aggregations
• Hierarchical dimensions (Year → Quarter → Month)
• Optimized for read-heavy analytical queries
• Real-time multi-dimensional analysis

Connecting to SSAS OLAP Server

⚠️ SECURITY: Use enterprise OLAP servers with authentication. See security notice at top of document.

Use Syncfusion's SSAS data provider to connect to SQL Server Analysis Services:

import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';

function OlapPivot() {
  const dataSourceSettings: DataSourceSettingsModel = {
    providerType: 'SSAS',  // ← SSAS provider type
    url: process.env.REACT_APP_OLAP_URL,  // ← Environment-based URL
    catalog: 'Adventure Works DW 2012',  // ← Database/Catalog
    cube: 'Adventure Works',  // ← Cube name
    localeIdentifier: 1033,  // ← Locale identifier
    enableSorting: true,  // ← Enable sorting
    rows: [
      { name: '[Date].[Fiscal]', caption: 'Fiscal Date' }  // ← Dimension
    ],
    columns: [
      { name: '[Customer].[Customer Geography]', caption: 'Customer Geography' }  // ← Dimension
    ],
    values: [
      { name: '[Measures].[Internet Sales Amount]', caption: 'Internet Sales' }  // ← Measure
    ],
    filters: []
  };

  return (
    <PivotViewComponent
      id="olap-pivot"
      dataSourceSettings={dataSourceSettings}
      height={400}
    />
  );
}

OLAP Field Format

Dimensions use bracket notation with hierarchy:

[Date].[Fiscal]                    ← Dimension.Hierarchy
[Product].[Product Categories]     ← Dimension.Hierarchy
[Customer].[Customer Geography]    ← Dimension.Hierarchy

Measures represent aggregations:

[Measures].[Internet Sales Amount]  ← Pre-aggregated values
[Measures].[Reseller Sales Amount]  ← Pre-aggregated values

Switching Between OLAP Providers

Support for multiple OLAP servers (SSAS, Mondrian, Essbase):

// SQL Server Analysis Services (SSAS)
const dataSourceSettings: DataSourceSettingsModel = {
  providerType: 'SSAS',
  url: process.env.REACT_APP_OLAP_URL  // Environment-based URL
};

// Mondrian:
const dataSourceSettings: DataSourceSettingsModel = {
  providerType: 'Mondrian',
  url: process.env.REACT_APP_MONDRIAN_URL  // Environment-based URL
};

Practical OLAP Example

function CompleteSalesOlap() {
  const dataSourceSettings: DataSourceSettingsModel = {
    providerType: 'SSAS',
    url: process.env.REACT_APP_OLAP_URL,  // Environment-based URL
    catalog: 'Adventure Works DW 2012',
    cube: 'Adventure Works',
    localeIdentifier: 1033,
    enableSorting: true,
    rows: [
      { name: '[Date].[Fiscal].[Year]', caption: 'Year' },
      { name: '[Date].[Fiscal].[Quarter]', caption: 'Quarter' }
    ],
    columns: [
      { name: '[Product].[Product Categories].[Category]', caption: 'Product Category' }
    ],
    values: [
      { 
        name: '[Measures].[Internet Sales Amount]',
        caption: 'Internet Sales'
      },
      {
        name: '[Measures].[Reseller Sales Amount]',
        caption: 'Reseller Sales'
      }
    ],
    filters: [
      { 
        name: '[Customer].[Customer Geography]',
        caption: 'Customer Geography'
      }
    ]
  };

  return (
    <PivotViewComponent
      id="olap-sales"
      dataSourceSettings={dataSourceSettings}
      height={400}
    />
  );
}

Real-Time Updates & Refresh

Polling for Data Updates

To refresh data periodically, use the component's refresh() method:

function PivotWithPolling() {
  const pivotRef = React.useRef<PivotViewComponent>(null);

  React.useEffect(() => {
    // Poll for fresh data every 30 seconds
    const interval = setInterval(() => {
      if (pivotRef.current) {
        pivotRef.current.refresh();
      }
    }, 30000);

    return () => clearInterval(interval);
  }, []);

  return (
    <PivotViewComponent
      ref={pivotRef}
      dataSourceSettings={dataSourceSettings}
      height={400}
    />
  );
}

Manual Data Refresh

function handleDataRefresh() {
  pivotRef.current?.refresh();
}

<button onClick={handleDataRefresh}>Refresh Data</button>

); }

## Connection Management

### Error Handling

function PivotWithErrorHandling() { return ( <PivotViewComponent dataSourceSettings={dataSourceSettings} actionFailure={(args: any) => {

// Show user-friendly error message alert(Failed to load pivot data: ${args.error}); }} height={400} /> ); }

### Connection Retry Logic

async function retryConnection( url: string, maxRetries: number = 3, delayMs: number = 1000 ): Promise<any> { let lastError;

for (let i = 0; i < maxRetries; i++) { try { const response = await fetch(url, { method: 'GET', timeout: 10000 // 10 second timeout });

if (!response.ok) { throw new Error(HTTP ${response.status}: ${response.statusText}); }

return await response.json(); } catch (error) { lastError = error; console.warn(Connection attempt ${i + 1} failed, retrying in ${delayMs}ms...);

if (i < maxRetries - 1) { await new Promise(resolve => setTimeout(resolve, delayMs)); } } }

throw new Error(Failed to connect after ${maxRetries} attempts: ${lastError}); }

Connecting to Databases

⚠️ CRITICAL SECURITY NOTICE

All database connections MUST be handled through secure backend APIs. Never expose database connection strings or credentials to the client-side React application.

✅ Required Security Controls:

• Backend API with authentication/authorization
• Secure connection strings (server-side only)
• SQL injection prevention (parameterized queries)
• Input validation and sanitization
• Rate limiting and CORS configuration
• HTTPS/TLS encryption
• API key or JWT authentication

❌ NEVER:

• Connect directly to databases from React
• Expose connection strings in client code
• Accept user-provided database endpoints
• Skip authentication on API endpoints
• Use HTTP for sensitive data
• Trust client-side data without validation

Table of Contents

• Overview
• Security Best Practices
• Database Support
• Architecture Pattern
• Web API Setup
• React Configuration
• Database Connection Examples

---

Overview

Connect PivotView to enterprise databases (SQL Server, MySQL, PostgreSQL, MongoDB, etc.) using a secure Web API intermediary. This pattern ensures database credentials remain server-side and provides proper authentication, validation, and authorization layers.

---

Security Best Practices

1. Backend API Security

✅ DO: Implement authentication and authorization

[Authorize(Roles = "DataAnalyst")]
[ApiController]
[Route("api/[controller]")]
public class PivotController : ControllerBase
{
    [HttpGet]
    public IActionResult GetData()
    {
        // Verify user permissions
        if (!User.HasClaim("Permission", "ViewSalesData"))
            return Forbid();
        
        return Ok(FetchDatabaseData());
    }
}

2. Connection String Security

✅ DO: Store in secure configuration (server-side)

// appsettings.json (never commit to source control)
{
  "ConnectionStrings": {
    "SalesDB": "Server=prod-db;Database=Sales;User Id=apiuser;Password=***;"
  }
}

// Usage in controller
private readonly IConfiguration _config;
string connectionString = _config.GetConnectionString("SalesDB");

❌ DON'T: Hardcode or expose connection strings

// NEVER DO THIS - Security Risk!
string conStr = "Server=prod;User=admin;Password=secret123;";

3. SQL Injection Prevention

✅ DO: Use parameterized queries

private DataTable FetchData(string customerId)
{
    string query = "SELECT * FROM Orders WHERE CustomerId = @customerId";
    var command = new SqlCommand(query, connection);
    command.Parameters.AddWithValue("@customerId", customerId);
    // Execute command
}

❌ DON'T: Use string concatenation

// VULNERABLE TO SQL INJECTION
string query = $"SELECT * FROM Orders WHERE CustomerId = '{customerId}'";

4. React Authentication

✅ DO: Secure API calls with authentication

const fetchData = async () => {
    const token = localStorage.getItem('authToken');
    const response = await fetch(process.env.REACT_APP_API_ENDPOINT, {
        method: 'GET',
        headers: {
            'Authorization': `Bearer ${token}`,
            'Content-Type': 'application/json'
        }
    });
    
    if (!response.ok) {
        throw new Error('Unauthorized');
    }
    
    return await response.json();
};

5. Input Validation

private bool ValidateInput(string input)
{
    // Whitelist validation
    if (string.IsNullOrWhiteSpace(input) || input.Length > 100)
        return false;
    
    // Check for SQL injection patterns
    if (input.Contains("--") || input.Contains(";") || input.Contains("'"))
        return false;
    
    return true;
}

6. Error Handling

try
{
    return Ok(FetchDatabaseData());
}
catch (Exception ex)
{
    _logger.LogError(ex, "Database error");
    // Don't expose internal details to client
    return StatusCode(500, "An error occurred");
}

Database Support

The PivotView integrates with these databases via ASP.NET Core Web API:

Architecture Pattern

Step 1: ASP.NET Core Web API Service

Create a PivotController that: 1. Connects to the database using appropriate NuGet library 2. Executes SQL query to fetch data 3. Serializes result to JSON using Newtonsoft.Json 4. Returns JSON to React frontend

Step 2: React Pivot Table Configuration

Configure the url property in dataSourceSettings to point to the Web API endpoint.

Web API Setup

Generic Controller Pattern

using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using System.Data;
using YourDatabaseLibrary;

namespace MyWebService.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class PivotController : ControllerBase
    {
        private readonly IConfiguration _configuration;
        private readonly ILogger<PivotController> _logger;
        
        public PivotController(IConfiguration configuration, ILogger<PivotController> logger)
        {
            _configuration = configuration;
            _logger = logger;
        }

        [Authorize] // 🔒 SECURITY: Require authentication
        [HttpGet(Name = "GetDatabaseResult")]
        public IActionResult Get()
        {
            try
            {
                // 🔒 SECURITY: Verify user has permission
                if (!User.HasClaim("Permission", "ViewPivotData"))
                    return Forbid();
                
                var data = FetchDatabaseData();
                return Ok(JsonConvert.SerializeObject(data));
            }
            catch (Exception ex)
            {
                // 🔒 SECURITY: Log error, don't expose details
                _logger.LogError(ex, "Error fetching database data");
                return StatusCode(500, "An error occurred");
            }
        }

        private DataTable FetchDatabaseData()
        {
            // 🔒 SECURITY: Get connection string from secure configuration
            string connectionString = _configuration.GetConnectionString("YourDatabase");
            
            if (string.IsNullOrEmpty(connectionString))
                throw new InvalidOperationException("Connection string not configured");

            // 1. Create database-specific connection
            var connection = new YourDatabaseConnection(connectionString);
            connection.Open();

            // 🔒 SECURITY: Use parameterized queries to prevent SQL injection
            string query = "SELECT * FROM TableName WHERE IsActive = @isActive";
            var command = new YourDatabaseCommand(query, connection);
            command.Parameters.AddWithValue("@isActive", true);
            
            var dataAdapter = new YourDatabaseDataAdapter(command);

            // 3. Populate DataTable
            DataTable dataTable = new DataTable();
            dataAdapter.Fill(dataTable);
            connection.Close();

            return dataTable;
        }
    }
}

Required NuGet Packages

• Newtonsoft.Json - JSON serialization
• Database-specific library (above table)
• Microsoft.AspNetCore.Mvc - ASP.NET Core framework

React Configuration

Approach 1: Direct URL Binding

Directly bind the Web API endpoint to the url property within dataSourceSettings. The API returns JSON data that is automatically mapped to the pivot table fields.

import { PivotViewComponent, FieldList, Inject } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

function App() {
  // 🔒 SECURITY: Use environment variables, not hardcoded URLs
  const apiEndpoint = process.env.REACT_APP_PIVOT_API_ENDPOINT;

  const dataSourceSettings: DataSourceSettingsModel = {
    url: apiEndpoint,  // Direct URL to Web API endpoint
    expandAll: false,
    enableSorting: true,
    columns: [{ name: 'Product' }],
    rows: [
      { name: 'Country' },
      { name: 'State' }
    ],
    values: [
      { name: 'Quantity' },
      { name: 'Amount', caption: 'Sold Amount' }
    ],
    filters: [],
    formatSettings: [
      { name: 'Amount', format: 'C0' }
    ]
  };

  return (
    <PivotViewComponent
      id='PivotView'
      height={350}
      dataSourceSettings={dataSourceSettings}
      showFieldList={true}
    >
      <Inject services={[FieldList]} />
    </PivotViewComponent>
  );
}

export default App;

Approach 2: Using DataManager

Use DataManager with WebApiAdaptor to fetch data from the Web API and configure report structure separately.

import { PivotViewComponent, FieldList, Inject } from '@syncfusion/ej2-react-pivotview';
import { DataManager, WebApiAdaptor } from '@syncfusion/ej2-data';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

function App() {
  // 🔒 SECURITY: Use environment variables, not hardcoded URLs
  const apiEndpoint = process.env.REACT_APP_PIVOT_API_ENDPOINT;
  const authToken = localStorage.getItem('authToken');

  // Create DataManager for secure data fetching
  const dataSource = new DataManager({
    url: apiEndpoint,
    adaptor: new WebApiAdaptor(),
    headers: [
      { 'Authorization': `Bearer ${authToken}` },
      { 'Content-Type': 'application/json' }
    ]
  });

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: dataSource,
    expandAll: false,
    enableSorting: true,
    columns: [{ name: 'Product' }],
    rows: [
      { name: 'Country' },
      { name: 'State' }
    ],
    values: [
      { name: 'Quantity' },
      { name: 'Amount', caption: 'Sold Amount' }
    ],
    filters: [],
    formatSettings: [
      { name: 'Amount', format: 'C0' }
    ]
  };

  return (
    <PivotViewComponent
      id='PivotView'
      height={350}
      dataSourceSettings={dataSourceSettings}
      showFieldList={true}
    >
      <Inject services={[FieldList]} />
    </PivotViewComponent>
  );
}

export default App;

Comparison

Database Connection Examples

Microsoft SQL Server

private static DataTable FetchSQLResult()
{
    string connStr = @"Server=localhost;Database=MyDb;User Id=sa;Password=YourPassword;";
    SqlConnection connection = new SqlConnection(connStr);
    connection.Open();
    SqlCommand cmd = new SqlCommand("SELECT * FROM Orders", connection);
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    connection.Close();
    return dt;
}

Port: Default 1433

MySQL

private static DataTable FetchMySQLResult()
{
    string connStr = "Server=localhost;Database=mydb;Uid=myuser;Pwd=password;";
    MySqlConnection connection = new MySqlConnection(connStr);
    connection.Open();
    MySqlCommand cmd = new MySqlCommand("SELECT * FROM orders", connection);
    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    connection.Close();
    return dt;
}

Port: Default 3306

PostgreSQL

private static DataTable FetchPostgreSQLResult()
{
    string connStr = "Server=localhost;Database=mydb;User Id=myuser;Password=password;";
    NpgsqlConnection connection = new NpgsqlConnection(connStr);
    connection.Open();
    NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM tablename", connection);
    NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    connection.Close();
    return dt;
}

Port: Default 5432

MongoDB

private static List<BsonDocument> FetchMongoDbResult()
{
    string connStr = "mongodb://localhost:27017";
    MongoClient client = new MongoClient(connStr);
    IMongoDatabase db = client.GetDatabase("sample_training");
    var collection = db.GetCollection<BsonDocument>("ProductDetails");
    return collection.Find(new BsonDocument()).ToList();
}

Port: Default 27017

Oracle

private static DataTable FetchOracleResult()
{
    string connStr = "Data Source=localhost;User Id=myuser;Password=password;";
    OracleConnection connection = new OracleConnection(connStr);
    connection.Open();
    OracleCommand cmd = new OracleCommand("SELECT * FROM EMPLOYEES", connection);
    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    connection.Close();
    return dt;
}

Port: Default 1521

Elasticsearch

private static object FetchElasticsearchData()
{
    var uri = new Uri("http://localhost:9200");
    var settings = new ConnectionSettings(uri);
    var client = new ElasticClient(settings);
    var response = client.Search<object>(s => s
        .Index("product")
        .Size(1000)
    );
    return response.Documents;
}

Port: Default 9200

Snowflake

private static DataTable FetchSnowflakeResult()
{
    using (var connection = new SnowflakeDbConnection())
    {
        connection.ConnectionString = 
            "account=myaccount;user=myuser;password=password;db=mydb;schema=public;";
        connection.Open();
        var adapter = new SnowflakeDbDataAdapter("SELECT * FROM MY_TABLE", connection);
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        return dt;
    }
}

Connection String Anatomy

Format: Server=host;Port=port;Database=dbname;User Id=user;Password=pwd;

Common Parameters:

• Server/Host: Database server address
• Port: Port number (varies by database)
• Database: Database name
• User Id/Uid: Username
• Password/Pwd: Password

Testing the Connection

1. Run Web API application 2. Access endpoint in browser: https://localhost:PORT/Pivot 3. Verify JSON data displays correctly 4. Update React url to match running port

Error Handling

Add error handling to PivotController:

[HttpGet]
public object Get()
{
    try
    {
        return JsonConvert.SerializeObject(FetchDatabaseData());
    }
    catch (Exception ex)
    {
        return new { error = ex.Message };
    }
}

CORS Configuration

Enable CORS in Startup.cs for React frontend:

services.AddCors(options =>
{
    options.AddPolicy("AllowReact",
        builder => builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader()
    );
});

app.UseCors("AllowReact");

Data Binding in PivotView

⚠️ SECURITY NOTICE

CRITICAL: This documentation contains examples for connecting to remote data sources. Always use trusted, authenticated data sources only. Never bind to untrusted URLs, user-provided endpoints, or public APIs without proper validation and security controls. See the Security Best Practices section below.

Table of Contents

• Security Best Practices
• JSON Data Binding
• CSV Data Binding
• Remote Data Binding
• Field Mapping

---

Security Best Practices

Critical Security Guidelines

When implementing data binding for pivot tables, follow these essential security practices:

1. Use Trusted Data Sources Only

✅ RECOMMENDED: Local in-memory data

// Safe: Local data array
const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: localDataArray as IDataSet[],
    rows: [{ name: 'Country' }],
    values: [{ name: 'Amount', type: 'Sum' }]
};

❌ AVOID: Untrusted remote endpoints

// UNSAFE: Never use arbitrary or user-provided URLs
url: userProvidedUrl  // Security risk!

2. Implement Authentication for Remote Sources

If you must use remote data, always use authenticated endpoints:

import { DataManager, WebApiAdaptor } from '@syncfusion/ej2-data';

// Use authenticated endpoints with proper headers
const dataSource = new DataManager({
    url: process.env.REACT_APP_API_ENDPOINT,  // Use environment variables
    adaptor: new WebApiAdaptor(),
    headers: [
        { 'Authorization': `Bearer ${getAuthToken()}` },
        { 'X-API-Key': process.env.REACT_APP_API_KEY }
    ]
});

3. Validate and Sanitize Data

Always validate data received from external sources:

const fetchData = async () => {
    const response = await fetch(trustedEndpoint, {
        headers: { 'Authorization': `Bearer ${token}` }
    });
    const data = await response.json();
    
    // Validate data structure
    if (!isValidDataStructure(data)) {
        console.error('Invalid data structure received');
        return;
    }
    
    // Sanitize data before binding
    const sanitizedData = sanitizeData(data);
    setDataSourceSettings({ dataSource: sanitizedData });
};

4. Use Environment Variables

Store API endpoints in environment files, never hardcode:

# .env
REACT_APP_API_ENDPOINT=https://your-trusted-api.com/data
REACT_APP_API_KEY=your-api-key

// Use in component
const apiEndpoint = process.env.REACT_APP_API_ENDPOINT;

Security Risks to Avoid

---

JSON Data Binding

The Pivot Table supports JSON data binding. JSON is the default data type, so you can bind JSON data without explicitly setting the type property in dataSourceSettings.

Binding JSON Data Locally

You can bind local JSON data by assigning a local variable to the dataSource property in dataSourceSettings:

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  const dataSourceSettings: DataSourceSettingsModel = {
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    filters: [],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
  };

  return (
    <PivotViewComponent
      id='PivotView'
      height={350}
      dataSourceSettings={dataSourceSettings}
    />
  );
}

export default App;

JSON Data with DataManager

Using DataManager with JsonAdaptor is optional for local JSON but provides flexibility and consistency:

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { DataManager, JsonAdaptor } from '@syncfusion/ej2-data';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  const dataSource: DataManager = new DataManager({
    json: pivotData,
    adaptor: new JsonAdaptor()
  });

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: dataSource,
    columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
    expandAll: false,
    filters: [],
    formatSettings: [{ name: 'Amount', format: 'C0' }],
    rows: [{ name: 'Country' }, { name: 'Products' }],
    values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
  };

  return (
    <PivotViewComponent
      id='PivotView'
      height={350}
      dataSourceSettings={dataSourceSettings}
    />
  );
}

export default App;

Loading JSON from File

Load JSON data from a local *.json file using the Uploader component:

import { Uploader } from '@syncfusion/ej2-inputs';
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';

function App() {
  const [dataSourceSettings, setDataSourceSettings] = React.useState({
    dataSource: [] as IDataSet[],
    columns: [{ name: 'Year' }],
    rows: [{ name: 'Country' }],
    values: [{ name: 'Amount' }]
  } as DataSourceSettingsModel);

  const handleFileChange = (event: Event) => {
    const input = event.target as HTMLInputElement;
    if (input.files && input.files[0]) {
      const reader = new FileReader();
      reader.onload = (e) => {
        try {
          const result = JSON.parse(e.target?.result as string);
          setDataSourceSettings(prev => ({
            ...prev,
            dataSource: result as IDataSet[]
          }));
        } catch (error) {
          console.error('Invalid JSON file');
        }
      };
      reader.readAsText(input.files[0]);
    }
  };

  return (
    <div>
      <input type="file" accept=".json" id="fileupload" onChange={handleFileChange} />
      <PivotViewComponent
        id='PivotView'
        height={350}
        dataSourceSettings={dataSourceSettings}
      />
    </div>
  );
}

export default App;

JSON Data Structure Guidelines

Ensure your JSON data follows the proper structure for optimal Pivot Table functionality:

• Flat object array: Each element represents one record (no nested objects)
• Field naming: Field names must exactly match those used in rows, columns, values, and filters configuration
• Type consistency:
• Numeric fields should be numbers, not strings
• Dates should be ISO format strings (YYYY-MM-DD)
• Field values should not have leading/trailing whitespace
• Required fields: Include all fields referenced in the pivot configuration

// ✅ Correct JSON format
const correctData = [
  { 
    Country: 'USA',            // string field for grouping
    Year: 2020,                // number for sorting
    Sales: 5000,               // number for aggregation
    Date: '2020-01-15'         // ISO date string
  }
];

// ❌ Avoid: nested objects and string numbers
const incorrectData = [
  {
    Location: { Country: 'USA' },  // ← Nested objects won't work
    Year: '2020',                   // ← String number causes issues
    Sales: '5000'                   // ← String number breaks aggregation
  }
];

Binding JSON from Remote URL

To bind remote JSON data, set the endpoint URL in the url property of dataSourceSettings:

const dataSourceSettings: DataSourceSettingsModel = {
  url: 'https://cdn.syncfusion.com/data/sales-analysis.json',
  expandAll: false,
  rows: [
    { name: 'EnerType', caption: 'Energy Type' }
  ],
  columns: [
    { name: 'EneSource', caption: 'Energy Source' }
  ],
  values: [
    { name: 'PowUnits', caption: 'Units (GWh)' },
    { name: 'ProCost', caption: 'Cost (MM)' }
  ]
};

CSV Data Binding

CSV (Comma-Separated Values) format is compact and reduces bandwidth usage. To use CSV, set the type property to CSV in dataSourceSettings.

CSV format uses approximately half the size of JSON, reducing bandwidth when transferring data.

Binding Local CSV Data

Convert CSV data into a string array and assign it to the dataSource property:

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { isNullOrUndefined } from '@syncfusion/ej2-base';
import * as React from 'react';
import { csvdata } from './datasource';

function App() {
  const getCSVData = () => {
    const dataSource = [];
    const jsonObject = csvdata.split(/\r?\n|\r/);
    for (let i = 0; i < jsonObject.length; i++) {
      if (!isNullOrUndefined(jsonObject[i]) && jsonObject[i] !== '') {
        dataSource.push(jsonObject[i].split(','));
      }
    }
    return dataSource;
  };

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: getCSVData(),
    type: 'CSV',
    expandAll: false,
    columns: [{ name: 'Item Type' }, { name: 'Sales Channel' }],
    filters: [],
    formatSettings: [{ name: 'Total Cost', format: 'C0' }, { name: 'Total Revenue', format: 'C0' }],
    rows: [{ name: 'Region' }, { name: 'Country' }],
    values: [{ name: 'Total Cost' }, { name: 'Total Revenue' }]
  };

  return (
    <PivotViewComponent
      id='PivotView'
      height={350}
      dataSourceSettings={dataSourceSettings}
    />
  );
}

export default App;

Loading CSV from File

Load CSV data from a local *.csv file:

import { Uploader } from '@syncfusion/ej2-inputs';
import { PivotViewComponent, FieldList, Inject } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';

function App() {
  const pivotRef = React.useRef<PivotViewComponent>(null);
  const inputRef = React.useRef<HTMLInputElement>(null);

  const ondataBound = () => {
    if (inputRef.current) {
      const uploader = new Uploader({});
      const input = document.querySelector('input[type="file"]') as HTMLInputElement;
      input?.addEventListener('change', loadCSV);
    }
  };

  const loadCSV = () => {
    const reader = new FileReader();
    const input = inputRef.current;
    
    reader.onload = function () {
      const result: string[][] = [];
      const readerResult: string[] = (reader.result as string).split(/\r?\n|\r/);
      
      result.push(readerResult[0].split(',').map(e => e.replace(/ /g, '').replace(/^\"(.+)\"$/, "$1")));
      
      for (let i = 1; i < readerResult.length; i++) {
        if (readerResult[i] !== '') {
          result.push(readerResult[i].split(','));
        }
      }

      if (pivotRef.current) {
        pivotRef.current.dataSourceSettings = {
          dataSource: result,
          type: 'CSV'
        };
      }
    };
    
    if (input?.files?.[0]) {
      reader.readAsText(input.files[0]);
      input.value = '';
    }
  };

  return (
    <div>
      <input type="file" ref={inputRef} id="fileupload" />
      <PivotViewComponent
        ref={pivotRef}
        id='PivotView'
        height={350}
        showFieldList={true}
        dataBound={ondataBound}
      >
        <Inject services={[FieldList]} />
      </PivotViewComponent>
    </div>
  );
}

export default App;

Remote CSV Data Binding

To bind remote CSV data, set the endpoint URL in the url property with type: 'CSV':

const dataSourceSettings: DataSourceSettingsModel = {
  url: 'https://bi.syncfusion.com/productservice/api/sales',
  type: 'CSV',
  expandAll: false,
  enableSorting: true,
  formatSettings: [{ name: 'Total Cost', format: 'C0' }, { name: 'Total Revenue', format: 'C0' }],
  rows: [
    { name: 'Region' },
    { name: 'Country' }
  ],
  columns: [
    { name: 'Item Type' },
    { name: 'Sales Channel' }
  ],
  values: [
    { name: 'Total Cost' },
    { name: 'Total Revenue' }
  ]
};

Remote Data Binding

⚠️ SECURITY CRITICAL: Remote data binding should only be used with trusted, authenticated services under your control. All examples below assume you are connecting to your own authenticated backend services.

Remote data binding allows you to connect the Pivot Table to data sources hosted on remote servers through web services, databases, and other external sources.

OData Service Binding

⚠️ Use only with trusted OData services that you control and authenticate.

OData (Open Data Protocol) provides a standard way to create and consume data APIs. Use DataManager with ODataAdaptor for OData v3:

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { DataManager, ODataAdaptor } from '@syncfusion/ej2-data';
import * as React from 'react';

function App() {
  // ⚠️ SECURITY: Only connect to your own authenticated OData services
  // Use environment variables for endpoints
  const dataSource: DataManager = new DataManager({
    url: process.env.REACT_APP_ODATA_ENDPOINT,  // Use environment config
    adaptor: new ODataAdaptor(),
    headers: [
      { 'Authorization': `Bearer ${getAuthToken()}` }
    ],
    crossDomain: true
  });

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: dataSource,
    expandAll: true,
    filters: [],
    columns: [{ name: 'OrderDate' }, { name: 'ShipCity' }],
    rows: [{ name: 'OrderID' }, { name: 'CustomerID' }],
    values: [{ name: 'Freight' }]
  };

  return (
    <PivotViewComponent
      id='PivotView'
      height={350}
      dataSourceSettings={dataSourceSettings}
    />
  );
}

function getAuthToken(): string {
  return localStorage.getItem('authToken') || '';
}

export default App;

OData V4 Service Binding

OData V4 provides enhanced query capabilities and improved performance. Use ODataV4Adaptor:

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { DataManager, ODataV4Adaptor } from '@syncfusion/ej2-data';
import * as React from 'react';

function App() {
  const dataSource: DataManager = new DataManager({
    url: 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders/?$top=7',
    adaptor: new ODataV4Adaptor(),
    crossDomain: true
  });

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: dataSource,
    expandAll: true,
    filters: [],
    columns: [{ name: 'OrderDate' }, { name: 'ShipCity' }],
    rows: [{ name: 'OrderID' }, { name: 'CustomerID' }],
    values: [{ name: 'Freight' }]
  };

  return (
    <PivotViewComponent
      id='PivotView'
      height={350}
      dataSourceSettings={dataSourceSettings}
    />
  );
}

export default App;

Web API Binding

Connect the Pivot Table to RESTful web services using WebApiAdaptor:

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { DataManager, WebApiAdaptor } from '@syncfusion/ej2-data';
import * as React from 'react';

function App() {
  const dataSource: DataManager = new DataManager({
    url: 'https://bi.syncfusion.com/northwindservice/api/orders',
    adaptor: new WebApiAdaptor(),
    crossDomain: true
  });

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: dataSource,
    expandAll: true,
    filters: [],
    columns: [{ name: 'ProductName', caption: 'Product Name' }],
    rows: [{ name: 'ShipCountry', caption: 'Ship Country' }, { name: 'ShipCity', caption: 'Ship City' }],
    formatSettings: [{ name: 'UnitPrice', format: 'C0' }],
    values: [{ name: 'Quantity' }, { name: 'UnitPrice', caption: 'Unit Price' }]
  };

  return (
    <PivotViewComponent
      id='PivotView'
      height={350}
      dataSourceSettings={dataSourceSettings}
    />
  );
}

export default App;

Querying Data with DataManager

Customize DataManager behavior using the defaultQuery property to apply filtering, sorting, and paging:

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { DataManager, ODataV4Adaptor, Query } from '@syncfusion/ej2-data';
import * as React from 'react';

function App() {
  const dataSource: DataManager = new DataManager({
    url: 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders',
    adaptor: new ODataV4Adaptor(),
    crossDomain: true
  });

  // Apply query to limit results to 10 records
  dataSource.defaultQuery = new Query().take(10);

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: dataSource,
    expandAll: false,
    columns: [{ name: 'CustomerID', caption: 'Customer ID' }],
    rows: [{ name: 'ShipCountry', caption: 'Ship Country' }, { name: 'ShipCity', caption: 'Ship City' }],
    values: [{ name: 'Freight' }]
  };

  return (
    <PivotViewComponent
      id='PivotView'
      height={350}
      dataSourceSettings={dataSourceSettings}
    />
  );
}

export default App;

Choosing Between Data Binding Approaches

Recommendation: For large datasets (> 50K records), use remote binding with server-side processing.

Field Mapping

Field mapping allows you to customize how fields appear and behave in the Pivot Table using the fieldMapping property without changing the original data source.

Field Mapping Configuration

Available options for field mapping:

• name: The actual field name in the data source
• caption: Display name in the Pivot Table UI

import { IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';
import { pivotData } from './datasource';

function App() {
  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: pivotData as IDataSet[],
    expandAll: false,
    rows: [
      { 
        name: 'Country',
        caption: 'Customer Country'  // ← Display name in UI
      }
    ],
    columns: [
      { 
        name: 'Year',
        caption: 'Fiscal Year'
      }
    ],
    values: [
      { 
        name: 'Amount',
        caption: 'Total Sales Amount'
      }
    ]
  };

  return (
    <PivotViewComponent
      id='PivotView'
      height={350}
      dataSourceSettings={dataSourceSettings}
    />
  );
}

export default App;

Best Practices for Data Binding

1. For local data: Use direct assignment or DataManager with JsonAdaptor 2. For large datasets: Use remote binding with server-side processing 3. For CSV imports: Convert to proper JSON structure before binding 4. For real-time data: Configure OData/Web API with automatic refresh intervals 5. Field naming: Keep field names consistent across data source and configuration 6. Data types: Ensure proper data types in source for accurate aggregations

Remote Data Binding

Binding JSON from Remote URL

To bind remote JSON data, set the endpoint URL in the url property of dataSourceSettings. The URL can point to:

• Direct downloadable JSON files (*.json)
• Web service endpoints (API/REST endpoints)

import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import * as React from 'react';

function PivotWithRemoteData() {
  const dataSourceSettings: DataSourceSettingsModel = {
    url: 'https://cdn.syncfusion.com/data/sales-analysis.json',
    expandAll: false,
    rows: [
      { name: 'EnerType', caption: 'Energy Type' }
    ],
    columns: [
      { name: 'EneSource', caption: 'Energy Source' }
    ],
    values: [
      { name: 'PowUnits', caption: 'Units (GWh)' },
      { name: 'ProCost', caption: 'Cost (MM)' }
    ]
  };

  return (
    <PivotViewComponent
      id="pivot"
      dataSourceSettings={dataSourceSettings}
      height={400}
    />
  );
}

export default PivotWithRemoteData;

Using DataManager for Remote Sources

DataManager simplifies remote data binding with built-in HTTP adaptor:

import { DataManager, UrlAdaptor } from '@syncfusion/ej2-data';
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';

function PivotWithDataManagerURL() {
  const dataManager = new DataManager({
    url: 'https://api.example.com/pivot-data',
    adaptor: new UrlAdaptor()
  });

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: dataManager,
    rows: [{ name: 'Category' }],
    columns: [{ name: 'Year' }],
    values: [{ name: 'Revenue', type: 'Sum' }]
  };

  return (
    <PivotViewComponent
      dataSourceSettings={dataSourceSettings}
      height={400}
    />
  );
}

export default PivotWithDataManagerURL;

Choosing Between Local and Remote Data

Recommendation: For large datasets (> 50K records), use remote binding or server-side pivot engine

Using DataManager with HTTP Endpoint

For large datasets or server-side processing, use DataManager with WebApiAdaptor:

import { DataManager, WebApiAdaptor } from '@syncfusion/ej2-data';

function PivotWithRemoteData() {
  const dataManager = new DataManager({
    url: 'https://api.example.com/api/sales-data',  // ← Your API endpoint
    adaptor: new WebApiAdaptor(),
    headers: [
      { 'Authorization': 'Bearer token' }  // ← Optional: API authentication
    ],
    crossDomain: true
  });

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: dataManager,
    rows: [{ name: 'Country' }],
    columns: [{ name: 'Product' }],
    values: [{ name: 'Sales', type: 'Sum' }]
  };

  return (
    <PivotViewComponent
      dataSourceSettings={dataSourceSettings}
      height={400}
    />
  );
}

API Response Format

Your backend API must return data in the expected JSON array format:

{
  "result": [
    { "Country": "USA", "Product": "Laptop", "Sales": 5000, "Quantity": 10 },
    { "Country": "USA", "Product": "Desktop", "Sales": 3000, "Quantity": 8 },
    { "Country": "Canada", "Product": "Laptop", "Sales": 2500, "Quantity": 6 }
  ],
  "count": 100
}

Backend Implementation (Node.js/Express example):

// Backend: routes/sales.ts
app.get('/api/sales-data', async (req, res) => {
  try {
    const data = await fetchSalesData();  // Fetch from database
    res.json({
      result: data,
      count: data.length
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

Handling Async Data Loading

function PivotWithAsyncRemoteData() {
  const [loading, setLoading] = React.useState(true);
  const [dataManager, setDataManager] = React.useState(null);

  React.useEffect(() => {
    const dm = new DataManager({
      url: 'https://api.example.com/api/sales-data',
      adaptor: new WebApiAdaptor()
    });
    setDataManager(dm);
    setLoading(false);
  }, []);

  if (loading) return <p>Loading pivot data...</p>;

  return (
    <PivotViewComponent
      dataSourceSettings={{
        dataSource: dataManager,
        rows: [{ name: 'Country' }],
        columns: [{ name: 'Product' }],
        values: [{ name: 'Sales' }]
      }}
      height={400}
    />
  );
}

CSV Data Handling

Loading CSV as JSON Array

Convert CSV data to a JSON array format first, then bind like local JSON:

// CSV: "Country,Product,Sales,Quantity\nUSA,Laptop,5000,10\n..."

function PivotWithCSVData() {
  const csvData = [
    { Country: 'USA', Product: 'Laptop', Sales: 5000, Quantity: 10 },
    { Country: 'USA', Product: 'Desktop', Sales: 3000, Quantity: 8 },
    { Country: 'Canada', Product: 'Laptop', Sales: 2500, Quantity: 6 }
    // Parse CSV file into this format
  ];

  const dataSourceSettings: DataSourceSettingsModel = {
    dataSource: csvData,  // ← Direct JSON array binding (no type property needed)
    rows: [{ name: 'Country' }],
    columns: [{ name: 'Product' }],
    values: [{ name: 'Sales', type: 'Sum' }]
  };

  return (
    <PivotViewComponent
      dataSourceSettings={dataSourceSettings}
      height={400}
    />
  );
}

CSV File Upload and Parsing

import Papa from 'papaparse';  // npm install papaparse

function PivotWithCSVUpload() {
  const [csvData, setCsvData] = React.useState([]);

  const handleCsvUpload = (event) => {
    const file = event.target.files[0];
    
    Papa.parse(file, {
      header: true,           // Use first row as headers
      dynamicTyping: true,    // Convert numbers
      skipEmptyLines: true,
      complete: (results) => {
        setCsvData(results.data);
      },
      error: (error) => {
        console.error('CSV parsing error:', error);
      }
    });
  };

  return (
    <>
      <input 
        type="file" 
        accept=".csv" 
        onChange={handleCsvUpload} 
      />
      {csvData.length > 0 && (
        <PivotViewComponent
          dataSourceSettings={{
            dataSource: csvData,
            rows: [{ name: 'Country' }],
            columns: [{ name: 'Product' }],
            values: [{ name: 'Sales' }]
          }}
          height={400}
        />
      )}
    </>
  );
}

Data Refresh Mechanisms

Manual Refresh

let pivotRef: PivotViewComponent;

function handleDataRefresh() {
  pivotRef.refresh();  // Refresh with current data
}

<button onClick={handleDataRefresh}>Refresh Data</button>

Common Scenarios

Binding Large Datasets

For large datasets (>10K records), use virtual scrolling:

<PivotViewComponent
  dataSourceSettings={dataSourceSettings}
  enableVirtualization={true}
  height={500}
/>