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 and Sport all have enableRowGroup=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 by Country and then Year as these columns have rowGroup=true.
Columns Gold, Silver and Bronze all have enableValue=true which means they can be aggregated on. To aggregate you drag the column to the Values section. When you are grouping, then all columns in the Values 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.

When filtering using the Server-side Row Model it's important to specify the filter parameter: newRowsAction: 'keep'. This is to prevent the filter from being reset as data is loaded into the grid.

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.

Example - Slice and Dice - Real Server

It is not possible to put up a full end to end example of the Server-side Row Model on the documentation website, as we cannot host servers on our website. Instead we have put a full end to end example in Github at https://github.com/ag-grid/ag-grid-enterprise-mysql-sample/.

The example puts all the olympic winners data into a MySQL database and creates SQL on the fly based on what the user is querying. This is a full end to end example of the type of slicing and dicing we want ag-Grid to be able to do in your enterprise applications.

The example does not demonstrate pivoting. This is because pivot is not easily achievable in MySQL.

You can also check out these guides on connecting to other data sources:

The example is provided to show what logic you will need on the server-side. It is provided 'as is' and we hope you find it useful. It is not provided as part of the ag-Grid Enterprise product, and as such it is not something we intend to enhance and support. It is our intention for ag-Grid users to create their own server-side connectors to connect into their bespoke data stores. In the future, depending on customer demand, we may provide connectors to server-side stores.

Next Up

Continue to the next section to learn about Server-side Pagination.