Skip to main content

Pivoting

Pivoting breaks down data in an additional dimension — it transforms distinct values from one column into separate result columns, each showing an aggregate for that value.

Enabling Pivoting​

To make a column available for pivoting, set the enablePivot parameter to true on ColDef, otherwise a pivoting attempt on this column will result in a runtime exception.

var priceColumn = ColDef.builder(Entity_.price)
    .enablePivot(true)
    .build();
  • Source code for this grid available here
  • Backend source code available here
Loading grid...

Filtering​

Filters work the same way as without pivoting, they restrict the rows the values are aggregated from. In pivot mode they also decide which columns are generated, while expanding a group does not. Filters on aggregated values behave as described in Filtering for Aggregated Values.

pivotMaxGeneratedColumns is checked against the columns the filtered rows generate, so a filter can bring a request back under the limit.

Example - Filtering​

In pivot mode the grid displays the generated columns, so the columns of your grid are reached through the Filters tool panel on the right.

  • Book is the pivot column - filtering it changes which columns are generated
  • Product and Portfolio are the row group columns - filtering them changes which rows are returned
  • Current Value and Previous Value are aggregated - filtering them drops rows before they are aggregated
  • a generated column (Book 1_currentValue) is filtered from its header menu, by its aggregated value
  • Source code for this grid available here
  • Backend source code available here
Loading grid...

Best Practices - Limiting Column Generation​

When pivoting, changes in data, aggregation or pivot columns can cause the number of generated columns to scale exponentially. To prevent this from happening, you can set the pivotMaxGeneratedColumns option on QueryBuilder. Server will count number of columns to be generated in advance.

When the grid generates a number of pivot columns exceeding this value, it halts column generation and throws the OnPivotMaxColumnsExceededException.

this.queryBuilder = QueryBuilder.builder(Entity.class, Entity_.id, entityManager)
                .colDefs(
                        // colDefs
                )
                .pivotMaxGeneratedColumns(1000)
                .build();

Example - Limiting Column Generation​

  • Column generation is checked on server (limit 10 columns)
  • Adding Bid Type pivoting will result in error on server
  • Source code for this grid available here
  • Backend source code available here
Loading grid...