JavaScript Pivot Table with Google Charts

Flexmonster Pivot Table & Charts seamlessly integrates with Google Charts — a web service for data visualization. Using these tools together, you can create a well-designed interactive dashboard and embed it into any application.

Dashboard analytics is a perfect instrument to assist in making data-driven decisions. With its help, end-users can highlight specific metrics, answer complex business questions, and communicate insights to the audience.

Income, Expenses, and Profit

Dashboards with Flexmonster and Google Charts can be interactive: changes applied to the pivot table's report are reflected in charts in an instant. Thankfully to this real-time behavior, end-users can look at the data from different angles on the fly.

Top 5 Countries by Sales

Edit in JSfiddle Get Free Trial

const pivot = new Flexmonster({
  container: "#pivot-container",
  componentFolder: "https://cdn.flexmonster.com/",
  height: 350,
  licenseFilePath: "https://cdn.flexmonster.com/jsfiddle.charts.key",
  report: {
    dataSource: {
      type: "json",
      filename: "data/demos/googlecharts-demo-data.json",
    },
    slice: {
      rows: [
        {
          uniqueName: "Date.Month",
        },
      ],
      columns: [
        {
          uniqueName: "[Measures]",
        },
      ],
      measures: [
        {
          uniqueName: "Income",
          formula: 'sum("Sales") * sum("Unit Price")',
          individual: true,
          caption: "Income",
          format: "currency",
        },
        {
          uniqueName: "Sales",
          active: false,
          format: "currency",
        },
        {
          uniqueName: "Expenses",
          format: "currency",
        },
        {
          uniqueName: "Profit",
          formula: 'sum("Income") - sum("Expenses")',
          individual: true,
          сaption: "Profit",
          format: "currency",
        },
      ],
    },
    formats: [
      {
        name: "",
        maxDecimalPlaces: 2,
      },
      {
        name: "currency",
        decimalPlaces: 1,
        currencySymbol: "$",
      },
    ],
    options: {
      grid: {
        showHeaders: false,
      },
      showAggregationLabels: false,
    },
  },
  reportcomplete: function () {
    pivot.off("reportcomplete");
    pivotTableReportComplete = true;
    createColumnChart();
    createPieChart();
  },
});

const colors = [
  "#4cbf8b",
  "#e8734c",
  "#ffcd4c",
  "#9875e3",
  "#4c9eff",
  "#8acfc3",
  "#cd97e6",
  "#f1d34c",
  "#65d2e7",
];

let pivotTableReportComplete = false;
let googleChartsLoaded = false;

google.charts.load("current", {
  packages: ["corechart", "bar"],
});
google.charts.setOnLoadCallback(onGoogleChartsLoaded);

function onGoogleChartsLoaded() {
  googleChartsLoaded = true;
  if (pivotTableReportComplete) {
    createColumnChart();
    createPieChart();
  }
}

function createColumnChart() {
  if (googleChartsLoaded) {
    pivot.googlecharts.getData(
      {
        type: "column",
      },
      drawColumnChart,
      drawColumnChart
    );
  }
}

function drawColumnChart(_data) {
  let data = google.visualization.arrayToDataTable(_data.data);
  let formatter = new google.visualization.NumberFormat({
    fractionDigits: 2,
    prefix: "$",
  });

  for (let i = 0; i < data.getNumberOfColumns(); i++) {
    if (data.getColumnType(i) === "number") {
      formatter.format(data, i);
    }
  }

  let options = {
    fontName: "SERIF TYPEFACE",
    chartArea: {
      height: "100%",
    },
    height: 300,
    colors: colors,
  };

  const columnChartContainer = document.getElementById("googlechart-column-container");

  let chart = new google.charts.Bar(columnChartContainer);

  chart.draw(data, options);
}

function createPieChart() {
  if (googleChartsLoaded) {
    pivot.googlecharts.getData(
      {
        type: "pie",
        slice: {
          rows: [
            {
              uniqueName: "Country",
              filter: {
                measure: {
                  uniqueName: "Sales",
                },
                query: {
                  top: 5,
                },
              },
            },
          ],
          columns: [
            {
              uniqueName: "[Measures]",
            },
          ],
          measures: [
            {
              uniqueName: "Sales",
            },
          ],
        },
      },
      drawPieChart,
      drawPieChart
    );
  }
}

function drawPieChart(_data) {
  let data = google.visualization.arrayToDataTable(_data.data);
  let options = {
    legend: {
      position: "bottom",
    },
    height: 300,
    pieSliceText: "none", // Remove text from pie slices
    pieHole: 0.35,
    chartArea: {
      height: "85%",
      top: 0,
    },
    pieSliceBorderColor: "none",
    colors: colors,
  };

  const pieChartContainer = document.getElementById("googlechart-pie-container");

  let chart = new google.visualization.PieChart(pieChartContainer);

  chart.draw(data, options);
}

<div id="pivot-container"></div>

<div class="demo-box">
  <div class="demo-title"><strong>Income, Expenses, and Profit</strong></div>
  <div id="googlechart-column-container"></div>
</div>

<div class="demo-box">
  <div class="demo-title"><strong>Top 5 Countries by Sales</strong></div>
  <div id="googlechart-pie-container"></div>
</div>

.demo-box {
  background-color: #fafafa;
  position: relative;
  padding: 20px 20px 20px 20px;
  border: 1px solid #e9e9e9;
  margin-bottom: 20px;
}

.demo-title {
  font-size: 18px;
  margin-bottom: 30px;
  white-space: nowrap;
  text-overflow: ellipsis;
  color: #555;
}

#pivot-container {
  margin-bottom: 20px;
}

/** For the background color of Google Charts 
 * (for material design charts, it can't be changed via options, only via CSS)
 */ 
#googlechart-column-container > div > div > svg > g:nth-child(2) > rect:nth-child(1) {
  fill: #fafafa !important;
}

#googlechart-pie-container > div > div:nth-child(1) > div > svg > rect {
  fill: #fafafa !important;
}

/* For text on xAxis of the column chart */
#googlechart-column-container > div > div > svg > g:nth-child(6) > text:nth-child(10) {
  color: #555;
  fill: rgb(117, 117, 117);
  font-family: Roboto;
  font-size: 12px;
}

#googlechart-pie-container > div > div:nth-child(n) > div > svg > g:nth-child(n) > g:nth-child(n) > g > text {
  color: #555;
  fill: rgb(117, 117, 117);
  font-family: Roboto;
  font-size: 12px;
}

With our Integration with Google Charts guide, you can learn how to configure a dashboard with Flexmonster and Google Charts.

The entire process is straightforward: add the pivot table and charts to the web page and make both components communicate via the specially prepared charting connector.

JavaScript Pivot Table with Google Charts

See our live examples