PivotData Microservice Web API for embedded pivot tables and charts
.NET Core backend can be hosted on Linux or Windows

PivotData Web API can be used with any REST Client

features web API for creating pivot tables

  • based on NReco PivotData Toolkit:
    • fast in-memory data cubes (no ETL, real-time reporting)
    • built-in ROLAP engine for SQL/MongoDB data sources
    • aggregate functions: count/count unique, sum, average, min/max, variance/standard deviation, list/list unique, custom formula
    • calculated members: dimensions and formula measures
  • advanced pivot table features:
    • labels grouping with sub-totals
    • multiple values in one table
    • sort table by values, totals or labels
    • percentage, difference, custom formatting options
    • server-side pagination and top N limits for large pivot tables
    • keyword-based filter for rows & columns
  • report parameters (data source level filtering):
    • multi-value parameters
    • custom user-defined conditions
  • export pivot tables & charts to HTML, JSON, CSV, Excel, PDF
  • embeddable web pivot table & chart builder for user-defined reports
  • supported data sources (connectors):
    • MS SQL Server, MySQL/MariaDB/MemSQL, PostgreSQL/Amazon Redshift, SQLite
    • MongoDB (aggregation pipeline)
    • pre-computed binary cube data files
    • custom connectors are possible (.NET plugins)
  • simple integration with any web application: ASP.NET, Java, PHP, Ruby, NodeJS, Python etc
  • PivotData REST service (.NET Core app) can be hosted on Linux, MacOS or Windows.

download and pricing Verified Quality by financesonline.com

PivotData microservice provides simple way to embed self-service analytics (pivot table, pivot chart reports) into any web application. Data sources are configured with simple JSON config. You can add web pivot tables to your web app in minutes!

quick purchase process

  • 1 Choose a package
  • 2 Pay online 2Checkout.com is a worldwide leader in online payment services
  • 3 Download the package

how to use

  1. Download trial pack and deploy PivotData Microservice on Linux or Windows server (.NET Core application), see readme.txt for instructions.
  2. Configure your data sources with simple appsettings.json config
  3. Review web pivot builder examples:
    • index.html
    • condition-builder.html
    Use sample code to embed pivot tables and charts into your web app.
  4. For all pivot table related functions: use simple web API

pivot table builder demo

Don't hesitate to contact us and ask for assistance or schedule a live demo.

frequently asked questions

PivotData microservice is useful in the following cases:
  • integrate advanced pivot tables and charts builder into any web application
  • get embedded analytics and ad-hoc BI reporting for SQL/MongoDB data sources
  • calculate data for custom web-based BI dashboards
  • generate reports on the server-side (scheduled reports, exports)
Pivot table builder UI (javascript fron-end part) is highly customizable: it is possible to use any custom controls for pivot table configuration.
SQL-compatible data sources are configured in "Cubes" section of appsettings.json file; cube "SourceType" should be "SqlDb".

Currently the following SQL databases are supported:
  • MS SQL Server / Azure SQL: "Connector":"mssql"
  • MySQL / MariaDB / MemSQL: "Connector":"mysql"
  • PostgreSQL: "Connector":"postgresql"
  • Amazon Redshift: "Connector":"postgresql" + include "Server Compatibility Mode=Redshift" into connection string
  • SQLite: "Connector":"sqlite"
SQL data source configuration example:
"Cubes": [
 "Id": "orders",
 "Name": "Orders from MySQL sample DB",
 "SourceType": "SqlDb", // sql data source type
 "SourceDb": {
   "Connector": "mysql", // mssql, postgresql, sqlite 
   "ConnectionString": "Server=db4free.net;Database=nreco_sampledb;Uid=[DB_USER];Pwd=[PASSWORD];",
   "SelectSql": "SELECT o.status, o.orderDate, c.customerName, c.country, c.state, c.city FROM orders o LEFT JOIN customers c ON (c.customerNumber=o.customerNumber)"
 "Dimensions": [
   { "Name": "status" },
    "Name": "OrderDate_year",
     "LabelText": "Order Date (Year)",
     "Type": "Expression", // derived dimension
     "Params": [ "Date.Year(orderDate)", "orderDate" ]
    "Name": "OrderDate_quarter",
    "LabelText": "Order Date (Quarter)",
    "Type": "Expression",
    "Params": [ "Date.Quarter(orderDate)", "orderDate" ]
    "Name": "OrderDate_month",
    "LabelText": "Order Date (Month)",
    "Type": "Expression",
    "Params": [ "Date.Month(orderDate)", "orderDate" ]
   { "Name": "customerName", "LabelText":"Customer" },
   { "Name": "country" },
   { "Name": "state" },
   { "Name": "city" }
 "Measures": [
  { "Type": "Count" }
 "Cache": {
  "AbsoluteExpirationMinutes" : 60
"SelectSql" should include all columns referenced from "Dimensions" or "Measures". Don't worry about dataset size or number of columns: PivotData engine will load only data needed for concrete pivot table. Aggregation is performed on SQL level (with GROUP BY) if possible.
For MongoDb data source "SourceType" should be "mongodb". Dimensions and Measures are configured in the same way as for SQL source; note that you can reference sub-document fields like "address.street".

MongoDb data source configuration example:
 "Id": "restaurants",
 "Name": "Restaurants from MongoDB sample dataset",
 "SourceType": "mongodb",
 "SourceMongoDb": {
  "ConnectionString": "mongodb://USER:PWD@MONGO_SERVER:PORT/DATABASE",
  "Database": "nrecodemo",
  "Collection": "restaurants",
  "FilterJson" : "{ \"cuisine\" : \"American \" }",
  "Unwinds": [  //sub-collections to unwind
    { "Path": "grades", "ApplyOnDemand": true }
  "UseAggregate": true // enable aggregation pipeline usage
 "Dimensions": [
  { "Name": "cuisine", "LabelText": "Cuisine" },
  { "Name": "borough", "LabelText": "Borough" },
  { "Name": "name", "LabelText": "Name" },
  { "Name": "address.street", "LabelText": "Street" },
  { "Name": "grades.grade", "LabelText": "Grade" }
 "Measures": [
  { "Type": "Count" },
   "Type": "Max",
   "Params": [ "grades.score" ],
   "LabelText": "Max Score"
   "Type": "Average",
   "Params": [ "grades.score" ],
   "LabelText": "Score Average"
	"Type": "Sum",
	"Params": [ "grades.score" ],
	"LabelText": "Score Sum"
Note that unwinds are performed only if report includes dimension or measure that uses field from sub-collection (this is important for "Count" aggregator).
Parameters may be used to filter data on a database-level (with SQL WHERE or MongoDb $filter / $match). The following configuration snippets illustrate how to define parameters with appsettings.json:

// for SQL-based cube
"SourceDb": {
  "Connector": "mssql",
  "ConnectionString": "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;",
  "SelectSql": "SELECT * FROM [Orders] WHERE 1=1 @country[and Country={0}]"
"Parameters": [
  {"Name": "country", "DataType": "string"}
// for MongoDb-based cube
"SourceMongoDb": {
    "ConnectionString": "mongodb://mongodbserver",
    "Database": "nrecodemo",
    "Collection": "restaurants",
    "FilterJson": "{ @cuisine[ \"cuisine\" : {0}, ]  }",
    "UseAggregate": true
"Parameters": [
    { "Name": "cuisine", "DataType": "string", "Type" : "Variable" }
Also it is possible to use "claim" values passed with JWT ("Type":"Claim").
Reports may include sensitive data and you might need to secure microservice API. The following options are available:
  • disable all incoming connections to the microservice's port (5000 by default) with server's firewall. This variant is applicable if you access PivotData REST API only from your server application on the same machine.
  • use reverse-proxy server for accessing REST API (IIS on windows, nginx on Linux) and use its authentication mechanisms (for instance, "Basic" or "Windows" authentication).
  • configure JWT-based authentication (appsettings.json):
"Auth": {
  "Type": "jwt",
  "Jwt": {
    "ValidIssuer": "Test",
    "ValidateIssuer": true,
    "ValidateAudience": false,
    "ValidateLifetime": true,
    "ValidateIssuerSigningKey": true,
    "IssuerSigningKeyString": "some_secure_key_value"
The following features may be implemented in the future releases:
Data sources
  • Support for custom data source connector plugins (.net)
  • SalesForce connector (SOQL)
  • Yandex ClickHouse OLAP connector
  • EXASOL connector (may be added to windows-build, there are no .NET Core compatible ADO.NET provider for now)
  • Google BigQuery connector
  • Oracle DB support (waiting for officialy announced Oracle ADO.NET provider for .NET Core ; connector may be added to windows-build )
  • CSV/TSV/JSON files (or URLs) as data source
  • Dynamic cube configurations in SQL database + REST API for cubes management
  • Hybrid cubes merged from several data sources (data blending); for example, pre-computed + SQL
Pivot table & UI (front-end components)
  • Custom conditions builder (js) for database-level filter
  • Web API for pivot chart exports: HTML, PDF, image (JPEG/PNG)
  • Drill-down functionality
  • Add ability to filter by pivot table values (like "exclude values <50")
  • search-driven analytics: ability to build a report by simple search query
If you are interested in one of these features please let us know.


2016 Dec 16 Version 1.0-final changes:
  • enable CORS by default
  • front-end js code was reorganized to simplify integration
  • added support for multi-value report parameters (used with IN condition)
  • added "Condition" report parameter type for complex user-defined filter conditions (may be combined with and/or). Filter is applied on the data source level (both SQL and MongoDb sources are supported).
  • added javascript Query Builder integration example (condition-builder.html)
  • added ability to specify export file name with API parameter
2016 Oct 29 Version 1.0-beta3 changes:
  • added Json Web Token (JWT) authentication and authorization for microservice web API
  • added custom report parameters (variables) for SQL selects / MongoDb filters (database-level data filtering):
    • variable values are specified with PivotReport model (JSON)
    • claim values are specified with JWT
2016 Oct 10 Version 1.0-beta2 changes:
  • added server-side pagination both for rows and columns (viewing large pivot tables)
  • MongoDB connector: uses aggregation pipeline, can unwind properties
  • Pivot report JSON config now uses dimension/measure names instead of index numbers
  • bugfixes
2016 Sep 26 Version 1.0-beta1 with features:
  • render pivot table to HTML, JSON, CSV, Excel, PDF
  • formatting options: custom formatting for numbers/labels, percentage values, difference values
  • top N limits
  • measures: count, min, max, average, count unique, list unique, variance/std-dev
  • JSON configuration for data cubes (appsettings.json)
  • data sources: MS SQL, MySQL/MariaDB, PostgreSQL, SQLite, pre-computed binary cube files
  • use of database-level aggregation (GROUP BY) if possible
  • Derived dimensions (calculated from parent dimension(s) - for example, date year/quarter/month/day)
  • Custom formula measures (calculated from parent measure(s) with formula expression)
  • keyword-based filter by pivot table row/column labels
  • in-memory caching (can be configured for each cube separately)

PivotData REST API methods

<BaseUrl> = http://localhost:5000/    [Q] = query string parameter    [H] = request header    [BODY] = request body
URL Description Method Parameters Result
<BaseUrl>/api/cube Returns list of available cubes. GET
[ {CubeDescriptor}, ]
<BaseUrl>/api/cube/{cubeId} Returns specified cube metadata. GET
Cube unique identifier (from appsettings.json config)
<BaseUrl>/api/cube/{cubeId}/pivot/render Calculates pivot table and returns HTML+JSON metadata needed for front-end UI (jquery.pivottable.js plugin) that provides interactive data exploration functions (sorting, pagination). GET
[Q] pvtReportJson
PivotReport model JSON
JSON structure:
 "Configuration" : { /* Pivot table config metadata */ },
 "HtmlContent" : "<table> ...",
 "JsonData" : { /* JSON export without values */ }
[H] Content-Type
PivotReport model JSON
<BaseUrl>/api/cube/{cubeId}/pivot/export/{format} Calculate pivot table and export to specified format. GET, POST
[Q] format
supported export formats: csv, excel, pdf, json, html
[Q] fileName (optional)
File name in "Content-Disposition" header
[Q] pvtReportJson
PivotReport model JSON
(export file for download)

PivotReport model API parameter

Property Value Description
Rows array of PivotReport.Dimension models list of dimensions for pivot table rows; empty by default
Columns array of PivotReport.Dimension models list of dimensions for pivot table columns; empty by default
Measures array of PivotReport.Measure models list of measures for pivot table values; "Count" is used by default (or first measure if count is not present in the cube)
SubtotalColumns true|false render sub-totals for grouped column labels; false by default
SubtotalRows true|false render sub-totals for grouped row labels; false by default
GrandTotal true|false render grand total value; true by default
TotalsRow true|false render row with totals; true by default
TotalsColumn true|false render column with totals; true by default
LimitRows integer (N) render only first N rows; when limit is specified, pagination options for rows are ignored
LimitColumns integer (N) render only first N columns. When limit is specified, pagination options for columns are ignored
  "Limit" : 100,
  "Offset" : 0
specifies pagination options for rows; "Limit" = max number of rows, "Offset" = start riw; if not specified all riws are shown
  "Limit" : 100,
  "Offset" : 0
specifies pagination options for columns; "Limit" = max number of columns, "Offset" = start column; if not specified all columns are shown
Filter string comma or space separated list of keywords to match table row or column labels (this filter is applied after data aggregation); it is possible to specify dimension hints (like "year:2015") or starts-with/ends-with match type ("start*" or "*end")
  "paramName" : "val"
custom parameters (name-value) for the report; specified parameters should be defined in the cube configuration
OrderBy PivotReport.OrderOptions model table sorting options; by default rows & columns are sorted by labels (A-Z order)
 "Columns" : [ 
 "Rows": [ 
  {"Name": "Order Date (Year)"} 
 "Measures": [ 
 "SubtotalColumns" : true,
 "RowPage": {
  "Limit": 100,
  "Offset": 0
 "Filter": "2015,2016,USA",
 "Parameters": {
  "customer": "somevalue"

PivotReport.Dimension model API parameter

Property Value Description
Name string dimension name from cube's config
LabelText string custom label for this dimension; overrides LabelText from the cube's config
Format string custom format string for dimension keys; overrides Format from the cube's config
 "Name", "order_date",
 "LabelText" : "Order Date",
 "Format" : "{0:d}"

PivotReport.Measure model API parameter

Property Value Description
Name string measure name from cube's config
LabelText string custom label for this measure; overrides LabelText from the cube's config
Format string custom format string for measure values; overrides Format from the cube's config
Percentage GrandTotal | RowTotal | ColumnTotal calculate percentage value for this measure
Difference PreviousRow | NextRow | PreviousColumn | NextColumn calculate difference value for this measure
 "Name", "SumOfAmount",
 "LabelText" : "Amount (Sum)",
 "Format" : "{0:#,#.##}",
 "Percentage": "RowTotal"

PivotReport.OrderOptions model API parameter

Property Value Description
PreserveGroupOrder true | false preserves labels grouping order when sort by value is specified; false by default
order by values:
row or column index
table measure index (0 if not specified)
  "Axis", "Rows",
  "Direction" : "Descending",
  "Index" : 0,
  "Measure": 0

more components

  • PivotData Toolkit for .NET

    .NET components for manipulating multidimensional dataset (in-memory data cube) without OLAP server, aggregating huge datasets, calculating data for pivot table / chart reports, ASP.NET pivot table builder control.

  • Javascript Pivot Table

    Advanced features for pivot.js jQuery plugin: sort by values/labels, fixed headers, pivot table data for exports, drill-down event. ASP.NET MVC integration (optional): UI builder for report SQL query, export to CSV/ExcelPDF.