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](references/getting-started.md) - Installation and package setup (Angular 21 standalone architecture) - Basic Angular Pivot Grid implementation - **Theme packages** — use the consolidated `@syncfusion/ej2-{theme}-theme` packages (e.g. `ej2-material3-theme`) with a single `styles/pivotview/index.css` import - CSS imports and theme configuration - Component initialization (`PivotViewModule`, `bootstrapApplication`) ### Aggregation 📄 **Read:** [references/aggregation.md](references/aggregation.md) - Aggregation functions: Sum, Avg, Count, Min, Max, Product, Median, DistinctCount - Advanced aggregations: DifferenceFrom, PercentageOfDifferenceFrom, PercentageOfParentTotal - Parent-total aggregations: `PercentageOfParentColumnTotal`, `PercentageOfParentRowTotal` (axis-scoped % of parent) - Running-totals aggregations: `RunningTotals` and **`PercentageOfRunningTotals`** (cumulative % of grand total; client-side only) - 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, actionFailure ### Grouping 📄 **Read:** [references/grouping.md](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](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](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](references/filtering-and-sorting.md) - **Member filtering**: Include or exclude specific field members (Include/Exclude, with `levelCount` for OLAP) - **Append current selection to filter**: Accumulate selections in the Member Editor instead of replacing them - **Member editor UX**: Select/unselect all, search members, sort members in the editor - **Limit members displayed**: Tune `maxNodeLimitInMemberEditor` for large hierarchies - **OLAP member loading**: On-demand loading via `loadOnDemandInMemberEditor` and `levelCount` - **Label filtering**: Filter header text by string (Equals, Contains, BeginWith, Between, ...), number, or date - **Value filtering**: Filter by aggregated values with operators (Equals, GreaterThan, Between, ...) and **Top/Bottom N** members - **Filtering events**: `memberFiltering`, `memberEditorOpen`, `actionBegin`/`actionComplete`/`actionFailure` - 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](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](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](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](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](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](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](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](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](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](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](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](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](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](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 ```typescript 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' },