Pivot Table Data for .NET create pivot tables in C# code
MVC/Blazor pivot table builder

PivotData can be used with large variety of data sources:

• in-memory .NET collections, anything with IEnumerable interface: see DynamicListGrouping example
• large CSV / TSV files: CsvSource class
• JSON data: array of objects / array of values: JsonSource class
• any SQL database that has ADO.NET provider (see ToolkitSqlDbSource example + SqlGroupByCube.cs in in ToolkitPivotBuilderMvc):
  • MS SQL Server, Azure SQL
  • MySQL, MariaDB, MemSql
  • PostgreSQL, Amazon RedShift
  • SQLite, Oracle DB, IBM DB2 etc
  • Yandex ClickHouse
  • any other SQL-compatible database with ODBC driver (say, Google BigQuery)
• MongoDB with support of aggregate pipeline: see ToolkitMongoDbSource example
• ElasticSearch: see ToolkitElasticSearchSource example + ElasticSearchCube.cs in ToolkitPivotBuilderMvc
• SSAS (Analysis Services) OLAP cube: see ToolkitAdomdSource example + MdxAdomdCube.cs in ToolkitPivotBuilderMvc

PivotData can aggregate millions of data rows in seconds; it is suitable for processing large data files (>1Gb) like CSV or log files.
Aggregation of large datasets (>1 mln rows) can be performed on database level (with GROUP BY query) and loaded into PivotData instance for further analytical processing.

PivotData Toolkit (NReco.PivotData.Extensions.dll) extends OLAP library with advanced components for typical analytics/reporting functionality:

• Data sources:
  • DbCommandSource: use SQL DB (MS SQL Server/MySQL/PostgreSQL etc) as input data source
  • JsonSource: use JSON as input data source (supports both arrays of objects and values)
  • CsvSource: use CSV file as input data source (can be used for processing data from large >1gb CSV files)
  • MongoDbSource is implemented in the ToolkitMongoDbSource example
  • ElasticSearchSource is implemented in the ToolkitElasticSearchSource example
  • GroupedSourceReader: load PivotData from pre-grouped data (for example, when data is aggregated on SQL level with GROUP BY)
  • DerivedValueSource: handle derived values: parse input values, apply formatting rules, define derived values (date parts like year/month/quarter, formulas)
• Pivot table renderers (writers):
  • HTML: PivotTableHtmlWriter
  • CSV: PivotTableCsvWriter
  • Excel: PivotTableExcelWriter
  • JSON: PivotTableJsonWriter
  • DataTable: PivotTableDataTableWriter (not available in netstandard1.5 build)
• Pivot table model wrappers:
  • PercentagePivotTable: calculates percentage values (by grand total, row or column totals).
  • DifferencePivotTable: calculates difference between values (by row or column).
  • RunningValuePivotTable: calculates running value (by row or column). Running total is calculated if aggregator is not specified.
  • TopPivotTable: limits table by top N (or last N) rows and/or columns.
  • PaginatePivotTable: takes section of the table by specified start-end rows and columns. Used for server-side pagination.
  • HeatmapPivotTable: highlights max values in the table.
• PivotDataFactory / PivotTableFactory: create PivotData/PivotTable instances by JSON configuration (used when pivot table is constructed dynamically)
• CubeFileReader / CubeFileWriter: read/write serialized PivotData (configuration and data) from/to local files

PivotData microservice is a headless BI (self-hosted NET6 app with web API) based on PivotData Toolkit components:

• advanced web API for pivot table HTML rendering and export functions + API for usual tabular reports (datagrids that display dataset's rows)
• implements lightweight ROLAP engine (direct query connections to data sources)
• includes built-in connectors for all popular DBs: SQL Server, MySQL, PostgreSQL, SQLite, ODBC, MongoDb, ElasticSearch, CSV any many others. A special web-API connector can be used to load data for pivot tables from your .NET app!
• uses short-living in-memory cache to eleminating excessive database queries and improve UX
• can be scaled horizontally
• includes pivot table builder front-end example (HTML/JS) that may be easily integrated into any web application (PHP, Ruby, Python, Java etc)
• Microservice NET6 can be hosted either on Linux or Windows server or Docker container.

PivotData Toolkit can be used for creating OLAP local cubes (represented by .cub file) with the following steps:

1. lets assume that some dataset is already aggregated with PivotData library
2. export PivotData instance into relational representation (DataSet with star schema) using DataSetStarSchemaWriter component
3. save DataSet into csv files with schema.ini for OLEDB Provider Text Driver
4. use AMO (Analysis Services .NET API) to create a new local cube (.cub file)
5. configure dimensions, data source (JET OLE Provider to csv files) and setup cube according to PivotData instance metadata
6. Process cube (this action will force OLAP to read all data from csv files and store them in the local cube)

This scenario is implemented in the "ToolkitCreateOfflineCube" example.

The following code snippet illustrates how to pivot a DataTable:

DataTable tbl;
var pvtData = new PivotData(new[] {"product", "year" }, new CountAggregatorFactory() );
pvtData.ProcessData(new DataTableReader(tbl));
var pvtTbl = new PivotTable(
    new [] {"product"}, //rows
	new [] {"year"}, //columns
	pvtData);

PivotTable object can be easily exported to DataTable with PivotTableDataTableWriter (Toolkit component):

var dataTableWr = new PivotTableDataTableWriter("Test");
var tbl = dataTableWr.Write(pvtTbl);

See also: how to pivot DataTable to Excel PivotTable.

Depending on situation PivotData can (re)calculate all subtotal/grand total values either as part of data processing (PivotData.ProcessData) or when totals are accessed for the first time (LazyTotals=true). This difference is essential for data cubes with a lot of dimensions (>3) and/or significant amount of unique dimension keys (>100): non-lazy calculation of subtotals for all dimensions recombinations may take a lot of time and lead to OutOfMemory exception.

In general LazyTotals=false mode is recommended in the following cases:

• you need extremely fast random access to sub-totals many times and sub-totals doesn't cause "out-of-memory" exception; for example, this may be concurrent requests to the same in-memory data cube.
• data cube is used for aggregating real-time data stream (ProcessData is called many times for data chunks) and sub-totals should be kept up-to-date without re-calculating totals when next data chunk is arrived (for example in case of real-time monitoring/alerts).
• all PivotData subtotals assumed to be used: good example is the pivot table report generation that renders subtotals for all row/column intersections. Since all subtotals should be calculated anyway, it is more effective to calculate them at once with LazyTotals=false.
  The typical scenario for generating pivot report from a big data cube (LazyTotals=true):
  • slicing by dimensions needed for pivot table with PivotData.Slice method (LazyTotals=false)
  • use sliced PivotData with PivotTable view to iterate through pivot table rows/columns

It is possible to render pivot table in WinForms/WPF/UWP apps in one of the following ways:

• export PivotTable model to DataTable (with PivotTableDataTableWriter) and bind it to DataGrid control. This approach is useful if you don't need labels grouping and other advanced rendering options.
• use WebBrowser (WinForms) or WebView (WPF/UWP) to show HTML provided by PivotTableHtmlWriter

Examples package includes "ToolkitPivotTableWinForms" example that illustrates these approaches.

PivotData SDK can be integrated with NReco.NLQuery library for reports generation by end-user's search-like queries. You can check how it works here: PivotData microservice example.

If you're interested in this feature and want to get an example of NLQ feature please contact us .

PivotData OLAP library

PivotData library (NReco.PivotData.dll) can be used for FREE in non-SaaS apps that have only one single-server production deployment.
Commercial license is required for in case of multiple deployments, redistribution as part of your product, or usage in SaaS app.

PivotData Toolkit
advanced components

PivotData Toolkit (NReco.PivotData.Extensions.dll, web pivot table builder js components, code from "toolkit" examples) requires a commercial license for production use:

• Standard per-developer license (perpetual, royalty-free).
  All developers who are working on the project that uses PivotData Toolkit need to be licensed. It is possible to purchase a license for unlimited developers.
  NOTE: this license cannot be used for SaaS applications.
• SaaS application license (perpetual, per-deployment).
  Anyone who works on the project may use PivotData Toolkit for the purpose of developing SaaS application. Each production deployment should have its own license. If you offer self-hosted (on-premise) version of SaaS application, each installation requires its own SaaS deployment license(s). It is possible to purchase SaaS license for unlimited deployments.

IMPORTANT: PivotData Toolkit cannot be used without PivotData OLAP library.
If you’re not sure what licenses are needed in your case, please consult our support team.

All commercial packages include 1 year subscription for email support (assistance in components usage, troubleshooting) and free component upgrades. After first year it is possible to renew the subscription with 50% of the full package price (this is optional).