Syncfusion Angular Pivot Table

Implementing Angular Pivot Grid

The Syncfusion Angular Pivot Grid is a powerful data visualization and analysis component for creating interactive pivot tables, aggregating multidimensional data, and performing advanced analytics operations.

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 users need to:

• Create and configure pivot tables from multidimensional data
• Bind data from OLAP or relational data sources
• Aggregate data with multiple aggregation functions (Sum, Avg, Count, etc.)
• Group data by number ranges, dates, or custom categories
• Create and manage calculated fields with formulas
• Enable drill-down and drill-through operations
• Visualize data with integrated pivot charts
• Apply custom formatting and conditional styling
• Optimize large dataset performance
• Export pivot grid data to Excel or PDF
• Persist and restore pivot grid state
• Customize UI with field lists, grouping bars, and toolbars

Documentation Guide

Getting Started

📄 Read: references/getting-started.md

• Installation and package setup
• Basic Angular Pivot Grid implementation
• CSS imports and theme configuration
• RTL (Right-to-Left) support
• Component initialization

Aggregation

📄 Read: references/aggregation.md

• Aggregation functions: Sum, Avg, Count, Min, Max, Product, Median, DistinctCount
• Advanced aggregations: DifferenceFrom, PercentageOfDifferenceFrom, PercentageOfParentTotal
• Base field configuration with baseField and baseItem properties
• Multiple aggregations on same field
• Customizing aggregation dropdown and UI
• Runtime aggregation type changes
• Events: aggregateCellInfo, actionBegin, actionComplete

Grouping

📄 Read: references/grouping.md

• Enable grouping with allowGrouping: true and inject GroupingService
• Number grouping: Configure ranges with rangeInterval, startingAt, endingAt
• Date grouping: Organize by Years, Quarters, Months, Days, Hours, Minutes, Seconds
• Custom grouping: Group data by business-defined categories
• UI-based grouping through context menu
• Ungrouping and programmatic ungrouping
• Common grouping patterns and troubleshooting

Calculated Fields

📄 Read: references/calculated-field.md

• Creating calculated fields interactively and programmatically
• Enabling with allowCalculatedField: true, injecting CalculatedFieldService
• Defining with calculatedFieldSettings (name, formula)
• Adding to values array with type: 'CalculatedField'
• Editing/renaming fields through UI (Field List, Grouping Bar)
• Formula syntax: Operators (+, -, , /, ^, <, >, ==, !=, &, |, ?), Functions (abs, min, max, isNaN, Math.)
• Aggregation functions in formulas: Sum, Count, Avg, Min, Max
• Formatting with separate formatSettings array: Currency (C), Number (N), Percentage (P)
• Events: calculatedFieldCreate (validation), actionBegin/actionComplete (control operations)

Pivot Chart Integration

📄 Read: references/pivot-chart-integration.md

• Inject PivotChartService provider to enable chart functionality
• Chart types: 21+ types including Line, Column, Area, Bar, StepArea, Pie, Doughnut, Funnel, Pyramid, Radar, Polar, Pareto, Bubble, Scatter, Spline
• Display options: Configure with displayOption property to show Table, Chart, or Both with view and primary settings
• Series customization: Customize charts via chartSeries in chartSettings (type, marker, dataLabel)
• Field list integration: Enable with showFieldList: true for dynamic field manipulation
• Grouping bar support: Enable with showGroupingBar: true for axis field switching
• Axis configuration: Customize X/Y axes via primaryXAxis and primaryYAxis in chartSettings
• Multiple axes: Configure enableMultipleAxis for multi-value visualization with multipleAxisMode
• Accumulation chart drill: Support drill-down/up on Pie, Doughnut, Funnel, Pyramid via context menu

Filtering & Sorting

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

• Member filtering: Include or exclude specific field members
• Label filtering: Filter based on header text or member names
• Value filtering: Filter based on aggregated values meeting conditions
• Member sorting: Arrange field members in ascending/descending order
• Custom member sorting: Sort field members in user-defined order using membersOrder
• Value sorting: Sort pivot table values and aggregated data with enableValueSorting
• Programmatic value sorting: Configure with valueSortSettings

Data Formatting & Conditional Formatting

📄 Read: references/data-formatting.md

• Number formatting: Apply Currency (C), Percentage (P), Number (N), Scientific (E) formats
• Custom format strings: Define format strings with placeholders for calculated fields
• Conditional formatting: Apply colors/styles based on cell values using conditionalFormatSettings
• Format settings configuration: Configure in separate formatSettings array in dataSourceSettings

Export & Printing

📄 Read: references/export-and-print.md

• Excel export: excelExport() with custom properties, themes
• PDF export: pdfExport() with headers, footers, page orientation
• CSV export: csvExport() for large datasets (1M+ rows)
• Print functionality: print() method for table and chart
• Multi-table export: Combine multiple pivot tables in single file
• Export customization: Cell styling, color themes, branding

UI Customization

📄 Read: references/ui-customization.md

• Toolbar configuration: Show/hide built-in toolbar items
• Report management: New, Save, Load, Delete reports
• View switching: Toggle between Grid and Chart modes
• Export options: Quick export buttons
• Grand totals/Subtotals: Show/hide and customize positioning
• Custom templates: Build custom toolbar or field list

Grouping Bar UI Operations

📄 Read: references/grouping-bar-ui-operations.md

• Enable grouping bar: Set showGroupingBar: true on component
• Drag-and-drop reorganization: Move fields between Row, Column, Value, Filter axes
• Filter operations: Access filters from grouping bar field buttons
• Sort operations: Configure sort options via grouping bar interface
• Remove operations: Remove fields directly from grouping bar
• Value field management: Switch between value fields in accumulation charts
• Hide specific icons: Control visibility of filter, sort, remove icons per field
• Grouping bar customization: Configure via groupingBarSettings

Tooltips Customization

📄 Read: references/tooltips-customization.md

• Enable/disable tooltips: Configure tooltip visibility on cells and charts
• Custom tooltip templates: Define dynamic tooltip content with placeholders
• Available placeholders: Row headers, column headers, value, and other cell metadata
• Pivot chart tooltip customization: Customize tooltip appearance for chart data points
• CSS styling: Style tooltip appearance with custom CSS
• Dynamic tooltip content: Configure based on cell values and context

Editing & Drill Operations

📄 Read: references/editing-drill-operations.md

• Enable editing: Set allowEditing: true on component (relational data only)
• Edit modes: Normal (inline), Dialog (popup), Batch (multiple), Command Column (dedicated)
• Edit settings: Configure via editSettings with allowAdding, allowDeleting, allowCommandColumns
• Edit events: Monitor editCompleted, actionBegin, actionComplete, actionFailure
• CRUD operations: Create, Read, Update, Delete records via editing interface
• Drill-through operations: View raw underlying data for aggregated values via context menu
• Drill-down operations: Click cells to navigate hierarchical data deeper
• Cell selection: Configure via selectionSettings
• Save data: Updated records persist via event handlers and data binding

OLAP Data Sources

📄 Read: references/olap-data-sources.md

• Connection configuration: Set url, catalog, cube, providerType: 'SSAS' in dataSourceSettings
• OLAP cube elements: Measures (numeric aggregates), Dimensions (hierarchical groupings), Hierarchies, Named Sets
• MDX support: Configure using MDX (Multidimensional Expressions) syntax for queries
• Hierarchies: Access via [Dimension].[Hierarchy] notation (e.g., [Date].[Date Hierarchy])
• Calculated fields: Create Calculated Measures and Dimensions in OLAP cubes
• Authentication: Configure via connection string or backend authentication headers
• Named sets: Predefined member groups for analysis
• Advanced features: Drill-down, virtual scrolling, value filtering with OLAP

Paging Configuration

📄 Read: references/paging-configuration.md

• Enable paging with enablePaging: true and inject PagerService
• Page settings: rowPageSize, columnPageSize, currentRowPage, currentColumnPage
• Pager UI configuration and positioning (Top/Bottom)
• Row vs column paging
• Compact view and inverse pager layout
• Custom page size options in dropdown
• Paging with virtual scrolling for optimization
• Server-side paging for huge datasets
• Mobile-optimized paging

Virtual Scrolling

📄 Read: references/virtual-scrolling.md

• Enable virtual scrolling with enableVirtualization: true for large datasets
• Single page mode: Use allowSinglePage: true to render only current view page
• Limitations: pixel-based columnWidth, avoid runtime sizing changes
• Static FieldList synchronization using enginePopulated events and update/updateView methods
• Performance optimization for 100K+ rows on client-side

Drill-Down & Drill-Through Operations

📄 Read: references/drill-down.md

• Drill-down: Navigate hierarchical data by clicking cells
• Drill-up: Navigate back to higher levels
• Drill-through: View raw underlying data for aggregated values
• Events: cellClick, fieldDrop, fieldsUpdated for drill interactions
• Context menu: Built-in drill operations

Database Connections

📄 Read: references/database-connections.md

• SQL Server: Connect via connection strings with SQL authentication
• MySQL: Configure with host, port, user, password, and database
• PostgreSQL: Connection configuration for PostgreSQL databases
• Oracle: Oracle database connectivity with named parameters
• MongoDB: Connect to MongoDB collections and query documents
• Elasticsearch: Big data analytics via Elasticsearch indices
• Snowflake: Cloud data warehouse integration
• Server-side processing: Use ASP.NET Core backend with database queries

Server-Side Pivot Engine

📄 Read: references/server-side-pivot-engine.md

• Server-side aggregation: Delegate heavy processing to ASP.NET Core backend
• Client configuration: Set mode: 'Server' with url endpoint in dataSourceSettings
• ASP.NET Core setup: Download and configure PivotController with Syncfusion.Pivot.Engine NuGet
• Data sources: Support Collection, JSON, CSV, DataTable, Dynamic objects on server
• Large datasets: Handle 100K+ rows with server-side processing
• Virtual scrolling: Combine with server mode for optimal performance
• Export operations: Excel/CSV export of server-processed data
• Authentication: Configure via beforeServiceInvoke event or headers

Performance Optimization

📄 Read: references/performance-optimization.md

• Virtual scrolling: Enable with enableVirtualization: true for 100K+ rows
• Single page mode: Use allowSinglePage: true for better performance
• Paging: Configure with pageSettings for row/column pagination
• Data compression: Enable allowDataCompression: true for duplicate record summarization
• Deferred updates: Use allowDeferLayoutUpdate: true to batch field operations
• Large dataset handling: Server-side processing with mode: 'Server'
• Best practices: Pre-filtering, optimized sorting, member filtering limits, avoiding built-in grouping

State Persistence & Hyperlinks

📄 Read: references/state-persistence-hyperlinks.md

• State persistence: Save and restore pivot configuration using getPersistData()
• Local storage: Persist report state in browser localStorage for user sessions
• Report management: Save, load, and delete named reports
• JSON serialization: Export/import report configurations as JSON
• Hyperlinks: Enable clickable hyperlinks in pivot cells via hyperlinkSettings
• Hyperlink events: Handle hyperlink clicks with appropriate event handlers
• Drill-through hyperlinks: Links to detailed data sources for aggregates

---

Quick Start Example

import { PivotViewAllModule, CalculatedFieldService } from '@syncfusion/ej2-angular-pivotview';
import { Component, OnInit, ViewChild } from '@angular/core';
import { PivotViewComponent, IDataSet } from '@syncfusion/ej2-angular-pivotview';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

@Component({
  imports: [PivotViewAllModule],
  providers: [CalculatedFieldService],
  standalone: true,
  selector: 'app-pivot-grid',
  template: `
    <ejs-pivotview #pivotview id='PivotView' 
      [dataSourceSettings]="dataSourceSettings"
      [height]="'500px'"
      [width]="'100%'"
      [allowCalculatedField]="true" 
      [allowGrouping]="true"
      [toolbar]="toolbarItems">
    </ejs-pivotview>
  `
})
export class AppComponent implements OnInit {
    @ViewChild('pivotview') pivotViewComponent!: PivotViewComponent;
    
    public pivotData!: IDataSet[];
    public dataSourceSettings!: DataSourceSettingsModel;
    public toolbarItems: string[] = ['New', 'Save', 'SaveAs', 'Rename', 'Remove', 'Load', 'Export'];

    ngOnInit(): void {
        this.pivotData = [
            { 'Sold': 31, 'Amount': 52824, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q1' },
            { 'Sold': 51, 'Amount': 86904, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q2' },
            { 'Sold': 90, 'Amount': 153360, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q3' },
            { 'Sold': 25, 'Amount': 42500, 'Country': 'France', 'Products': 'Road Bikes', 'Year': 'FY 2015', 'Quarter': 'Q1' },
            { 'Sold': 40, 'Amount': 68000, 'Country': 'Germany', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q2' }
        ];

        this.dataSourceSettings = {
            dataSource: this.pivotData,
            expandAll: false,
            rows: [{ name: 'Country' }, { name: 'Products' }],
            columns: [{ name: 'Year' }, { name: 'Quarter' }],
            values: [
                { name: 'AvgAmount', type: 'CalculatedField' },
                { name: 'Sold', type: 'Count' }
            ],
            calculatedFieldSettings: [
                {
                    name: 'AvgAmount',
                    formula: '"Sum(Amount)"/"Count(Sold)"'  // Calculated field: Average
                }
            ],
            formatSettings: [
                { name: 'Amount', format: 'C2' },             // Currency format
                { name: 'AvgAmount', format: 'C2' }           // Format calculated field
            ]
        };
    }
}

---

Common Patterns

Pattern 1: Multiple Fields with Different Aggregation Types

Each field supports only ONE aggregation type. To analyze different aspects, use different fields with their respective aggregation functions:

values: [
    { name: 'Amount', type: 'Sum' },      // Total sales amount
    { name: 'Quantity', type: 'Avg' },    // Average quantity sold
    { name: 'Sold', type: 'Count' },      // Number of transactions
    { name: 'Price', type: 'Min' },       // Minimum price
]

Note: Each field in the values array can only have ONE type. To get multiple aggregation types for analysis, use different fields or create calculated fields combining aggregations.

Pattern 2: Hierarchical Grouping

Organize data with multi-level grouping:

rows: [
    { name: 'Country' },    // Primary level
    { name: 'Region' },     // Secondary level
    { name: 'City' }        // Tertiary level
],
columns: [
    { name: 'Year' },       // Year level
    { name: 'Quarter' }     // Quarter level
]

Pattern 3: Using Calculated Fields with Aggregations

Combine calculated fields with multiple aggregation types for advanced analysis:

values: [
    { name: 'Amount', type: 'Sum' },       // Total amount
    { name: 'Quantity', type: 'Avg' },     // Average quantity
    { name: 'AvgRevenue', type: 'CalculatedField' }  // Custom calculation
],
calculatedFieldSettings: [
    {
        name: 'AvgRevenue',
        formula: '"Sum(Amount)" / "Count(Quantity)"'  // Revenue per unit
    }
],
formatSettings: [
    { name: 'Amount', format: 'C2' },      // Currency format for Amount
    { name: 'AvgRevenue', format: 'C2' }   // Currency format for calculated field
]

Pattern 4: Number Range Grouping

Group numeric fields into ranges:

groupSettings: [
    {
        name: 'ProductID',
        type: 'Number',
        rangeInterval: 5,
        startingAt: 1000,
        endingAt: 1010
    }
]

Pattern 5: Date Hierarchy Grouping

Organize dates with time-based hierarchies:

groupSettings: [
    {
        name: 'OrderDate',
        type: 'Date',
        groupInterval: ['Years', 'Months'],  // Year then month hierarchy
        startingAt: new Date(2020, 0, 1),
        endingAt: new Date(2023, 11, 31)
    }
]

---

Key Configuration Properties

Important: When adding calculated fields to values, use type: 'CalculatedField' to distinguish them from regular aggregations. Format settings must be applied in a separate formatSettings array, not within the value field object.

---

Next Steps

Foundation (Start here): 1. Read Getting Started for setup and initialization 2. Read Aggregation to implement aggregation functions and base field aggregations 3. Read Grouping to configure number, date, and custom grouping

Data Manipulation: 4. Read Calculated Fields for complex calculations and custom field formulas 5. Read Filtering & Sorting for label/value filtering and custom sorting 6. Read Data Formatting for number formats and conditional formatting

Visualization & Interaction: 7. Read Pivot Chart Integration for charting and drill operations 8. Read UI Customization for toolbars and custom interfaces 9. Read Drill-Down & Drill-Through for hierarchical data exploration

Advanced Features: 10. Read Export & Printing for multi-format export (Excel, PDF, CSV) 11. Read State Persistence for saving and restoring configurations 12. Read Database Connections for SQL/NoSQL/big data integration 13. Read Server-Side Pivot Engine for processing 100K+ rows on backend 14. Read Performance Optimization for large datasets, deferred updates, and virtual scrolling

---

Foundational References

These files provide foundational knowledge and may be referenced by feature guides:

📄 [references/core-concepts.md](references/core-concepts.md) - Data binding types (JSON, CSV, OLAP), client-side vs server-side comparison, choosing appropriate data sources

📄 [references/layout-and-columns.md](references/layout-and-columns.md) - Classic layout, row/column sizing, column features, cell selection and customization

📄 [references/field-list.md](references/field-list.md) - Field list UI (popup/fixed modes), field organization, deferred updates patterns

Aggregation Functions

Table of Contents

• Overview
• Available Aggregation Types
• Configuring Aggregation Types
• Base Field Aggregation
• Runtime Aggregation Changes
• Customizing Aggregation UI
• Events

---

Overview

Aggregation enables you to perform calculations on groups of values in the pivot table. By default, values are combined by summing them, but you can use 20+ different aggregation types including Sum, Average, Count, Min, Max, and more advanced statistical functions.

Note: Numeric fields support all aggregation types. Fields of type string, date, datetime, boolean support only Count and DistinctCount aggregation.

---

Available Aggregation Types

---

Configuring Aggregation Types

Setting Aggregation via API

Configure aggregation for value fields using the type property in the values array:

dataSourceSettings: IDataOptions = {
  dataSource: data,
  rows: [{ name: 'Category' }],
  columns: [{ name: 'Region' }],
  values: [
    { name: 'Amount', type: 'Sum' },      // Sum aggregation
    { name: 'UnitsSold', type: 'Count' }, // Count aggregation
    { name: 'Price', type: 'Avg' }        // Average aggregation
  ]
};

Aggregating Different Fields

Add different fields to the values array, each with its own aggregation type:

values: [
  { name: 'Amount', type: 'Sum' },       // Sum of Amount
  { name: 'UnitsSold', type: 'Count' },  // Count of UnitsSold
  { name: 'Price', type: 'Avg' },        // Average Price
  { name: 'Rating', type: 'Max' }        // Maximum Rating
]

Note: Each field in the values array can have only one aggregation type. If you need different aggregations for the same field, modify it at runtime through the UI dropdown.

Default Behavior

• Numeric fields: Default aggregation is Sum
• Non-numeric fields (string, date, boolean): Default aggregation is Count

---

Base Field Aggregation

Base field aggregations compare values against a specific baseline using baseField and baseItem properties.

DifferenceFrom

Compare each value to a specific item in a base field:

values: [
  {
    name: 'Amount',
    type: 'DifferenceFrom',
    baseField: 'Region',  // Field to compare against
    baseItem: 'USA'       // Specific item to use as baseline
  }
]

Example: Shows sales difference from USA sales for each region.

PercentageOfDifferenceFrom

Calculate percentage difference from a base item:

values: [
  {
    name: 'Amount',
    type: 'PercentageOfDifferenceFrom',
    baseField: 'Year',
    baseItem: '2022'
  }
]

Example: Shows % change in sales compared to 2022 for each year.

PercentageOfParentTotal

Calculate percentage relative to parent group total:

values: [
  {
    name: 'Amount',
    type: 'PercentageOfParentTotal',
    baseField: 'Category'  // Parent field to calculate against
  }
]

Example: Shows each product's sales as % of its category total.

---

Runtime Aggregation Changes

Changing Aggregation Types at Runtime

Users can change aggregation types through the UI dropdown in:

• Grouping Bar: Click the dropdown icon on value field buttons
• Field List: Select aggregation from the value field dropdown

The pivot table updates instantly when a new aggregation is selected.

Programmatic Modification

Modify aggregation types dynamically using the GetValueFields() method:

// Change aggregation type for a value field
this.pivotGridObj.dataSourceSettings.values
  .find(field => field.name === 'Amount').type = 'Avg';

// Refresh the pivot table
this.pivotGridObj.refresh();

---

Customizing Aggregation UI

Show Specific Aggregation Types

Limit the dropdown to only relevant aggregation types using aggregateTypes property at component level:

import { Component, OnInit } from '@angular/core';
import { AggregateTypes } from '@syncfusion/ej2-angular-pivotview';

@Component({
  template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings
    [aggregateTypes]='aggregateTypesOption'></ejs-pivotview>`
})
export class PivotGridComponent implements OnInit {
  public aggregateTypesOption: AggregateTypes[] = ['Sum', 'Avg', 'Count', 'Max'];
  public dataSourceSettings: IDataOptions;
  
  ngOnInit(): void {
    this.dataSourceSettings = {
      dataSource: data,
      // ... other settings
    };
  }
}

Result: Only Sum, Avg, Count, and Max appear in the aggregation dropdown. All other types are hidden.

Hide Aggregation Type in Button Text

Display only field name (e.g., "Amount") instead of "Sum of Amount" using showAggregationOnValueField:

dataSourceSettings: IDataOptions = {
  rows: [{ name: 'Category' }],
  columns: [{ name: 'Region' }],
  values: [{ name: 'Amount', type: 'Sum' }],
  showAggregationOnValueField: false  // Hide aggregation type in button text
};

Result: Value field button shows "Amount" instead of "Sum of Amount".

Hide Aggregation Type Icon

Remove the aggregation dropdown icon from the grouping bar using groupingBarSettings:

@Component({
  template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings
    [groupingBarSettings]='groupingBarOptions'></ejs-pivotview>`
})
export class PivotGridComponent implements OnInit {
  public groupingBarOptions: GroupingBarSettings = {
    showValueTypeIcon: false  // Hide aggregation type dropdown icon
  };
  
  ngOnInit(): void {
    // dataSourceSettings setup...
  }
}

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

---

Events

aggregateCellInfo Event

Triggered when a value cell is rendered. Allows overriding cell values or skipping formatting:

aggregateCellInfo(args: AggregateCellInfoEventArgs) {
  // args.fieldName - Current cell's field name
  // args.value - Current cell's value
  // args.aggregateType - Type of aggregation (Sum, Avg, etc.)
  
  if (args.fieldName === 'Amount' && args.value < 1000) {
    args.value = 0;  // Override small values
  }
  
  // Skip formatting if needed
  args.skipFormatting = false;
}

actionBegin Event

Triggered when aggregation type is selected via dropdown:

actionBegin(args: PivotActionBeginEventArgs) {
  if (args.actionName === 'Aggregate field') {
    console.log('Aggregation type changed:', args.fieldInfo);
    
    // Prevent aggregation change if needed
    if (args.fieldInfo.name === 'CriticalField') {
      args.cancel = true;
    }
  }
}

actionComplete Event

Triggered after aggregation change completes:

actionComplete(args: PivotActionCompleteEventArgs) {
  if (args.actionName === 'Field aggregated') {
    console.log('Aggregation successfully changed');
    // Update UI or perform additional actions
  }
}

---

Common Patterns

Financial Analysis

values: [
  { name: 'Revenue', type: 'Sum' },      // Total revenue
  { name: 'Profit', type: 'Sum' },       // Total profit
  { name: 'Cost', type: 'Sum' }          // Total cost
]

At Runtime: Users can change aggregation type (Sum → Avg → Max) using the dropdown in the UI.

Performance Metrics

values: [
  { name: 'Score', type: 'Avg' },       // Average score
  { name: 'Attempts', type: 'Count' },  // Count of attempts
  { name: 'MaxScore', type: 'Max' }     // Maximum score achieved
]

Percentage Analysis

Show percentage breakdowns of sales data:

values: [
  { name: 'Amount', type: 'Sum' }  // Base amount field
]

At Runtime: User changes aggregation dropdown to PercentageOfGrandTotal, PercentageOfRowTotal, or PercentageOfColumnTotal to view percentages.

Statistical Analysis

Analyze distribution and variability:

values: [
  { name: 'Value', type: 'Avg' },              // Average value
  { name: 'Variance', type: 'PopulationVar' }  // Population variance
]

Alternative Using Calculated Fields:

calculatedFieldSettings: [
  {
    name: 'StdDevPercent',
    formula: '("Sum(StdDev)" / "Sum(Value)") * 100'
  }
],
values: [
  { name: 'Value', type: 'Avg' },
  { name: 'StdDevPercent', type: 'CalculatedField' }
]

Difference-Based Analysis

Compare values across groups:

values: [
  { name: 'Amount', type: 'Sum' }  // Current amount
]

At Runtime: User can change aggregation to DifferenceFrom and select a base field and item to compare values.

Programmatically:

values: [
  {
    name: 'Amount',
    type: 'DifferenceFrom',
    baseField: 'Year',
    baseItem: '2022'
  }
]

---

Troubleshooting

Issue: Aggregation type not available for field

• Solution: Ensure field data type is numeric. Non-numeric fields only support Count and DistinctCount.

Issue: DifferenceFrom/PercentageOfDifferenceFrom showing unexpected results

• Solution: Verify baseField and baseItem are correctly configured to reference existing data.

Issue: Aggregation dropdown not showing

• Solution: Check if showValueTypeIcon: false is set in groupingBarSettings. Re-enable to see dropdown.

Calculated Fields

Table of Contents

• Creating Calculated Fields
• Defining Calculated Fields Programmatically
• Opening Dialog Programmatically
• Editing Through UI
• Renaming Calculated Fields
• Editing Formula
• Reusing Existing Formulas
• Formatting Calculated Field Values
• Supported Operators & Functions
• Calculated Field Events

---

Creating Calculated Fields

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.

Users can create calculated fields in two convenient ways:

• Interactive Method: Using the built-in dialog accessible from the Field List UI
• Code-Based Method: Configuring fields programmatically using the calculatedFieldSettings property

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.

import { PivotViewAllModule, CalculatedFieldService } from '@syncfusion/ej2-angular-pivotview';
import { Component } from '@angular/core';

@Component({
  imports: [PivotViewAllModule],
  providers: [CalculatedFieldService],
  selector: 'app-pivot',
  template: `<ejs-pivotview 
    [dataSourceSettings]="dataSourceSettings"
    [allowCalculatedField]="true">
  </ejs-pivotview>`
})
export class AppComponent {
  dataSourceSettings: any = {
    dataSource: [
      { 'Sold': 31, 'Amount': 52824, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015' }
    ],
    rows: [{ name: 'Country' }],
    columns: [{ name: 'Year' }],
    values: [{ name: 'Amount', type: 'Sum' }]
  };
}

---

Defining Calculated Fields Programmatically

You can define calculated fields programmatically using the calculatedFieldSettings property. This approach is ideal for pre-configuring specific calculations. The following properties are essential for creating a calculated field:

• name: Specifies a unique name for the calculated field
• formula: Defines the mathematical expression using existing field names and arithmetic operators
• formatSettings: Configures the number format for displaying calculated results (optional)

To use the calculated field feature, you must inject the CalculatedFieldService module into the pivot table.

Note: 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.

import { PivotViewAllModule, CalculatedFieldService } from '@syncfusion/ej2-angular-pivotview';
import { Component } from '@angular/core';

@Component({
  imports: [PivotViewAllModule],
  providers: [CalculatedFieldService],
  selector: 'app-pivot',
  template: `<ejs-pivotview 
    [dataSourceSettings]="dataSourceSettings"
    [allowCalculatedField]="true">
  </ejs-pivotview>`
})
export class AppComponent {
  dataSourceSettings: any = {
    dataSource: [
      { 'Sold': 31, 'Amount': 52824, 'Country': 'France', 'Year': 'FY 2015' },
      { 'Sold': 51, 'Amount': 86904, 'Country': 'France', 'Year': 'FY 2015' }
    ],
    rows: [{ name: 'Country' }],
    columns: [{ name: 'Year' }],
    values: [
      { name: 'Amount', type: 'Sum' },
      { name: 'TotalSold', type: 'CalculatedField' }
    ],
    calculatedFieldSettings: [
      {
        name: 'TotalSold',
        formula: '"Sum(Sold)" * 10'  // Multiply sold count by 10
      }
    ]
  };
}

---

Opening Dialog Programmatically

You can display the calculated field dialog by calling the createCalculatedFieldDialog method when an external button is clicked. This provides additional flexibility for accessing the calculated field functionality:

import { Component, ViewChild } from '@angular/core';
import { PivotViewComponent } from '@syncfusion/ej2-angular-pivotview';

@Component({
  selector: 'app-pivot',
  template: `
    <button (click)="openDialog()">Open Calculated Field Dialog</button>
    <ejs-pivotview 
      #pivotView
      [dataSourceSettings]="dataSourceSettings"
      [allowCalculatedField]="true">
    </ejs-pivotview>
  `
})
export class AppComponent {
  @ViewChild('pivotView') pivotView: PivotViewComponent;

  openDialog() {
    this.pivotView.createCalculatedFieldDialog();
  }
}

---

Editing Through UI

You can easily modify existing calculated fields using the built-in edit option available in both the field list and grouping bar. This feature allows you to update formulas, change field names, or adjust formatting without recreating the entire calculated field.

To edit an existing 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 settings 4. Make changes to the field name, formula, or format as needed 5. Click OK to apply the changes

---

Renaming Calculated Fields

You can rename any existing calculated field directly through the user interface at runtime. This option helps you maintain clear and meaningful names for your calculated fields as your analysis requirements evolve.

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 4. Replace the existing name with your preferred name 5. Click OK to save the new name

---

Editing Formula

This option allows you to modify the formulas of existing calculated fields directly through the user interface, ensuring your calculations remain accurate and up to date with changing requirements.

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 Existing Formulas

This option enables you to quickly create new calculated fields by reusing formulas from existing fields, saving time and ensuring consistency across your calculations.

To reuse an existing formula:

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

---

Formatting Calculated Field Values

Formatting calculated field values enhances the readability and insight of your data in the pivot table. You can apply different formats using the calculated field dialog in the UI or programmatically through code.

To format calculated field values in your code, use the formatSettings property. Apply formats to calculated fields using a separate formatSettings array in dataSourceSettings:

dataSourceSettings: any = {
  dataSource: data,
  rows: [{ name: 'Country' }],
  columns: [{ name: 'Year' }],
  values: [
    { name: 'Amount', type: 'Sum' },
    { name: 'ProfitMargin', type: 'CalculatedField' },
    { name: 'GrowthRate', type: 'CalculatedField' }
  ],
  calculatedFieldSettings: [
    {
      name: 'ProfitMargin',
      formula: '("Sum(Amount)" - "Sum(Cost)") / "Sum(Amount)" * 100'
    },
    {
      name: 'GrowthRate',
      formula: '(("Sum(Current)" - "Sum(Previous)") / "Sum(Previous)") * 100'
    }
  ],
  formatSettings: [
    { name: 'Amount', format: 'C2' },          // Currency format
    { name: 'ProfitMargin', format: 'N2' },    // Number format
    { name: 'GrowthRate', format: 'P2' }       // Percentage format
  ]
};

Format Options Through UI

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 (default)

Format Codes

Note: By default, None is selected in the format dropdown. For specific formatting requirements, select the Custom option to enter custom format patterns.

---

Supported Operators & Functions

Below is a list of operators and functions that can be used in the formula to create calculated fields:

Arithmetic Operators

• + Addition operator: X + Y
• - Subtraction operator: X - Y
• * Multiplication operator: X * Y
• / Division operator: X / Y
• ^ Power operator: X^2

Comparison Operators

• < Less than operator: X < Y
• <= Less than or equal operator: X <= Y
• > Greater than operator: X > Y
• >= Greater than or equal operator: X >= Y
• == Equal operator: X == Y
• != Not equal operator: X != Y

Logical Operators

• & AND operator: X & Y
• | OR operator: X | Y
• ? Conditional/Ternary operator: condition ? then : else

Functions

• isNaN() Checks if the value is not a number: isNaN(value)
• !isNaN() Checks if the value is a number: !isNaN(value)
• abs() Returns the absolute value of a number: abs(number)
• min() Returns the minimum value: min(number1, number2)
• max() Returns the maximum value: max(number1, number2)

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

Example Using Math Functions

calculatedFieldSettings: [
  {
    name: 'StandardDeviation',
    formula: 'Math.sqrt("Sum(Variance)")'
  },
  {
    name: 'LogValue',
    formula: 'Math.log("Sum(Amount)")'
  },
  {
    name: 'RoundedValue',
    formula: 'Math.round("Sum(Amount)")'
  }
]

---

Formatting Calculated Field Values

Apply number formats to calculated fields using a separate formatSettings array in dataSourceSettings (NOT inside calculatedFieldSettings):

dataSourceSettings: IDataOptions = {
  dataSource: data,
  rows: [{ name: 'Category' }],
  columns: [{ name: 'Region' }],
  values: [
    { name: 'Amount', type: 'Sum' },
    { name: 'TotalRevenue', type: 'CalculatedField' },       // Calculated field
    { name: 'GrowthPercentage', type: 'CalculatedField' },   // Calculated field
    { name: 'SuccessRate', type: 'CalculatedField' }         // Calculated field
  ],
  calculatedFieldSettings: [
    {
      name: 'TotalRevenue',
      formula: '"Sum(Amount)" * "Count(Units)"'
    },
    {
      name: 'GrowthPercentage',
      formula: '("Sum(Current)" - "Sum(Previous)") / "Sum(Previous)" * 100'
    },
    {
      name: 'SuccessRate',
      formula: '("Sum(Success)" / "Sum(Total)") * 100'
    }
  ],
  // SEPARATE formatSettings array in dataSourceSettings
  formatSettings: [
    {
      name: 'TotalRevenue',
      format: 'C2'  // Currency: $1,234.56
    },
    {
      name: 'GrowthPercentage',
      format: 'N2'  // Number: 1,234.56
    },
    {
      name: 'SuccessRate',
      format: 'P2'  // Percentage: 12.34%
    }
  ]
};

Important: formatSettings is a SEPARATE array at the dataSourceSettings level, NOT nested inside calculatedFieldSettings.

Format Codes:

• N = Number (e.g., 'N2' for 2 decimals: 1,234.56)
• C = Currency (e.g., 'C2' for currency: $1,234.56)
• P = Percentage (e.g., 'P2' for percentage: 12.34%)
• G = General format (default)

---

Calculated Field Events

CalculatedFieldCreate Event

The calculatedFieldCreate event enables you to validate and manage calculated field details before they are applied to the pivot table. This ensures data accuracy and prevents invalid configurations. The 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 calculatedFieldSettings of the pivot table
• cancel: A boolean property that prevents the dialog changes from being applied when set to true
• dataSourceSettings: Contains the current data source configuration
• fieldName: Specifies the name of the field being created or updated

Example: Prevent fields without format

import { Component } from '@angular/core';
import { PivotViewAllModule, CalculatedFieldService } from '@syncfusion/ej2-angular-pivotview';

@Component({
  imports: [PivotViewAllModule],
  providers: [CalculatedFieldService],
  selector: 'app-pivot',
  template: `<ejs-pivotview 
    [dataSourceSettings]="dataSourceSettings"
    [allowCalculatedField]="true"
    (calculatedFieldCreate)="onCalculatedFieldCreate($event)">
  </ejs-pivotview>`
})
export class AppComponent {
  dataSourceSettings: any = {
    dataSource: [],
    rows: [{ name: 'Country' }],
    columns: [{ name: 'Year' }],
    values: [{ name: 'Amount', type: 'Sum' }]
  };

  onCalculatedFieldCreate(args: any) {
    if (!args.calculatedField.formatString) {
      args.cancel = true;
      alert('Please specify a format for the calculated field');
    }
  }
}

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:

• Clicking the calculated field button
• Clicking the edit icon for an existing calculated field
• Using the context menu in the tree view within the calculated field dialog

Event Parameters:

• dataSourceSettings: Contains the current data source configuration
• actionName: Identifies the specific action the user is attempting. Possible values:
• "Open calculated field dialog"
• "Edit calculated field"
• "Calculated field context menu"
• fieldInfo: Provides information about the selected field
• cancel: A boolean property that allows you to prevent the current action from completing

Example: Prevent specific field editing

actionBegin(args: any) {
  if (args.actionName === 'Edit calculated field') {
    if (args.fieldInfo.name === 'ProtectedField') {
      args.cancel = true;
      console.log('This calculated field cannot be edited');
    }
  }
}

ActionComplete Event

The actionComplete event enables you to track when calculated field operations are successfully completed. This event is useful for performing additional actions or logging activities after users create or modify calculated fields.

Event Parameters:

• dataSourceSettings: Contains the updated data source configuration after the operation
• actionName: Identifies the specific action completed. Possible values:
• "Calculated field applied" (when created)
• "Calculated field edited" (when modified)
• fieldInfo: Provides information about the selected field
• actionInfo: Contains detailed information about the completed action

Example: Log completed operations

actionComplete(args: any) {
  if (args.actionName === 'Calculated field applied') {
    console.log('Calculated field successfully created:', args.actionInfo);
  }
  
  if (args.actionName === 'Calculated field edited') {
    console.log('Calculated field successfully updated');
  }
}

ActionFailure Event

The actionFailure event is triggered when a UI action fails to produce the expected result. This event provides detailed information about the failure.

Event Parameters:

• actionName: Identifies the failed action
• errorInfo: Contains the error information

Example:

actionFailure(args: any) {
  console.error('Action failed:', args.actionName, args.errorInfo);
}

---

Best Practices

1. Enable CalculatedFieldService: Always inject the CalculatedFieldService provider when using calculated fields 2. Use Meaningful Names: Give calculated fields descriptive names that reflect their purpose (e.g., 'ProfitMargin', 'AvgOrderValue') 3. Validate Formulas: Use the calculatedFieldCreate event to validate formulas before creation 4. Apply Formats: Always format calculated fields with appropriate number formats (Currency, Percentage, etc.) 5. Document Complex Formulas: Add comments explaining complex formula logic for maintainability 6. Test Edge Cases: Verify calculated field behavior with edge case data values 7. Use Aggregation Functions: Leverage Sum, Count, Avg, Min, Max functions in formulas for dynamic calculations

Core Concepts & Data Binding

⚠️ 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
• Data Binding Types
• JSON Data Binding
• CSV Data Binding
• Remote Data Binding
• OLAP Data Binding
• Choosing the Right Data Source

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
this.dataSourceSettings = {
    dataSource: this.localDataArray,
    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
this.dataSourceSettings = {
    dataSource: new DataManager({
        url: environment.apiEndpoint,  // Use environment config
        adaptor: new WebApiAdaptor(),
        headers: [
            { 'Authorization': `Bearer ${this.authToken}` },
            { 'X-API-Key': environment.apiKey }
        ]
    }),
    // ... configuration
};

3. Validate and Sanitize Data

Always validate data received from external sources:

this.http.get(trustedEndpoint).subscribe((data: any) => {
    // Validate data structure
    if (!this.isValidDataStructure(data)) {
        console.error('Invalid data structure received');
        return;
    }
    
    // Sanitize data before binding
    const sanitizedData = this.sanitizeData(data);
    this.dataSourceSettings = { dataSource: sanitizedData };
});

4. Use Environment Configuration

Store API endpoints in environment files, never hardcode:

// environment.ts
export const environment = {
    production: false,
    apiEndpoint: 'https://your-trusted-api.com/data',
    allowedOrigins: ['https://your-domain.com']
};

// component
import { environment } from '../environments/environment';

this.dataSourceSettings = {
    url: environment.apiEndpoint  // Controlled endpoint
};

5. Implement Server-Side Validation

Process and validate data on your backend before sending to client:

// Backend API should:
// - Authenticate requests
// - Validate data sources
// - Sanitize output
// - Implement rate limiting
// - Log access attempts

Security Risks to Avoid

Data Binding Types

The Pivot Table supports four primary data binding approaches, each optimized for different scenarios and data volumes:

1. JSON (Local & Remote)

• Best for small to medium datasets (up to 100K rows)
• Works with in-memory arrays or remote JSON endpoints
• Client-side processing using the built-in Pivot Engine
• Fastest setup and deployment

2. CSV (Local & Remote)

• Compact format (approximately half the size of JSON)
• Good for relational data and spreadsheet-style datasets
• Requires conversion to string arrays for local data
• Reduced bandwidth usage for remote sources

3. OLAP (Cube-Based)

• Designed for pre-aggregated, multi-dimensional data
• Connects to SQL Server Analysis Services (SSAS), Mondrian, or other OLAP servers
• Ideal for enterprise BI scenarios
• Supports complex hierarchies and calculated members

4. Server-Side Pivot Engine

• Processes data on the ASP.NET Core server instead of the client
• Essential for large datasets (100K+ rows)
• Reduces network traffic by sending only viewport data
• Best performance for complex operations

---

JSON Data Binding

JSON is the default data type for the Pivot Table. Binding JSON data provides flexible options for both local in-memory data and remote sources.

Binding Local JSON Array

The simplest approach is to assign a local variable containing JSON data to the `dataSource` property:

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

@Component({
    selector: 'app-container',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        this.dataSourceSettings = {
            dataSource: [
                { 'Sold': 31, 'Amount': 52824, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q1' }
            ],
            rows: [{ name: 'Country' }],
            columns: [{ name: 'Year' }],
            values: [{ name: 'Amount', type: 'Sum' }]
        };
    }
}

Using DataManager with JsonAdaptor

For more flexible data management, use the `DataManager` with JsonAdaptor:

import { Component, OnInit } from '@angular/core';
import { DataManager, JsonAdaptor } from '@syncfusion/ej2-data';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

@Component({
    selector: 'app-container',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        let data = [
            { 'Month': 'Jan', 'Company': 'Jet Airways', 'Profit': 21, 'Loss': -1 }
        ];

        this.dataSourceSettings = {
            dataSource: new DataManager({
                data: data,
                adaptor: new JsonAdaptor
            }),
            rows: [{ name: 'Month' }],
            columns: [{ name: 'Company' }],
            values: [
                { name: 'Profit', type: 'Sum' },
                { name: 'Loss', type: 'Sum' }
            ]
        };
    }
}

Loading JSON from File Upload

To load JSON data from a local *.json file using the file uploader:

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { Uploader } from '@syncfusion/ej2-inputs';

@Component({
    selector: 'app-container',
    template: `
        <input type="file" id="fileupload">
        <ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>
    `
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;
    public uploadObj: Uploader;

    ngOnInit(): void {
        // Initialize the file uploader
        this.uploadObj = new Uploader({});
        this.uploadObj.appendTo('#fileupload');

        let input = document.querySelector('input[type="file"]');
        // Listen for file upload
        input.addEventListener('change', (e: Event) => {
            let reader = new FileReader();
            reader.onload = () => {
                // Parse the JSON string and bind to Pivot Table
                let result = JSON.parse(reader.result as string);
                this.dataSourceSettings = {
                    dataSource: result,
                    rows: [{ name: 'Country' }],
                    columns: [{ name: 'Product' }],
                    values: [{ name: 'Sales', type: 'Sum' }]
                };
            };
            reader.readAsText((input as any).files[0]);
        });
    }
}

Binding Remote JSON Data

⚠️ SECURITY WARNING: Only use trusted, authenticated endpoints. Never bind to user-provided or untrusted URLs.

To connect to a trusted and authenticated remote JSON endpoint, set the `url` property:

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { environment } from '../environments/environment';

@Component({
    selector: 'app-container',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        // ⚠️ SECURITY: Only use controlled, trusted endpoints
        // Store API URLs in environment configuration
        this.dataSourceSettings = {
            url: environment.trustedApiEndpoint,  // Use environment config
            rows: [{ name: 'Country' }],
            columns: [{ name: 'Product' }],
            values: [{ name: 'Sales', type: 'Sum' }]
        };
    }
}

Security Checklist for Remote JSON:

• ✅ Use HTTPS only
• ✅ Implement authentication (API keys, tokens)
• ✅ Store endpoints in environment config
• ✅ Validate data structure after fetching
• ✅ Implement CORS policies
• ❌ Never use user-provided URLs
• ❌ Never use untrusted public endpoints

---

CSV Data Binding

CSV (Comma-Separated Values) format provides a compact alternative to JSON, using approximately half the bandwidth. Set the `type` property to CSV.

Binding Local CSV Data

To bind local CSV data, convert it to a string array:

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

@Component({
    selector: 'app-container',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        // CSV data as string array
        let csvData = [
            ['Country', 'Product', 'Sales', 'Year'],
            ['USA', 'Laptop', '5000', '2023']
        ];

        this.dataSourceSettings = {
            dataSource: csvData,
            type: 'CSV',
            rows: [{ name: 'Country' }],
            columns: [{ name: 'Product' }],
            values: [{ name: 'Sales', type: 'Sum' }]
        };
    }
}

Loading CSV from File Upload

To load CSV data from a local *.csv file:

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { Uploader } from '@syncfusion/ej2-inputs';

@Component({
    selector: 'app-container',
    template: `
        <input type="file" id="fileupload">
        <ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>
    `
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        const input = document.querySelector('input[type="file"]');
        input.addEventListener('change', (e: Event) => {
            let reader = new FileReader();
            reader.onload = () => {
                // Convert CSV string to string array
                let csvArray = (reader.result as string).split('\n').map(line => line.split(','));
                this.dataSourceSettings = {
                    dataSource: csvArray,
                    type: 'CSV',
                    rows: [{ name: 'Country' }],
                    columns: [{ name: 'Product' }],
                    values: [{ name: 'Sales', type: 'Sum' }]
                };
            };
            reader.readAsText((input as any).files[0]);
        });
    }
}

Binding Remote CSV Data

⚠️ SECURITY WARNING: Only use trusted, authenticated CSV endpoints under your control.

To connect to a trusted and authenticated remote CSV file or endpoint:

import { environment } from '../environments/environment';

// ⚠️ SECURITY: Only use controlled, trusted endpoints
this.dataSourceSettings = {
    url: environment.trustedCsvEndpoint,  // Use environment config, not arbitrary URLs
    type: 'CSV',
    rows: [{ name: 'Country' }],
    columns: [{ name: 'Product' }],
    values: [{ name: 'Sales', type: 'Sum' }]
};

Security Requirements for Remote CSV:

• ✅ Use authenticated backend endpoints
• ✅ Validate CSV content on server-side
• ✅ Implement rate limiting
• ✅ Use HTTPS only
• ❌ Never accept user-uploaded CSV URLs directly
• ❌ Never use public, unverified CSV sources

---

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 connects to web services and external data sources through various adaptor types.

OData Service Binding

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

Connect to authenticated OData (Open Data Protocol) services using the default `ODataAdaptor`:

import { Component, OnInit } from '@angular/core';
import { DataManager, ODataAdaptor } from '@syncfusion/ej2-data';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { environment } from '../environments/environment';

@Component({
    selector: 'app-container',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        // ⚠️ SECURITY: Only connect to your own authenticated OData services
        // The URL below is for demonstration only - replace with your secured endpoint
        this.dataSourceSettings = {
            dataSource: new DataManager({
                url: environment.odataEndpoint,  // Use your authenticated OData service
                adaptor: new ODataAdaptor(),
                headers: [
                    { 'Authorization': `Bearer ${this.getAuthToken()}` }
                ]
            }),
            rows: [{ name: 'CustomerID' }],
            columns: [{ name: 'OrderDate' }],
            values: [{ name: 'Freight', type: 'Sum' }]
        };
    }

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

OData V4 Service Binding

⚠️ Use only with your own authenticated OData V4 services.

For OData V4 services, use the `ODataV4Adaptor`:

import { DataManager, ODataV4Adaptor } from '@syncfusion/ej2-data';
import { environment } from '../environments/environment';

// ⚠️ SECURITY: Only use your own authenticated OData V4 endpoint
this.dataSourceSettings = {
    dataSource: new DataManager({
        url: environment.odataV4Endpoint,  // Your authenticated service
        adaptor: new ODataV4Adaptor(),
        headers: [
            { 'Authorization': `Bearer ${this.getAuthToken()}` }
        ]
    }),
    rows: [{ name: 'CustomerID' }],
    columns: [{ name: 'OrderDate' }],
    values: [{ name: 'Freight', type: 'Sum' }]
};

Web API Binding

⚠️ Use only with your own authenticated RESTful Web APIs.

Connect to authenticated RESTful Web APIs using the `WebApiAdaptor`:

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

// ⚠️ SECURITY: Only use your own authenticated Web API endpoint
this.dataSourceSettings = {
    dataSource: new DataManager({
        url: environment.webApiEndpoint,  // Your authenticated Web API
        adaptor: new WebApiAdaptor(),
        headers: [
            { 'Authorization': `Bearer ${this.getAuthToken()}` },
            { 'X-API-Key': environment.apiKey }
        ]
    }),
    rows: [{ name: 'Region' }],
    columns: [{ name: 'Product' }],
    values: [{ name: 'Sales', type: 'Sum' }]
};

Dynamic Data Loading with HttpClient

⚠️ SECURITY WARNING: Always validate and sanitize data from remote sources.

For more control over data fetching with proper security measures, use Angular's HttpClient:

import { HttpClient, HttpHeaders } from '@angular/common/http';
import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { environment } from '../environments/environment';

@Component({
    selector: 'app-container',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel = {};

    constructor(private http: HttpClient) { }

    ngOnInit(): void {
        // ⚠️ SECURITY: Use authenticated endpoints with validation
        const headers = new HttpHeaders({
            'Authorization': `Bearer ${this.getAuthToken()}`,
            'Content-Type': 'application/json'
        });

        // Use environment-configured endpoint
        this.http.get(environment.trustedApiEndpoint, { headers }).subscribe({
            next: (data: any) => {
                // Validate data structure before binding
                if (this.validateDataStructure(data)) {
                    // Sanitize data before use
                    const sanitizedData = this.sanitizeData(data.records);
                    this.dataSourceSettings = {
                        dataSource: sanitizedData,
                        rows: [{ name: 'Region' }],
                        columns: [{ name: 'Product' }],
                        values: [{ name: 'Sales', type: 'Sum' }]
                    };
                } else {
                    console.error('Invalid data structure received');
                }
            },
            error: (error) => {
                console.error('Failed to fetch data:', error);
                // Handle error appropriately
            }
        });
    }

    private getAuthToken(): string {
        // Retrieve token from secure storage
        return localStorage.getItem('authToken') || '';
    }

    private validateDataStructure(data: any): boolean {
        // Implement validation logic
        return data && Array.isArray(data.records);
    }

    private sanitizeData(data: any[]): any[] {
        // Implement data sanitization
        // Remove or escape potentially harmful content
        return data.map(record => ({
            ...record,
            // Sanitize string fields to prevent XSS
        }));
    }
}

Security Best Practices with HttpClient: 1. ✅ Use authentication headers (Bearer tokens, API keys) 2. ✅ Store endpoints in environment configuration 3. ✅ Validate data structure before binding 4. ✅ Sanitize all data fields 5. ✅ Implement proper error handling 6. ✅ Use HTTPS only 7. ❌ Never use user-provided URLs 8. ❌ Never skip data validation

OLAP Data Binding

OLAP (Online Analytical Processing) connects to cube-based data sources like SQL Server Analysis Services (SSAS) or Mondrian. OLAP data is pre-aggregated and organized in multi-dimensional hierarchies.

OLAP Data Source Configuration

Configure the Pivot Table to connect to an OLAP cube:

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

@Component({
    selector: 'app-container',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        this.dataSourceSettings = {
            catalog: 'Adventure Works DW 2008 SE',
            cube: 'Adventure Works',
            providerType: 'SSAS',
            enableSorting: true,
            url: 'https://bi.syncfusion.com/olap/msmdpump.dll',
            localeIdentifier: 1033
        };
    }
}

Adding OLAP Cube Elements

Define rows, columns, values, and filters using OLAP hierarchies and measures:

this.dataSourceSettings = {
    catalog: 'Adventure Works DW 2008 SE',
    cube: 'Adventure Works',
    providerType: 'SSAS',
    url: 'https://bi.syncfusion.com/olap/msmdpump.dll',
    rows: [
        { name: '[Customer].[Customer Geography].[Country]', caption: 'Country' }
    ],
    columns: [
        { name: '[Product].[Product Categories].[Category]', caption: 'Product Category' }
    ],
    values: [
        { name: '[Measures].[Customer Count]', caption: 'Customer Count' },
        { name: '[Measures].[Internet Sales Amount]', caption: 'Internet Sales Amount' }
    ],
    filters: []
};

When to Use OLAP

Best for:

• Multi-dimensional analysis
• Pre-aggregated enterprise data
• Complex hierarchies (Year → Quarter → Month)
• Large data volumes with fast query performance
• Organizations using SSAS or other OLAP servers

Not ideal for:

• Small datasets (overhead of cube infrastructure)
• Real-time transaction data
• Simple relational data analysis

Server-Side Pivot Engine

For very large datasets (100K+ rows), the server-side Pivot Engine processes data on an ASP.NET Core server, reducing client-side load and improving performance.

Prerequisites

1. Download and install the Server-side Pivot Engine from GitHub 2. Ensure the application includes:

• PivotController.cs: Handles client-server communication
• DataSource.cs: Defines data source models
• Syncfusion.Pivot.Engine NuGet package

Connecting to Server-Side Engine

Configure the Pivot Table to use server-side mode:

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

@Component({
    selector: 'app-container',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        this.dataSourceSettings = {
            url: 'https://localhost:44350/api/pivot/post',
            mode: 'Server',
            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' }
            ]
        };
    }
}

Server-Side Data Configuration

Define your data source in the server-side application's DataSource.cs:

public class PivotViewData
{
    public string ProductID { get; set; }
    public string Country { get; set; }
    public string Product { get; set; }
    public double Sold { get; set; }
    public double Price { get; set; }
    public string Year { get; set; }

    public List<PivotViewData> GetData()
    {
        // Return data from database, CSV, JSON, or collection
    }
}

In PivotController.cs:

private PivotEngine<PivotViewData> pivotEngine = new PivotEngine<PivotViewData>();

[HttpPost]
public async Task<object> GetData(FetchData param)
{
    return await _cache.GetOrCreateAsync("dataSource" + param.Hash, async (cacheEntry) =>
    {
        cacheEntry.SetSize(1);
        cacheEntry.AbsoluteExpiration = DateTimeOffset.UtcNow.AddMinutes(60);
        return new DataSource.PivotViewData().GetData();
    });
}

Supported Server-Side Data Sources

• Collection: List<T> or IEnumerable
• JSON: From *.json files or API endpoints
• CSV: From *.csv files or web services
• DataTable: From SQL Server or other databases
• Dynamic: From any custom data source

---

Choosing the Right Data Source

⚠️ SECURITY NOTE: Always prioritize security when choosing data sources. Use local data when possible, and implement proper authentication and validation for remote sources.

Performance Considerations

Client-Side (JSON/CSV):

• ✓ Instant local filtering/sorting
• ✓ Works offline
• ✗ Limited to ~100K rows
• ✗ Full dataset in memory

Server-Side Engine:

• ✓ Handles millions of rows
• ✓ Server performs heavy lifting
• ✓ Only viewport data sent to client
• ✗ Requires ASP.NET Core backend
• ✗ Network latency for operations

OLAP:

• ✓ Lightning-fast pre-aggregated queries
• ✓ Complex hierarchies
• ✗ Setup overhead
• ✗ Not for real-time data

---

Security Summary

✅ Recommended Secure Practices

1. Default to Local Data: Use in-memory arrays whenever possible 2. Authenticate Remote Sources: Always use authentication headers for remote data 3. Environment Configuration: Store all endpoints in environment files 4. Data Validation: Validate all external data before binding 5. HTTPS Only: Never use HTTP for remote connections 6. Error Handling: Implement proper error handling for failed requests 7. Rate Limiting: Implement rate limiting on backend APIs 8. Audit Logging: Log all data access attempts

❌ Security Anti-Patterns to Avoid

1. Never accept user-provided URLs for data sources 2. Never bind to untrusted public APIs without validation 3. Never skip authentication on remote endpoints 4. Never hardcode API keys or tokens in source code 5. Never trust external data without validation 6. Never use HTTP for sensitive data transmission 7. Never expose internal API endpoints publicly without authentication 8. Never ignore CORS policies - they exist for security

🔒 Implementation Checklist

Before implementing remote data binding:

• [ ] Is the data source trusted and under my control?
• [ ] Have I implemented authentication (tokens, API keys)?
• [ ] Are endpoints stored in environment configuration?
• [ ] Have I implemented data validation and sanitization?
• [ ] Am I using HTTPS for all remote connections?
• [ ] Have I implemented proper error handling?
• [ ] Have I added rate limiting to backend APIs?
• [ ] Have I tested with malicious data inputs?

If you answered "No" to any of these, use local data instead or implement the missing security measures before proceeding.

Data Formatting

Table of Contents

• Number Formatting
• Format Type Codes
• Custom Formats
• Format Settings Properties
• Runtime Number Formatting
• Conditional Formatting
• Conditional Format Conditions
• Style Properties
• Runtime Conditional Formatting

---

Number Formatting

Format Settings Configuration

Define number formats using the formatSettings property in dataSourceSettings:

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

@Component({
    selector: 'app-pivot',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        this.dataSourceSettings = {
            dataSource: data,
            rows: [{ name: 'Region' }],
            columns: [{ name: 'Year' }],
            values: [
                { name: 'Sales', type: 'Sum' },
                { name: 'Quantity', type: 'Count' }
            ],
            formatSettings: [
                { name: 'Sales', format: 'C2', useGrouping: true, currency: 'USD' },
                { name: 'Quantity', format: 'N0' }
            ]
        };
    }
}

---

Format Type Codes

---

Custom Formats

Use format specifiers (0, #, %, $, ;) for custom patterns:

formatSettings: [
    { name: 'Sales', format: '$#,##0.00' },        // $1,234.56
    { name: 'Revenue', format: '#,##0,, "M"' },    // 1.2M (millions)
    { name: 'Rate', format: '0.00%' },             // 12.34%
    { name: 'Variance', format: '#,##0;(#,##0)' }  // Negative: (1,234)
]

---

Format Settings Properties

Note: When using custom format, useGrouping and currency are ignored.

---

Runtime Number Formatting

Enable users to apply formatting through the toolbar dialog:

import { Component } from '@angular/core';
import { NumberFormattingService } from '@syncfusion/ej2-angular-pivotview';

@Component({
    selector: 'app-pivot',
    template: `<ejs-pivotview 
        [dataSourceSettings]=dataSourceSettings
        [allowNumberFormatting]="true"
        [showToolbar]="true"
        [toolbar]="['NumberFormatting']">
    </ejs-pivotview>`,
    providers: [NumberFormattingService]
})
export class AppComponent {
    public dataSourceSettings: any = { /* config */ };
}

Open dialog programmatically:

// this.pivot is ViewChild reference
this.pivot.showNumberFormattingDialog();

---

Conditional Formatting

Code-Based Configuration

Apply formatting rules using conditionalFormatSettings:

dataSourceSettings: DataSourceSettingsModel = {
    dataSource: data,
    rows: [{ name: 'Region' }],
    columns: [{ name: 'Year' }],
    values: [{ name: 'Sales', type: 'Sum' }],
    conditionalFormatSettings: [
        {
            measure: 'Sales',
            conditions: 'GreaterThan',
            value1: 100000,
            style: {
                backgroundColor: '#4CAF50',
                color: '#FFF',
                fontFamily: 'Arial',
                fontSize: '13px'
            }
        },
        {
            measure: 'Sales',
            conditions: 'LessThan',
            value1: 50000,
            style: {
                backgroundColor: '#F44336',
                color: '#FFF'
            }
        }
    ]
};

Apply to all value fields (omit measure):

conditionalFormatSettings: [
    {
        conditions: 'GreaterThan',
        value1: 500000,
        style: { backgroundColor: '#FFD700', color: '#000' }
    }
]

---

Conditional Format Conditions

---

Style Properties

Customize cell appearance with the style object:

style: {
    backgroundColor: '#4CAF50',     // Hex or RGB color
    color: '#FFF',                  // Text color
    fontFamily: 'Arial',            // Font family
    fontSize: '13px'                // Font size with unit
}

---

Runtime Conditional Formatting

Enable users to create formatting rules at runtime:

import { Component } from '@angular/core';
import { ConditionalFormattingService } from '@syncfusion/ej2-angular-pivotview';

@Component({
    selector: 'app-pivot',
    template: `<ejs-pivotview 
        [dataSourceSettings]=dataSourceSettings
        [allowConditionalFormatting]="true"
        [showToolbar]="true"
        [toolbar]="['ConditionalFormatting']"
        (conditionalFormatting)="onConditionalFormatting($event)">
    </ejs-pivotview>`,
    providers: [ConditionalFormattingService]
})
export class AppComponent {
    public dataSourceSettings: any = { /* config */ };

    onConditionalFormatting(args: any) {
        // Validate or modify formatting before applying
        if (args.measure === 'InternalField') {
            args.cancel = true;  // Prevent formatting
        }
    }
}

Open dialog programmatically:

this.pivot.showConditionalFormattingDialog();

Database Connectivity

⚠️ CRITICAL SECURITY NOTICE

All database connections MUST be handled through authenticated backend APIs. Never expose database connection strings or credentials to the client-side application. This documentation assumes you have:

✅ A secure backend API (ASP.NET Core, Node.js, etc.) ✅ Proper authentication and authorization ✅ Server-side database connection handling ✅ Input validation and SQL injection prevention

❌ NEVER connect directly to databases from the browser ❌ NEVER expose connection strings in client code ❌ NEVER accept user-provided database endpoints

Table of Contents

• Connection Patterns
• SQL Server
• MySQL
• PostgreSQL
• MongoDB
• Elasticsearch
• Oracle
• Snowflake
• Connection Best Practices

Connection Patterns

Server-Side Engine Connection

⚠️ SECURITY: This pattern uses a secure backend API. Never expose database connections to the client.

// Client-side: Configure AUTHENTICATED endpoint only
import { environment } from '../environments/environment';

dataSourceSettings: IDataOptions = {
  url: environment.apiEndpoint,  // Use environment config, not hardcoded URLs
  mode: 'Server',
  rows: [{ name: 'Region' }],
  columns: [{ name: 'Year' }],
  values: [{ name: 'Sales', type: 'Sum' }]
};

// Server-side: C# ASP.NET Core
[HttpPost("api/pivot/data")]
public IActionResult GetPivotData(PivotViewDataOptions options)
{
  var connectionString = "your_db_connection_string";
  var data = FetchDataFromDatabase(connectionString);
  
  var pivotEngine = new PivotEngine();
  var result = pivotEngine.ProcessJsonData(data, options);
  
  return Ok(result);
}

---

SQL Server

Basic Connection

// C# Connection String
string connectionString = "Server=server_name;" +
  "Database=database_name;" +
  "User Id=sa;" +
  "Password=your_password;";

using (SqlConnection conn = new SqlConnection(connectionString))
{
  conn.Open();
  string query = "SELECT * FROM SalesData WHERE Year = 2024";
  SqlCommand cmd = new SqlCommand(query, conn);
  SqlDataReader reader = cmd.ExecuteReader();
  // Read and process data
}

API Endpoint

[HttpPost]
public IActionResult GetSalesData(PivotViewDataOptions options)
{
  var connectionString = Configuration.GetConnectionString("DefaultConnection");
  
  using (SqlConnection conn = new SqlConnection(connectionString))
  {
    conn.Open();
    
    // Build dynamic query based on filters
    string query = BuildDynamicQuery(options);
    
    SqlCommand cmd = new SqlCommand(query, conn);
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    
    // Convert to JSON
    var jsonData = ConvertDataTableToJson(dt);
    var engine = new PivotEngine();
    var result = engine.ProcessJsonData(jsonData, options);
    
    return Ok(result);
  }
}

Angular Client

@Component({
  selector: 'app-sql-pivot'
})
export class AppComponent {
  dataSourceSettings: IDataOptions = {
    url: 'https://your-server/api/sales-data',
    mode: 'Server',
    rows: [{ name: 'Region' }],
    columns: [{ name: 'Year' }],
    values: [{ name: 'Sales', type: 'Sum' }]
  };
}

---

MySQL

Connection Setup

// C# Connection String
string connectionString = "Server=localhost;" +
  "Database=sales_db;" +
  "User Id=root;" +
  "Password=password;";

using (MySqlConnection conn = new MySqlConnection(connectionString))
{
  conn.Open();
  MySqlCommand cmd = new MySqlCommand("SELECT * FROM sales", conn);
  // Execute query
}

Query Data

public List<dynamic> GetMySQLData()
{
  var connector = "Server=mysql-server;Database=analytics;User Id=user;Password=pass;";
  var data = new List<dynamic>();
  
  using (var conn = new MySqlConnection(connector))
  {
    conn.Open();
    var query = "SELECT Region, Year, SUM(Sales) as Sales FROM sales_data GROUP BY Region, Year";
    var cmd = new MySqlCommand(query, conn);
    
    using (var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        data.Add(new {
          Region = reader["Region"],
          Year = reader["Year"],
          Sales = reader["Sales"]
        });
      }
    }
  }
  
  return data;
}

---

PostgreSQL

Connection Setup

// C# Connection String
string connectionString = "Host=localhost;" +
  "Username=postgres;" +
  "Password=password;" +
  "Database=sales_db;";

using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
  conn.Open();
  NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM sales", conn);
  // Execute query
}

Aggregate Query

public List<dynamic> GetPostgreSQLData()
{
  var connectionString = "Host=pg-server;Database=analytics;User Id=analyst;Password=pwd;";
  var data = new List<dynamic>();
  
  using (var conn = new NpgsqlConnection(connectionString))
  {
    conn.Open();
    var query = @"
      SELECT 
        region,
        EXTRACT(YEAR FROM transaction_date) as year,
        SUM(amount) as sales,
        COUNT(*) as transactions
      FROM transactions
      WHERE transaction_date >= NOW() - INTERVAL '2 years'
      GROUP BY region, year
      ORDER BY year, region
    ";
    
    using (var cmd = new NpgsqlCommand(query, conn))
    {
      using (var reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
          data.Add(new {
            Region = reader["region"],
            Year = reader["year"],
            Sales = reader["sales"],
            Transactions = reader["transactions"]
          });
        }
      }
    }
  }
  
  return data;
}

---

MongoDB

Connection & Query

// C# MongoDB
var connectionString = "mongodb://localhost:27017";
var client = new MongoClient(connectionString);
var database = client.GetDatabase("sales_db");
var collection = database.GetCollection<BsonDocument>("sales");

var filter = Builders<BsonDocument>.Filter.Gte("amount", 1000);
var documents = collection.Find(filter).ToList();

Aggregation Pipeline

public List<dynamic> GetMongoDBData()
{
  var connectionString = "mongodb+srv://user:pwd@cluster.mongodb.net";
  var client = new MongoClient(connectionString);
  var database = client.GetDatabase("warehouse");
  var collection = database.GetCollection<BsonDocument>("orders");
  
  var pipeline = new[]
  {
    new BsonDocument("$match", new BsonDocument("status", "completed")),
    new BsonDocument("$group", new BsonDocument
    {
      { "_id", new BsonDocument("region", "$region").Add("year", new BsonDocument("$year", "$orderDate")) },
      { "totalSales", new BsonDocument("$sum", "$amount") },
      { "count", new BsonDocument("$sum", 1) }
    }),
    new BsonDocument("$project", new BsonDocument
    {
      { "region", "$_id.region" },
      { "year", "$_id.year" },
      { "Sales", "$totalSales" },
      { "Count", "$count" }
    })
  };
  
  var results = collection.Aggregate<dynamic>(pipeline).ToList();
  return results;
}

---

Elasticsearch

Connection & Query

// C# Elasticsearch
var settings = new ConnectionSettings(new Uri("http://localhost:9200"))
  .DefaultIndex("sales");
var client = new ElasticClient(settings);

var response = client.Search<SalesData>(s => s
  .Query(q => q
    .Match(m => m
      .Field("status")
      .Query("completed")
    )
  )
);

Aggregation Query

public List<dynamic> GetElasticsearchData()
{
  var settings = new ConnectionSettings(new Uri("https://elastic-cloud.example.com"))
    .ApiKeyAuthentication("api_key_id", "api_key");
  
  var client = new ElasticClient(settings);
  
  var response = client.Search<dynamic>(s => s
    .Index("sales-*")
    .Size(1000)
  );
  
  return ExtractAggregationResults(response);
}

---

Oracle

Connection Setup

// C# Oracle
string connectionString = "Data Source=oracle_db;User Id=scott;Password=tiger;";

using (OracleConnection conn = new OracleConnection(connectionString))
{
  conn.Open();
  OracleCommand cmd = new OracleCommand("SELECT * FROM SALES", conn);
  // Execute query
}

Query Data

public List<dynamic> GetOracleData()
{
  var connectionString = "Data Source=ORACL12C;User Id=admin;Password=pwd;";
  var data = new List<dynamic>();
  
  using (var conn = new OracleConnection(connectionString))
  {
    conn.Open();
    var query = @"
      SELECT 
        region,
        EXTRACT(YEAR FROM transaction_date) as year,
        SUM(amount) as sales,
        COUNT(*) as count
      FROM transactions
      WHERE TRUNC(transaction_date) >= TRUNC(SYSDATE - 730)
      GROUP BY region, EXTRACT(YEAR FROM transaction_date)
    ";
    
    using (var cmd = new OracleCommand(query, conn))
    {
      using (var reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
          data.Add(new {
            Region = reader["region"],
            Year = reader["year"],
            Sales = reader["sales"]
          });
        }
      }
    }
  }
  
  return data;
}

---

Snowflake

Connection & Query

// C# Snowflake
using (IDbConnection conn = new SnowflakeDbConnection())
{
  conn.ConnectionString = "account=xy12345;user=user_name;" +
    "password=user_password;warehouse=compute_wh;database=SALES_DB;";
  
  conn.Open();
  using (IDbCommand cmd = conn.CreateCommand())
  {
    cmd.CommandText = "SELECT * FROM SALES_TABLE";
    using (IDataReader reader = cmd.ExecuteReader())
    {
      // Read results
    }
  }
}

Cloud Data Warehouse Query

public List<dynamic> GetSnowflakeData()
{
  var connectionString = "account=cloud;user=analyst;" +
    "password=secure_pwd;warehouse=analytics;database=DW;";
  
  var data = new List<dynamic>();
  
  using (var conn = new SnowflakeDbConnection())
  {
    conn.ConnectionString = connectionString;
    conn.Open();
    
    var query = @"
      SELECT 
        REGION,
        YEAR(TRANSACTION_DATE) as YEAR,
        SUM(AMOUNT) as SALES,
        COUNT(*) as TRANSACTIONS
      FROM TRANSACTIONS
      WHERE TRANSACTION_DATE >= DATEADD(year, -2, CURRENT_DATE)
      GROUP BY REGION, YEAR(TRANSACTION_DATE)
      ORDER BY REGION, YEAR
    ";
    
    using (var cmd = new SnowflakeDbCommand(query, (SnowflakeDbConnection)conn))
    {
      using (var reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
          data.Add(new {
            Region = reader["REGION"],
            Year = reader["YEAR"],
            Sales = reader["SALES"]
          });
        }
      }
    }
  }
  
  return data;
}

---

Connection Best Practices

1. Use Connection Pooling

// Connection pooling enabled by default in most drivers
// Reuse connections efficiently

2. Secure Credentials

// Use Azure Key Vault or environment variables
var connectionString = Environment.GetEnvironmentVariable("DB_CONNECTION_STRING");
// Never hardcode credentials

3. Query Optimization

// Pre-aggregate data at database level
// Filter early (WHERE clause before grouping)
// Limit columns returned (SELECT specific columns)

string query = "SELECT region, year, SUM(amount) FROM sales " +
  "WHERE status='completed' " +
  "GROUP BY region, year";

4. Error Handling

try
{
  using (var conn = new SqlConnection(connectionString))
  {
    conn.Open();
    // Execute queries
  }
}
catch (SqlException ex)
{
  logger.Error("Database error: " + ex.Message);
  throw;
}
finally
{
  // Connection automatically closed and returned to pool
}

5. Caching

// Cache query results to reduce database load
var cache = new MemoryCache(new MemoryCacheOptions());
const string CACHE_KEY = "pivot_data";

if (!cache.TryGetValue(CACHE_KEY, out List<dynamic> data))
{
  data = FetchFromDatabase();
  cache.Set(CACHE_KEY, data, TimeSpan.FromHours(1));
}

return data;

Drill Down & Drill Up

Table of Contents

• Drill Down & Drill Up
• Drill Position
• Expand All Features
• Expand/Collapse Specific Members
• Drill Events

---

Drill Down & Drill Up Overview

The drill-down and drill-up features allow users to expand or collapse hierarchical data for detailed or summarized views. When a field member contains child items, expand and collapse icons automatically appear in the corresponding row or column header.

Automatic Drill Icons

When field members contain child items, expand (▼) and collapse (▲) icons automatically appear. Clicking these icons expands the selected item to display child members or collapses it to show a summarized view.

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

@Component({
    selector: 'app-pivot',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        this.dataSourceSettings = {
            dataSource: data,
            rows: [
                { name: 'Country' },      // Parent level
                { name: 'Region' }        // Child level (can drill)
            ],
            columns: [{ name: 'Year' }],
            values: [{ name: 'Sales', type: 'Sum' }]
        };
    }
}

Use Cases

• Analyze overall sales by country, then drill into specific regional details
• View annual performance, then expand to quarterly and monthly views
• Examine top-level summaries before looking at detailed transactions
• Navigate hierarchical data without losing the broader context

---

Drill Position

The drill-down and drill-up features allow you to expand or collapse data for a specific member without affecting the same member in other positions. For example, if both "FY 2015" and "FY 2016" have "Quarter 1" as a child, drilling down into "Quarter 1" under "FY 2015" expands only that specific instance, while "Quarter 1" under "FY 2016" remains unchanged.

Key Feature: Position-based drilling makes the pivot table faster and more efficient by only affecting the targeted member.

Example Scenario

// The same "Q1" member appears under both years
// Expanding Q1 under 2015 does NOT expand Q1 under 2016
dataSourceSettings: {
    dataSource: data,
    rows: [{ name: 'Region' }],
    columns: [
        { name: 'Year' },      // 2015, 2016
        { name: 'Quarter' }    // Q1, Q2, Q3, Q4 (per year)
    ],
    values: [{ name: 'Sales', type: 'Sum' }]
};

---

Expand All Features

Expand all features allow programmatic control over the initial state of hierarchical data in the Pivot Table.

Expand All Members

To display all hierarchical members in an expanded state, set the `expandAll` property to true:

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

@Component({
    selector: 'app-pivot',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        this.dataSourceSettings = {
            dataSource: data,
            expandAll: true,  // All hierarchies initially expanded
            rows: [
                { name: 'Country' },
                { name: 'State' },
                { name: 'City' }
            ],
            columns: [{ name: 'Year' }],
            values: [{ name: 'Sales', type: 'Sum' }]
        };
    }
}

Expand Specific Field Headers

To expand all headers for specific fields only, set the `expandAll` property on individual field definitions:

rows: [
    { name: 'Country', expandAll: true },   // All countries expanded
    { name: 'State', expandAll: false }      // States collapsed initially
],
columns: [
    { name: 'Year', expandAll: true }        // All years expanded
]

Collapse All (Default Behavior)

By default, `expandAll` is false, so only top-level field members are shown until the user manually expands them.

// Default behavior - collapsed
dataSourceSettings = {
    expandAll: false,  // or omit this property (false is default)
    rows: [{ name: 'Country' }, { name: 'State' }],
    // Only Country level is visible initially
}

---

Expand/Collapse Specific Members

Control the initial expanded or collapsed state of specific field members using the `drilledMembers` property.

Expand Specific Members Only

To expand only specific members while keeping others collapsed:

import { Component, OnInit } from '@angular/core';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';

@Component({
    selector: 'app-pivot',
    template: `<ejs-pivotview [dataSourceSettings]=dataSourceSettings></ejs-pivotview>`
})
export class AppComponent implements OnInit {
    public dataSourceSettings: DataSourceSettingsModel;

    ngOnInit(): void {
        this.dataSourceSettings = {
            dataSource: data,
            rows: [{ name: 'Country' }, { name: 'Region' }],
            columns: [{ name: 'Year' }],
            values: [{ name: 'Sales', type: 'Sum' }],
            // Only expand France, collapse all others
            drilledMembers: [
                { name: 'Country', items: ['France'] }
            ]
        };
    }
}

Expand All Except Specific Members

To expand all members except for specific ones:

expandAll: true,
drilledMembers: [
    { name: 'Country', items: ['France'] }  // France stays collapsed despite expandAll: true
]

Properties of drilledMembers

Multi-Level Member Specification

Use the delimiter to specify nested members:

drilledMembers: [
    {
        name: 'Geography',
        items: ['USA.North', 'USA.South'],  // Expand specific regions
        delimiter: '.'                       // Parent.Child format
    }
]

---

Drill Events

The Pivot Table provides several events to handle drill operations programmatically.

Drill Event

Triggered each time a field member is expanded or collapsed:

import { Component, ViewChild } from '@angular/core';
import { PivotViewComponent } from '@syncfusion/ej2-angular-pivotview';

@Component({
    selector: 'app-pivot',
    template: `<ejs-pivotview 
      [dataSourceSettings]=dataSourceSettings
      (drill)="onDrill($event)">
    </ejs-pivotview>`
})
export class AppComponent {
    @ViewChild('pivotGrid') pivotGrid: PivotViewComponent;
    
    dataSourceSettings: any = { /* config */ };

    onDrill(args: any) {
        console.log('Drill Info:', args.drillInfo);  // Member being drilled
        console.log('PivotView:', args.pivotview);   // Component reference
        
        // Cancel drill operation if needed
        if (args.drillInfo.name === 'France') {
            args.cancel = true;  // Prevent expanding France
        }
    }
}

Event Parameters:

• drillInfo - Contains information about the currently drilled field member
• pivotview - Reference to the Pivot Table component instance
• cancel - Set to true to prevent the drill action

ActionBegin Event

Triggered when a user starts a drill action (expand/collapse):

actionBegin(args: PivotActionBeginEventArgs) {
    if (args.actionName === 'Drill down') {
        console.log('User expanding:', args.fieldInfo);
    } else if (args.actionName === 'Drill up') {
        console.log('User collapsing:', args.fieldInfo);
    }
    
    // Prevent drill action if needed
    // args.cancel = true;
}

Action Names:

• 'Drill down' - User expanding a member
• 'Drill up' - User collapsing a member

ActionComplete Event

Triggered when a drill action completes successfully:

actionComplete(args: PivotActionCompleteEventArgs) {
    if (args.actionName === 'Drill down') {
        console.log('Expansion completed:', args.actionInfo);
    } else if (args.actionName === 'Drill up') {
        console.log('Collapse completed:', args.actionInfo);
    }
}

ActionFailure Event

Triggered when a drill action fails:

actionFailure(args: PivotActionFailureEventArgs) {
    if (args.actionName === 'Drill down') {
        console.error('Failed to expand:', args.errorInfo);
    } else if (args.actionName === 'Drill up') {
        console.error('Failed to collapse:', args.errorInfo);
    }
}

---

Common Patterns

Pattern 1: Show All Expanded Initially

dataSourceSettings = {
    expandAll: true,  // All levels visible on load
    rows: [{ name: 'Country' }, { name: 'Region' }],
    columns: [{ name: 'Year' }],
    values: [{ name: 'Sales', type: 'Sum' }]
}

Pattern 2: Select Specific Expanded Members

dataSourceSettings = {
    expandAll: false,  // Default collapsed
    drilledMembers: [
        { name: 'Country', items: ['USA', 'Canada'] }  // Only these expanded
    ],
    rows: [{ name: 'Country' }, { name: 'Region' }],
    columns: [{ name: 'Year' }],
    values: [{ name: 'Sales', type: 'Sum' }]
}

Pattern 3: Prevent Expansion of Specific Members

drill(args: any) {
    const restrictedMembers = ['France', 'Germany'];
    if (restrictedMembers.includes(args.drillInfo.name)) {
        args.cancel = true;  // Block expansion
    }
}

Pattern 4: Track Drill Depth

actionBegin(args: PivotActionBeginEventArgs) {
    if (args.actionName === 'Drill down') {
        this.drillDepth++;
        console.log('Current depth:', this.drillDepth);
    } else if (args.actionName === 'Drill up') {
        this.drillDepth--;
    }
}

Export & Printing

Table of Contents

• Excel Export
• CSV Export
• PDF Export
• Print Options
• Common Scenarios

Excel Export

Enable Excel Export

To enable Excel export:

1. Inject ExcelExportService into the Pivot Table 2. Set allowExcelExport to true 3. Call excelExport() method

import { ExcelExportService } from '@syncfusion/ej2-angular-pivotview';

@Component({
  selector: 'app-excel-export',
  providers: [ExcelExportService],
  template: `
    <button (click)="exportToExcel()">Export to Excel</button>
    <ejs-pivotview 
      #pivotGrid
      [dataSourceSettings]="dataSourceSettings"
      [allowExcelExport]="true">
    </ejs-pivotview>
  `
})
export class AppComponent {
  @ViewChild('pivotGrid') pivotGrid: PivotViewComponent;

  exportToExcel() {
    const excelExportProperties = {
      fileName: 'SalesReport.xlsx'
    };
    this.pivotGrid.excelExport(excelExportProperties);
  }

  dataSourceSettings: IDataOptions = {
    dataSource: data,
    rows: [{ name: 'Region' }],
    columns: [{ name: 'Year' }],
    values: [{ name: 'Sales', type: 'Sum' }]
  };
}

Custom File Name & Theme

exportToExcel() {
  const excelExportProperties = {
    fileName: `Report_${new Date().toISOString().split('T')[0]}.xlsx`,
    theme: {
      header: { fontName: 'Calibri', fontSize: 14, bold: true },
      record: { fontName: 'Calibri', fontSize: 12 }
    }
  };
  this.pivotGrid.excelExport(excelExportProperties);
}

Export Multiple Tables

Export to same worksheet or separate worksheets:

// Same worksheet with separation
exportToSameSheet() {
  const excelExportProperties = {
    fileName: 'CombinedReport.xlsx',
    pivotTableIds: ['PivotGrid1', 'PivotGrid2'],
    multipleExport: {
      type: 'AppendToSheet',
      blankRows: 5
    }
  };
  this.pivotGrid1.excelExport(excelExportProperties, true);
}

// Separate worksheets
exportToDifferentSheets() {
  const excelExportProperties = {
    fileName: 'SeparateSheets.xlsx',
    pivotTableIds: ['PivotGrid1', 'PivotGrid2'],
    multipleExport: { type: 'NewSheet' }
  };
  this.pivotGrid1.excelExport(excelExportProperties, true);
}

Customize Cell Styling

Use export events to style cells via the gridSettings object (events belong to gridSettings in the PivotView API):

@Component({
  template: `<ejs-pivotview [gridSettings]="gridSettings"></ejs-pivotview>`
})
export class AppComponent implements OnInit {
  public gridSettings: any;

  ngOnInit(): void {
    this.gridSettings = {
      excelHeaderQueryCellInfo: this.onExcelHeaderCell.bind(this),
      excelQueryCellInfo: this.onExcelQueryCell.bind(this)
    };
  }

  onExcelHeaderCell(args: any) {
    args.style = {
      bold: true,
      backColor: '#2E75B6',
      fontColor: '#FFFFFF'
    };
  }

  onExcelQueryCell(args: any) {
    if (args.value > 100000) {
      args.style = { backColor: '#E2EFDA', fontColor: '#376D34' };
    }
  }
}

CSV Export

Export to CSV format (supports 1M+ rows):

import { ExcelExportService } from '@syncfusion/ej2-angular-pivotview';

@Component({
  providers: [ExcelExportService],
  template: `
    <button (click)="exportToCSV()">Export to CSV</button>
    <ejs-pivotview 
      #pivotGrid
      [allowExcelExport]="true">
    </ejs-pivotview>
  `
})
export class AppComponent {
  @ViewChild('pivotGrid') pivotGrid: PivotViewComponent;

  exportToCSV() {
    const csvExportProperties = {
      fileName: `Report_${new Date().toISOString().split('T')[0]}.csv`
    };
    this.pivotGrid.csvExport(csvExportProperties);
  }
}

Note: Use CSV for datasets exceeding 1,048,576 rows (Excel's row limit).

PDF Export

Enable PDF Export

import { PDFExportService } from '@syncfusion/ej2-angular-pivotview';

@Component({
  providers: [PDFExportService],
  template: `
    <button (click)="exportToPDF()">Export to PDF</button>
    <ejs-pivotview 
      #pivotGrid
      [allowPdfExport]="true">
    </ejs-pivotview>
  `
})
export class AppComponent {
  exportToPDF() {
    const pdfExportProperties = {
      fileName: 'SalesReport.pdf'
    };
    this.pivotGrid.pdfExport(pdfExportProperties);
  }
}

Page Orientation & Size

exportToPDF() {
  const pdfExportProperties = {
    fileName: 'Report.pdf',
    pageOrientation: 'Landscape',  // or 'Portrait'
    pageSize: 'A4'                 // Letter, Legal, A3, A4, etc.
  };
  this.pivotGrid.pdfExport(pdfExportProperties);
}

Add Headers & Footers

exportToPDF() {
  const pdfExportProperties = {
    fileName: 'ReportWithHeader.pdf',
    header: {
      fromTop: 0,
      height: 60,
      contents: [
        {
          type: 'Text',
          value: 'Quarterly Sales Report',
          position: { x: 200, y: 25 },
          style: { textBrushColor: '#000000', fontSize: 14 }
        }
      ]
    }
  };
  this.pivotGrid.pdfExport(pdfExportProperties);
}

Footer with Page Numbers

footer: {
  fromBottom: 150,
  height: 60,
  contents: [
    {
      type: 'PageNumber',
      pageNumberType: 'Arabic',
      format: 'Page {$current} of {$total}',
      position: { x: 300, y: 50 },
      style: { fontSize: 10 }
    }
  ]
}

Export Table and Chart Together

@Component({
  template: `<ejs-pivotview 
    [displayOption]="{ view: 'Both' }"
    [chartSettings]="chartSettings"
    [allowPdfExport]="true">
  </ejs-pivotview>`
})
export class AppComponent {
  chartSettings = { value: 'Sales', chartSeries: { type: 'Column' } };

  exportBoth() {
    const pdfExportProperties = { fileName: 'TableAndChart.pdf' };
    this.pivotGrid.pdfExport(pdfExportProperties, false, false, true);
  }
}

Multiple Tables to PDF

exportMultipleTables() {
  const pdfProperties = { fileName: 'Report.pdf' };
  
  this.grid1.pdfExport(pdfProperties, true).then((pdfDoc: any) => {
    this.grid2.pdfExport(pdfProperties, false, pdfDoc);
  });
}

Customize Column Width

@Component({
  template: `<ejs-pivotview 
    (onPdfCellRender)="onPdfCellRender($event)">
  </ejs-pivotview>`
})
export class AppComponent {
  onPdfCellRender(args: any) {
    if (args.column.headerText === 'Sales') {
      args.column.width = 80;
    }
  }
}

Apply Theme

exportToPDF() {
  const pdfExportProperties = {
    fileName: 'ThemedReport.pdf',
    theme: {
      header: { fontName: 'Helvetica', fontSize: 13, bold: true },
      record: { fontName: 'Helvetica', fontSize: 10 }
    }
  };
  this.pivotGrid.pdfExport(pdfExportProperties);
}

Print Options

Print Pivot Table

@Component({
  template: `
    <button (click)="printTable()">Print</button>
    <ejs-pivotview #pivotGrid [dataSourceSettings]="dataSourceSettings">
    </ejs-pivotview>
  `
})
export class AppComponent {
  @ViewChild('pivotGrid') pivotGrid: PivotViewComponent;

  printTable() {
    this.pivotGrid.print();
  }
}

Print Chart

import { PivotChartService } from '@syncfusion/ej2-angular-pivotview';

@Component({
  providers: [PivotChartService],
  template: `
    <button (click)="printChart()">Print Chart</button>
    <ejs-pivotview 
      #pivotGrid
      [chartSettings]="chartSettings"
      [displayOption]="{ view: 'Chart' }">
    </ejs-pivotview>
  `
})
export class AppComponent {
  chartSettings = { value: 'Sales', chartSeries: { type: 'Column' } };

  printChart() {
    this.pivotGrid.print();
  }
}

Print Grid and Chart Together

import { PivotChartService } from '@syncfusion/ej2-angular-pivotview';

@Component({
  providers: [PivotChartService],
  template: `
    <button (click)="printBoth()">Print Both</button>
    <ejs-pivotview 
      #pivotGrid
      [displayOption]="{ view: 'Both' }"
      [chartSettings]="chartSettings">
    </ejs-pivotview>
  `
})
export class AppComponent {
  chartSettings = { value: 'Sales', chartSeries: { type: 'Column' } };

  printBoth() {
    this.pivotGrid.print();
  }
}

Common Scenarios

Monthly Report Export

exportMonthlyReport() {
  const month = new Date().toLocaleDateString('en-US', { month: 'long' });
  const year = new Date().getFullYear();
  
  const excelExportProperties = {
    fileName: `Sales_${month}_${year}.xlsx`
  };
  this.pivotGrid.excelExport(excelExportProperties);
}

Multi-Format Export

onExportClick(format: string) {
  const fileName = `Report_${new Date().toISOString().split('T')[0]}`;
  
  if (format === 'excel') {
    this.pivotGrid.excelExport({ fileName: `${fileName}.xlsx` });
  } else if (format === 'csv') {
    this.pivotGrid.csvExport({ fileName: `${fileName}.csv` });
  } else if (format === 'pdf') {
    this.pivotGrid.pdfExport({ fileName: `${fileName}.pdf` });
  }
}

<ejs-pivotview #pivotGrid [dataSourceSettings]="dataSourceSettings"> </ejs-pivotview> ` }) ---

Best Practices

For Excel Export: 1. Use meaningful file names with dates for tracking 2. Apply themes for consistent branding 3. Use conditional formatting for data insights 4. Exclude hidden columns for cleaner exports 5. Test export with large datasets to ensure performance

For PDF Export: 1. Choose appropriate orientation (Portrait/Landscape) based on data width 2. Add headers/footers for professional appearance 3. Include page numbers for multi-page documents 4. Use virtual scrolling for large datasets 5. Apply custom fonts for non-English content

For Printing: 1. Test print layout before enabling printing features 2. Provide clear print button with visual indication 3. Consider print-friendly styling (no hover states) 4. Maintain data readability in printed output 5. Inform users about page orientation and size

````markdown

Field List

Table of Contents

• Field List Overview
• Field List Modes
• Field Management
• Field Organization
• Deferred Updates
• Best Practices

---

Field List Overview

The Field List provides a user-friendly interface to organize and analyze data. You can add/remove fields, move them between different axes (rows, columns, values, filters), apply sorting, and manage field visibility. Similar to Microsoft Excel's field list, it supports both popup and fixed display modes.

---

Field List Modes

In-built Field List (Popup)

Opens as a dialog when clicking the field list icon:

@Component({
    template: `<ejs-pivotview 
      [dataSourceSettings]=dataSourceSettings
      [showFieldList]="true">
    </ejs-pivotview>`
})
export class AppComponent {
    dataSourceSettings = {
        dataSource: data,
        rows: [{ name: 'Country' }],
        columns: [{ name: 'Year' }],
        values: [{ name: 'Sales', type: 'Sum' }]
    };
}

Features:

• Field list icon appears in top-left (or top-right with grouping bar)
• Opens in dialog overlay
• Requires FieldListService

Stand-alone Field List (Fixed)

Displays permanently alongside the pivot table:

import { PivotViewAllModule, PivotFieldListModule } from '@syncfusion/ej2-angular-pivotview';

@Component({
  imports: [PivotViewModule, PivotFieldListModule],
  standalone: true,
  selector: 'app-root',
  template: `<div>
    <ejs-pivotfieldlist 
    [dataSourceSettings]=dataSourceSettings
    renderMode="Fixed"
    (enginePopulated)="onEnginePopulated($event)">
    </ejs-pivotfieldlist>
    <ejs-pivotview 
    [dataSourceSettings]=dataSourceSettings
    (enginePopulated)="onEnginePopulated($event)">
    </ejs-pivotview></div>`
})

Synchronization:

• enginePopulated: Fires when the pivot engine is populated and rendered
• Both field list and pivot view can listen to this event to stay synchronized

---

Field Management

Field Searching

Locate fields quickly by typing field name:

<ejs-pivotfieldlist 
  [enableFieldSearching]="true">
</ejs-pivotfieldlist>

Works in both popup and fixed modes.

Adding/Removing Fields

Users check/uncheck field checkboxes to include/exclude from current analysis:

// Programmatic field modification
this.pivotView.dataSourceSettings.values.push({
    name: 'NewField',
    type: 'Sum'
});
this.pivotView.refresh();

Excluding Fields

Hide specific fields from field list entirely:

dataSourceSettings: {
    excludeFields: ['InternalID', 'SystemKey'],
    rows: [{ name: 'Country' }],
    columns: [{ name: 'Year' }],
    values: [{ name: 'Sales', type: 'Sum' }]
}

Field Rearrangement

Drag fields between axes (rows, columns, values, filters):

// Programmatic reordering
this.pivotView.dataSourceSettings.rows = [
    { name: 'Country' },
    { name: 'Region' },
    { name: 'City' }
];
this.pivotView.refresh();

// Move field from rows to columns
const field = this.pivotView.dataSourceSettings.rows
  .find(f => f.name === 'Year');
if (field) {
    this.pivotView.dataSourceSettings.rows = 
      this.pivotView.dataSourceSettings.rows.filter(f => f.name !== 'Year');
    this.pivotView.dataSourceSettings.columns.push(field);
    this.pivotView.refresh();
}

---

Field Organization

Sort Fields

Control field display order in field list:

load(args: LoadEventArgs) {
    args.defaultFieldListOrder = 'Descending';  // Ascending, Descending, or Default
}

Group Fields Under Folders

Organize related fields in folders:

dataSourceSettings: {
    fieldMapping: [
        { name: 'ProductID', groupName: 'Products' },
        { name: 'ProductName', groupName: 'Products' },
        { name: 'Quantity', groupName: 'Sales' },
        { name: 'Amount', groupName: 'Sales' }
    ],
    rows: [{ name: 'Country' }],
    columns: [{ name: 'Year' }],
    values: [{ name: 'Sales', type: 'Sum' }]
}

For grouping bar UI operations, see [grouping-bar-ui-operations.md](grouping-bar-ui-operations.md) - Drag-drop field management, icon controls, responsive design.

---

Deferred Updates

Deferred layout update allows you to batch multiple field operations and apply them all at once, significantly improving performance with large datasets or complex configurations.

How Deferred Updates Work

When enabled, users can:

• Drag fields between axes without immediate refresh
• Apply sorting and filtering in field list without rendering changes
• Make multiple configuration changes
• Click "Apply" button to update the pivot table once with all changes

This reduces unnecessary renders and improves performance, especially with 100K+ rows.

Enable in Popup Field List

Set allowDeferLayoutUpdate to true:

@Component({
    template: `<ejs-pivotview 
      [dataSourceSettings]=dataSourceSettings
      [showFieldList]="true"
      [allowDeferLayoutUpdate]="true">
    </ejs-pivotview>`
})
export class AppComponent {
    dataSourceSettings: IDataOptions = {
        dataSource: data,
        rows: [{ name: 'Country' }],
        columns: [{ name: 'Year' }],
        values: [{ name: 'Sales', type: 'Sum' }]
    };
}

Enable in Stand-alone Field List

<ejs-pivotfieldlist 
  [dataSourceSettings]=dataSourceSettings
  renderMode="Fixed"
  [allowDeferLayoutUpdate]="true"
  (enginePopulated)="onUpdateView($event)"
  #fieldList>
</ejs-pivotfieldlist>

<ejs-pivotview 
  [dataSourceSettings]=dataSourceSettings
  (enginePopulated)="onUpdate($event)"
  #pivotView>
</ejs-pivotview>

Synchronize Field List and Pivot Table

When using deferred updates with stand-alone field list:

onEnginePopulated(args: any) {
    // Handle engine population event for both field list and pivot view
    // This ensures both components stay in sync when data is populated
}

When to Use Deferred Updates

• Large Datasets: 100K+ rows benefit significantly
• Multiple Field Operations: Users making several changes at once
• Complex Reports: Many fields, dimensions, and calculations
• Performance Critical: Applications with strict performance requirements

---

Best Practices

1. Offer Both Modes - Let users choose popup or fixed field list 2. Enable Search - Helpful with many fields 3. Group Related Fields - Organize for clarity 4. Protect Critical Fields - Hide remove icons for essential fields 5. Exclude Irrelevant Fields - Reduce UI clutter 6. Use Deferred Updates - For large datasets and complex operations 7. Synchronize Properly - Update views when fields change 8. Document Field Usage - Help users understand what each field does

````