Pivot Table Data for .NET create advanced pivot tables with C#
embedded in-memory OLAP library


OLAP library NReco.PivotData.dll

  • pure C# library for data aggregation and grouping: calculates data for pivot table (pivot grid, pivot chart) from any enumerable data source or SQL database.
  • implements fast in-memory data cube (multidimensional dataset) and OLAP query operations (slice/dice/filter/roll-up/pivot).
  • aggregate (summary) functions : count, sum, average, min, max, count unique values, list unique values, list values, variance, standard deviation, quantile (median), custom formula. Several measures may be collected at once.
  • supports incremental data processing (real-time data aggregation), merge calculation results (parallel/distributed aggregation), lazy totals calculation.
  • fast data cube serialization (save/load in-memory cube state).
  • pivot table data model (2D or n-Dimensional): several dimensions per axis (label grouping), sort by labels, sort by values, totals, sub-totals.
  • simplest way to pivot a DataTable or typed list - with one line of C# code.
  • FREE for usage in a single-deployment projects (see FAQ section for more details)

download and pricing

PivotData OLAP library implements in-memory multidimensional dataset (data cube) for further analytical processing in .NET applications. Library performs fast real-time data grouping and aggregation, calculates data for pivot tables and charts without OLAP server.

BI toolkit NReco.PivotData.Extensions.dll + web pivot

  • advanced components for pivot table visualization and exports:
    • HTML pivot table renderer that supports labels grouping, multiple measures, totals and groups subtotals, custom formatting options and CSS styling
    • export pivot table to CSV, Excel, PDF, JSON or .NET DataTable
    • calculate pivot table percentage / difference / running total / heatmap
    • javascript pivot table control widget: sort by labels/values/totals, fixed headers, server-side pagination for large tables
    • keyword-based filter for data cube or pivot table
    • ASP.NET pivot table control interactive pivot grid (crosstab, pivot chart) builder by end-user
  • data processing:
    • ETL components: parse numbers/dates, merge/match by regex, combine columns, calculate derived values
    • load tabular data from SQL database, CSV/TSV, JSON
    • load in-memory cube with pre-aggregated data: SQL SELECT GROUP BY, MongoDb aggregate pipeline results, OLAP server MDX results
    • export cube to DataSet (star schema)
    • save/load cube data to file
    • memory-optimized read-only cube implementation (FixedPivotData)
  • read more details about PivotData Toolkit...

quick purchase process

  • 1 Choose a package
  • 2 Pay online 2Checkout.com is a worldwide leader in online payment services
  • 3 Download the package
PivotData Toolkit provides .NET components for rendering pivot table to HTML, exports to CSV/JSON/Excel/PDF, data source connectors (SQL/CSV/JSON/MongoDb/MDX) etc.
Includes web pivot table builder that can be easily integrated into any ASP.NET application.

how to use create pivot table example

  1. Install NReco.PivotData nuget package
  2. Aggregate data using PivotData class with one line of C# code:
    var pivotData = new PivotData(
    	new string[] {"name","age"},
    	new AverageAggregatorFactory("salary"),
    	new DataTableReader(t) );
    
  3. Access data cube values:
    var grandTotal = pivotData[Key.Empty,Key.Empty].Value;
    var subTotalFor29 = pivotData[Key.Empty,29].Value;
    var allDimensionKeys = pivotData.GetDimensionKeys();
    
  4. Create pivot table model:
    var pivotTable = new PivotTable(
    	new []{"name"}, // row dimension(s)
    	new []{"age"}, // column dimension(s)
    	pivotData );
  5. Render pivot table to HTML table:
    For this code snippet you need to install NReco.PivotData.Extensions nuget package.
    var htmlResult = new StringWriter();
    var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult);
    pvtHtmlWr.Write(pivotTable);
    
  6. What's next:

online c# pivot table demo

see also
ASP.NET example: web pivot table builder interactive pivot tables and pivot charts builder
ASP.NET Example: BI dashboard with charts custom BI dashboard wih pivot tables and charts
SeekTable: online pivot table tool based on PivotData SDK

frequently asked questions

PivotData OLAP library can do the following:
  • perform fast in-memory aggregation of tabular data and collect one or several measures in one pass
  • execute OLAP queries from C# code in linq-style: slice/dice, drill up/drill down, roll-up/pivot (without OLAP server and MDX)
  • calculate data for pivot tables (pivot charts, cohort analysis) in .NET applications
PivotData BI Toolkit usage scenarios:
  • reports automation: generate highly customizable pivot tables and pivot charts from any data source
  • render/export pivot tables to HTML/CSV/Excel/PDF/JSON formats on the server side
  • add web pivot table builder to ASP.NET application (MVC, MVC Core, WebForms) or any web applications (with PivotData .NET Core microservice on backend)
  • embedded business intelligence and analytics, scheduled reports generation, custom BI dashboards, real-time reporting functionality
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 ("ToolkitSqlDbSource" example):
    • MS SQL Server, Azure SQL
    • MySQL, MariaDB, MemSql
    • PostgreSQL, Amazon RedShift
    • SQLite, Oracle DB, IBM DB2 etc
    • Yandex ClickHouse
    • other SQL-compatible databases with ODBC driver, like Google BigQuery
  • MongoDB with support of aggregate pipeline: see "ToolkitMongoDbSource" example
  • SSAS (Analysis Services) OLAP cube: see "ToolkitAdomdSource" example
  • any OLAP server with XMLA/MDX endpoint: interested in this connector? Let us know!
  • ElasticSearch: interested in this connector? Let us know!
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:
PivotData Tool is a command line (console) utility that can be used for automating data aggregation and pivot table generation tasks (no programming needed):
  • aggregate data from csv file, MS SQL or any SQL database with ODBC driver
  • define derived values and parse rules for input data
  • save/load cube data from files
  • filter data cube (slice/dice)
  • export pivot table to HTML/CSV/Excel file
Binary cube files produced by PivotData Tool can be loaded from C# code with PivotData Toolkit or used as data source in PivotData microservice.
Typical PivotData Tool usage:
  • scheduled re-calculation of data cube files (for example, daily or hourly)
  • batch pivot table reports generation
PivotData microservice is an installable product (.NET Core app) built on the top of PivotData Toolkit:
  • provides simple web API for all pivot table HTML rendering and export functions
  • implements lightweight ROLAP engine
  • connects to various data sources (configured with simple json config): MS SQL Server, MySQL, PostgreSQL, SQLite, MongoDb, ODBC
  • uses in-memory caching for eleminating excessive database queries
  • can be scaled horizontally
  • includes pivot table builder front-end (HTML/JS) that may be easily embedded into any web application (PHP, Ruby, Python, Java etc)
  • .NET Core microservice can be hosted either on Linux or Windows server.
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
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.

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 library
NReco.PivotData.dll
netstandard builds (for NET Core 1 & 2 apps) are available starting from version 1.3.1.
Differences from full .NET Framework build:
  • PivotData.ProcessData(IEnumerable<DataRow>) is not available in netstandard1.5
PivotData Toolkit
NReco.PivotData.Extensions.dll
netstandard builds are available starting from version 1.3.0.
Differences from full .NET Framework build:
  • DataSetStarSchemaWriter, PivotTableDataTableWriter are not available in netstandard1.5

PivotData SDK can be integrated with NReco.NLQuery component for reports generation by natural-language queries. You can check this functionality as "Ask a question" feature in our online pivot table tool.

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 a single-deployment projects (websites, intranet/extranet web applications) or applications for company's internal business purposes (redistributed only internally inside the company).
Commercial license is required for external redistribution (ISV) or SaaS deployments (included into PivotData OLAP library enterprise pack).
PivotData Toolkit
advanced components
PivotData Toolkit (NReco.PivotData.Extensions.dll, web pivot table builder js components, "toolkit" examples) usage or redistribution requires a commercial license:
  • 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.
Don't forget that Toolkit cannot be used without Pivot 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 updates (including bug fixes and new features). Expired subscription can be renewed for +1 year with 50% of the full package price.

what's new NReco.PivotData.dll

2018 Apr 06 v.1.3.6 (latest)
2018 Feb 26 v.1.3.5
2017 Feb 01 v.1.3.4
2016 Oct 18 v.1.3.3
2016 Aug 14 v.1.3.2
2016 Jul 05 v.1.3.1
2016 May 10 v.1.3.0
2016 Feb 04 v.1.2.6
2016 Jan 13 v.1.2.5
2015 Dec 11 v.1.2.4
2015 Sep 30 v.1.2.3
2015 July 27 v.1.2.2
2015 May 26 v.1.2.0
2015 Apr 10 v.1.1.0
2015 Mar 09 v.1.0.1
2015 Feb 11 v.1.0.0

toolkit changes NReco.PivotData.Extensions.dll

2018 Apr 12 v.1.4.1 (latest)
2018 Jan 09 v.1.4.0
2017 Sep 25 v.1.3.8
2017 Jul 26 v.1.3.7
2017 Jun 26 v.1.3.6
2017 Mar 15 v.1.3.5
2017 Feb 01 v.1.3.4
2016 Dec 02 v.1.3.3
2016 Oct 18 v.1.3.2
2016 Sep 16 v.1.3.1
2016 Aug 14 v.1.3.0
2016 Jun 03 v.1.2.0
2016 Feb 04 v.1.1.0
2016 Jan 01 v.1.0.2
2015 Sep 30 v.1.0.1
2015 Jul 28 v.1.0.0
2015 Jul 14 v.0.7
2015 Jun 25 v.0.6
2015 Jun 04 v.0.5

more components

  • PivotData Microservice

    .NET Core app that provides web API for PivotData Toolkit functionality: create pivot tables & charts by SQL/MongoDb databases, export to JSON/CSV/Excel/PDF. Can be used as backend for web pivot table builder.

  • PivotData Command Line Utility

    Pivotdata can aggregate data from CSV files and SQL databases and produce pretty pivot table reports (HTML/Excel/CSV/PDF). Doesn't need any programming and can be used by power users.