Release Notes PivotData Microservice Documentation

Get PivotData Microservice

Version 1.11.0 2024 November 19

SQL cubes: added DuckDB connector (can be used as a client for MotherDuck).
SQL cubes: added InitSql for SQL statements that should be executed before every report's query (the same connection/session).
Pivot report: added new difference options to display diff values as HTML suffixes (keep original values) + apply conditional colors depending on the diff sign.
Flat table JSON export: added row_mode that allows to serialize data rows as objects (key:value) and onlyRows to export only JSON serialized rows.
Expressions: fixed issues with difference/percentage/heatmap calculations when table contains custom HTML-formatted values.
Pivot export to Excel PivotTable: fixed incorrect output for "% of Column" measure calculation.
Pivot export to Excel PivotTable: fixed incorrect format when "Data bars" are enabled with "original value" mode.
Docker image nreco/pivotdataservice is now based on mcr.microsoft.com/dotnet/aspnet:8.0.
Web report builder: block table actions (sort/pagination/expand-collapse) while loading (when table is greyed).

Version 1.10.0 2024 August 26

Cube configuration: added SuppressDbErrorMessage option (false by default) which hides DB-specific exception message from client. When this option is enabled in case of DB error API returns "Database query execution failed (reference code: UNIQUE_CODE)" message. A reference code allows to locate this error details in the PivotDataService log.
Cube configuration: added Cache.CubeIdPrefix option that can be used for cached cube data isolation between users. This can be needed when dynamic cubes provider is configured, and different schema JSON of the same cube ID is returned depending on the user's context. To avoid use of potentially incorrect cached data special prefix can be set to isolate data caches of the same cube ID but accessed from different contexts.
Search-driven reporting: upgraded to NReco.NLQuery v.1.2 (a lot of improvements) + scoring function tuning (based on WikiSQL testing).
XMLA client connector: icCube OLAP server is now supported as a data source.
XMLA client connector: MDX token expressions Range and Where slicers - now it is possible to specify .& suffix to apply a key-based slicer. Added CustomSlicer helper to format a custom MDX expression based on a report parameter.
Expressions: added IsEmpty function which return true for: null, empty string, zero-length arrays or lists.
Flat table reports: fixed an issue with month-name columns filtering (formatted with {0:MMM} or {0:MMMM} specifiers).
Charts: jquery.nrecoPivotChart.js - fixed an issue with stacked bar chart (empty values).
Charts: patched chartist.js to fix incorrectly aligned first bar in specific situations.

Version 1.9.0 2024 January 16

Starting from this release the main build is for NET8 runtime. NET6 build is still provided until its EOL (2024-Nov). No build for legacy .NET Core 3.1 anymore (can be provided for existing customers upon request).
All lib dependencies are updated to the latest versions.
Drivers are also updated for: MongoDb, ElasticSearch, Snowflake, PostgreSql, Oracle, SQL Server, SQLite.
In this release PivotDataService binaries added significantly in size; the primary reason is the newest versions of drivers that have a lot of dependencies (esp. Snowflake.Data driver and Microsoft.Data.SQLite that adds a lot into "runtimes" to support all possible platforms).
CSV cube: fixed "Object reference not set" when report has the same measure selected twice and report has empty cells.
CSV cube: fixed column data type autodetection issue when CsvLineBufferSize was ignored (only Linux deployments).

Version 1.8.14 2023 November 02

Pivot report: now a measure percentage calculation can be shown as data bars (like in Excel / PowerBI). These data bars are preserved in report's PDF and Excel exports.
Pivot report JSON export: fixed an issue with HTML-formatted measure value when non-HTML value is not defined (affects pivot charts that use JSON data).
Export to Excel: complex numbers formatting with prefixes and separate negative/zero parts (like {0:$##,###,##0.00;$-##,###,##0.00;-}) now converted to Excel number formatting too.
Report parameters: now it is possible to specify parameter's placeholder (@paramName) in dimension's or measure's "LabelText" or "Format", in this way dynamic-grouping dimension (or dynamic measure) can have dynamic label / format that depend's on the parameter.

Version 1.8.13 2023 September 29

SQL cube: fixed an issue with subtotals + 'System.Object' items when 'group by cube' is enabled + Expression-type dimension + 'First Value' measure.
Pivot report: keep white-space values as-is in CSV exports (when EmptyKeyText is not set).
Pivot report: fixed an issue with group items order when SubtotalInsideGroup option is enabled and TotalsRowPosition is Last.
Pivot report: fixed incorrect handling of 'less than'/'greater than' conditions on dimensions in report's Filter.
Pivot report: now HeatmapColor accepts web color in hex format like #ff0000.
App settings: new option Export:CsvQuoteIfTrimPossible (appsettings.json).
Expressions: now it is possible to use local variables (example: var a = 10; var b = 5; a*b) + ternary (conditional) operator may be used in a sequence (example: a>10 ? "green" : a<0 ? "red" : "white").
Web report builder: ChartistJS upgraded + now nrecoPivotChart.js can render data labels for bar-type charts.

Version 1.8.12 2023 August 22

Pivot report: added new Styles.FontSize, Styles.FontName, Styles.GridLabelsHighlight options.
Pivot report: fixed incorrect colspan issue with prev/next pagination columns when report has several measures on values and "RepeatColumnKeysInGroups" is enabled.
Pivot report: fixed incorrect "data-value-index" for subtotals (used in drill-down handler).
Pivot report: fixed styles issue in Excel export (borders / style may lost for some cells under certain conditions).

Version 1.8.11 2023 July 17

Pivot report: added new SubtotalInsideGroup option.
Pivot report: added new Styles options (that affect web view, PDF and Excel exports).
Export to Excel: avoid number formatting with decimals for integer values to avoid excessive decimal point (like "5.").
Export to Excel (flat tables): now 'table' style is applied by default.
Pivot report: added special kind of dimension filter that keeps only top-N (or bottom-N) items in each group (works in the same way as Excel's PivotTable "Filter > Top 10"). Syntax: <dimension keyword>:top(10) or <dimension keyword>:bottom(10) or <dimension keyword>:top(10, 1) (where 1 is a zero-based measure index = use report's 2nd measure subtotals to determine top items).

Version 1.8.10 2023 June 05

Pivot report export to Excel PivotTable: fixed issue when HTML-formatted values are exported incorrectly and values for non-HTML export were not used.
Pivot report export to Excel PivotTable: now measure's "Format" is affects exported pivot table values.
Pivot table: fixed issue when "PreserveGroupOrder" was ignored when "ExpandCollapse" enabled (order was always by group's subtotals).
CSV cube: now flat tables based on CSV data can be ordered by multiple columns.

Version 1.8.9 2023 Apr 07

Pivot report: now ExpandCollapse mode takes into account pagination/limits ("RowPage"/"ColumnPage" and "LimitRows"/"LimitColumns") and they work in the same way as for non-collapsed pivot tables. Note that 'jquery.nrecopivottable.js' and 'webpivotbuilder.js' are updated too, these changes are needed for correct sort/drill-down handling for paginated pivot table with Expand/Collapse enabled.
Pivot export web API: added "jsonData" to make possible JSON exports without values (only keys).
Pivot report: added "DifferenceSameGroup" option for measures.
Oracle driver: added workaround to prevent "ORA-01882: timezone region not found".
Added "InferSchemaRules" section (appsettings.json) for additional filtering of auto-generated measures by type / column name.

Version 1.8.8 2023 Mar 06

Web API: now pivot/render and table/export/json endpoints return "Metadata" section with some info about DB query (data timestamp, query execution & data load time, DB query itself - only if "ExposeRawQuery" is enabled in appsetting.json)
Pivot API: added refresh=1 query parameter to force data load (execute DB query & ignore cached data if present)
Pivot report: fixed an issue with sort by totals when Type=FirstValue measure is used and dimensions are present for both rows and columns.
Pivot report: now it is possible to apply sort-by-value for both rows & columns (at least one of axes should be ordered by totals).
Pivot report: reset sort-by-value if sort column or row are collapsed (now behaviour is more consistent from user's point of view).
Format: now simplified format syntax may be used (without "0:" prefix), for example: {#.00} instead of {0:#.00}.
Format: in the case of incorrect format specifier more descriptive error message is returned.
Caching: more efficient cached data usage.
Expressions: added extra functions for Expression-type measures: PivotTable.IsCellRowTotal(), PivotTable.IsCellColumnTotal(), PivotTable.IsCellGrandTotal(), PivotTable.IsCellSubTotal().

Version 1.8.7 2022 Dec 27

CSV: fixed an issue with separator autodetection in some cases.
Pivot report: if chart is enabled now export to Excel generates one more worksheet ("Chart" by default) that contains series data and pre-configured Excel chart. It is possible to disable Excel chart feature with a special setting in 'appsettings.json' if needed.

Version 1.8.6 2022 Nov 11

SQL Server driver: upgraded to the latest version to mitigate CVE-2022-41064.
SQL connector: fixed an issue (SQL error) with GROUP BY <dim_name> when dimension has a custom SQL expression (affects, at least, SQL Server cubes). This issue affects only v.1.8.5.
SQL connector: now it is possible to use 'nested' report parameter placeholders like that: @param1[ col1={0} @param2[ and col2={0} ] ] (this applies "param2" only if "param1" is applied).
CSV connector: now it is possible to use report parameters in dimension's column-level expression. In this way it is possible to organize dynamic grouping criteria (controlled via report parameters).

Version 1.8.5 2022 Oct 20

SQL connector: now it is possible to use parameters (via @param_name syntax) in custom SQL expressions of "Field"-type dimensions/measures (1st entry in "Parameters").
CSV connector: added 'unpivot' to transpose a subset of CSV columns into values of a special dimension.
CSV connector: now it is possible to use "=" and "in" in the "FilterRelex" with a "(Split)" field even if it not used in the report (='unwind' of multivalue column is not performed). In that case comparison is performed by 'at least one' criteria.
Presto/Trino driver: now it is possible to use "X-Trino-" headers by specifying TrinoHeaders=1 in the connection string.

Version 1.8.4 2022 Sep 02

Pivot table HTML and Excel outputs: extra heading row with "Totals" is excluded if no columns and number of measures > 1.
Format: now it is possible to format weekday number (0-6) as day name with {0:ddd} (Mon, Tue etc) or {0:dddd} (Monday, Tuesday etc) format specifiers. Day names are resolved according to the PivotDataService culture code (in appsettings.json) and when these special formats are used for a dimension it is ordered in respect to culture's 'first day of week' (for "en-US" this is "Sunday"). By default, when culture code is not specified it is assumed that 'first day of week' is Monday.
Expressions: now functions Date.DayOfWeek and Date.DayOfWeekShort can accept a day-of-week number (0-6). Added Date.FirstDayOfWeek() function that returns a first day-of-week number (0-6) in respect to the PivotDataService culture code: for "en-US" this will be 0 (Sunday), for "en-GB" 1 (Monday). This function is useful for ordering weekdays (names) using SortBy function in respect to the culture code.
Flat table JSON export: fixed errors handling that occur on data read (show relevant error instead of just JsonWriterException: No token to close. Path '').
Measures: Sum and Average behaviour now aligned with SQL when no aggregated rows (when Count=0 aggregator's Value=null instead of 0).
nrecoPivotChart.js: keep nulls for datapoints for non-pie chart types.

Version 1.8.3 2022 Sep 02

Pivot reports: now all DB connectors work asynchronously (scalability / performance improvement).
Dynamic cube schema providers: added CacheByUrl option to enable schema caching controlled with "If-Modified-Since" / "Last-Modified" HTTP headers (performance improvement).
pivot/render endpoint perfromance: significantly reduced RAM usage for large HTML outputs.
Export to Excel: now custom "Format" for dates/timestamps is converted to Excel formatting if possible.
Export to Excel (pivot table reports): now numbers and dates are not exported as strings for dimensions as well + SeekTable "Format" is converted to Excel formatting if possible.

Version 1.8.2 2022 Jun 14

Starting from this release main build is for NET6 runtime; a build for .NET Core 3.1 is still provided.
Export to Excel: now links are exported if cell's content is formatted with Html.Link function.
Export to Excel: now "Format" rules are converted into Excel's equivalents when possible.
Export to Excel PivotTable: value calculations (percentage of totals, difference from prev/next col/row, running value) are translated into Excel's PivotTable equivalents.
Export to Excel PivotTable: now axes (rows/columns) are sorted according to the web view's selection.

Version 1.8.1 2022 Apr 02

PivotReport JSON: now it is possible to control subtotals for each dimension separately (Subtotals).
Export to Excel PivotTable: added Export:ExcelPivotTableCompact option that can disable 'compact' pivot table view in Excel.

Version 1.8.0 2022 Mar 17

Custom connector (web API data source): now it is possible to implement a custom connector in the form of a web API endpoint that can process PivotDataService queries (JSON). Sample custom connector implementation is here.
Expressions: added new functions Date.ISOWeek.GetWeekOfYear, Date.ISOWeek.GetWeeksInYear, Date.ISOWeek.GetYear, Date.ISOWeek.GetYearStart, Date.ISOWeek.GetYearEnd.
CSV connector: now it is possible to split multi-value column values with regex (when multi-value metadata is provided in dimension's "Params").
CSV connector: ability to use columns that have a duplicate header name.
Oracle driver: updated Oracle.ManagedDataAccess.Core to 3.21.50 (should fix "oracle error timezone ora01882" without need to specify TZ env variable).
Mysql driver: updated MySqlConnector to 2.1.8.

Version 1.7.6 2021 Dec 02

Added app setting EnableResponseCompression to enable HTTP response compression on PivotDataService side.
API error handling: use JSON response convention in case of 404/NotFound.
CSV cube: now it is possible to specify column's metadata explicitely in dimension's "Params".

Version 1.7.5 2021 Oct 25

SQL connector: added Presto/Trino driver to use PrestoDB/TrinoDB as a data source.
SQL connector: fixed "InferSchema" for columns that contain a dot (.) in the name.
SQL connector: now "PivotFilter.ApplyAsCondition" is compatible with "SourceDb.UseGroupByCube". This means that sub-totals/grand-total will work when a report uses "FirstValue" (custom SQL) measure AND "Filter" is fully translated into DB WHERE conditions.
SQL connector: now "PivotFilter.ApplyAsCondition" translates filter entries for the same dimension with AND.
API error JSON details for 'pivot/export' endpoint + now available for non-SQL DBs too.

Version 1.7.4 2021 Sep 03

Expressions: added Html.SetTextColor and Html.SetBackgroundColor functions that work in the same way for HTML, PDF, JSON and Excel exports.
Expressions: fixed an issue with Html.Raw(value) (one argument) function.
Excel export: fixed an issue with row subtotal labels colspan.

Version 1.7.3 2021 Aug 16

Pivot table: added report options RepeatRowKeysInGroups and RepeatColumnKeysInGroups.
API errors: more details in the error JSON + now it is possible to include an exception details.
API usage limit: now it is possible to limit the number of concurrent report generations depending on the criteria. Criteria could be: a HTTP header value, cube ID or the report config itself (or combination of these).
ElasticSearch: now it is possible to use a field in the "FilterRelex" even if it is not declared as a dimension.
MongoDb: fixed compatibility issue with Azure CosmosDB (pivot reports without dimensions).
ClickHouse: correctly handle sub-totals when "Use GROUP BY CUBE" option is enabled.
Logging: now report parameters are included into log entries.

Version 1.7.2 2021 Jun 10

Hotfix for pivot tables caching issue that occurs in case of SQL-based cube AND if measure "Parameters" contain custom SQL expression (with special symbols that cannot be used in the DB identifier).

Version 1.7.1 2021 Jun 01

PivotTable expand/collapse: fixed issue with groups duplication when axis is ordered by values (occurs when 2 groups have the same value that is ordered)
PivotTable JSON export: now it is possible to include subtotals (controlled with a query parameter)
MongoDb: for unwinds added PreserveNullAndEmptyArrays option
Export API: fixed issue with non-ASCII characters in the download filename
Drivers updates: MySqlConnector (1.3.9), Npgsql (5.0.5), ClickHouse.Client (3.1), Snowflake.Data (2.0.1), MongoDB.Driver (2.12.3), Oracle.ManagedDataAccess.Core (3.21.1).
Starting from this release a build for netcore21 is no longer provided. It is still possible to get this build upon request.

Version 1.7.0 2021 Mar 16

Added an alternative Clickhouse driver (ClickHouse.Client) that connects with HTTP(S) interface. To use this driver it is enough to specify Driver=ClickHouse.Client in the connection string.
Default ClickHouse driver (that connects with native TCP/IP protocol) was updated to fix 2 issues: now it supports LowCardinality data types + handles correctly high-scale decimals that cannot be represented with .NET System.Decimal type (now such values are converted to System.Double with a possible precision loss).

Version 1.6.9 2021 Feb 22

Oracle: fixed issue "Cannot compose 'XYZ' aggregator state from field 'AAA': Specified cast is not valid" related to decimals handling in ODP.NET
Connection secrets: now it is possible to define name-value pairs in appsettings.json (or ENV variables) and use @name placeholders in the connection strings/URLs. This is useful when cubes JSON config is loaded dynamically.
Flat table: fixed issue with "Filter" condition generation: with exact-match hint is specified do not generate OR for partially-matched column names
SQL connector: fixed issue with Expression-type measure uses "FirstValue" measure as an argument + "GROUP BY CUBE" option is enabled
SQL connector: fixed SQL generation issues with "FirstValue" measure type (BigQuery: "Fields must contain only letters, numbers, and underscores", SQL Server: "the identifier is too long").
Driver update: Oracle.ManagedDataAccess.Core (2.19.101).

Version 1.6.8 2021 Jan 17

Expression-type measures: added ability to use pivot table totals in the formula: PivotTable.GrandTotal("measure_name"), PivotTable.RowTotal("measure_name"), PivotTable.ColumnTotal("measure_name").
Expression-type members: added ability to specify a separate value for sorting purposes with SortBy("displayValue", "sortValue") function.
Drivers updates: System.Data.SqlClient (4.8.2), Npgsql (4.1.7), ClickHouse.Ado (1.2.6), Snowflake.Data (1.1.4), MongoDB.Driver (2.11.5).

Version 1.6.7 2020 Nov 24

JWT auth: now encrypted JWTs are supported (for decoding TokenDecryptionKeyString should be set in the "Auth":"Jwt" section)
JWT auth: alternative header name may be used instead of "Authorization" with AltHeaderName (in the "Auth":"Jwt" section)

Version 1.6.6 2020 Oct 20

SQL connector: fixed SQL generation issue when dimension name contains '-' (Type=Field)
SQL connector: fixed SQL generation issue when flat table is ordered by column that is NOT included into the report
CSV connector: better heuristics for multi-value columns detection + now it is possible to add a dimension with " (Split)" suffix manually (even if column is not detected as multi-value)
ElasticSearch connector: added option to skip SSL certificate validation (useful if self-signed certificate is used)
Pivot table: fixed high CPU load issue when report has multiple measures (>2) with 'running total' calculation

Version 1.6.5 2020 Aug 17

SQL connector: now Snowflake is supported as a data source (with official ADO.NET provider is used)
Flat table: now numbers/dates in Excel export are shown as typed values
Report parameters: added Sql.Raw function that can be used in parameter's expression to insert 'raw' (unescaped) string value into the SQL query

Version 1.6.4 2020 July 08

now search API works for all supported data sources (not only for CSV cubes)
added cube config "Search" section (affects search API)
SQL connector: fixed issue with measure Type=FirstValue (incorrect SQL syntax when custom SQL expression contains escaped column name)
Report parameters: now it is possible to check what dimensions/measures are used in the report (in "Expression")
ElasticSearch: fix for 'Unexpected character while parsing path' when field name with a space used in a flat-table report

Version 1.6.3 2020 May 05

Added support for IIS in-process hosting model in .NET Core 3.1 build
Now PivotReport options Labels.TotalsColumnText and Labels.TotalsRowText are applied for all export formats (not only for HTML output)
Fix for 'Unknown dimension' error message

Version 1.6.2 2020 Mar 24

Now official .NET Core build targets ASP.NET Core Runtime 3.1 LTS which offers better performace and decreases RAM usage.
Build for .NET Core 2.1 LTS is considered legacy but still provided until 2.1 LTS EOL (2021-Aug-21).
Build for .NET Framework 4.6.1+ now provided only upon request.
Cube member format: added simple way to specify custom text if value is empty or null with {0:ifempty=N/A} format specifier.
Report parameters: fixed an issue with @param[ tpl_if_param_defined ; tpl_if_not ] placeholders - now it is possible to provide alternative template for case if parameter is not defined.
Excel export: preserve new-lines in text content cells
SQL cube: now query parameters are included into console log output.
Flat table: better SQL errors handling in certain cases (affects HTML/CSV/JSON exports only).
Flat table: more relevant error message if report configuration doesn't contain columns at all.
Pivot table: added special null output format that executes data source query and populates in-memory cache but don't render anything.
Pivot table: fixed issue with incorrect table order by values in case of "Last N" limits.
Cube members with Type=Expression: added CubeP function that is similar to Cube but accepts 2nd argument for parameters.
Front-end demo / widgets changes:
- jQuery updated to latest 3.4.1
- legacy jQueryUI sortable replaced with sortableJS (supports touch screens)
- compatibility with latest select2 4.0.12+

Version 1.6.1 2020 Jan 31

XMLA Client connector: now "InferSchema" is supported (dimensios and measures may be determined automatically)
XMLA Client connector: added support of 'range' filters
SQL Connector: now it is possible to use more than one 'parent' field in the "Conditional JOIN"
SQL Connector: fixed issue with pivot report without dimensions when "Use GROUP BY CUBE" is enabled
CSV connector: now when date-part dimensions are inferred their names have format like <date_field>.Year
CSV connector: fixed issue with 'unknown measure type Median' error
Pivot Report: now validation of max table size takes into account expand/collapse mode
Pivot Report: improved performance of rendering in expand/collapse mode
Pivot Report: new options TotalsRowPosition, TotalsColumnPosition
Pivot Report: negative values in LimitRows, LimitColumns handled as 'show last-N'
jquery.nrecopivotchart.js: added support of legends and animations
Added example: flat-table report builder
Updated database drivers: MongoDB.Driver (2.9.3), MySqlConnector (0.61.0), NpgSql (4.1.2), Oracle (2.19.60)

Version 1.6.0 2019 Dec 12

XMLA Client connector^new: now PivotData microservice can connect to OLAP cubes that are accessible via XMLA-over-HTTP(S) endpoints like SQL Server Analysis Services and Azure Analysis Services.
- custom MDX SELECT template, you can define custom sets using WITH
- drill-down filter can be translated into MDX slicers (any-level drill-downs are possible)
- report parameters are supported (only 'equals' comparison for dimension keys)
Added tabular report builder example (usual data tables that displays DB rows, no aggregation)
When Expression-type dimension uses Html.Raw function with 2 arguments, pivot table is ordered by 2-n argument (non-HTML value)
Additional specifiers for cube members "Format": format month number to short or full month name, format large numbers with k/M/B suffix
Difference calculation: now sub-totals are calculated if possible
CSV connector: fixed issue in flat-table API when both offset and sort are specified
CSV connector: added option to specify a separator explicitely
MongoDB.Driver upgraded to 2.9

Version 1.5.5 2019 Sep 30

SQL connector (any DB): now it is possible to calculate totals/sub-totals of custom-SQL measures with GROUP BY CUBE syntax. This feature is disabled by default and can be enabled with cube config "UseGroupByCube" option
Added ability to translate pivot table user-defined filter (textbox, also used by drill-down) into database query conditions when possible. This feature is controlled by cube configuration "PivotFilter" options
Added "FormatCulture" option (both global & cube-specific) to configure culture-specific formatting for numbers/dates
Added "Properties" option for cube members definitions. In this way it is possible to add your own metadata and use it in front-end code.
MongoDb connector: more memory-efficient handling of large aggregate query result

Version 1.5.4 2019 Aug 12

ElasticSearch connector: correct sorting of dimension number values calculated with 'painless' script (like year/month/day date parts)
ElasticSearch connector: added ability to define custom bucket aggregation type (JSON) for dimensions
ElasticSearch connector: added ability to define custom metric aggregation (JSON) for measures with "FirstValue" type
MongoDb connector: set HeartbeatTimeout=10 (seconds) if not specified in the connection string to prevent usage of 'zombie' connections from pool
Expressions: added Date.TimezoneUtcOffsetHours function that unlocks ability to use datetime 'range' filter for concrete timezone
Added global "AggregateRowsLimit" option (appsettings.json) to override default limit (1,000,000) for max number of aggregated rows

Version 1.5.3 2019 Jul 20

Pivot table rendering: now if cell value is 'object' or 'array' it is rendered in JSON format.
MongoDb connector: fixed issue with dimensions that use custom projection definition in flat table reports.
MongoDb connector: correct unwinds generation by InferSchema for fields with arrays of simple values.
MongoDb connector: inlcude incorrect JSON into exception in case of incorrect config.
ElasticSearch connector: fixed ES query generation for "in" condition + parse dimensions values as numbers even if ES returns them as strings (if data type is specified or get-mapping API is allowed).
jquery.nrecopivotchart.js: display only integer values for axes if all values are integers
jquery.nrecopivottable.js: added 'fixedHeadersDisableByAreaFactor' option that disables fixed headers if they take too much space
Expressions: now String.Join accepts any array/list or even single value, added Math.Round, Math.Sqrt, Math.Pow functions.
Connector driver update: MongoDB.Driver 2.8.1, Oracle.ManagedDataAccess.Core 2.19.3

Version 1.5.2 2019 Jun 17

ElasticSearch connector: now flat tables use 'scroll API' if rows limit > 10k.
ElasticSearch connector: fixed issue with pivot table without dimensions (grand total only).
MongoDb connector: fixed 'Duplicate element name' error (can occur when dimension is resolved with $lookup stage)
Expressions: now Html.Raw accepts (optional) 2nd argument for value that is used in non-HTML exports (Excel, CSV).
Expressions: now Parameter["param_name"] can be used in calculated cube members.
Connector drivers update: MySqlConnector 0.55, Npgsql 4.0.7
Better errors handling and other minor improvements

Version 1.5.1 2019 Apr 02

CSV cube: added ability to specify filtering expression for rows which unlocks usage of report parameters for this kind of data sources.
CSV cube: fixed parse issue of timestamps that end with "GMT+X:YZ"
CSV cube: added detection of multivalue CSV columns. These values may be deconstructed and processed separately when report uses CSV column dimension with suffix " (Split)".
Report parameters: added ability to specify an expression that is evaluated to resolve parameter value for the data query.
Export to image: now it is possible to render pivot table or flat table to PNG image.
Expressions: added Regex.Replace and Regex.IsMatch functions.

Version 1.5.0 2019 Feb 22

Expand/collapse mode for pivot tables with 2 or more dimensions for rows and/or columns
Expressions: now functions Html.Raw and Html.Link may be used for custom HTML in pivot table cells
New pivot report options: custom 'emtpy' label text, default value for empty cells, '...' text, "Totals" header text, sub-totals labels suffix
Fix: SQL errors in some cases when "Select Query" starts with "select * from"
Fix: correct handling of column names with spaces
Fix: correct handling of sub-objects in ElasticSearch results
Fix: row headers are not rendered if no columns and totals column is disabled
Fix: correct handling of expression-type measure when this is only measure in the pivot table
Fix: type-cast error in case of CSV source + flat table + filter by number-type column

Version 1.4.4 2019 Jan 22

MVC Core updated to 2.2, removed excessive dependencies (-9mb in distribution size)
Export to PDF: ability to define custom 'tokens' and use them in the template
ElasticSearch connector: better error handling of some strange responses
SQL connector: correct handling of select query with hardcoded 'ORDER BY'
export to Excel PivotTable fixes: when several measures are used, when label (either dimension or measure) has special symbols (like ampersand).

Version 1.4.3 2018 Nov 29

ElasticSearch connector: added support of parameter Type=Condition.
ElasticSearch connector: include into 'Count' aggregation results documents without dimension field ('missing').
SQL connector (all databases): resolve custom SQL expressions for dimensions used in parameter Type=Condition
PDF Export: better errors handling when underlying tool (wkhtmltopdf) returns error. Now PDF with error message is returned.
PDF Export: added more options that may be configured to prevent server overload with huge exports (execution timeout, process priority, max allowed input HTML size).

Version 1.4.2 2018 Nov 19

ElasticSearch connector: added ability to specify SQL-like filter expression (enables ability to use report parameters).
Now all 'technical' limits are configurable (max size of lookup for "Cube.Lookup" function, max number of rows for exports etc).
Bugfix: custom format is ignored in 'flat table' JSON export.
Bugfix: error when 'flat table' report has expression-type column.

Version 1.4.1 2018 Oct 22

Now native "oracle" connector is supported in .NET Core build of PivotData microservice
More efficient flat table API: async, do not use data buffer for SQL data sources (stream processing)
Dynamic cube schema URL provider: more efficent cache invalidation when cube config changes
MongoDb connector: add query limit to prevent loading of huge resultset that exceedes "AggregateRowsLimit"
ElasticSearch connector: correct infer schema for legacy ElasticSearch (<6.0)
ElasticSearch connector: fixed issue with empty grand total when no dimensions selected
Pivot table JSON export: fixed incorrect output for expression-based measures

Version 1.4.0 2018 Sep 14

added flat table API for reports by non-aggregated rows
added max pivot table cells option (helps avoid heavy server load if user selects incorrect dimensions for the report)
added examples of canned reports: pivot table, chart, dashboard
new heatmap options: HeatmapColor, and new Heatmap modes: RowTotal, ColumnTotal
avoid unnecessary data transformations in some cases
ElasticSearch connector: bugfixes
CSV connector: correct detection and parsing of datetime values with timezone like EST
SQL sources: fixed issue with incorrect SQL when measure is configured for column with table alias
SQL sources: fixed issues with incorrect SQL for "oracle" connector
Pivot table report: fixed 'Index out of range' when several expression-based measures use the same arguments
Pivot table report: added ability to combine measures from several cubes (data sources)
Pivot table report: added ability to resolve lookups by data from another cube
Pivot table export API: added valueFormatting option that can force values formatting for JSON/Excel output
Pivot table JSON export: fixed issue with incorrect handling of formula (expression-based) measures
Now main build is for .NET Core 2.1 (which is LTS release).
Alternative build for .NET Framework 4.6.1+ is also available.
Builds for legacy versions of .NET Core (1.0, 2.0) may be provided upon request.
Now official nreco/pivotdataservice docker image is available.

Version 1.3.2 2018 May 14

added ElasticSearch connector
MongoDb connector: fixed issue with inferred unwinds and "Element '_id' not found" error
make front-end code (index.html, webpivotbuilder.js) compatible with latest jQuery 3.3.x and select2 4.0.5
jquery.nrecopivottable.js: fixed headers now based on position:sticky for better UX and smooth scroll
fixed issue with CSV separator detection in some cases
fixed issues with keyword-based filter, now it is possible to specify conditions for dimensions
a lot of other fixes of minor bugs

Version 1.3.1 2018 Mar 22

added ability to filter by table values (like "count<50", "sum>=100")
Front-end (webpivotbuilder.js): now typeahead is used for filter autocomplete
alternative way to provide JWT auth in POST forms parameter (useful for report export download with HTML form)
MongoDb Driver updated to latest version (supports MongoDb 3.6)
added more helper functions to expressions evaluation context (like Date.Week, Date.DayOfWeek)
added "Quantile" (median) aggregator (can be used if aggregation is performed by microservice)
other minor-issues fixes
now .NET Core 1 build is provided only upon request

Version 1.3.0 2018 Feb 20

added microservice .NET Core 2 build (with "odbc" connector) in addition to .NET Core 1 and .NET Framework 4.6.1 builds
added "odbc" connector options for SQL-dialect
drill-down by click (front-end feature)
added "FirstValue" measure type for custom database aggregation functions
added export to Excel PivotTable
added chart rendering in PDF export (template is customizable)
added row-level expression-based dimensions in CSV connector
upgraded to latest PivotData Toolkit components:
- CSV files processed 3x times faster with a new parser
- sub-totals are calculated 10x times faster
- many other fixes and improvements
bugfixes:
- incorrect infer schema for PostgreSql
- incorrect detection of CSV separator in some cases
- prevent out-of-memory exceptions for large pivot tables

Version 1.2.0 2017 Sep 14

star-schema support for SQL databases: added "JoinsAfterGroup" option
star-schema support for MongoDb + more control over aggregate pipeline: added "CustomStagesBeforeGroup" and "CustomStagesBeforeGroup" options
added MongoDb connector options: "AllowDiskUse", "MaxTimeMS"
added .NET Framework 4.6.2 build (supports "oracle" and "odbc" connectors)
front-end updates: new chart types (stacked area, donut), smooth scroll on mouse wheel
SqlDb connector optimization: avoid inner select when possible (when "SelectSql" like "SELECT * FROM ...")
trace all SQL/Mongo queries to log when "LogLevel" for "NReco.PivotData.Engine" = "Debug"
added integration example (MVC Core): secure API with JWT, provide cubes config dynamically from main app
bugfixes:
- SQL Server connector: incorrect infer schema
- PostgreSQL connector: column does not exist exception for
- CSV connector: correct handling of CSV headers with special symbols (like newline)
- server error (500) returned by export API for some reports
- ignore incorrect report order parameters instead of throwing an exception

Version 1.1.1 2017 Jun 10

added new options for pivot report measure:
- Heatmap: enables heatmap table styling
- RunningTotal: calculate running total value
- Difference: calculate difference value
- DifferenceAsPercentage: calculate difference value as percentage
SQL data sources:
- implemented InferSchema option
- added ability to specify custom SQL expression for dimension
- bugfix: pivot report without dimensions
MongoDB data sources:
- implemented InferSchema option
- added ability to specify custom aggregation pipeline operators for dimension

Version 1.1 2017 May 22

new connector for CSV/TSV files:
- automatic encoding/delimiter detection (tab-delimited, semicolon-delimited etc)
- can infer cube schema automatically, no need to specify dimensions/measures
- supports gzipped and zipped files
- effectively handles date-part dimensions (year/quarter/month/day)
- fast: query to 200mb CSV file is processed in seconds
new connector for Yandex ClickHouse database
added report parameters UI to sample web pivot builder (index.html), + enhanced web API to return parameters metadata
added support for dynamic data sources configuration (provided by URL, URL may use JWT context values)
SqlDb, MongoDb connectors: added ability to use parameter in the connection string (useful for SaaS-apps)
MongoDb connector fixes: throw error when sub-collection is used without unwind, avoid KeyNotFoundException when field is not present in the document
Fixed in-memory cache issue for some cube configurations
add logging to a file (configurable)
switched to .NET Core 1.1 (better performance and stability)

Version 1.0-final 2016 Dec 16

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

Version 1.0-beta3 2016 Oct 29

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

Version 1.0-beta2 2016 Oct 10

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

Version 1.0-beta1 2016 Sep 26

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)