In previous posts I’ve talked about how to use javascript to query the Office Live web services from within site pages. In my article I used the List Data Retrieval web service to query an arbitrary list extracting rows of information as XML or an object array. As well as this method, we can also use the GetListItems method of the Lists Web Service to get data from lists and views. Today I’m going to describe how to use this service along with a Flash charting component to build live charts in web parts which are populated directly from data held in lists within your Office Live or SharePoint site.

XML Flash Charts

In this article we’ll be using XML/SWF Charts which is an excellent flash component capable of drawing many different types of charts from very flexible XML definitions. If you like what you see and use this component in your sites I would encourage you to register your copy.

The Lists web service

The Lists Web Service is used to (unsurprisingly) perform operations on lists. Today we’ll be using it to retrieve all of the rows in a given list or view of a list. To do this we’ll use a javascript method similar to the following which simply uses XMLHTTP to call the lists.asmx web service.

// Gets all of the list items in a specified list and view
// Params:
// list: list name
// view: view name
// columns: an array of column names in the view to retrieve
//
// Returns:
// The response XML from lists.asmx-->GetListItems or null on an error
this.getListItems = function(list, view, columns)
{
	var a = new ActiveXObject("Microsoft.XMLHTTP");
	var cols = "<viewFields>";

	if(a == null) return null;

	for (var i = 0; i < columns.length; i++)
	{
		cols += "<fieldRef Name='" + columns[i][0] + "'/>";
	}

	cols += "</viewFields>";

	a.Open("POST", this.getRootUrl() + "_vti_bin/Lists.asmx", false);
	a.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
	a.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/GetListItems");
	var d = '<?xml version="1.0" encoding="utf-8"?>'
	+ "<soap:Envelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\">"
	+ "<soap:Body>"
	+ "<getListItems xmlns=\"http://schemas.microsoft.com/sharepoint/soap/\">"
	+ "<listName>" + list + "</listName>";

	if (view != "")
		d += "<viewName>" + view + "</viewName>";

	d += "<viewFields>" + cols + "</viewFields>"
	+ "</getListItems>"
	+ "</soap:Body>"
	+ "</soap:Envelope>";

	a.Send(d);

	if (a.status != 200)
		return null;
	else
		return a.responseXML;

}

The aim of all of this is to get data from a list or view and display it as a chart. Pulling list items from views has the advantage of allowing the user to configure the way they want their list data presented in a chart. They can, for example, specify sorting and filtering criteria and can limit the columns they want to return. Here we want them to configure a list view for the chart they need and we’ll pull out the list items using the method above.

Generating the chart

List data is returned to us as an XML message and so is ripe for transforming into the XML format needed by the charting component. Once the list data is retrieved we use a client side transformation to create our charting data using a javascript method like this:

// Performs a client transform of an XML dom using a specificed transform
// Params:
// xmlDOM: the DOM to transform
// transPart: the path to the transform file
// params: an array of parameters- array(array(name,value)...)
//
// Returns:
// The transformed result (as a string)
this.clientTransform = function(xmlDOM, transPath, params)
{
	var xsl = new ActiveXObject("Msxml2.FreeThreadedDOMDocument");
	var template = new ActiveXObject("Msxml2.XSLTemplate");
	var proc;

	xsl.async = false;
	xsl.load(transPath)

	template.stylesheet = xsl;
	proc = template.createProcessor();
	proc.input = xmlDOM;

	// params
	for (var i=0; i<params.length; i++)
	{
		proc.addParameter(params[i][0], params[i][1], "");
	}

	proc.transform();

	return proc.output;
}

This method accepts an array of user defined parameters which are passed to the XSLT for use during processing. This allows us a high degree of flexibility as the user can specify both the XSLT to use and an arbitrary set of parameters to affect the way in which it processes.

The transforms will produce XML documents similar to the following which generates a basic bar chart in XML/SWF Chart.

<chart>
	<chart_data>
		<row>
			<null/>
			<string>2001</string>
			<string>2002</string>
			<string>2003</string>
			<string>2004</string>
		</row>
		<row>
			<string>Region A</string>
			<number>5</number>
			<number>10</number>
			<number>30</number>
			<number>63</number>
		</row>
		<row>
			<string>Region B</string>
			<number>100</number>
			<number>20</number>
			<number>65</number>
			<number>55</number>
		</row>
		<row>
			<string>Region C</string>
			<number>56</number>
			<number>21</number>
			<number>5</number>
			<number>90</number>
		</row>
	</chart_data>
</chart>

Putting it together

To make all of this happen I’ve created a javascript class called OLChart which is responsible for querying the appropriate view, transforming the data, and generating the flash chart.

As an example, let’s take some data about the results of the oh-so-controversial Scottish parlimentary elections in 2007:

I’ve put this data into a list and set it to sort in descending order of percentage vote. I want to display a simple pie chart of the different parties and their percentage of the vote. To do this, I use the following javascript code inside a content editor web part.

<div id="chart1"></div>
<script type="text/javascript">
var chart = new OLChart("chart1", "pie", "500",
"400", "pie.xsl", "Election", "", "Scottish Election Results 2007",
[["Title", "Party", false], ["Percentage", "Percentage vote", true]]);
</script>

This code makes a new instance of the OLChart class and instructs it to generate a pie chart and place it inside the div with an id of “chart1″ (which I’ve declared at the top of the script). We’ll go over the various parameters for OLChart in a minute. For now let’s just see what this produces.

That seems to do the job nicely. I’ve got a chart title and individual series titles. I need to tell OLChart where all of this data comes from when I instantiate the object. At first the paramter list looks a little complicated, but it’s actually quite simple.

function OLChart(id, charttype, width, height, template, list, view, maintitle, columns, params)

The sample template

To get you started I’ve included a sample site template in the downloads area of this site. The sample allows you to explore the various types of charts using two sets of data. I’ve also included the javascript code, a copy of XML/SWF flash, and some sample transforms. To install the sample just upload the GraphDemo.stp template into your Office Live site and create a new application based on it. To install the code in a new application, copy the charts folder into the root of the application using SharePoint designer. You then need to import the charts_lib.js javascript file into the page you want to draw charts on(default.aspx for the dashboard).