JavaScript/jQuery PivotGrid - Summaries
Summaries are values (often numeric) displayed in the pivot grid cells. Each summary value is located at the intersection of a row header group and column header group. Headers describe the meaning of each summary value. Summaries are configured in the fields of the "data" area (data fields or measures in OLAP terminology). If the "data" area contains more than one field, the column header is supplemented with another row that helps to identify the data field to which the summary value belongs. The following image explains how to interpret summary values.
Client-Side Data Aggregation
Follow these steps to configure custom client-side data aggregation for a field:
Make sure that the remoteOperations property is not set or set to false.
Set the field's summaryType property to "custom".
Implement the calculateCustomSummary function as detailed in its description.
Server-Side Data Aggregation
Follow the instructions below to implement custom server-side data aggregation for a field. You will need the DevExtreme.AspNet.Data library.
Set the remoteOperations property to true.
Implement and register a custom server-side data aggregator using DevExtreme.AspNet.Data as described in this article.
Pass the string identifier that you used in registration to the field's summaryType property:
ASP.NET MVC Controls
Razor C#@(Html.DevExtreme().PivotGrid() .DataSource(ds => ds // ... .Fields(fields => { fields.Add() // ... .SummaryType("totalSales"); }) .RemoteOperations(true) ) )
jQuery
JavaScript$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ // ... summaryType: "totalSales" }], remoteOperations: true }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
TypeScriptHTMLimport PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... summaryType: "totalSales" }], remoteOperations: true }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Predefined Summaries
To configure a data field for using one of the predefined aggregation methods, set the summaryType property to one of the following values.
- "sum"
Sums up all facts within an area. - "min"
Calculates the minimum fact's value. - "max"
Calculates the maximum fact's value. - "avg"
Calculates the average of all facts within an area. - "count"
Calculates the number of facts within an area.
You can configure the type of a value held in a field using the dataType property. This is useful when field data has an unusual format, for example, a date saved as a string. When the dataType is not set, it is defined automatically.
Custom Summaries
If the list of built-in summary types does not contain a property satisfying your requirements, you can implement your own aggregate function. Assign it to the calculateCustomSummary property and set the summaryType property to "custom". In this case, the function assigned to the calculateCustomSummary field will be called for each fact and the overall result will be used as a summary value. The example below shows how to implement the variance aggregation function.
var pivotGridSettings = { //... dataSource: { //... fields: [{ //... area: 'data', caption: 'Var', summaryType: 'custom', calculateCustomSummary: function (options) { if (options.summaryProcess == 'start') { options.totalValue = 0; //Sum options.sum2 = 0; //Sum of squared values options.n = 0; } if (options.summaryProcess == 'calculate') { options.totalValue += options.value; options.sum2 += Math.pow(options.value, 2); options.n++; } if (options.summaryProcess == 'finalize') { options.totalValue = options.sum2 - Math.pow(options.totalValue, 2) / options.n; options.totalValue /= options.n - 1; } } }, //... ] } };
Runtime Summary Type Selection
Frequently, the user requires changing the summary type at runtime. Although this capability is not provided out-of-the-box, you can easily implement it using the onContextMenuPreparing event handler. For example, consider the following self-explanatory code sample.
var pivotGridOptions = { // ... onContextMenuPreparing: function (e) { // Filtering off all non-data fields if (e.field && e.field.area == 'data') { // Obtaining the PivotGrid's data source var dataSource = e.component.getDataSource(); // Implementing a click event handler for the context menu items var changeSummaryType = function (clickedItem) { dataSource.field(e.field.index, { summaryType: clickedItem.itemData.value }); dataSource.load(); }; // Declaring an array of summary types to be present in the context menu var items = [ { text: 'Sum', value: 'sum', onItemClick: changeSummaryType }, { text: 'Avg', value: 'avg', onItemClick: changeSummaryType }, { text: 'Min', value: 'min', onItemClick: changeSummaryType }, { text: 'Max', value: 'max', onItemClick: changeSummaryType }, { text: 'Count', value: 'count', onItemClick: changeSummaryType }, // { text: 'Custom', value: 'custom', onItemClick: changeSummaryType }, ]; // Applying the "selected" style to the item that represents the current summary type $.each(items, function (_, item) { if (item.value == dataSource.field(e.field.index).summaryType) item.selected = true; }); // Pushing the array of summary types to the array of context menu items Array.prototype.push.apply(e.items, items) } } };
Altering certain parts of this code, you can enable the user to perform a wide range of actions using the context menu. For example, to allow the user to change the summaryDisplayMode property at runtime, slightly modify the click handler and the array of items.
var pivotGridOptions = { // ... onContextMenuPreparing: function (e) { if (e.field && e.field.area == 'data') { // ... var changeSummaryDisplayMode = function (clickedItem) { dataSource.field(e.field.index, { summaryDisplayMode: clickedItem.itemData.value }); dataSource.load(); }; var items = [ { text: 'Absolute Variation', value: 'absoluteVariation', onItemClick: changeSummaryDisplayMode }, { text: 'Percent Variation', value: 'percentVariation', onItemClick: changeSummaryDisplayMode }, { text: 'Percent of Column Total', value: 'percentOfColumnTotal', onItemClick: changeSummaryDisplayMode }, // ... ]; $.each(items, function (_, item) { if (item.value == dataSource.field(e.field.index).summaryDisplayMode) item.selected = true; }); // ... } } };
Summary Post-Processing
If you need to perform additional calculations on summary values so that you can take into account the values in the neighboring cells and analyze the trends, you can apply a post-processing function called summary display mode. You can use one of the predefined summary display modes or implement your own one using the calculateSummaryValue property.
When implementing a custom post-processing function, you can use the cell object called Summary Cell, which allows you to easily collect data from the neighboring cells.
At the final step of summary values processing, you can enable the running total feature if such processing is applicable for your data.