Pivot Pipes Specification

Owned by

Grinders

Developer Name

Designer Name

Requires approval from

Peer Developer Name | Date:
Design Manager Name | Date:

Signed off by

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

Revision History

Version	Users	Date	Notes
1	Deyan Kamburov	25 Aug 2021	Initial spec draft
2	Deyan Kamburov	2 Sept 2021	Update draft new requirements

1. Overview

Pivot table is a new table build by grouping and aggregating specific values. Pivoting through data structures requires declaring four basis elements: rows, columns, values and filters.

Objectives

In order to achieve pivot view, the flat data must go through the following pipes:

Filter pipe - filters out the data
Sort pipe - sorts the data by the row values
Row pipe - groups and aggregate the row values
Row expansion pipe - resolve row expansion states
Column Sort pipe - sorts the data by the column values
Column pipe - groups and aggregate the column values, based on the result of the row pipe

The combination of these pipes generates pivot view, where the data is grouped and aggregated accordingly.

Acceptance criteria

The combination of the six pipes should allow transforming flat data into pivot data.
Row pipe should allow declaring multiple rows, aggregations.
Column pipe should allow declaring multiple columns, aggregations.
Filter pipe should allow filtering out some rows from the original data and they shouldn't be part of the final result.
Final output the of data should be in a format understandable for a basic grid

2. User Stories

Developer stories:

Story 1: As a developer, I want to pass rows, columns, values and filters parameters so that I can transform flat data into pivot data
Story 2: As a developer, I want to use the three pipes so that I can create pivot view from a flat data
Story 3: As a developer, I want to be have a flexible way to declare values parameter, so that I can have different kind of aggregations
Story 4: As a developer, I want to be have a flexible way to declare hierarchies and be able to have additional total field in rows and columns.

3. Functionality

In order to achieve proper pivot view, these pipes should be used in specific order: First - filter, then row, row expansion and at the end column pipe. Sorting pipes can change the order of the data, but do not take part in aggregations. Also row and column pipe takes aggregations parameter. It makes no sense to have do pivoting without aggregations(called also values and measures).

Filter Pipe

The filtering UI should be excel style filtering. The pipe should allow FilteringExpressionsTree to be used as parameter. The filtering expression tree should be build be the pivot data selector and used in filter pivot pipe to filter out the unselected records, before starting to grouping and aggregating by rows and columns.

Row Pipe

Starting from the rows we need to group their values and create a row for each of their unique values, if there is more than one row we need to do a subgroup of its values and create a sub row of it. If there are hierarchies in the row dimension (childLevel) they needs to be grouped and stored in the data as well for further processing. This pipe allows declaring

hierarchies
aggregations of the values

The pivot row strategy used in this pipe can be customized or skipped by setting a custom rowStrategy via the IPivotConfiguration. Logic can be skipped, if for example the data is retrieved from a remote endpoint and is already pre-processed. To skip it you can use the predefined NoopPivotDimensionsStrategy:

rowStrategy: NoopPivotDimensionsStrategy.instance()

Or you can defined your own custom strategy that extends the existing one:

export class MyRowStrategy extends NoopPivotDimensionsStrategy {
    public process(collection: any[], _: IPivotDimension[], __: IPivotValue[]): any[] {
        ...
        return collection;
    }
}

And set it as the current row strategy:

rowStrategy: MyRowStrategy.instance()

Row Expansion Pipe

Once the rows and their hierarchies are created in depth their expansion state can be resolved. Depending on whether the row has children and if they are expanded (expansion state is stored in the grid's expansionState collection by the row id) the hierarchy will be flattened in a single flat collection for further processing.

Column Pipe

The approach in the last step is similar, but it is in horizontal matter. We need to take the values for the columns and group them. Each unique value is a column in the pivot view. There could be more than one columns and this requires creating a subgroup of these columns. This pipe allows declaring

hierarchies, when there are multiple subgroups need to be collapsed and presented as a single value
aggregations of the values

The pivot column strategy used in this pipe can be customized or skipped by setting a custom columnStrategy via the IPivotConfiguration. Logic can be skipped, if for example the data is retrieved from a remote endpoint and is already pre-processed (all aggregations are populated). To skip it you can use the predefined NoopPivotDimensionsStrategy:

columnStrategy: NoopPivotDimensionsStrategy.instance()

Or you can define your own custom strategy that extends the existing one:

export class MyColumnStrategy extends NoopPivotDimensionsStrategy {
    public process(collection: any[], _: IPivotDimension[], __: IPivotValue[]): any[] {
        ...
        return collection;
    }
}

And set it as the current row strategy:

columnStrategy: MyColumnStrategy.instance()

Note: in most of the cases aggregations in row and column pipes will be the same

Here's an example. The following flat json data:

[
    { "ProductCategory": "Clothing", "UnitPrice": 12.81, "SellerName": "Stanley", "Country": "Bulgaria", "Date": "01/01/2021", "UnitsSold": 282 },
    { "ProductCategory": "Clothing", "UnitPrice": 49.57, "SellerName": "Elisa", "Country": "USA", "Date": "01/05/2019", "UnitsSold": 296 },
    { "ProductCategory": "Bikes", "UnitPrice": 3.56, "SellerName": "Lydia", "Country": "Uruguay", "Date": "01/06/2020", "UnitsSold": 68 },
    { "ProductCategory": "Accessories", "UnitPrice": 85.58, "SellerName": "David", "Country": "USA", "Date": "04/07/2021", "UnitsSold": 293 },
    { "ProductCategory": "Components", "UnitPrice": 18.13, "SellerName": "John", "Country": "USA", "Date": "12/08/2021", "UnitsSold": 240 },
    { "ProductCategory": "Clothing", "UnitPrice": 68.33, "SellerName": "Larry", "Country": "Uruguay", "Date": "05/12/2020", "UnitsSold": 456 },
    { "ProductCategory": "Clothing", "UnitPrice": 16.05, "SellerName": "Walter", "Country": "Bulgaria", "Date": "02/19/2020", "UnitsSold": 492 }
]

The data can be presented in the following simple table:

ProductCategory	UnitPrice	SellerName	Country	Date	UnitsSold
Clothing	12.81	Stanley	Bulgaria	01/01/2021	282
Clothing	49.57	Elisa	USA	01/05/2019	296
Bikes	3.56	Lydia	Uruguay	01/06/2020	68
Accessories	85.58	David	USA	04/07/2021	293
Components	18.13	John	USA	12/08/2021	240
Clothing	68.33	Larry	Uruguay	05/12/2020	456
Clothing	16.05	Walter	Bulgaria	02/19/2020	492

The filter is not a required parameter in order to achieve pivoting and will be ignored for the purpose of this example. If we use UnitsSold to be the values parameter(the aggregation is sum of UnitsSold), then Country to be the columns parameter and ProductCategory for rows.

Expected data should look like this:

ProductCategories	Bulgaria	USA	Uruguay
Clothing	774	296	456
Bikes			68
Accessories		293
Components		240

Note: the columns Bulgaria, USA, Uruguay were values in the original data and now are defined as columns.

Measures (aggregations)

The pivot aggregations or measures are functions that accept an array of values and return a single value that represent a property of that group. Each data cell of igxPivotGrid is populated with an aggregation for the subset of data determined by the position in the row and column dimensions (grouping) it represents.

Measures are created as implementations of the IPivotValue interface.

export interface IPivotValue {
    member: string;
    displayName?: string;
    aggregate: IPivotAggregator;
    aggregateList?: IPivotAggregator[];
    enabled: boolean;
    styles?: any;
    dataType?: GridColumnDataType;
    formatter?: (value: any, rowData?: any) => any;
}

Where IPivotAggregatoris a predefined type that specifies the aggregation function.

export interface IPivotAggregator {
    key: string;
    label: string;
    aggregator: (members: any[], data?: any[]) => any;
}

The aggregator function is sent two parameters by igxPivotGrid one array containing the values of the property specified by member and the other the same size array of the records.

Note: member has to be unique but it doesn't have to represent a property in the data source. If no property in the data source matches member the first array contains undefined values.

A number of predefined aggregations are exposed for different types of values. They aggregate only based on the members parameter and therefore can only be used for simple measures that correspond to values in the data source.

IgxPivotAggregate - exposes count and can be used for any type. The rest extend this to expose count for a specific type.
IgxPivotNumericAggregate - exposes min, max, sum and average for number values.
IgxPivotDateAggregate - exposes latest and earliest for Date values.
IgxPivotTimeAggregate - exposes latestTime and earliestTime for Date values.

3.1 Developer Experience

IgxPivotRowPipe, IgxPivotColumnPipe and IgxPivotFilterPipe definition and parameters

export class IgxPivotRowPipe implements PipeTransform {
    public transform(collection: any,
        config: IPivotConfiguration,
        _: Map<any, boolean>,
        _pipeTrigger?: number,
        __?
    )
...
}

export class IgxPivotColumnPipe implements PipeTransform {
    public transform(collection: any,
        config: IPivotConfiguration,
        _: Map<any, boolean>,
        _pipeTrigger?: number,
        __?
    )
...
}

export class IgxPivotRowExpansionPipe implements PipeTransform {
    public transform(collection: any[],
        config: IPivotConfiguration,
        expansionStates: Map<any, boolean>,
        defaultExpand: boolean,
        _pipeTrigger?: number,
        __?
    )
    ...
}

export class IgxPivotGridFilterPipe implements PipeTransform {
    public transform(collection: any[],
        config: IPivotConfiguration,
        filterStrategy: IFilteringStrategy,
        advancedExpressionsTree: IFilteringExpressionsTree,
        _filterPipeTrigger: number,
        _pipeTrigger: number): any[] {
...
}

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;
    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 `igxPivotSelector` 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?: any) => any;
}

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;
}