igxPivotGrid Specification

Owned by

Team Name Grinders

Developer Name

Stefan Ivanov

Requires approval from

Peer Developer Name | Date:
Simeon Simeonov | Date:

Signed off by

Product Owner Name | Date:
Platform Architect Name | Date:

Revision History

Version	Users	Date	Notes
1	Maya Kirova, Stefan Ivanov	28 Oct 2021	Initial draft
2	Stamen Stoychev	9 Feb 2022	Adding empty grid definition
3	Svetoslav Krastev	30 April 2024	Adding row dimension headers

1. Overview

The igxPivotGrid is a data presentation control for displaying data in a pivot table. It enables users to perform complex analysis on the supplied data.

Objectives

Main purpose is to transform and display a flat array of data into a complex grouped structure with aggregated values based on the main 3 dimensions: rows, columns and values, which the user may specify depending on his/her business needs.

Acceptance criteria

Must-have before we can consider the feature a sprint candidate

Should allow binding to a flat array of data and displaying pivot data described by the specified rows, columns and values.
Should allow setting custom transformation strategy in case user receives an already processed data collection and just needs to display it.
Should allow changing the specified rows, columns, values runtime via the UI, via the API or configuration.
Should allow specifying global filters as well that can allow filtering unique values for the related row/column fields in an excel style like fashion.
Should allow sorting the data represented in both values and rows / columns.
Should display aggregate summary rows and columns for the specified pivot data structure.
Should provide means to display the configuration made via the API as column headers with draggable chip elements. ...

2. User Stories

Elaborate more on the multi-facetted use cases

Developer stories:

Story 1: As a developer, I want to define the pivot rows, so that I can create row-based hierarchies.
Story 2: As a developer, I want to define the pivot columns, so that I can create column-based hierarchies.
Story 3: As a developer, I want to define the pivot values, so that I can show the relevant data for the rows/columns configuration.
Story 4: As a developer, I want to define grid UI configuration visibility on/off so the end-user cannot change the pivot configuration.

End-user stories:

Story 1: As an end-user, I want to filter the pivot data, so that I can show only relevant values.
Story 2: As an end-user, I want to expand/collapse rows and columns, so that I can view data with more/less granularity.
Story 3: As an end-user, I want to remove some of the applied filters, rows, columns, and values, so that I can change the pivot configuration.
Story 4: As an end-user, I want to move an applied filters, rows, columns or values entity and change it to a different one, so that I can change the pivot configuration.
Story 5: As an end-user, I want to sort the rows, columns or values pivot values, so that I can show relevant values in a more convenient way for me.
Story 6: As an end-user, I want to be able to have column headers matching with the row dimension values, so I can easily determine what type of value is displayed.

3. Functionality

3.1 End-User Experience

The igxPivotGrid provides means to configure the pivot state from the API and show it in dedicated grid header elements in the left and top part of the header area prototype. filters, rows, columns, and values are shown as chips that can be moved to another area, changing their impact on the pivot configuration, or one may also remove them via the clear icon to the right of each chip.

3.1.1 Filtering

The filtering on the pivot grid is shown in a dropdown akin to the ESF filtering interface for unique value filtering.

When the chips can't fit into the filter area they are rendered in the dropdown.

dd-filter

As more and more columns are added to the filtering it may happen that depending on the column names the area is insufficient for all the chips that need to be displayed and therefore we strive for behavior akin to the grid header cells of row filtering.

3.1.2 Values

Users should be able to choose the aggregation function for a given value column from a list with some default values combined with custom values if such have been added by developers via the API. Selection is possible via a dropdown shown from the values chip prefix.

3.1.3 Configuration through chips

When you drag a chip from one of the four pivot entities, there should be indications in areas where there are already other chips from the pivot state that the dragged one can be dropped there and added to them.

It is also possible to drag a chip between other chips.

It is possible to hide the configuration through chips as well, through the pivotUI input. It will hide all chip areas:

3.1.4 Row Dimensions Headers

Through pivotUI input also row dimension value headers can be enabled:

They are also visible when the configuration through chips is disabled, for more context:

Resizing

When enabled they can also be resized, similar to the row headers. When enabled no matter from where you resize, the handle will go up to the top of the row dimension headers as well.

Sorting

Sorting is also supported and enabled by default for all columns. It can be disabled by setting sortable to false on the dimensions themselves. Sorting order takes into account the row dimension order, not which one is sorted first and the sorting index will update and indicate that.

3.2 Developer experience

The igxPivotGrid inherits the igxBaseGrid and as such inherits most of its functionality and features.

Because the pivot grid is defined by the dimensions and values it produces for the data, a pivot grid without these configured is undefined. Such grids load an empty template with the following message: "Pivot grid has no dimensions and values." . The same result will be displayed if dimensions and values are defined but currently disabled via the enabled property. Developers can assign a Pivot Data Selector for such grids to allow their users to enabled or disable existing configurations of dimensions and values runtime via the pivot selector's UI.

3.2.1 Features

However, the following features will not be supported as they do not make sense in the context of a pivot table:

Defining your own columns - With pivot data columns are generated runtime based on the specified pivot configuration, hence you cannot define your own custom columns.
CRUD - Pivot tables are meant to display aggregated information. Not to update the underlying data and as such CRUD does not make sense for them.
Grouping - Pivot tables already display grouped and aggregated information. Further grouping of columns would not make sense.
Row Pinning - you cannot pin individual rows as they are typically part of a aggregation group and would not make sense outside of it.
Summaries - the pivot data already shows aggregated information, so summaries also do not make sense in its context.
Paging - doesn't seem to be a common use case for pivot data.

For the columns in the pivot grid the following features are not supported:

Hiding - if you wish you can instead remove the column from your pivot grid configuration.

Also, some features have slight different behavior:

Row Selection - Only single mode supported. A row is selected when clicked on according dimension cell. If there are more than one row dimensions, selecting a parent dimension cause all related sibling rows to be selected.

3.2.2 Row Dimension Headers Templating

The igxPivotGrid provides also the ability for templating the row dimension headers. It is achieved using the IgxPivotRowDimensionHeaderTemplateDirective similar to how Base Grid headers are templated. The context provided is the column itself, which represents the top level row dimensions configured in the pivotConfiguration.

Field represend the dimension memberName value, while the header property represents the displayName dimension value or the memberName, if the previous is not provided.

<igx-pivot-grid #grid1 [data]="origData" [width]="'100%'" [height]="'800px'" [pivotConfiguration]="pivotConfigHierarchy"
    [pivotUI]="{ showConfiguration: true, showRowHeaders: true }" >
    <ng-template igxPivotRowDimensionHeader let-col>
        <span>{{col.header}}</span>
    </ng-template>
</igx-pivot-grid>

3.2.3 Row Dimensions Layout

The grid can support two ways of row dimensions rendering. This can be configured using the pivotUI rowLayout option. By default it is vertical.

Vertical

This is default way of rendering and displays the different children values of a row dimension vertically in the same column.

Sibling dimensions are shown in the order of their configuration left to right. Each sibling has a separate independent expansions, that way the user could expand/collapse whichever sibling desires. For example how it would look with all row values expanded:

Or with some collapsed:

Horizontal

This is an alternative mode, where the children of a single row dimension when expanded are shown horizontally in the same parent multi row layout. When even a single value from a row dimension is expanded, a new column for the child value is displayed where it is rendered. The rest of the values that are not expanded are merged with the previous cell.

Sibling dimensions are also shown in the same row and at least their root level is visible always. In the case where a used would want to be able to collapse all siblings as well, all siblings would need to put as children to the previous sibling row dimension.

Collapsing a previous sibling would merge the cells right to where the next sibling starts. The next sibling would also merge depending on the situation and how many rows it has rendered for it as well. For example how would it look with all rows for the first dimension collapsed:

With only one expanded value for a row dimension:

Mixed with some of the sibling dimension collapsed:

Due to the nature of the horizontal layout, if a user wants to keep visible the parent dimension value aggregations in a separate row when expanded, he would need to enable the horizontalSummary option for each row dimension and their children in the pivotConfiguration. It is only applicable for the horizontal rendering mode. There the user can specify "None"(default), "Top" and "Bottom" position..

Pipes

For the data rows
- Filter pipe
  
  To support filters applied in excel-style like fashion on the unique values of a row field, which should exclude the unselected records from the result.
- Pivot Transformation pipes (Row pipe, Row expansion pipe and Column pipe)
  
  Pipe that applies grouping and aggregation based on the specified rows/columns/values. Details here
- Sorting by aggregations pipe
  
  To allow sorting the aggregation values per column.
- Sorting by row dimension pipe To support sorting applied based on the unique values of a column dimension field.
For the columns:
- Filter pipe To support filters applied in excel-style like fashion on the unique values of a column field, which should exclude the unselected column fields from the result.
- Sorting by column dimension pipe To support sorting applied based on the unique values of a column dimension field.
- Column auto-generation logic Should generate the full column collection based on the columns settings (including any hierarchies as column groups).

3.1. End-User Experience

** Integration scenarios or functionality with other features/components prototype ** End-to-end user experienceprototype ** Prepared design files for styling e.g. interplay with features and light/dark variants design hand-off

3.2. Developer Experience

Dimensions

User needs to be able to define the main 3 dimensions : rows, columns, values for the aggregation. It should be possible to define a more complex hierarchy for the row/column dimensions and also it should be possible to set aggregation (predefined or custom) for the values.

This can be done via a pivotConfiguration input property for the igxPivotGrid with the following interface:

Example:

/**
 * Configuration of the pivot grid.
 */
export interface IPivotConfiguration {
    /** A strategy to transform the rows. */
    rowStrategy?: IPivotDimensionStrategy | null;
    /** A strategy to transform the columns. */
    columnStrategy?: IPivotDimensionStrategy | null;
    /** A list of the rows. */
    rows: IPivotDimension[] | null;
    /** A list of the columns. */
    columns: IPivotDimension[] | null;
    /** A list of the values. */
    values: IPivotValue[] | null;
    /** Dimensions to be displayed in the filter area. */
    filters?: IPivotDimension[] | null;
    /** Properties stored into the records during the aggregations. Avoid properties with the same names as in the original data. */
    pivotKeys?: IPivotKeys;
}


export interface IPivotDimension {
    /** Allows defining a hierarchy when multiple sub groups need to be extracted from single member. */
    childLevel?: IPivotDimension;
    /** Field name to use in order to extract value. */
    memberName: string;
    /** Function that extracts the value */
    memberFunction?: (data: any) => any;
    /** Display name to show instead of the field name of this value. **/
    displayName?: string;
    /** Enables/Disables a particular dimension from pivot structure. */
    enabled: boolean;
    /**
     * A predefined or defined via the `igxPivotDataSelector` filter expression tree for the current dimension to be applied in the filter pipe.
     * */
    filter?: FilteringExpressionsTree | null;
    /** Enable/disable sorting for a particular dimension. True by default. */
    sortable?: boolean;
    /**
     * The sorting direction of the current dimension. Determines the order in which the values will appear in the related dimension.
     */
    sortDirection?: SortingDirection;
    /**
     * The dataType of the related data field.
     */
    dataType?: GridColumnDataType;
    // The width of the dimension cells to be rendered.Can be pixel or %.
    width? : string;
    // Enabled/Disabled the summary row for this dimension, which will be shown in horizontal rendering mode.
    horizontalSummary? : pivotSummarySetting;
}

export interface IPivotValue {
    member: string;
    // display name if present shows instead of member for the column header of this value
    displayName?: string;
    /**
     * Active aggregator definition with key, label and aggregator.
     */
    aggregate: IPivotAggregator;
    /**
     * List of aggregates to show in aggregate drop-down.
     */
    aggregateList?: IPivotAggregator[];
    // Enables/Disables a particular value from pivot aggregation.
    enabled: boolean;
    // Allow conditionally styling of the IgxPivotGrid cells
    styles?: any;
    // Enables a data type specific template of the cells
    dataType?: GridColumnDataType;
    // Applies display format to cell values.
    formatter?: (value: any, rowData?: IPivotGridRecord, columnData?: IPivotGridColumn) => any;
}

export interface IPivotKeys {
    children: string;
    records: string;
    aggregations: string;
    level: string;
    columnDimensionSeparator: string;
    rowDimensionSeparator: string;
}

export interface IPivotAggregator {
    // Aggregation unique key.
    key: string;
    // Aggregation label to show in the UI.
    label: string;
    // Aggregator function can be a custom implementation of PivotAggregation or
    // use predefined ones from IgxPivotAggregate and its variants.
    aggregator: (members: any[], data?: any[]) => any;
}

Note: In order to have multiple value dimensions with the same data field the user should set each dimension a different name and implement custom IPivotAggregator.

The same configuration can be shared with the IgxPivotDataSelector to determine its rows/columns/values/filters and will also bind the enabled property runtime in order to allow enabling/disabling the particular elements in the grid view. Communication between IgxPivotGrid and IgxPivotDataSelector is achieved through IPivotConfiguration interface. The interface fully describes how the data should be transformed into pivot view data and the IgxPivotGrid uses it to render the pivot data. Changes into the properties of IPivotConfiguration are dynamically reflected in the IgxPivotGrid. The data operations/transformations are done by Pivot Transformation pipes.

The IgxPivotDataSelector determines the full structure of all dimensions and the enabled property determines whether the particular element is enabled for the related grid view. The value can be changed runtime via the pivot selector UI.

Predefined dimensions

Date dimension

The Ignite UI library provides also a predefined Date pivot dimension type, that can be instanced when defining row or column dimension for IPivotConfiguration.

Its type is a class named IgxPivotDateDimension that requires a base dimension, which is used to generate further hierarchical dimensions like:

All Periods
Years
Quarters
Months
Full Date

Example:

public pivotConfigHierarchy: IPivotConfiguration = {
    rows: [
        new IgxPivotDateDimension({ memberName: 'Date', enabled: true });
    ]
}

The IgxPivotDateDimension also accept an options object, that is used to override the default configuration, which generates All periods, Years and Months levels. The options are of type IPivotDateDimensionOptions and can configure each level of the dimension by setting it true or false.

Example how to specify to display only the years and date levels of the dimension:

public pivotConfigHierarchy: IPivotConfiguration = {
    rows: [
        new IgxPivotDateDimension({ memberName: 'Date', enabled: true }, { total: false, months: false });
    ]
}

The default options for the IgxPivotDateDimension are:

{
    total: true,
    years: true,
    months: true,
    fullDate: true,
    quarters: false
}

AutoGenerate configuration

Users can set the autoGenerateConfig property to true to attempt to generate dimensions and values based on the provided data source fields.

Field that have numeric values will be created as a IPivotValue with the default IgxPivotNumericAggregate.sum aggregator and added to the values collection as enabled.

Other fields will be created as a IPivotDimension and will be disabled by default. Users can then enable them via the pivot data selector and this will add them to the columns collection by default. Users can then move them to the rows collection if they want.

For example for a pivot bound to the following data:

DATA_TO_ANALYZE = [
  {
    Date: new Date('1/6/08'),
    Region: 'Quebec',
    Rep: 'Jones',
    Item: 'Pencil',
    Units: 95,
    Cost: 1.99,
    Total: 189.05,
  },
  //...
];

The first 4 fields will become a dimension, while the last 3 will be values. The Date field will be resolved as a IgxPivotDateDimension row dimension and will be enabled by default. The rest will be column dimensions, disabled by default:

Filters

Additional filters can be applied via the filters property. Note that the filter is applied for the rows/column and not for the aggregated value (values). It will remove those unique row/column values from the view and they will be excluded from further aggregations.

In case the related field is associated with dimension that has a hierarchy level defined (childLevel) then the excel style filter should show a tree-like structure representing the hierarchy with the unique values on each level as extracted via the memberName property of the dimension or memberFunction. Filter expression should contains that unique value with condition equals and should compare it to the memberName's extracted value for the related level.

For example, for a dimension with 4 levels applied to a single date field, with the following data:

[{ "Date": "01/01/2012", ... },
{ "Date": "01/05/2013", ...},
{ "Date": "01/06/2011", ...},
{ "Date": "04/07/2012", ...},
{ "Date": "01/19/2013" , ... }]

And the following dimension

const dimension: IPivotDimension = {
            memberName: 'All Periods',
            memberFunction: () => 'All Periods',
            childLevel: {
                memberName: 'Year',
                memberFunction: (data) => new Date(data.Date).getFullYear(),
                childLevel:{
                    memberName: 'Month',
                    memberFunction: (data) => new Date(data.Date).getMonth(),
                    childLevel: {
                        memberName: 'Date'
                        memberFunction: (data) => new Date(data.Date)
                    }
                }
            }
        };

We'd should get a tree structure with 4 levels:

When only 2011 is selected the filter expression should be:

     const expression = [
     {
        condition: IgxNumberFilteringOperand.instance().condition('equal'),
        memberName: 'Year',
        memberFunction:  (data) => new Date(data.Date).getFullYear(),
        searchVal: 2011
      }];

Condition type should match the data type ( in this case it's number) and it should use the same member extraction logic (field name or custom function) on the data fields it compares.

This expression should be added to the FilteringExpressionsTree for the related dimension in filters and later processed by the pivot filtering pipe in the igxPivotGrid. Result should be just 1 data row (since only 1 row is from year 2011). The result can then be passed on to the pivot data pipe for aggregation.

3.3 Communication with Pivot Data Selector The Pivot Grid exposes its model as IPivotConfiguration interface and it can be fully configured through it. It is a responsibility of the Pivot Data Selector to build the PivotConfiguration and change it dynamically through its UI. Pivot Data Selector should have an input for PivotConfiguration and be able to work with initial configuration, created by the developer.

3.3. Globalization/Localization

Describe any special localization requirements such as the number of localizable strings, regional formats

3.4. Keyboard Navigation

Keys	Description

3.5. API

Options

Name	Description	Type	Default value	Valid values
pivotConfiguration	Gets/Sets the pivot configuration with all related dimensions and values.	IPivotConfiguration
autoGenerateConfig	Gets/Sets whether the pivot configuration should get autogenerated based on the provided data.	boolean
pivotUI	Gets/Sets whether to show the ui for the pivot grid configuration - chips and their corresponding containers for row, filter, column dimensions and values. Also enables/disabled row dimension headers.	IPivotUISettings	{ showConfiguration: true , showRowHeaders: false }
rowExpandDirection	Gets/Sets the direction of expansion of row dimensions	RowExpandDirectionType	Vertical	Vertical or Horizontal
superCompactMode	Enables a super compact theme for the component.	boolean	false	true or false
defaultExpandState	Gets/Sets the default expand state for all row dimensions that have hierarchy.	boolean	false	true or false.
emptyPivotGridTemplate	Gets/Sets a custom template when pivot grid is empty.	TemplateRef

Methods

Name	Description	Return type	Parameters
insertDimensionAt	Inserts dimension in target collection by type at specified index or at the collection's end.	void	dimension, targetCollectionType, index
moveDimension	Move dimension from its currently collection to the specified target collection by type at specified index or at the collection's end.	void	dimension, targetCollectionType, index
removeDimension	Removes dimension from its currently collection.	void	dimension
toggleDimension	Toggles the dimension's enabled state on or off.	void	dimension
insertValueAt	Inserts value at specified index or at the end.	void	value, index
moveValue	Move value from its currently at specified index or at the end.	void	value, index
removeValue	Removes value from collection.	void	value
toggleValue	Toggles the value's enabled state on or off.	void	value
sortDimension	Sort the dimension and its children in the provided direction.	void	dimension, sortDirection
filterDimension	Filters the dimension with the provided value and condition or expression tree.	void	dimension, value, conditionOrExpressionTree
autoSizeRowDimension	Auto-sizes row dimension cells.	void	dimension

Events

Name	Description	Cancelable	Parameters
dimensionsChange	Emitted when the dimension collection is changed.	false	IDimensionsChange
valuesChange	Emitted when the values collection is changed.	false	IValuesChange
dimensionsSortingExpressionsChange	Emitted when a dimension is sorted.	false	ISortingExpression[]

4. Test Scenarios

Automation

Basic

Apply formatter and type to value dimensions.
Apply CSS classes to value dimensions.
Allow changing default aggregation via value chip drop-down.
Allow showing custom aggregations via pivot configuration.
Allow removing dimensions from chip areas.

Integration

Filtering
- Show Excel Style Filtering via dimension chips.
- Filter rows via Excel Style Filtering dimension chips.
- Filter columns via Excel Style Filtering dimension chips.
Sorting
- Sort column for single row dimension.
- Sort column for all sibling dimensions.
- Sort row dimension values via row chip.
- Sort column dimension values via column chip.
Selection
- Select/deselect a row.
- Select/deselect a parent row and all its siblings.
- Select/deselect a column.
- Select/deselect a column group.