BI Toolkit Release Notes PivotData Documentation

Version 1.7.0 (latest) 2023 Sep 28

  • PivotTableHtmlWriter: fixed incorrect "data-value-index" for subtotals (affects 'drill-down' click handler on subtotals).
  • PivotTableHtmlWriter: fixed incorrect rowspan value for 'prev'/'next' columns pagination cells (only when report has multiple measures on values).
  • PivotTableHtmlWriter and PivotTableExcelWriter: fixed an issue when RepeatKeysInGroups is enabled for columns and number of measures > 1.
  • PivotTableHtmlWriter and PivotTableExcelWriter: added new SubtotalInsideGroup option (determines where subtotals are rendered: if true subtotals go as an item inside group like in Excel PivotTable; otherwise it is placed on the same level as group).
  • PivotTableExcelWriter: now default styles (including borders/labels lightgrey bg) are consistent with HTML output + added named styles to allow overrides as this is possible for HTML output with CSS rules (via CreateStyles override).
  • PivotTableCsvWriter: added CsvQuoteIfTrimPossible option that controls whether to quote values that may be trimmed (with leading or trailing white-space chars).
  • CubeKeywordFilter: fixed incorrect handling of 'less than'/'greater than' conditions on dimensions (like 'year>2020').
  • CubeKeywordFilter: added an extension point to handle custom filters (via ResolveSpecialKeyword override). For example, top-N group filter dimension:top(5) can be added in this way.
  • Added CubeGroupTopFilter class to filter pivot table groups by top-N (or last-N) criteria.
  • ToolkitPivotBuilderMvc example: nrecoPivotChart fix for horizontal bars (height) + option to show data labels for bar charts

Version 1.6.2 2023 Apr 17

  • PivotTableHtmlWriter and PivotTableExcelWriter: do not render extra "Totals" header row if no columns and number of measures > 1.
  • PivotTableFactory.SortByValue is now 'protected' to allow inheritors to implement custom sort-by-value behaviour (in particular, possibility to order by values by 2 axes).
  • DifferencePivotTable: added SameGroupOnly to calculate difference only between values that belong to the same group.
  • PivotTableHtmlWriter: now it is possible to apply CollapsePivotTable wrapper for interactive HTML rendering. This unlocks possibility to use PaginatePivotTable or TopPivotTable with collapsed pivot tables. Old way (when CollapsePivotTableConfiguration is handled inside PivotTableHtmlWriter) is preserved so this is backward-compatible change.
  • ToolkitPivotBuilderMvc example: now "Expand/Collapse" mode fully supports pagination / top-N limits
  • ToolkitPivotBuilderMvc example: added "Name"/"LabelText" that may be assigned for measures in CubeSchema (to avoid index-based measure references).

Version 1.6.1 2022 July 28

  • Fixed incorrect assembly version in 1.6.0 release (can cause project's build error).
  • PivotTableExcelWriter: now it is possible to override AutoFitColumns to implement custom cells width adjustment logic + fixed an issue with width calculation when pivot table doesn't have dimensions on columns.

Version 1.6.0 2022 July 25

  • Added netstandard2.1 build. All async implementations are available only for this build.
  • IPivotDataSource: added ReadDataAsync with a default implementation (that simply fallbacks to ReadData).
  • DbCommandSource: added ReadDataAsync implementation.
  • GroupedSourceReader: added ReadStateAsync / ReadAsync implementations that work asynchonously if provided IPivotDataSource supports that.
  • FixedPivotData is a thread-safe now and can be accessed from multiple threads simultaneously.
  • ToolkitPivotBuilderMvc example: enable chart legend on the right by default.

Version 1.5.5 2022 April 12

  • PivotTableExcelWriter: fixed issue with incorrect colspans for subtotals/collapsed rows
  • PivotTableExcelWriter: added ApplyDimensionKeyStyles protected method (used to customize 'headers' cells).
  • PaginatePivotTable: fixed an exception when CompositeAggregator has only one nested aggregator.
  • CubeKeywordFilter: added Filter overload that returns actually applied filter (reconstructed conditions, all with 'hints').
  • CsvSource: added an ability to handle CSVs with duplicate headers (column's index is added to the name to make it unique).

Version 1.5.4 2021 May 29

  • RunningValuePivotTable: fixed issue with high CPU usage / slow computations in case of multiple measures
  • PivotTableJsonWriter: now it is possible to include sub-totals (this is controlled by SubtotalRows and SubtotalColumns properties)
  • PivotTableFactory: fixed issue with incorrect grouping in expand/collapse mode (the same group may duplicate many times when axis is ordered by values)
  • ToolkitPivotDataServiceSource example: enhanced to support loading of pre-aggregated totals from PivotDataService JSON export.

Version 1.5.3 2020 May 27

  • FixedPivotData: more efficient GetEnumerator implementation
  • PivotTableHtmlWriter: fixed issue with incorrect colspan for disabled totals row when grand-total is enabled
  • PivotTableFactory: added protected CreatePivotTableInstance method
  • CubeKeywordFilter: added ResolveDimensionKey property (used for matching formatted values)
  • ToolkitPivotBuilderMvc example changes:
    • legacy jqueryui.sortable replaced with SortableJS
    • updated jquery.nrecopivotchart.js: added option to enable chart animations
    • updated webpivotbuilder.js: added options for totals row/column placement (at start/at end)

Version 1.5.2 2019 Dec 30

  • DifferencePivotTable hotfix: fixed issue with incorrect aggregator type when underlying PivotData is configured with CompositeAggregatorFactory (which causes 'invalid cast' exception)

Version 1.5.1 2019 Dec 11

  • HTML/Excel/CSV/DataTable writers: added ability to render totals at the beginning of the group (TotalsRowPosition/TotalsColumnPosition properties)
  • TopPivotTable: added 'last N items' mode (controlled with ColumnTopMode/RowTopMode properties)
  • DifferencePivotTable: now differences are calculated for sub-totals if possible

Version 1.5.0 2019 Mar 17

  • Expand/collapse (new feature): implemented as CollapsePivotTable wrapper (used in exports) + PivotTableHtmlWriter.CollapseConfiguration property (used for interactive expand/collapse).
  • Added PivotTableConfiguration.SortGroupsBySubtotals option that determines how group labels are ordered if PreserveGroupOrder is enabled
  • Fixed issue when row-headers are not rendered when no columns and column with totals is disabled
  • PivotDataDataTableWriter: added ability to include sub-totals
  • Fixed incorrect handling of FormulaAggregator (when this is only aggregator)
  • ToolkitPivotBuilderMvc example changes:
    • added FormulaCubeWrapper.cs that illustrates how to define formula-based measures for web pivot builder
    • front-end widgets are updated to support interactive expand/collapse

Version 1.4.2 2018 Aug 23

  • fixed issue with FixedPivotData.Count(always returns zero until first indexer access)
  • CubeKeywordFilter changes:
    • do not lowercase keywords
    • fixed issue with AND (+) filter by measures
    • enable conditions (>, <, =) for dimensions if hint is provided
    • fixed issue with index-out-of-range for filters like "aaa, +"
  • HeatmapPivotTable: added 2 new options for HeatmapMode "RowTotal" and "ColumnTotal"
  • Added ToolkitElasticSearchSource example that illustrates how to use ElasticSearch index as a data source for pivot tables.
  • ToolkitPivotBuilderMvc example changes:
    • added ElasticSearchCube.cs (ElasticSearch connector for web pivot builder)
    • now front-end widgets are fully compatible with jQuery 3.3 / select2 4.x
    • now element selectors are configurable with options in webpivotbuilder.js
    • fixed issue with horizontal bar charts in jquery.nrecopivotchart.js

Version 1.4.1 2018 Apr 12

  • PivotTableHtmlWriter: fixed incorrect groups rendering if more than 3 dimensions are selected for rows or columns.
  • PivotTableDataTableWriter: infer column type if possible to avoid object-type columns.
  • CubeKeywordFilter changes:
    • correct filtering by empty keyword or when started/ended with space char.
    • added ability to filter by measure value with syntax like "sum>20".
    • now it is possible to resolve dimensions/measures by hints with custom handlers.
  • PivotDataFactory: new 'Quantile' and 'Mode' aggregators are registered by default.
  • ToolkitPivotBuilderMvc example changes:
    • jquery.nrecopivottable.js: now table fixed headers are based on position:sticky (gives perfect UX) with fallback to old scroll handler for browsers that don't support sticky.
    • jquery.nrecopivottable.js: added disableByAreaFactor option for small screens (disables fixed headers mode if they take most table space determined by the specified factor value).
    • added MdxAdomdCube class which connects to MS OLAP server and composes MDX dynamically.
    • filter autocomplete switched to typeahead jQuery plugin.
    • correct handling of drill-down when all dimensions already present in the report
  • Added ToolkitAdomdSource example that illustrates how to use SSAS OLAP cube as a data source for pivot tables (based on Microsoft.AnalysisServices.AdomdClient).
  • Fixed issue with HeatmapPivotTable in TRIAL mode (without license key).
  • Updated NReco.PivotData.Extensions dependencies:
    • netstandard2.0 build now uses official EPPlus v.4.5.1 nuget package
    • netstandard1.5 build uses EPPlus.Core v.1.5.4 nuget package (as official EPPlus doesn't support netstandard1.5)

Version 1.4.0 2018 Jan 09

  • Added ability to specify custom 'from value' provider in DifferencePivotTable and PercentagePivotTable
  • CsvSource now uses own ultra-fast CSV parser instead of CSVHelper (3x times faster!)
  • PivotTableCsvWriter now uses own CSV writer instead of CSVHelper (no dependency on this lib any more)
  • added support of sub-totals in PivotTableExcelWriter
  • added auto-fit columns heuristics in PivotTableExcelWriter for netstandard builds
  • HeatmapPivotTable fixes:
    • argument null exception
    • incorrect work for tables with several values (aggregators)
  • fixed CubeKeywordFilter logic for "not" + non-existing keyword (filters everything)
  • "ToolkitPivotBuilderMvc" example changes:
    • added drill-down by click functionality (applies a filter + adds a dimension)
    • empty chart bug fix in jquery.nrecoPivotChart.js
    • minor CSS fixes for pivot table with fixed headers

Version 1.3.8 2017 Sep 25

  • PercentagePivotTable: calculate percentage even for negative base (like in Excel)
  • FixedPivotData: added ability to load sub-totals from state (use-case: result of SELECT .. WITH CUBE or MDX query)
  • PivotTableFactory: ignore incorrect measure index in "SortByValue"
  • CsvSource: correct handling of headers with control symbols (like new line)
  • fixed issue with pivot table writers when CompositeAggregatorFactory is configured for one aggregator

Version 1.3.7 2017 Jul 26

  • fixed incorrect difference percentage calculation in DifferencePivotTable (now works like in Excel)
  • now FixedPivotData uses pregrouped totals if they are present in PivotDataState (the same behavior as in PivotData class)
  • now GroupedSourceReader supports all standard aggregator types (CountUnique, Variance etc)
  • now it is possible to specify variance value mode (as 2nd parameter) for "Variance" aggregator in PivotDataFactory

Version 1.3.6 2017 Jun 26

  • added RunningValuePivotTable wrapper: calculates running total (or running value for specified aggregator)
  • added HeatmapPivotTable that highlights max values (HTML/Excel writers only)
  • fixed issue with PivotTableHtmlWriter (invalid character exception)
  • fixed issue with AggregatorFactoryConfiguration.Equals
  • fixed issue with DifferencePivotTable (argument null exception)
  • ToolkitPivotBuilderMvc example enhancements:
    • fixed issue with fixed headers and mouse wheel (in smooth mode)
    • changed ICube interface to support dynamically calculated cubes
    • added SqlCube that aggregates SQL query results with PivotData.ProcessData

Version 1.3.5 2017 Mar 15

  • fixed bug in PivotTableDataTableWriter (trial notice not removed even with valid key)
  • PivotTableDataTableWriter now derives from PivotTableWriterBase and supports totals row/column and grand total (configurable)

Version 1.3.4 2017 Feb 01

Version 1.3.3 2016 Dec 02

  • added JsonSource: use JSON data (file, REST API response) as input for PivotData.
  • PivotTableHtmlWriter RepeatKeysInGroups and RepeatDuplicateKeysAcrossDimensions options now can be enabled for rows, columns or both.
  • added RepeatKeysInGroups and RepeatDuplicateKeysAcrossDimensions properties to PivotTableExcelWriter (they have the same affect as in PivotTableHtmlWriter).
  • added PivotTableHtmlWriter.AllowHtml option (allows usage of HTML tags in table labels or cell values; false by default)
  • fixed PivotTableExcelWriter writer output when there are no dimensions for rows.
  • ToolkitPivotReportMvc example enhancements:
    • front-end code was reorganized into several reusable jQuery plugins to simplify integration
    • added ability to save/load report state
    • added SqlGroupByCube that illustrates how to calculate cube by SQL data source with GROUP BY query

Version 1.3.2 2016 Oct 18

  • added PaginatePivotTable wrapper: used for efficient pivot table pagination on the server-side + related internal changed
  • added pagination support to front-end component (jquery.nrecopivottable.js) and ToolkitPivotReportMvc example
  • added new PivotTableHtmlWriter option: RepeatKeysInGroups (prevents labels grouping; false by default)
  • added new PivotTableHtmlWriter option: RepeatDuplicateKeysAcrossDimensions (prevents the same label merging across dimensions)
  • added more formatting options to PivotTableCsvWriter / PivotTableExcelWriter
  • GroupedSourceReader significant refactoring; some changes are NOT backward compatible.
  • fixed FixedPivotData issue (ArgumentOutOfRange exception when PivotDataState contains only several values)
  • fixed PivotTableDataTableWriter issue (appears when pivot table contains several measures)

Version 1.3.1 2016 Sep 16

  • published NReco.PivotData.Extensions nuget package; it needs a license key to remove trial limitations
  • CubeKeywordFilter enhancements: starts-with / ends-with syntax (*keyword, keyword*), dimension hints syntax (year:2015)
  • PivotTableExcelWriter is now available in netstandard build (for .NET Core apps)
  • added more formatting options to ExcelWriter and CsvWriter (FormatKey/FormatValue)
  • fixed PivotTableJsonWriter JSON serialization issue (null and DBNull values)
  • fixed PivotTableDataTableWriter nullreference exception when MinAggregator or MaxAggregator is used

Version 1.3.0 2016 Aug 14

  • added .NET Core-compatible NReco.PivotData.Extensions build (netstandard1.5)
  • added CubeKeywordFilter for simple keyword-based filtering
  • PivotReportMvc example: added keyword-based filter (CubeKeywordFilter + Awesomplete integration)
  • added PivotTableFactory.RegisterDimensionComparer (custom comparers for dimension keys)
  • added TopPivotTable.IncludeOtherGroups option
  • DifferencePivotTable wrapper for calculating difference between pivot table values
  • formatting options for PivotTableJsonWriter (FormatKey, FormatValue, FormatMeasureHeader, FormatDimensionLabel)
  • fixed PivotTableJsonWriter multiple aggregators bug
  • fixed CsvSource issue (CSV headers with whitespaces)

Version 1.2.0 2016 Jun 03

  • added TopPivotTable wrapper (limits pivot table by top N rows and/or columns)
  • now PivotTableHtmlWriter can render dimension labels (for rows, columns, or both: controlled by RenderDimensionLabel property)
  • added PivotTableHtmlWriter option that controls THEAD/TBODY rendering ( RenderTheadTbody property )
  • added PercentagePivotTable.RoundDigits property
  • enhanced CsvSource to support CSV files without headers (Headers property)
  • MVC PivotReportMvc example enhancements:
    • added pivot charts (ChartistJS + PivotTableJsonWriter output)
    • pivot table js plugin: added fixed headers (freeze column/row headers) for large tables
    • added simple dashboard example
  • added new Toolkit package examples: ExcelPivotTable, DynamicListGrouping
  • fixed issue with grand total rendering without columns (PivotTableHtmlWriter)
  • fixed issue with rendering multiple measures in PivotTableDataTableWriter

Version 1.1.0 2016 Jun 03

  • now all pivot table writers accept IPivotTable as input
  • added PercentagePivotTable for calculating pivot table percentage values (by grand total, row total or column total).
  • PivotTableHtmlWriter rendering logic was significantly refactored to support new sub-totals options: SubtotalColumns, SubtotalRows
  • PivotReportMvc example enhancements:
    • added option for calculating percentage values
    • added options for row and column sub-totals
    • added pivot chart vizualization: bar chart, stacked bar chart, horizontal bar chart, pie chart (Chartist JS library)
    • added customized summary report sample with export to Excel and PDF

Version 1.0.2 2016 Jan 01

Version 1.0.1 2015 Sep 30

  • added more options for PivotTable writers (HTML/CSV/Excel)
  • now all PivotTable writers support several measures (aggregators)
  • added simple WebForms example that renders pivot table
  • improved ToolkitPivotReportMvc example (data source selection, several measures support)
  • added DataSetStarSchemaWriter (exports PivotData to DataSet with star schema)
  • added ToolkitCreateOfflineCube example: export PivotData to offline AS cube (.cub file)

Version 1.0.0 2015 Jul 28

  • added FixedPivotData: read-only in-memory data cube implementation with optimized memory consumption.
  • added ToolkitSqlDbSource example (how to aggregate data from SQL database)
  • added ToolkitPivotBuilderMvc example (ASP.NET web pivot table builder)
  • a lot of bugfixes and performance improvements

Version 0.7 2015 Jul 14

  • added PivotTableFactory (creates PivotTable by dynamic configuration)
  • added PivotTableCsvWriter (export PivotTable to CSV format)
  • added PivotTableExcelWriter (export PivotTable to Excel file)
  • added SliceQuery (implements OLAP operations 'slice and dice' for PivotData cube)

Version 0.6 2015 Jun 25

  • added DbCommandSource (data source based on ADO.NET IDbCommand)
  • added GroupedSourceReader (loads pre-grouped dataset into PivotData)

Version 0.5 2015 Jun 04

  • render PivotTable object to HTML table
  • PivotDataFactory (creates PivotData object by config)
  • parsers and derived value handlers for input data source
  • added CSV data source