SPServices Stories #2 – Charting List Data with HighCharts

This entry is part 2 of 21 in the series SPServices Stories

Introduction

This submission comes to us from an anonymous reader, who can’t publish the details under his (or her) name due to confidentiality issues. However, s/he has been able to generate some very useful charts with HighCharts using SharePoint list data as the underlying data sources.

Charting List Data with HighCharts

HighChartsIn the following code, the source list contains columns with Year (string), Month (1-12), and Value (number with decimals).

While HighCharts isn’t free, the licensing costs are quite reasonable. A similar approach would work with other charting engines out there which may be free.

The page has a Content Editor Web Part dropped into it with the Content Link pointing to a file containing the following:

<!-- jquery and spservices are in the masterpage here this is a CEWP noConflict is on-->

<script type="text/javascript"src="//code.highcharts.com/highcharts.js"></script>
<script type="text/javascript" src="//code.highcharts.com/modules/exporting.js"></script>
<script type="text/javascript">
function GetYearSeries(series, year)
{
  var gotOne = false;
  var seriesOptions;

  jQuery.each (series, function(index, dataItem) {
    if (dataItem.name === year)
    {
      seriesOptions = dataItem;
      gotOne=true;
    }
  });

  if (!gotOne)
  {
    seriesOptions = {
      name: year,
      data: [0,0,0,0,0,0,0,0,0,0,0,0]
    };

    series.push (seriesOptions);
  }

  return seriesOptions;

}

jQuery(function($) {

  var CamlQuery = "<Query><OrderBy><FieldRef Name='Year' /><FieldRef Name='Month' Ascending='False' /></OrderBy></Query>";

  $().SPServices({
    operation: "GetListItems",
    async: true,
    listName: "Sales",
    CAMLQuery: CamlQuery,
    CAMLViewFields: "<ViewFields><FieldRef Name='Year' /><FieldRef Name='Month' /><FieldRef Name='Value' /></ViewFields>",
    completefunc: GraphIt
  });

  function GraphIt(xmlResponse)
  {

    var options = {
      chart: {
        renderTo: 'container',
        type: 'column'
      },
      title: {
        text: 'Sales'
      },
      xAxis: {
        categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
      },
      yAxis: {
        title: {
          text: 'Value'
        }
      },
      series: []
    };

    $(xmlResponse.responseXML).SPFilterNode("z:row").each(function() {

      var seriesOptions = GetYearSeries(options.series, $(this).attr('ows_Year'));
      var month=parseInt($(this).attr('ows_Month'))-1;

      seriesOptions.data[month]=parseFloat($(this).attr('ows_Value'));
    });
    var chart = new Highcharts.Chart(options);

  }

});//docReady
</script>

<div id="container" style="min-width: 400px; height: 400px; margin: 0 auto"></div>

This generates a chart which looks something like this:

HighCharts Example

Series Navigation<< SPServices Stories #1 – How to Start a Workflow on Multiple Items in a ListSPServices Stories #3 – AddWebPart Method of the WebPartPages Web Service >>

Similar Posts

9 Comments

  1. Cool stuff, question, could you please explain how would I use stacked bars for sales and group the result by salesman / month / year ?

  2. Hi Marc,

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.