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.

Follow these steps to configure custom client-side data aggregation for a field:

1. Make sure that the remoteOperations property is not set or set to false.

2. Set the field's summaryType property to "custom".

3. Implement the calculateCustomSummary function as detailed in its description.

Follow the instructions below to implement custom server-side data aggregation for a field. You will need the DevExtreme.AspNet.Data library.

1. Set the remoteOperations property to true.

2. Implement and register a custom server-side data aggregator using DevExtreme.AspNet.Data as described in this article.

3. 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

   TypeScript

   HTML

   import 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>

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.

If the list of built-in summary types does not contain an 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;
                }
            }
        },
        //...
        ]
    }
};

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;
            });

            // ...
        }
    }
};

View Demo

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.