Angular Data GridSSRM Pivoting

angular logo
Enterprise

In this section we add Server-Side Pivoting to create an example with the ability to 'Slice and Dice' data using the Server-Side Row Model (SSRM).

Enabling Pivoting

To pivot on a column pivot=true should be set on the column definition. Additionally, the grid needs to be in pivot mode which is set through the grid option pivotMode=true.

In the snippet below a pivot is defined on the 'year' column and pivot mode is enabled:

<ag-grid-angular
    [pivotMode]="pivotMode"
    [columnDefs]="columnDefs"
    /* other grid options ... */>
</ag-grid-angular>

// pivot mode enabled
this.pivotMode = true;
this.columnDefs = [
    { field: 'country', rowGroup: true },
    // pivot enabled
    { field: 'year', pivot: true },
    { field: 'total' },
];

For more configuration details see the section on Pivoting.

Pivoting on the Server

The actual pivoting is performed on the server when using the Server-Side Row Model. When the grid needs more rows it makes a request via getRows(params) on the Server-Side Datasource with metadata containing row grouping details.

The properties relevant to pivoting in the request are shown below:

// IServerSideGetRowsRequest
{
   // pivot columns, cols with 'pivot=true'
   pivotCols: ColumnVO[];

    // true if pivot mode is one, otherwise false
   pivotMode: boolean;

   ... // other properties
}

Note in the snippet above that pivotCols contains all the columns the grid is pivoting on, and pivotMode is used to determine if pivoting is currently enabled in the grid.

Providing Pivot Result Columns

Pivot Result Columns are the columns that are created as part of the pivot function. You must provide these to the grid in order for the grid to display the correct columns for the active pivot function.

For instance, when pivoting on the year field, you must provide columns to the grid corresponding to each distinct year present in the data, such as 2000, 2002, 2004, and so on.

Supplying Pivot Result Fields (Simple)

The simplest way to provide pivot result columns is by supplying the fields containing your pivoted data to the pivotResultFields attribute in the getRows success callback. These fields are used to generate pivot result columns and appropriate column groups. By default, the grid expects the fields to be separated by an underscore ('_'), however, this can be altered via the serverSidePivotResultSeparator grid option as shown below:

<ag-grid-angular
    [columnDefs]="columnDefs"
    [rowModelType]="rowModelType"
    [pivotMode]="pivotMode"
    [serverSidePivotResultFieldSeparator]="serverSidePivotResultFieldSeparator"
    /* other grid options ... */>
</ag-grid-angular>

this.columnDefs = [
    { field: 'country', rowGroup: true },
    { field: 'year', pivot: true }, // pivot on 'year'
    { field: 'gold', aggFunc: 'sum' },
    { field: 'silver', aggFunc: 'sum' },
    { field: 'bronze', aggFunc: 'sum' },
];

this.rowModelType = 'serverSide';

this.pivotMode = true;

// specify the field separator, e.g. '2000_gold' should be '_' which is also the default
this.serverSidePivotResultFieldSeparator = '_';

Note above that serverSidePivotResultFieldSeparator is not necessary as the default value is '_'.

The following snippet shows how to supply the pivotResultFields to the grid via the success callback:

const createDatasource = server => {
    return {
        // called by the grid when more rows are required
        getRows: params => {

            // get data for request from server
            const response = server.getData(params.request);

            if (response.success) {
                // supply rows for requested block to grid
                params.success({
                    rowData: response.rows,
                    pivotResultFields: response.pivotFields, // ['2000_gold', '2000_silver',...]
                });
            } else {
                // inform grid request failed
                params.fail();
            }
        }
    };
}

The example below demonstrates this, note the following:

  • The pivot fields are returned from the server and then passed to the grid via the getRows success callback via the pivotResultFields property. These are logged to the console as a demonstration.
  • The grid splits the pivotResultFields by _ and creates the pivot result columns and column groups where the generated columns use the provided fields to access the data from the rows.

When using managed columns, you can use Pivot Callbacks to customise the pivot result column definitions.

Creating Pivot Result Columns (Advanced)

It is also possible to create your own pivot result columns and provide them to the grid. This offers complete flexibility but can become complex when column groups are involved.

Pivot result columns are defined identically to the columns supplied to the grid options: you provide a list of Column Definitions passing a list of columns and / or column groups using the following column API method: