PivotData Microservice Web pivot tables and pivot charts for SQL/MongoDb
.NET Core backend can be hosted on Linux or Windows

PivotData Web API can be used with any REST Client

features on-premise web API for pivot tables

  • based on NReco PivotData SDK:
    • fast in-memory OLAP operations (no ETL, real-time reporting)
    • built-in ROLAP engine for SQL / MongoDB / CSV data sources
    • efficiently works with star-schema data marts
    • aggregate functions: count/count unique, sum, average, min/max, variance/standard deviation, list/list unique, custom formula
    • calculated members: derived dimensions and formula measures
  • advanced HTML pivot table features:
    • labels grouping, sub-totals
    • multiple values in one table
    • sort table by values, totals or labels
    • calculations: percentage, difference, running total
    • custom formatting options (numbers, dates), heatmap table styling
    • server-side pagination and top N limits for large pivot tables
    • easy data exploration with drill-down function
    • user-friendly keyword-based filter for rows & columns
  • report parameters for database query filters:
    • any number of custom parameters
    • multi-value parameters (IN conditions)
    • custom user-defined conditions (and/or)
  • export web pivot table report to HTML, PDF, CSV, Excel (worksheet with pivoted data), Excel PivotTable, JSON
  • includes web pivot table & chart builder JS control for self-service analytics, user-defined reports
  • supported data sources (connectors):
    • MS SQL Server (Azure SQL), MySQL (MariaDB, MemSQL), PostgreSQL (Amazon Redshift), SQLite, Yandex ClickHouse
    • any other SQL-compatible database with ODBC driver
    • MongoDB (aggregation pipeline), Azure DocumentDB (MongoDb protocol)
    • large CSV/TSV files (up to 1GB)
  • easy integration with any web application (ASP.NET, Java, PHP, Ruby, NodeJS, Python etc)
  • PivotData microservice .NET Core app can be hosted on Linux, Windows or MacOS

download and pricing Verified Quality by financesonline.com

PivotData microservice is a simple way to add self-service analytics with pivot table and pivot chart reports into any web application. Data sources are configured with simple JSON config, all functions are provided with web API. You can add HTML 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 PivotData Microservice binaries.
  2. Deploy to Linux or Windows server
  3. Configure microservice by editing appsettings.json file
  4. Review web pivot builder examples:
    • index.html
    • condition-builder.html
    Use sample code to embed pivot tables and charts into your web app.
  5. For other usage scenarios explore microservice web API

online demo

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

frequently asked questions

Typical PivotData microservice usage:
  • embed end-user self-service web pivot table builder into existing web application (without OLAP server).
  • replace Excel PivotTable files and/or SSAS with centralized web pivot tables and lightweight ROLAP solution.
  • get real-time analytics and ad-hoc BI reporting for SQL-compatible and MongoDB data sources, without any limitations on the dataset size.
  • aggregate data, calculate metrics for custom summary reports and web BI dashboards
  • automate reports generation on the server-side (scheduled reports, exports)
Web pivot table builder (js front-end) is highly customizable; it is possible to change layout, use another controls for report configuration etc.
SQL Data source documentation page explains how configure SQL-compatible database as a data source. Currently the following SQL databases are supported:
  • MS SQL Server, Azure SQL ("mssql" connector)
  • MySQL, MariaDB, MemSQL ("mysql" connector)
  • PostgreSQL, Amazon Redshift, PipelineDB ("postgresql" connector)
  • Yandex ClickHouse ("clickhouse" connector)
  • SQLite local file ("sqlite" connector)
  • any SQL compatible database with ODBC driver (not available in .NET Core 1 build)
  • Oracle (.NET Framework only build, will be available soon in .NET Core 2 build)
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
MongoDb Data source documentation page explains how configure Mongo database as a data source. Microservice uses aggregation pipeline and report generation time mainly depends on your cluser performance. 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"
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 answer is yes: microservice works as ROLAP engine and delegates data aggregation to the undelying data source, and this can be distributed database with billions of records like:
  • Amazon Redshift
  • MongoDb
  • Yandex ClickHouse
  • Google BigQuery (with ODBC driver)
  • Vertica (with ODBC driver)
For real-time reporting database should able to execute aggregate queries fast enough (in seconds - up to minute), and no matter how large is your dataset.
The following features may be implemented in the future releases:
Data sources
  • OLAP Server (XMLA/MDX) connector
  • ElasticSearch connector
  • JSON connector / URL as data source
  • Druid connector
  • Oracle native connector currently supported only in .NET Framework build. Note: it is possible to connect to Oracle with ODBC connector.
  • IBM DB2 native connector. Note: it is possible to connect to DB2 with ODBC connector.
  • Google BigQuery native connector. Note: it is possible to connect to BigQuery with ODBC connector.
  • SalesForce connector (SOQL)
Data cubes
  • External dimension lookups (in-memory dictionaries)
  • Hybrid cubes merged from several data sources (data blending)
  • search-driven analytics: build reports with natural language queries (already available as experimental feature for CSV sources)
  • custom reports defined by HTML templates + export to PDF or image
  • endpoint for basic XMLA/MDX that can be used from Excel, BI tools, 3rd party pivot table controls
Pivot table & UI (front-end components)
  • Collapse/expand label groups
  • Web API for pivot chart exports: HTML, PDF, render to image (JPEG/PNG)
  • Add ability to filter by pivot table values (like "exclude values <50")
If you are interested in one of these features please let us know.

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, exports to CSV/Excel/PDF.