Pivoting
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.
Pivoting
Now that we have covered Row Grouping we are now going to add server-side pivoting. This will allow the user to 'Slice and Dice' the data, meaning the user can decide what they want to group, aggregate and pivot on by dragging the columns around in the grid.
When the user changes the status of the columns (ie the user changes how the data is grouped, aggregated or pivoted) then the grid data is cleared out and loaded again from scratch using the new configuration.
Example - Slice and Dice - Mocked Server
A mock data store running inside the browser is used in the example below. The purpose of the mock server is to demonstrate the interaction between the grid and the server. For your application, your server will need to understand the requests from the client and build SQL (or the SQL equivalent if using a no-SQL data store) to run the relevant query against the data store.
The example demonstrates the following:
-
Columns
Athlete, Age, Country, Year
andSport
all haveenableRowGroup=true
which means they can be grouped on. To group you drag the columns to the row group panel section. By default the example is grouping byCountry
and thenYear
as these columns haverowGroup=true
. -
Columns
Gold, Silver
andBronze
all haveenableValue=true
which means they can be aggregated on. To aggregate you drag the column to theValues
section. When you are grouping, then all columns in theValues
section will be aggregated. - You can turn the grid into Pivot Mode. To do this, you click the pivot mode checkbox. When the grid is in pivot mode, the grid behaves similar to an Excel grid. This extra information is passed to your server as part of the request and it is your servers responsibility to return the data in the correct structure.
-
Columns Athlete, Age, Country, Year and Sport all have
enablePivot=true
which means they can be pivoted on when Pivot Mode is active. To pivot you drag the column to the Pivot section. - Note that when you pivot, it is not possible to drill all the way down the leaf levels.
- In addition to grouping, aggregation and pivot, the example also demonstrates filtering. The columns Country and Year have grid provided filters. The column Age has an example provided custom filter. You can use whatever filter you want, as long as your server-side knows what to do with it.
Pivoting Challenges
Achieving pivot on the server-side is difficult. If you manage to implement it, you deserve lots of credit from your team and possibly a few hugs (disclaimer, we are not responsible for any inappropriate hugs you try). Here are some quick references on how you can achieve pivot in different relational databases: All databases will either implement pivot (like Oracle) or require you to fake it (like MySQL).
- Oracle: Oracle has native support for filtering which they call pivot feature.
- MySQL: MySQL does not support pivot, however it is possible to achieve by building SQL using inner select statements. See the following on Stack Overflow: MySQL Pivot Table and MySQL Pivot Table Query with Dynamic Columns .
To understand Pivot Mode and Secondary Columns please refer to the relevant sections on Pivoting in Client-side Row Model. The concepts mean the same in both Client-side Row Model and the Server-side Row Model.
Secondary 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 example, if you pivot on Year
, you need to tell the grid to create columns for
2000, 2002, 2004, 2006, 2008, 2010
and 2012
.
Secondary columns are defined identically to primary columns, you provide a list of
Column Definitions to the grid. The columns are set
by calling columnApi.setSecondaryColumns()
and passing a list of columns and / or column
groups. There is no limit or restriction as to the number of columns or groups you pass - the only
thing you should ensure is that the field (or value getter) that you set for the columns matches.
If you do pass in secondary columns with the server response, be aware that setting secondary columns will reset all secondary column state. For example if resize or reorder the columns, then setting the secondary columns again will reset this. In the example above, a hash function is applied to the secondary columns to check if they are the same as the last time the server was asked to process a request. This is the examples way to make sure the secondary columns are only set into the grid when they have actually changed.
If you do not want pivot in your Server-side Row Model grid, then you can remove it from the tool
panel by setting toolPanelSuppressPivotMode=true
and
toolPanelSuppressValues=true
.
Next Up
Continue to the next section to learn about Server-side Pagination.