Web pivot 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 plugins).

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.
  • 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).

Web pivot builder features already implemented in the ToolkitPivotBuilderMvc example

  • pivot table interactivity: 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.

Features that you can add by yourself:

  • report parameters (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.

NOTE: if you don't have time and/or dev resources for "ToolkitPivotBuilderMvc" integration take a look to PivotData microservice - this is installable on-premise solution that implements lightweight ROLAP engine and it can be used as backend for web pivot builder described in this article. Microservice can be integrated into any web application (even non-ASP.NET) very quickly without any C# programming at all.