Web pivot table builder PivotData C# Examples


Online Demo PivotData Toolkit can be used as ASP.NET web pivot builder control: UI for pivot table reports creation by end-users. ToolkitPivotReportMvc example illustrates this use-case and includes all necessary front-end components (jQuery, Bootstrap).

How web pivot builder works

Web Browser
Front-end JS components
ASP.NET Backend
PivotController.cs
Data Source
Web pivot builder data sources
  • jquery.pivottable.js adds pivot table interactive features (sorting, pagination, fixed headers).
  • jquery.nrecopivotchart.js renders pivot chart by JSON with pivot table data (uses ChartistJS library).
  • webpivotbuilder.js integrates pivot table configuration form with widgets listed above.
  • provides necessary metadata for pivot builder: list of data sources, available dimensions and measures.
  • accepts report configuration, applies all necessary formatting/calculations (percentage, difference, running total, heatmap), and renders pivot table HTML or produces an export content (CSV, PDF, Excel, JSON).
  • InMemoryCube.cs: simple data source based on in-memory IPivotData instance.
  • PivotDataFileCube.cs: serialized PivotData library cube files (produced by CubeFileWriter).
  • SqlCube.cs: any SQL-compatible database with ADO.NET provider. Data reader is used to read tabular dataset; aggregation is performed with PivotData.ProcessData.
  • SqlGroupBy.cs: simple ROLAP-style (generates GROUP BY query) for any SQL-compatible database; aggregation is performed by a database and results are loaded with GroupedSourceReader.

Unlike typical ASP.NET pivot control that is usually "one solid" component PivotData web pivot builder is more like a lego model; you can use "ToolkitPivotReportMvc" example as is, or take only widgets and functionality you need. Since you have full source code of js front-end and ASP.NET backend you can easily customize web pivot builder:

  • add custom data sources by implementing ICube interface (say, use MongoDb as illustrated in "ToolkitMongoDbSource" example).
  • change UI layout, use another controls for report configuration, add/remove options.
  • replace ChartistJS with your favourite js charts library, add more chart types.
  • customize styles (by default Bootstrap is used)
  • define your own drill-down logic (say, you may open data grid with aggregated rows instead of applying pivot table filter and adding additional dimension).

How to integrate with Angular/React/Vue

Web pivot builder front-end implemented as jQuery plugins because they could be used both in non-SPA and SPA web applications. Modern SPA frameworks could correctly embed webpivotbuilder.js plugin:

Angular
How to use jQuery Plugin with Angular 4?
ReactJS
Integrating with Other Libraries
VueJS
Wrapper Component Example

Web pivot table builder features implemented in the ToolkitPivotBuilderMvc example

  • interactive pivot table: sorting by labels/values, server-side pagination, fixed headers, keyword-based table filter, drill-down by click on value cell.
  • report configuration: select any number of dimensions for rows/columns, select any number of values.
  • formatting/calculation options: perentage, difference, running total, heatmap, top N limit, totals/subtotals.
  • pivot chart types: bar/stacked bar, horizontal bar, line, scatter, area, pie, donut.
  • drill-down by click on the value cell

Features that you can add by yourself:

  • report parameters for data source level filtering: you can specify custom filter form, or use generic query builder widget (like NReco JS Query Builder).
  • replace keyword-based filter with checkbox-based (Excel-like) filter.
  • enhance SqlGroupBy for more efficient query generation, or implement your custom data source.

NOTE: if you want to integrate web pivot builder functionality with minimal efforts take a look to PivotData microservice: this is on-premise server software that implements mini ROLAP engine and provides web API for pivot table generation (no programming needed, data sources are configured with JSON). Microservice can be integrated into any web application (even non-ASP.NET).