New to Kendo UI for jQuery? Download free 30-day trial

Integrate the PivotGrid with the Kendo UI Chart

Environment

Product Progress Kendo UI PivotGrid Progress Kendo UI Chart
Operating System Windows 10 64bit
Browser Google Chrome
Browser Version 61.0.3163.100

Description

How can I integrate a Kendo UI PivotGrid with a Kendo UI Chart?

Solution

The following example demonstrates how to integrate a Kendo UI PivotGrid widget with a Kendo UI Chart widget.

The example omits the totals from the PivotGrid, so that they are not shown in the data visualization of the Chart.

<div id="example">
    <div id="pivotgrid"></div>
    <div id="chart"></div>
    <script>
        //function flatten the tree of tuples that datasource returns
        function flattenTree(tuples) {
            tuples = tuples.slice();
            var result = [];
            var tuple = tuples.shift();
            var idx, length, spliceIndex, children, member;

            while (tuple) {
                //required for multiple measures
                if(tuple.dataIndex !== undefined) {
                    result.push(tuple);
                }

                spliceIndex = 0;
                for (idx = 0, length = tuple.members.length; idx < length; idx++) {
                    member = tuple.members[idx];
                    children = member.children;
                    if (member.measure) {
                        [].splice.apply(tuples, [0, 0].concat(expandMeasures(children, tuple)));
                    } else {
                        [].splice.apply(tuples, [spliceIndex, 0].concat(children));
                    }
                    spliceIndex += children.length;
                }

                tuple = tuples.shift();
            }

            return result;
        }

        function clone(tuple, dataIndex) {
            var members = tuple.members.slice();

            return {
                dataIndex: dataIndex,
                members: $.map(members, function(m) {
                    return $.extend({}, m, { children: [] });
                })
            };
        }

        function replaceLastMember(tuple, member) {
            tuple.members[tuple.members.length - 1] = member;
            return tuple;
        };

        function expandMeasures(measures, tuple) {
            return $.map(measures, function(measure) {
                return replaceLastMember(clone(tuple, measure.dataIndex), measure);
            });
        }

        //Check whether the tuple has been collapsed
        function isCollapsed(tuple, collapsed) {
          var members = tuple.members;

          for (var idx = 0, length = collapsed.length; idx < length; idx++) {
            var collapsedPath = collapsed[idx];
            if (indexOfPath(fullPath(members, collapsedPath.length -1), [collapsedPath]) !== -1) {
              return true;
            }
          }

          return false;
        }

        //Work with tuple names/captions
        function indexOfPath(path, paths) {
            var path = path.join(",");

          for (var idx = 0; idx < paths.length; idx++) {
            if (paths[idx].join(",") === path) {
                return idx;
            }
          }

          return -1;
        }

        function fullPath(members, idx) {
            var path = [];
          var parentName = members[idx].parentName;

          idx -= 1;

          while (idx > -1) {
            path.push(members[idx].name);
            idx -= 1;
          }

          path.push(parentName);

          return path;
        }

        function memberCaption(member) { return member.caption };

        function extractCaption(members) {
            return $.map(members, memberCaption).join(" ");
        };

        function fullPathCaptionName(members, dLength, idx) {
            var result = extractCaption(members.slice(0, idx + 1));
            var measureName = extractCaption(members.slice(dLength, members.mLength));

            if (measureName) {
                result += " - " + measureName;
            }

            return result;
        }

        //the main function that convert PivotDataSource data into understandable for the Chart widget format
        function convertData(dataSource, collapsed) {
          var columnDimensionsLength = dataSource.columns().length;
          var rowDimensionsLength = dataSource.rows().length;

          var columnTuples = flattenTree(dataSource.axes().columns.tuples || [], collapsed.columns);
          var rowTuples = flattenTree(dataSource.axes().rows.tuples || [], collapsed.rows);
          var data = dataSource.data();
          var rowTuple, columnTuple;

          var idx = 0;
          var result = [];
          var columnsLength = columnTuples.length;

          for (var i = 0; i < rowTuples.length; i++) {
            rowTuple = rowTuples[i];

            if (!isCollapsed(rowTuple, collapsed.rows)) {
              for (var j = 0; j < columnsLength; j++) {
                columnTuple = columnTuples[j];

                if (!isCollapsed(columnTuple, collapsed.columns)) {
                  if (idx > columnsLength && idx % columnsLength !== 0) {
                    for (var ri = 0; ri < rowTuple.members.length; ri++) {
                      for (var ci = 0; ci < columnTuple.members.length; ci++) {
                        //do not add root tuple members, e.g. [CY 2005, All Employees]
                        //Add only children
                        if (!columnTuple.members[ci].parentName || !rowTuple.members[ri].parentName) {
                          continue;
                        }

                        result.push({
                          measure: Number(data[idx].value),
                          column: fullPathCaptionName(columnTuple.members, columnDimensionsLength, ci),
                          row: fullPathCaptionName(rowTuple.members, rowDimensionsLength, ri)
                        });
                      }
                    }
                  }
                }
                idx += 1;
              }
            }
          }

          return result;
        }
    </script>
    <script>
        $(document).ready(function () {
            var collapsed = {
              columns: [],
              rows: []
            };

            var pivotgrid = $("#pivotgrid").kendoPivotGrid({
                filterable: true,
                //gather the collapsed members
                collapseMember: function(e) {
                  var axis = collapsed[e.axis];
                  var path = e.path;

                  if (indexOfPath(path, axis) === -1) {
                     axis.push(path);
                  }
                },
                //gather the expanded members
                expandMember: function(e) {
                  var axis = collapsed[e.axis];
                  var index = indexOfPath(e.path, axis);

                  if (index !== -1) {
                    axis.splice(index, 1);
                  }
                },
                columnWidth: 100,
                height: 330,
                dataSource: {
                    type: "xmla",
                    columns: [{ name: "[Date].[Calendar]", expand: true }, { name: "[Employee].[Gender]" }],
                  rows: [{ name: "[Product].[Category]", expand: true }],
                    measures: ["[Measures].[Reseller Freight Cost]", "[Measures].[Internet Order Quantity]"],
                    transport: {
                        connection: {
                            catalog: "Adventure Works DW 2008R2",
                            cube: "Adventure Works"
                        },
                        read: "https://demos.telerik.com/olap/msmdpump.dll"
                    },
                    schema: {
                        type: "xmla"
                    },
                    error: function (e) {
                        alert("error: " + kendo.stringify(e.errors[0]));
                    }
                },
                dataBound: function() {
                  //create/bind the chart widget
                  initChart(convertData(this.dataSource, collapsed));
                }
            }).data("kendoPivotGrid");

            function initChart(data) {
              var chart = $("#chart").data("kendoChart");

              if (!chart) {
                $("#chart").kendoChart({
                  dataSource: {
                    data: data,
                    group: "row"
                  },
                  series: [{
                      type: "column",
                      field: "measure",
                      name: "#= group.value # (category)",
                      categoryField: "column"
                  }],
                  legend: {
                      position: "bottom"
                  },
                  categoryAxis: {
                      labels: {
                          rotation: 310
                      }
                  },
                  valueAxis: {
                      labels: {
                          format: "${0}"
                      }
                  },
                  dataBound: function(e) {
                      var categoryAxis = e.sender.options.categoryAxis;

                      if (categoryAxis && categoryAxis.categories) {
                          categoryAxis.categories.sort();
                      }
                  }
                });
              } else {
                chart.dataSource.data(data);
              }
            }
        });
    </script>
</div>

See Also

For more runnable examples on the Kendo UI PivotGrid, browse its How To documentation folder.

In this article