Release Notes PivotData Microservice Documentation

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 connectornew: 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)