DevExtreme jQuery/JS - Summaries

NOTE
Summaries configuration is available only for the pivot grid data sources whose store differs from the XmlaStore. If the pivot grid is configured for displaying data of a remote OLAP cube, an OLAP server provides data fields configuration.

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.

Predefined Summaries

To configure a data field for using one of the predefined aggregation methods, set the summaryType option 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 option. 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 an option satisfying your requirements, you can implement your own aggregate function. Assign it to the calculateCustomSummary option and set the summaryType option 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.

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

JavaScript
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)
        }
    }
};
NOTE
Applying the "custom" summary type demands implementing the calculateCustomSummary callback function.

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 option at runtime, slightly modify the click handler and the array of items.

JavaScript
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

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

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.

NOTE
You can allow or disallow the calculation of running total and variation summary display modes across different groups using the allowCrossGroupCalculation option.