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

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…, so that I can… prototype
Story 2: As a developer, I want to…, so that I can… prototype
Story 3: As a developer, I want to…, so that I can… prototype

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 define the pivot rows, so that I can create row-based hierarchies.
Story 3: As an end-user, I want to define the pivot columns, so that I can create column-based hierarchies.
Story 4: As an end-user, I want to define the pivot values, so that I can show the relevant data for the rows/columns configuration.
Story 5: As an end-user, I want to expand/collapse rows and columns, so that I can view data with more/less granularity.
Story 6: 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 7: 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.

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.

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.

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.

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.

3.2 Developer experience

The igxPivotGrid inherits the igxBaseGrid and as such inherits most of its functionality and 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 - 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.

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.

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;
    /** 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;
    sortDirection?: SortingDirection;
    dataType?: GridColumnDataType;
    // The width of the dimension cells to be rendered.Can be pixel or %.
    width? : string;
}

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
}

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
showPivotConfigurationUI	Gets/Sets whether to show the ui for the pivot grid configuration - chips and their corresponding containers for row, filter, column dimensions and values.	boolean	true	true or false
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
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.