Subscribe via RSS Feed Connect with me on LinkedIn

Utilizing SPServices for SharePoint 2010 to create charts with High Charts

[ 13 ] May 21, 2013 |

Marc Anderson’s SPServices is an awesome way to take advantage of SharePoint 2010’s web services. Likewise, HighCharts is an awesome way to present data. In this blog post, I’m going to demonstrate my experiment with SPServices and how I used it to pump data into HightCharts to render a cool and informative pie chart.

The Data

Lets say hypothetically there is an Owl Watchers Club who likes to log each time they encounter an Owl into a SharePoint List. They are keeping track of each encounter by logging the location, and the type of owl seen.

A SharePoint List

Over time, they amass a significant number of log entries and now would like to summarize the number of encounters per location. Without any server side code, I’m going to demonstrate how I summarized the data into a pie chart.

Setup

First thing I need to do is to load all the prerequisite javascript files. This includes the jQuery library, SPServices, Highcharts.js, and lets also add underscore.js for some utility functions. There are many ways to load these javascript files into SharePoint 2010 which I will not detail here. I’m simply going to upload my javascript files into a Document Library and then reference them from a Content Editor web part.

Some JS Files

Content Editor Web Part

Now lets create a new site page that will be used to display the chart. On the page, lets add a Content Editor Web Part and edit its HTML source; this will be where all the magic happens.

Load JS in Content Editor Webpart

Magical Source Code

Here I’ll get to the meat of the pie. All of this also goes into the HTML Source section of the Content Editor web part.

I start off with a jQuery document ready function.

$(document).ready(function () {
    //do stuff in here here later
});

Within the document ready function, I use SPServices to get some data from a Sharepoint list.

$().SPServices({
    operation: "GetListItems",
    CAMLQuery: "",
    CAMLViewFields: "",
    listName: "{00B97564-01BC-4B76-A44E-F3ABB9725F55}",
    completefunc: processData
});

I’m not doing much in the CAMLQuery parameter, but you can specify a CAML Query to limit your data set. For example I can query for all owls spotted in Vancouver by doing something like the following:

CAMLQuery: "Vancouver"

In the CAMLViewFields parameter, I specify which fields get returned in the web service’s soap message.
The listName parameter is the name of the Sharepoint list that contains your data. I believe you can just specify the list’s name but I haven’t had much luck with it so I’m using the list’s GUID instead.

The completefunc parameter is the name of the success function. Which, for me, looks like this:

function processData (xData, status) {
	var owlData = [];

    $(xData.responseXML).SPFilterNode("z:row").each(function () {
        owlData.push({
            owl:    	$(this).attr('ows_Title'),
            date: 		$(this).attr('ows_Date_x0020_of_x0020_Sighting'),
            action:     $(this).attr('ows_Action'),
            location:   $(this).attr('ows_Location')
        });
    });

    var chartData = [];
    var locationData = _.groupBy(owlData, 'location');

    _.each(locationData, function(row) {
        var locCount = row.length;

        chartData.push( {
        	name: 	row[0].location,
        	y: 		locCount
        });

    });

    renderChart (chartData);
}

In this function, I’m basically processing the data and grouping all the Sharepoint list items with the same location. I use underscore’s groupBy function to help group my rows based on location, then I do a count and push that data to an array that will later be used in Highcharts.

Rendering Highcharts:

function renderChart (data) {
	var chart = new Highcharts.Chart({
        chart: {
            renderTo: 'owlchart',
            plotBackgroundColor: null,
            plotBorderWidth: null,
            plotShadow: false
        },
        credits: {
            enabled: true
        },
        title: {
            text: 'Owl Sightings by Location'
        },
        tooltip: {
            pointFormat: '{series.name}: <b>{point.percentage}%</b>
{point.y} Times',
            percentageDecimals: 1
        },
        plotOptions: {
            pie: {
                allowPointSelect: true,
                cursor: 'pointer',
                dataLabels: {
                    enabled: true,
                    color: '#000000',
                    connectorColor: '#000000',
                    formatter: function() {
                        return '<b>'+ this.point.name +'</b>: '+ this.y +' Times';
                    }
                },
            }
        },
        series: [{
            type: 'pie',
            name: 'Location Count',
            data: data
        }]
    });
}

Check out Highchart’s documentation and sample charts on the various parameters and settings you can pass into it. In this example, I’m plotting a pie chart. The renderTo parameter is the id of the div element that the chart will be rendered to.

<div id="owlchart"></div>

Thats all of it, and it all goes into the Content Editor Web Part.

Everything all at once

hchart-pie

<script type="text/javascript" src="http://code.jquery.com/jquery-1.8.3.min.js"></script><script type="text/javascript" src="http://code.highcharts.com/highcharts.js"></script>
<script type="text/javascript" src="http://jeff2k8/blog/SiteAssets/jquery.SPServices-0.7.2.js"></script><script type="text/javascript" src="http://jeff2k8/blog/SiteAssets/underscore.js"></script>

<script type="text/javascript">
	$(document).ready(function () {

		$().SPServices({
	        operation: "GetListItems",
	        CAMLQuery: "<Query><OrderBy><FieldRef Name='Location'/></OrderBy></Query>",
	        CAMLViewFields: "<ViewFields><FieldRef Name='Title'/><FieldRef Name='Date_x0020_of_x0020_Sighting'/><FieldRef Name='Action'/><FieldRef Name='Location'/></ViewFields>",
	        listName: "{00B97564-01BC-4B76-A44E-F3ABB9725F55}",
	        completefunc: processData
	    });
	});

	function processData (xData, status) {
		var owlData = [];

        $(xData.responseXML).SPFilterNode("z:row").each(function () {
            owlData.push({
                owl:    	$(this).attr('ows_Title'),
                date: 		$(this).attr('ows_Date_x0020_of_x0020_Sighting'),
                action:     $(this).attr('ows_Action'),
                location:   $(this).attr('ows_Location')
            });
        });

        var chartData = [];
        var locationData = _.groupBy(owlData, 'location');

        _.each(locationData, function(row) {
            var locCount = row.length;

            chartData.push( {
            	name: 	row[0].location,
            	y: 		locCount
            });

        });

        renderChart (chartData);
	}

	function renderChart (data) {
		var chart = new Highcharts.Chart({
	        chart: {
	            renderTo: 'owlchart',
	            plotBackgroundColor: null,
	            plotBorderWidth: null,
	            plotShadow: false
	        },
	        credits: {
	            enabled: true
	        },
	        title: {
	            text: 'Owl Sightings by Location'
	        },
	        tooltip: {
	            pointFormat: '{series.name}: <b>{point.percentage}%</b>
{point.y} Times',
	            percentageDecimals: 1
	        },
	        plotOptions: {
	            pie: {
	                allowPointSelect: true,
	                cursor: 'pointer',
	                dataLabels: {
	                    enabled: true,
	                    color: '#000000',
	                    connectorColor: '#000000',
	                    formatter: function() {
	                        return '<b>'+ this.point.name +'</b>: '+ this.y +' Times';
	                    }
	                },
	            }
	        },
	        series: [{
	            type: 'pie',
	            name: 'Location Count',
	            data: data
	        }]
	    });
	}
</script>
<div id="owlchart"></div>

Tags: , , , ,

Category: SharePoint 2010

About Jeff Yan: View author profile.

Comments (13)

Trackback URL | Comments RSS Feed

  1. Simon says:

    I’m a bit of a newbie and have spend several days figuring out how this works.

    I’v tested several ways but I’can’t get the highchart pie to appear on my page…

    I’v tested the url’s to the js to make sure that they are working using different alerts. At the same time I’v tested my Owllist to make sure that the listname (using the GUID) and fieldnames were correct using SPServices an GetListItems.

    I do get all the items in the list back so it works fine…

    The problem seems to be the code rendering the Highchart and I just can figure it out so I would be gratefull if you can point me in the correct direction…

    Using SP2010Foundation and IE8.

    • Jeff Yan says:

      Hello Simon, thanks for trying out my little demo. From the source code above, it looks like lines 59 and 60 should actually be one line of code. I’m not sure if that is the source of your problem, but maybe try removing the carriage return between line 59 and 60. Other than that, I advise debugging using Firefox’s firebug, Chrome, or even IE’s developer tools (press F12).

      • Simon says:

        Jeff,

        My man…removing the belov mentioned code it worked…

        removed code (line 62-66 in my notepad++):

        tooltip: {
        pointFormat: ‘{series.name}: {point.percentage}%
        {point.y} Times’,
        percentageDecimals: 1
        },

        Thanks a lot man…

  2. Simon says:

    …and I just can’t figure it out…

    I’m using your code and have’nt made any changes to the functions – the code below the SPServices call…

  3. Divya says:

    Hi,

    Nice post and explained very well.. I used and was successfully able to do the sample one and also imlpemented in one of my project but i was wondering on using Column chart and read few samlpes in highcharts link. But my data is dynamic and column is also dynamic , Is it possible to implement ?

    • chinni says:

      It possibel to do and display charts dynamically as per the selection of item.
      Recently i have implemented in my site.
      i have successfully implemented and it is running in production also.

  4. Kai says:

    I’m trying to mirror what you have put together here, however the chart never loads. It just renders a blank screen. I’ve duplicated exactly what you have. Just out of curiosity, should this work in SharePoint 2013 as well?

  5. sagar says:

    Do you have any working example for badic bar chart with highchart Nd spservices.

  6. Terrence says:

    I am creating a column chart rather than a pie chart. Do you know how I would sum the values of the group rather then the count?

    I am grouping by a department field I also have a cost field and I need to sum the total cost for each department.

    Thank you in advance

  7. […] There must be an issue with the grouping/counting step outlined above because I am seeing no output, but I simply can’t find where. I am basing my solution on the following worked example: Worked example. […]

  8. Arley Alves Ribeiro says:

    Hi Jeff,

    I am using PageViewer webpart to display a web site that uses highcharts, but I can’t make the exporting feature to work. Can you try to export the charts using the highcharts menu and tell me if it works for you. Also can you tell me if you did something special to make it work?

    Thanks in advance

  9. gangareddy says:

    hello sir i am using your code but the charts is not displaying in share point 2103 i am taking the content editor web part and past the code change the some changes after past the code click the ok then edit the page edit source only script is there please help in this issue because i have urgent requirement in this requirement i have to use database tables and prepare the high charts please help i am new this topic

  10. gangareddy says:

    i have copy the all js file into document library i given the path

Leave a Reply




If you want a picture to show with your comment, go get a Gravatar.