SuperAPI AI Directory

syncfusion-aspnetmvc-pivot-table

General & other

View repository

Updated 10-16 05:52

About this skill

Implementing Pivot Table (ASP.NET MVC)

Syncfusion ASP.NET MVC Pivot Table (PivotView) enables multi-dimensional data visualization and analysis with interactive row/column/value axes, real-time aggregation, flexible filtering, and comprehensive export options.

⚠️ 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. Use Configuration: Store connection strings and API URLs in secure configuration (appsettings.json with IConfiguration)
  5. Implement Authentication: Require authentication for all database and API connections
  6. Apply Authorization: Use [Authorize] attributes and role-based access control

Security Risks:

  • Indirect Prompt Injection: Untrusted third-party data can contain malicious content that manipulates AI agent behavior
  • Data Exposure: Unvalidated remote connections may expose sensitive information
  • SQL Injection: Unsanitized database queries can lead to data breaches
  • XSS Attacks: Unescaped data rendered in the UI can execute malicious scripts

✅ Safe: Local data, authenticated APIs under your control, parameterized SQL queries ❌ Unsafe: User-provided URLs, unauthenticated endpoints, hardcoded connection strings

When to Use This Skill

Use this skill when building an ASP.NET MVC application and the user needs:

  • Pivot reporting with row, column, value, and filter axes
  • Data aggregation with 20+ summary types (Sum, Avg, Count, Percentage, RunningTotals, etc.)
  • Interactive report building via field list and grouping bar UI
  • Data source binding (local JSON/CSV, SQL Server, OLAP, relational databases)
  • Filtering (member, label, value filtering with search/sort)
  • Sorting (field sorting, custom order, value sorting)
  • Advanced features (drill-down/up, drill-through, calculated fields, pivot chart)
  • Formatting (conditional formatting, number formatting, custom styling)
  • Export (Excel, PDF, CSV, print)
  • Performance optimization (virtual scrolling, paging, data compression)

Trigger keywords: pivot table, pivotview, PivotView, field list, grouping bar, OLAP, pivot aggregation, pivot export, pivot drill-down, pivot report, ASP.NET MVC pivot

Quick Start Example

@using Syncfusion.EJ2.PivotView

@Html.EJS().PivotView("pivotview")
    .DataSourceSettings(ds => ds
        .DataSource((IEnumerable<object>)ViewBag.DataSource)
        .Rows(rows => rows.Name("Country").Add())
        .Columns(columns => columns.Name("Year").Add())
        .Values(values => values.Name("Sales").Caption("Total Sales").Add())
        .Filters(filters => filters.Name("Region").Add()))
    .ShowFieldList(true)
    .ShowGroupingBar(true)
    .Width("100%")
    .Height("450")
    .Render();

Navigation Guide

Getting started and setup

📄 Read: references/getting-started.md

  • Installation and NuGet package setup
  • Project configuration and namespace registration
  • Basic PivotView initialization
  • Themes and styling setup

Pivoting and data binding

📄 Read: references/data-binding.md

  • Local/remote data binding patterns
  • CSV and JSON data sources
  • DataManager configuration
  • Field mapping and setup

Data source connections

📄 Read: references/mysql.md

  • MySQL database setup, connection configuration, Web API implementation
  • DataAdapter pattern, JSON serialization, connection pooling

📄 Read: references/mssql.md

  • SQL Server setup, authentication methods, Azure SQL Database integration
  • T-SQL queries, stored procedures, performance optimization

📄 Read: references/postgresql.md

  • PostgreSQL connection strings, SSL/TLS configuration, authentication methods
  • Query patterns, connection pooling, JSONB data support

📄 Read: references/oracle.md

  • Oracle database setup, TNS configuration, authentication
  • SQL and PL/SQL queries, performance tuning

📄 Read: references/mongodb.md

  • MongoDB document database, BSON document handling, aggregation pipeline
  • POCO model mapping, bulk operations

📄 Read: references/elasticsearch.md

  • Elasticsearch search engine, NEST client library, aggregations
  • Query DSL patterns, index management

📄 Read: references/snowflake.md

  • Snowflake cloud warehouse, virtual warehouse configuration, warehouse sizing
  • Cost optimization, semi-structured data, role-based access

Advanced data sources

📄 Read: references/olap.md

  • OLAP cube setup and hierarchical data binding
  • Dimension and measure configuration
  • Pivot engine integration

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

  • Server-side processing and aggregation
  • Large dataset optimization for backend
  • Batch processing workflows

Data shaping and analysis

📄 Read: references/aggregation.md

  • Summary types: Sum, Avg, Count, Min, Max, Product, Median, StDev, Variance
  • Percentage calculations and running totals (including PercentageOfRunningTotals)
  • Custom aggregation at runtime

📄 Read: references/calculated-field.md

  • Define and use calculated fields in formulas
  • Runtime field creation and updates
  • Performance considerations

📄 Read: references/grouping.md

  • Field-level grouping by date, value ranges
  • Axis grouping for dimension hierarchies

📄 Read: references/filtering.md

  • Member filtering with include/exclude
  • Append current selection to existing filters (incremental multi-member selection)
  • Label filtering (string, date, numeric)
  • Value filtering on aggregated data (Equals, Between, GreaterThan, LessThan, etc.)
  • Top and Bottom operators for value filtering (client-side ranking)

📄 Read: references/sorting.md

  • Member field sorting (ascending/descending)
  • Custom member order configuration
  • Value sorting on computed cells

📄 Read: references/drill-through.md

  • Access underlying data behind pivot cells
  • Drill-through event handling

Visualization and layout

📄 Read: references/pivot-chart.md

  • Bind pivot chart to pivot table data
  • Chart type selection and configuration

📄 Read: references/classic-layout.md

  • Classic (Compact) layout rendering
  • Compact vs Outline vs Tree layout options

📄 Read: references/drill-down.md

  • Drill-down into dimension members
  • Drill-up navigation between hierarchy levels

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

  • Row and column axis configuration
  • Value field positioning

Formatting and display

📄 Read: references/number-formatting.md

  • Format value cells with currency, decimals, percentage
  • Custom number format providers

📄 Read: references/conditional-formatting.md

  • Apply style rules based on values or conditions
  • Color scales and data bars

📄 Read: references/summary-customization.md

  • Show/hide grand totals and subtotals
  • Custom total labels and captions

📄 Read: references/hyper-link.md

  • Hyperlink columns for navigation
  • Custom URL generation and click handling

User interface controls

📄 Read: references/grouping-bar.md

  • Enable/disable grouping bar
  • Drag-drop field configuration
  • Grouping bar event handling

📄 Read: references/field-list.md

  • Field list popup and fixed modes
  • Field search and sorting
  • Field grouping by folders

📄 Read: references/defer-update.md

  • Batch changes without intermediate updates
  • Improve performance with large field changes

📄 Read: references/tool-bar.md

  • Toolbar button configuration
  • Built-in and custom commands
  • Export and action buttons

📄 Read: references/tool-tip.md

  • Cell and header tooltips
  • Custom tooltip content

Editing and data manipulation

📄 Read: references/editing.md

  • In-place cell editing workflows
  • Update underlying data from pivot
  • Cell edit event handling

Performance and optimization

📄 Read: references/virtual-scrolling.md

  • Virtual scrolling for large pivot tables
  • Memory-efficient rendering

📄 Read: references/paging.md

  • Pagination configuration
  • Page size and navigation

📄 Read: references/data-compression.md

  • Payload compression for data transfer
  • Bandwidth optimization

📄 Read: references/performance-best-practices.md

  • Tuning guidelines for large datasets
  • Server-side vs client-side rendering decisions
  • Common performance bottlenecks and fixes

State and persistence

📄 Read: references/state-persistence.md

  • Save pivot report state
  • Load saved configurations
  • State storage options

Export and printing

📄 Read: references/print.md

  • Print pivot table with custom settings
  • Print preview and page layout

📄 Read: references/excel-export.md

  • Export to Excel workbook
  • Formatting and multi-sheet export

📄 Read: references/pdf-export.md

  • Export to PDF document
  • Page orientation and sizing

Common Patterns

Pattern 1: Complete pivot with all UI:

  • Enable ShowFieldList, ShowGroupingBar, and toolbar buttons
  • User can build and refine reports interactively

Pattern 2: Server-side aggregation for large datasets:

  • Use server-side pivot engine with Syncfusion.EJ2.PivotView.RenderMode.Server to process on backend
  • Enable paging and virtual scrolling (EnableVirtualization) for responsive UX
  • Requires Syncfusion.Pivot.Engine package

Pattern 3: Read-only analytical view:

  • Disable field list and grouping bar
  • Set fixed row/column/value configuration
  • Use drill-down only for exploration

Key Properties Overview

PivotView (Root Class)

  • DataSourceSettings - Data source and field configuration
  • ShowFieldList - Toggle field list UI (popup mode)
  • ShowGroupingBar - Toggle grouping bar UI
  • ShowToolbar - Display toolbar buttons
  • AllowExcelExport - Enable Excel export capability
  • AllowPdfExport - Enable PDF export capability
  • EnableValueSorting - Allow value cell sorting
  • EnableVirtualization - Enable virtual scrolling for large datasets
  • Height, Width - Component dimensions

DataSourceSettings (Configuration)

  • Rows, Columns, Values, Filters - Axis field arrays
  • AllowMemberFilter - Enable member filtering UI
  • AllowLabelFilter - Enable label filtering UI
  • AllowValueFilter - Enable value filtering UI
  • EnableSorting - Enable member sorting (default: true)
  • FormatSettings - Number formatting for value fields

Related Skills

  • implementing-syncfusion-aspnetmvc-components - Parent library skill