A couple weeks ago, I introduced pocketSOAP, an experimental JavaScript library that facilitates interactions with SharePoint SOAP services.
Today let me share two more examples built on SOAP and the templating engine, combined with Google Visualization Charts, to render charts from SharePoint lists.
If you have subscribed to the SPELL newsletter, you’ll find pocketSOAP in your mailbox this week. Keep in mind that it is an experimental library, only the full SPELL library is meant for production environments (cf. disclaimer in the previous post).
I am not expecting you to be impressed by the screenshots below. After all, I’ve been publishing client side charting demos in this blog since 2008, and since then many others have followed. The real kicker here is how compact and versatile the code is. Even if you are not interested in client side scripting for a production environment, a tool like SPELL/pocketSOAP could prove really useful in a prototyping phase, and save you many hours of hardcore (re)programming!
Example 1: Pie Chart
For this first example, I used the same SharePoint list as in the previous post, with two columns:
- the default “Title” for the item name
- “Priority” as a choice field with 3 options (high/medium/low)
The code:
<div id="results"></div>
<script type="text/javascript" src="pocketSOAP.min.js"></script>
<script type="text/javascript" src="//www.google.com/jsapi"></script>
<script type="text/javascript">
// Load the Google charting library
google.load("visualization", "1", {packages:["corechart"]});
// Get items from the Projects list
var promisedItems = pS.soap({
// Service info
site:"https://usermanaged.sharepoint.com/TestZone",
service:"Lists",
operation:"GetListItems",
// Service parameters
listName:"Projects",
viewFields:"<ViewFields><FieldRef Name='Title' /><FieldRef Name='Priority' /></ViewFields>"
});
pS.when(promisedItems)
.then(function(request){
// When ready, run the charting function
google.setOnLoadCallback(function(){drawChart(request);});
});
function drawChart(request) {
// The templating engine formats the data string according to Google format
var dataString=pS.applyTemplate({
template:'[["Project","Priority"][<|,["|Title|","|Priority|"]|>]]',
prefix:"ows_",
data:pS.byTagNS(request.responseXML,"row","z")
});
var dataTable = google.visualization.arrayToDataTable(JSON.parse(dataString));
// Group by priority. Priority is the second column (index = 1)
var data = google.visualization.data.group(dataTable, [1], [{'column': 1, 'type': 'number', 'aggregation': google.visualization.data.count}]);
var chartOptions = {'title':'Priority','width':400,'height':300,colors:['red','#FFC200','green']}; // #FFC200 is the color code for amber
var chart = new google.visualization.PieChart(document.getElementById('results'));
chart.draw(data, chartOptions);
}
</script>
The SOAP call is almost the same as in the previous post. The only difference is that I have explicitly told SharePoint which fields I want to retrieve, via the viewFields option. This is not mandatory but it will reduce the size of the dataset and improve performance.
The use of the templating pattern (pS.applyTemplate) to convert data formats is unusual – as a matter of fact I have never seen this used elsewhere. I am not advertising it as a best practice, and more traditional object manipulation could be used here to the same effect. But I really like how templating makes the code easier to maintain.
Templating is a very popular pattern in modern JavaScript, and can be found in many libraries, like jQuery, mustache, handlebars and others. The SPELL/pocketSOAP flavor might not be as powerful as those big names, but in just 1 kb of code it still has a lot to offer, and – as you would expect – it is well adapted to work with SharePoint Web Services, for both XML and JSON output (to use it with JSON instead of XML, simply replace the [< >] notation with [{ }]).
Example 2: Bubble Chart
For this second example, I created a SharePoint list with 5 columns:
- the default “Title” for the product name
- “Risk” as a choice field with 3 options (low/medium/high)
- “Cost” and “Revenue” are numbers
- “Profit” is a calculated column:
[Profit]=[Revenue]-{Cost]
The code:
<div id="results"></div>
<script type="text/javascript" src="pocketSOAP.min.js"></script>
<script type="text/javascript" src="//www.google.com/jsapi"></script>
<script type="text/javascript">
// Load the Google charting library
google.load("visualization", "1", {packages:["corechart"]});
// Get items from the PMO list
var promisedItems = pS.soap({
// Service info
site:"https://usermanaged.sharepoint.com/TestSite",
service:"Lists",
operation:"GetListItems",
// Service parameters
listName:"PMO"
});
pS.when(promisedItems)
.then(function(request){
// When ready, run the charting function
google.setOnLoadCallback(function(){drawChart(request);});
});
function drawChart(request) {
// The templating engine formats the data string according to Google format
var dataString=pS.applyTemplate({
template:'[["Product","Cost","Revenue","Risk","Profit"][<|,["|Title|",|Cost|,|Revenue|,"|Risk|",|Profit|]|>]]',
prefix:"ows_",
data:pS.byTagNS(request.responseXML,"row","z"),
sanitize:function(string){return string.replace(/float;#/,"");}
});
var dataTable = google.visualization.arrayToDataTable(JSON.parse(dataString));
var chartOptions = {
title: 'PMO',
width:500,
height:400,
colors:['green','#FFC200','red'], // #FFC200 is the color code for amber
legend:{position:'top'},
hAxis:{title: 'Cost',maxValue:100},
vAxis:{title: 'Revenue',maxValue:100}
};
var chart = new google.visualization.BubbleChart(document.getElementById('results'));
chart.draw(dataTable, chartOptions);
}
</script>
Note in the template the difference between text fields (for example “|Title|”) and number fields (for example |Cost|). Also, this time I had to include a sanitize function to remove the weird “float;#” string that SharePoint prepends to calculated columns.
The above code samples could easily be tweaked to work with other client side charting libraries, like Dojo, HighCharts, or [name-your-own]. If you are interested in a demo, leave a comment with the name of your favorite charting tool!
Path to SharePoint... and Beyond! 