Setting up Pivot Grid & GeoChart to Show Data

posted on : Tuesday, 9 Apr, 2019

author avatar John Hashim

Hey! I’m a Junior web developer at PROTECHIG And founder of Unwrote Blog.

Catch the tutorial on how to make a web report look more interactive and appealing. Our goal is to create a dashboard with data in a table and a chart. The chart must be updated in real-time after data changing in the table.

This road takes only 5 steps. There is also a ready-to-use code snippet that you can copy. Move further on these steps!

Step 1. Load your data

After including a pivot table component into the web page, add a URL to CSV/JSON file with the data directly into the report. Just specify the URL to your file in a ‘filename’ property

1
filename: "URL-to-your-CSV-or-JSON-file"

Step 2. Configure the slice and aggregate the data

Next step - choose which data is going to be displayed within the rows and the columns.

For example, put the Country in the rows, Category in the columns. Define the measures and apply an aggregation function to them.

Step 3. Connect to the Charting Library

Add the loader of Google Charts by including the script into the web page:

1
<script src="https://www.gstatic.com/charts/loader.js"></script>

Add the WebDataRocks connector for Google Charts

1
<script src="https://cdn.webdatarocks.com/latest/webdatarocks.googlecharts.js"></script>

Add a container where the chart will be displayed:

1
<div id="googlechartContainer"></div>

Add an event handler to the reportcomplete event by writing the following code in the configurations of the pivot table:

1
reportcomplete: function() {       pivot.off("reportcomplete");       pivotTableReportComplete = true;       createGoogleChart();}

This event is fired when the data from the report is loaded and the grid is rendered. Create flags to track when the report and charts finished loading:

1
var pivotTableReportComplete = false;var googleChartsLoaded = false;

Load the charts from google.charts library and specify mapsApiKey:

1
google.charts.load('current', {'packages': ['geochart'],'mapsApiKey': 'AIzaSyD-9tSrke72PouQMnMX-a7eZSW0jkFMBWY'});

Step 4: Show the data from the pivot table in the chart

Start with adding functions for drawing and creating the chart.

Firstly, set the onGoogleChartsLoaded() function as a callback for the google.charts event handler:

1
 google.charts.setOnLoadCallback(onGoogleChartsLoaded);

Secondly, define create GoogleChart() and onGoogleChartsLoaded() functions:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
function createGoogleChart() {

if (googleChartsLoaded) {

    pivot.googlecharts.getData({

    type: "geochart" // specify the chart type

    }, 

   drawChart,

   drawChart

    );

}

}


function onGoogleChartsLoaded() {

   googleChartsLoaded = true;

    if (pivotTableReportComplete) {

   createGoogleChart();

    }

}

Thirdly, write a function which will be called once the data is ready or updated. It is responsible for drawing and updating the chart. Also, here you should specify the type of the chart you need. Optionally, define the color palette for the chart.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
function drawChart(data) {

    var data = google.visualization.arrayToDataTable(_data.data);

    var options = {

    colorAxis: {

    colors: ['#449544', '#4ca64c', '#7fbf7f', '#b2d8b2']

    },

    backgroundColor: '#b3e5fc',

    datalessRegionColor: '#ffffff',

    defaultColor: '#f5f5f5',

};



    var chart = new google.visualization.GeoChart(document.getElementById('googlechart-container'));

    chart.draw(data, options);

}

Step 5. Enjoy the result!

Finally, you can see how wonderful the pivot table looks in combination with Google Charts.

You can confidently change the data in the table and see how the results are reflected in the chart immediately! This is what we call a real-time interaction. Be open to new experiments - filter, sort the data, change the measures and the aggregation functions.

Getting new insights is closer than you think!