Integration with Chart

The Pivot Grid is used to visualize large amounts of data that is numeric, which can be hard to understand. You can make your pivot grid clearer by showing its data on a chart. This topic provides basic information on how to convert dxPivotGrid data to the format acceptable for dxChart and link these two widgets together.

The dxPivotGrid's data source has the getData() method that is used to fetch data currently displayed in the pivot grid. The object returned by this method can be used for presenting pivot grid data in other ways, for example, in a chart. The dxChart's data source structure and the pivot grid data returned by the getData() method are different, thus you should manually convert the pivot grid data and use it for charting.

Data Structure Description

The getData() method returns pivot grid data in the following structure.

JavaScript
{
    "rows":[
        {
            "value": /*Row 1 value*/,
            "index": /*Row 1 index*/,
            "text": /*Row 1 caption*/
        },
        {
            "value": /*Row 2 value*/,
            "index": /*Row 2 index*/,
            "text": /*Row 2 caption*/
            "children":[
                {
                    "value": /*Row 2.1 value*/,
                    "index": /*Row 2.1 index*/,
                    "text": /*Row 2.1 caption*/,
                    "children": [/*Level 3 and deeper*/]
                },
                . . .
            ]
        },
        . . .
    ],
    "columns":[
        {
            "value": /*Column 1 value*/,
            "index": /*Column 1 index*/,
            "text": /*Column 1 caption*/
        },
        {
            "value": /*Column 2 value*/,
            "index": /*Column 2 index*/,
            "text": /*Column 2 caption*/
            "children":[
                {
                    "value": /*Column 2.1 value*/,
                    "index": /*Column 2.1 index*/,
                    "text": /*Column 2.1 caption*/,
                    "children": [/*Level 3 and deeper*/]
                },
                . . .
            ]
        },
        . . .
    ],
    "values":[
        [
            [
                /*Measure 1 value*/,
                /*Measure 2 value*/,
                . . .
            ],
            . . .
        ],
        . . .
    ]
}
NOTE
Measure is an alternative name for a field to be used for calculating summary values. Such fields are located in the Data Fields section of the Field Chooser.

As shown above, the object has three fields — rows, columns and values. The rows and columns arrays contain the following fields:

  • value — the item as it was retrieved from the data source;
  • index — the row/column index pointing to the corresponding summary value from the values array;
  • text — the item's caption as it appears in the pivot grid;
  • children — the optional array that contains items of the deeper hierarchical levels.

The values array contains the summary values, each one has three indices. You can get a summary value using the following pattern.

JavaScript
var value = values[/*row index*/][/*column index*/][/*measure index*/];

The target structure is accepted by the dxChart widget may be the following.

JavaScript
[
    {
        "name": /*the series name*/,
        "arg": /*the argument axis (horizontal) value*/,
        "val": /*the value axis (vertical) value*/
    },
    //...
]

Data Structure Conversion

The main idea of the conversion is to fill the chart's argument axis with column names, the value axis — with summary values, and make the chart series from pivot grid rows. Here, the chart will display only the deepest expanded hierarchical level, because it makes the chart clearer.

The first thing to be determined is the deepest expanded level. For this task, as well the ones that will follow, the recursive tree traversal function will be required. The example of its implementation is shown below.

JavaScript
var foreachTree = function (items, func, members) {
    members = members || [];
    for (var i = 0; i < items.length; i++) {
        members.unshift(items[i]);
        func(members);
        if (items[i].children) {
            foreachTree(items[i].children, func, members);
        }
        members.shift();
    }
}

The deepest expanded row and column level can be now determined by traversing the rows and columns arrays and saving the maximum nesting level. The implementation is shown below.

JavaScript
var data = pivotGridDataSource.getData(),
    rowLevel = 1,
    columnLevel = 1;

foreachTree(data.rows, function (members) {
    rowLevel = Math.max(rowLevel, members.length);
});

foreachTree(data.columns, function (members) {
    columnLevel = Math.max(columnLevel, members.length);
});

The next thing to do is to use the "Grand Total" row in a situation where either rows or columns are absent in the pivot grid (column or row area is empty).

JavaScript
var rows = data.rows.length ? data.rows : [{ index: 0, value: "Grand Total" }];
var columns = data.columns.length ? data.columns : [{ index: 0, value: "Grand Total" }];

At this step, everything is ready for the conversion. The following code is a sample implementation of the conversion algorithm.

JavaScript
var dataSource = [],
    measureIndex = 0;
foreachTree(rows, function (rowMembers) {
    if (rowLevel === rowMembers.length) {
        var names = $.map(rowMembers, function (member) { return member.value; }).reverse();
        foreachTree(columns, function (columnMembers) {
            if (columnLevel === columnMembers.length) {
                var args = $.map(columnMembers, function (member) { return member.value; }).reverse(),
                    value = ((data.values[rowMembers[0].index] || [])[columnMembers[0].index] || [])[measureIndex];

                dataSource.push({
                    name: names.join(" - "),
                    arg: args.join("/"),
                    val: value === undefined ? null : value
                });
            }
        });
    }
});

The code above converts the row, column and data values of the deepest expanded level to the chart's data format. The row and column values whose hierarchy level is more than one are complemented with the names of all previous levels.

Integration Example

The example below shows how to use the data conversion procedure described in this article to integrate the dxPivotGrid widget with a dxChart widget.

Show Example:
AngularJS
Knockout
jQuery

This example demonstrates a dxChart widget integrated with the pivot grid. The chart shows only data of the deepest expanded level.


                                    

                                    

This example demonstrates a dxChart widget integrated with the pivot grid. The chart shows only data of the deepest expanded level.


                                    

                                    

This example demonstrates a dxChart widget integrated with the pivot grid. The chart shows only data of the deepest expanded level.