Visualise SP2010 Rest Data With Google Visualization API

This post is over a year old, some of this information may be out of date.

Some time ago I was testing out the Google Visualization API with SharePoint 2010 Rest functionality. The Google Visualization API is very handy if you want to visualize your data.

My intention was to create a pie chart to visualise the task statuses. The data was consumed from the SharePoint 2010 REST service with the help of jQuery.

With jQuery it is very simple to retrieve the results from the REST service. All you need to do is to create a call to the URL of the REST service with the getJSON method.

1
$.getJSON(url, function(data))

The URL to the REST service is the following: http://YOUR SITE/_vti_bin/ListData.svc. In my case the full url to the task list is: http://your site/_vti_bin/ListData.svc/Tasks?$orderby=Status asc. As you can see it will retrieve all my tasks and order them by the status value. This result can than be used to create a pie chart.

On the Google Visualization site I did a quick search for a pie chart. Google shows you an example of the pie chart and the corresponding code.

Here is my code, feel free to make some code optimisation.

1
<script src="jquery.min.js" type="text/javascript"></script>
2

3
<script type="text/javascript" src="jsapi"></script>
4

5
<script language="javascript" type="text/javascript">
6
  google.load("visualization", "1", {packages:["corechart"]});
7
  google.setOnLoadCallback(drawChart);
8

9
  function drawChart() {
10
    //Get the Rest url
11
    var url = "http://YOUR SITE/_vti_bin/ListData.svc/Tasks?$orderby=Status asc";
12

13
    //Request json data
14
    $.getJSON(url, function(data){
15
      //Prepare Google data
16
      var googleData = new google.visualization.DataTable();
17
      googleData.addColumn('string', 'Task Status');
18
      googleData.addColumn('number', 'Total');
19

20
      var check = 0;
21
      var checkStatus = "";
22
      //Store the statuses and total values
23
      var clientData = [];
24
      var total;
25
      var checkTotal = 0;
26

27
      //Get the number of results
28
      checkTotal = data.d.results.length;
29
      $.each(data.d.results, function (i, result) {
30
        total = i;
31
        //Check if first item
32
        if(i === 0) {
33
          checkStatus = result.StatusValue;
34
          check = 1;
35
        }
36
        else {
37
          if(checkStatus != result.StatusValue) {
38
            clientData.push( { Status: checkStatus, Total: check } );
39

40
            checkStatus = result.StatusValue;
41
            check = 1;
42
          }
43
          else {
44
            check++;
45
          }
46
          //Check if it is the last item
47
          if(total === (checkTotal - 1)) {
48
            clientData.push( { Status: checkStatus, Total: check } );
49
          }
50
        }
51
      });
52
      googleData.addRows(checkTotal);
53
      var i = 0;
54
      for (x in clientData)
55
      {
56
        //Add value to Status
57
        googleData.setValue(i, 0, clientData[x].Status);
58
        //Add value to Status total
59
        googleData.setValue(i, 1, clientData[x].Total);
60
        i++;
61
      }
62

63
      var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
64
      chart.draw(googleData, {width: 450, height: 300, title: 'Task Status Overview - Total: ' + checkTotal, is3D: true});
65
    });
66
  }
67
</script>
68

69
<div id="chart_div"></div>

You can place this code in a HTML Form Web Part. The end result will be the following.

Visualise SP2010 Rest Data With Google Visualization API

Related articles

Data View Web Part: Create a Google Chart With XSLT

How to Define a Custom Group Display Template (GroupTemplateId) for the Search Results WebPart via JavaScript

How to use the Search Results WebPart as Replacement When Content Search WebPart is not available in Your Farm

Report issues or make changes on GitHub

Comments

Elio Struyf