More pocketSOAP examples: Pie and Bubble Charts

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="//"></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
 // Service parameters
 viewFields:"<ViewFields><FieldRef Name='Title' /><FieldRef Name='Priority' /></ViewFields>"
 // When ready, run the charting function
 function drawChart(request) {
 // The templating engine formats the data string according to Google format
 var dataString=pS.applyTemplate({
 var dataTable = google.visualization.arrayToDataTable(JSON.parse(dataString));

// Group by priority. Priority is the second column (index = 1)
 var data =, [1], [{'column': 1, 'type': 'number', 'aggregation':}]);
 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);

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:

The code:

<div id="results"></div>
<script type="text/javascript" src="pocketSOAP.min.js"></script>
<script type="text/javascript" src="//"></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
 // Service parameters

 // When ready, run the charting function

function drawChart(request) {
 // The templating engine formats the data string according to Google format
 var dataString=pS.applyTemplate({
 sanitize:function(string){return string.replace(/float;#/,"");}

var dataTable = google.visualization.arrayToDataTable(JSON.parse(dataString));

var chartOptions = {
 title: 'PMO',
 colors:['green','#FFC200','red'], // #FFC200 is the color code for amber
 hAxis:{title: 'Cost',maxValue:100},
 vAxis:{title: 'Revenue',maxValue:100}

var chart = new google.visualization.BubbleChart(document.getElementById('results'));
 chart.draw(dataTable, chartOptions);

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!

Interactive demo: charting in SharePoint

Last month I published a first demo based on the SPELL mini-BI solution, showcasing the ability to build multi-level drill-down charts.

Today we are taking a look at the edit mode, thanks to an interactive demo. I think the best way to understand what this is about is to simply follow the link, read the instructions on the page, and start playing with the charts. Then you’re welcome to post feedback and questions here, and I’ll try my best to respond.

The previous demo used the Google GeoChart API. This time, the pie and column charts rely on the Dojo Toolkit. Dojo offers a wide range of charts, as well as multiple themes (I only picked a handful for the live demo). It also comes with plugins like animations and tooltips (also featured in the demo).

The Matrix View is homemade, and part of SPELL.

Here again, the data comes from grouped views (3 to choose from for the demo). This time I have only set up a two level drill-down, and clicking on a chart element will directly open the SharePoint list.

I made a recent addition to the demo that I find interesting, I call it “client side calculated column”. It allows you to enter directly in the form a formula, similar to what can be done with calculated columns in SharePoint lists, just much simpler (currently only simple operations). In the demo, the “Value” field plays the role of the calculated column (see the context help for an example).

I won’t describe this advanced customization in detail on my blog, but I’ll be happy to elaborate on specific points. If you think it could address your business needs, you’re welcome to subscribe to the SPELL interest list and you’ll receive in a couple weeks a sample to test on your own site. And if you are looking for a free, entry-level charting tool, remember to check out the SharePoint User Toolkit!

Teaser: real time Business Intelligence in SharePoint


I am making progress on my SPELL project. Its main component, the SPELL JavaScript library ($P), recently reached version 0.6 and has been implemented on a couple sites. I expect to reach version 0.7 by the end of the month and version 1 by the end of the year.

To showcase some of the capabilities of the SPELL library, I have set up a live demo featuring airline ontime statistics. In this demo, you can explore data across 3 dimensions: State (first level), Month and Carrier (second level). The third level are the list items themselves.

The first level is rendered via Google GeoCharts (for v1 SPELL will have other map options). The second level – matrix view – is a custom SPELL solution that mimics an Excel pivot table (much simpler though). You saw it in the slides if you read my previous post.

“Real time” refers to the fact that data is directly pulled from the SharePoint list. The charts always read the latest updates, as opposed to traditional BI patterns where data transfers are done at regular intervals. And there is no need for intermediate storage, as would be the case for Excel Services for example.

Note: for practical reasons the demo takes some shortcuts. For example the map doesn’t collect data directly from the list.

 I collected the airline data  (January to July 2012) from the Bureau of Transportation Statistics, I’ll add August as soon as it becomes available. Obviously interacting with more than 3 million items would not be reasonable, so I have done some pre-processing to aggregate the data by month and state. This brings the number of items down to ~500/month, for a total of 3,500 since the beginning of the year.

This is the downside of this solution: because it directly interacts with lists, it is subject to SharePoint’s usual limitations, for example the 5000 item threshold on views in SP 2010. Technically the tool could work with much bigger data sets, but this would require some adjustments and might not be desirable.

The solution showcased here has no ambition to compete with well established tools like PerformancePoint, SSRS or even Excel, but rather to offer a lightweight alternative. It presents a number of advantages that make it attractive :

  • Compatible with SharePoint 2007, 2010, Office 365 (evaluation of SP 2013 in progress)
  • Can easily be implemented by an experienced end user. No server side install, files are simply uploaded to a document library.
  • Direct, real time access to the SharePoint data (which means for example that it follows site, list and item level permissions).
  • Pure html (no Flash or Silverlight), accessible from both desktop and mobile devices.

This could for example be the ideal tool for a mid-size matrix organization, with multiple teams working on multiple programs. Managers could monitor the organization health, use the matrix view to analyze trends (what brings my organization up/down, a specific project or a specific team?), and finally access the items themselves.

Pie and Bar Charts (Google connector)


I already mentioned it briefly in an earlier post: the SharePoint User Toolkit now includes a tool that allows you to add simple pie or bar charts to your SharePoint pages.

The code is actually a connector that points to the powerful Google Visualization interface. For this reason, Internet access is required.

A common concern with online tools is data security. In this case, we are safe, Google’s privacy policy explicitly states that no data is sent to the server. The charts are directly rendered in your browser. This is a major difference with the Google Image Chart API, where data is sent in clear to the server, which uses it to build and return an image.

Under the hood, the code works the same way as the Image Rotator: it retrieves data from a specific view of your SharePoint list.

Interactive Charts using Google Visualization API (via SharePoint JavaScripts)

Proposed by Alexander Bautz, a client side charting solution for SharePoint that relies on the Google Visualization API.

To understand the difference between the Google Chart API and the Google Visualization API, check out this page:

This is a solution for creating charts directly from SharePoint lists using Google Visualization API. The solution is a CEWP-solution and requiring no other than a few script references. All code and data are processed and rendered in the browser. No data is sent to any server.[…] You can pull data from any existing list view (even from personal views – for personal use), or using a custom CAML-query … Read More

via SharePoint JavaScripts

Case study: course compliance report (Part III)

Author: Larry Pfaff, Sr Systems Analyst, Convergys Corp.

Adding The Final Pieces

Can you see the finish line yet? As you remember in Part 1 we reviewed the requirements and expectations of the data. This output needed to be easy to read and use by all end users throughout the entire organization and most of the views returned the desired results.

One advantage of using these views was no additional scripts were required to display data in table format with one column. Once we had a direction, in Part 2, we discussed some of the obstacles and limitations we ran into and how we overcame them. Although we are very close to completion there are still some details to review and modifications to make. For this all to work we will be adding the final parts (scripts) into a Content Editor Web Parts (CEWP).

Create a view – one column
Begin creating a Standard view with the listed options. This should be fairly simple. If you need details for this you may go here.

  • Added one column, which contained all the data for the table layout.
    • Table – (table of data)

  • Select your Sort and Filter options. I set sort to Ascending by Process Total. This allowed the teams who were less compliant to show closer to the top of the view
  • No Group By
  • No Totals none available
  • Style – tested in all views
  • Item limit – your choice, this one was set at 150


Add a CEWP above your list view. This will contain the classes and formatting of the table. Be specific in the class names otherwise the formatting would be transferred to other parts of the page.

There are several references available on the net to provide you with more details: is one of the most commonly used.

Once you have added your CEWP to your list view you can add the CSS below. Initially my styles were off, which cause some formatting conflicts. Thanks Christophe for the style update for the table borders mentioned in Part 1.

<style type="text/css">
table.myboxed, table.myboxed TD, table.myboxed TH {
				border: 1px solid black!important;
				border-collapse: collapse;
				font-size: 10pt;
.twidth {
				width: 100px;
.comp {
				color: green;
				font-weight: bold;
.nc {
				color: maroon;
				font-weight: bold;
.e {
				color: orange;
				font-weight: bold;
.label {
				font-weight: bold;

HTML Calculated Column

The key script to display the images, color and table layout can be downloaded here with instructions for installation.

Final product looks like this:

Final thoughts

As I sat down to complete this final part in this 3 part series “Case study: course compliance report” I found IE and FF definitely don’t play well together. I also found there are several things I would do differently. With all the pieces placed together the results are astounding, but this is only one of many ways that scripts, intuitiveness and desire can produce outstanding results.

One of the changes I thought about making was to the progress bar. Currently I am in the process of showing both Complete and Exempt on the same bar as one continuous bar like this.

54% 8%

In theory this works. I can replicate it in a regular html table with div tags, but when implemented into a CC I lose either the color or the percent for the % Exempt. Will keep you posted on the progress, but for now I will continue to hack away until I figure it out.

Since the beginning of this article I was inspired to start my own blog at Although this blog may never have any new intermediate scripts it will have scripts implemented in real world scenarios, which I feel will provide beneficial learning. For a long time I always considered myself just a hacker, never wrote any original scripts, so I thought. I would always find someone else’s code and tweak it to fit my needs until one day a blogger said to me:

“More than the script we come up with, what is important is the innovative way in which we approach a real world issue”.


Thanks again Christophe for your support.

Case study: course compliance report (Part II)


Guest author: Larry Pfaff, Convergys.

I left off explaining how I went from raw data to manipulating views to achieve a table layout with usable results.
Continuing forward I will describe how to achieve this output through the use of Calculated Columns (CC) in a custom list. It’s fairly easy to build a table in a CC with some simple HTML. I built my first test table with some test data and found it worked well. I began interchanging my test data with my list data until I hit a wall. The simple HTML table worked perfectly with test data, but when all my formulas replaced my test data it failed. Initially, I thought my formulas contained errors, but upon further research I found it to be the result of a character length limitation in the CC.

Size Limitation

There isn’t much discussion on the web about the character length limitation of a CC. I actually stumbled upon it without realizing. To troubleshoot this limitation I began to break down my formula into useable chunks. By adding the useable chunks to the CC I could confirm parts of the formula were correct. Once I confirmed all the parts were correct I added them back together, but they failed again! I was receiving the error “The formula contains a syntax error or is not supported.”
I confirmed the formulas were correct piece by piece, but once I put them all together it was no good.

This was very frustrating so I reached out to our SP Admin to inquire why this is happening. He stated something about the formula was too long and I couldn’t use it. Well, I am not one that takes no for an answer. So I began trying to figure out ways to reduce the size of my formula string. This is when I had two ideas for reduction.

  1. Remove the inline styles and replace with CSS classes
  2. Go back to breaking the formula into usable chunks, leave them as separate CC’s and use a final CC to pull all the parts together.

This result was very successful. In fact later research I found a single post “Calculated Column Limits (1024 Characters)”.

Now that we have a work around for the size limitation we can discuss the fields and calculations.

My list data was updating twice daily through the use of a macro in an Access database paired with the use of scheduled tasks. Below is the list of fields used in my reporting requirements. The gray highlighted rows in the table below is my raw data automatically updated. Each CC will be described and what calculations were used, when necessary.

Field Name Field Type Function
Sr. Executive Single line of text Raw data
Name – Executive Single line of text Raw data
Completed Number Raw data
Not Completed Number Raw data
Exempt Number Raw data
Grand Total Calculated Total of the 3 above
Report Calculated not required
% Not Comp Calculated  
% Complete Calculated  
% Exempt Calculated  
Progress Totla Calculated Display progress bar
Table Calculated Display table with data
Chart Calculated Chart Image

Calculated Columns

Grand Total
Data type returned as Number with Auto decimals places. Simple field returning the summed value of three columns for a specific row.

 =SUM([Completed],[Not Completed],[Exempt]) 

Report – Progress by Executive Data type returned as Single line of text

This is not a required field, but it was useful in providing a navigation method to the source data (numbers/details). Here I used an IF statement to build a hyperlink back to the original excel file stored in a local document library. The excel files were names according to the Sr Executive, making the hyperlink easier to build dynamically.

“Ayers” the first name in the formula had the most line items. The report was so large my Access macro would fail trying to upload data into SharePoint. So I split the report into 3 separate files, this formula takes that into account and links to one of three excel files for Ayers.

 ="<DIV><a href="http://cvgsharepoint/sites/learning/DataSecurityPrivacy/Data%20Privacy%20and%20Security%20Status%20-%2520"&IF([EXEC%20Select]="Ayers,Andrea%20J",IF(OR(Name="Frank%20J%20Corso",Name="Victoria%20Perez%20Mueco"),[EXEC%20Select]&"-New",IF(OR(Name="Steve%20M%20Heffron",Name="Sukant%20Srivastava"),[EXEC%20Select]&"-New",[EXEC%20Select]&"-New")),[EXEC%20Select])&".xls' title='Report Details for "&[EXEC Select]&"' target='_blank'>Report Status Details -
  "&[EXEC Select]&".xls</a></DIV>"

figure 2.1

The next three formulas are just calculating a percentage for that value

% Complete
Data type returned as Number with 2 decimals places.

 =ROUND(([Completed]/[Grand Total]),2) 

% Not Comp Data type returned as Number with 2 decimals places.

 =ROUND(([Not Completed]/[Grand Total]),2) 

% Exempt Data type returned as Number with 2 decimals places.

 =ROUND(([Exempt]/[Grand Total]),2) 

Progress Total
Another Idea came from a progress bars and adding color.
Currently this CC totals [% Complete] and [% Exempt] as total progress. Because some new employees and upper management were exempt from taking the course Exempt could not count against the total. .

This formula worked perfectly in IE, but I found it to fail in Firefox (FF). I tried multiple changes, but still no success. Christophe brought to my attention I was missing the non breaking space discussed in this post Progress bar + color coding. Now it made sense why it wasn’t working correctly in FF. I made the simple change and confirmed no visual difference between both IE and FF. This is the resulting formula:

 ="<DIV style='position:relative;'> <div style='background-color:#ccccff;'>
<div style='background-color:#0000ff; width:"&(([% Complete]+[% Exempt])*100)&"%;'>&nbsp;</div> <div style='position:absolute;top:0px;color:white;font-weight:bold;padding:0 0 0 2px;'>"&TEXT(([% Complete]+[% Exempt]),"0%")&"</div> </div></DIV>"

figure 2.2

Table This formula is a simple table with some added styles and nested CCs. Styles and nested CCs were used to help with the character length limitation by minimizing total string length. A few trial and errors later and a successful table output. This formula was tested in both IE and FF with success. This is the resulting formula:

 ="<div><b>Sr. Exec.: </b> "&[Report]&"<table class='myboxed'><tr><th colspan='2'><b>Exec.:</b> "&[Name]&"</th><th class='twidth'>Completed</th><th class='twidth'>Not Completed</th><th class='twidth'>Exempt</th><th class='twidth'>Total</th></tr> <tr style='text-align:right'><td rowspan='2'>"&[Chart]&"</td><td  class='label'>Number</td><td class='comp'>"&[Completed]&"</td><td class='nc'>"&[Not Completed]&"</td><td class='e'>"&[Exempt]&"</td><td>"&[Grand Total]&"</td></tr> <tr style='text-align:right'><td class='label'>Percentage</td><td class='comp'>"&[% Complete]*100&"%</td><td class='nc'>"&[% Not Comp]*100&"%</td><td class='e'>"&[% Exempt]*100&"%</td><td></td></tr> <tr><td colspan='6' style='text-align:left;'>"&[Progress Total]&"</td></tr></table></div>"

figure 2.3

Note: the styles used for the above classes will be provided later.

Data type returned as Single line of text. There are several types of chart providers. At the time I was developing this I only knew of google charts. This CC was created for 2 purposes. 1. to return the data as a chart and 2. to save on character length and to be shared in the Table field.

 ="<DIV><img src='"&[% Complete]&","&[% Exempt]&","&[% Not Comp]&"&chl=Comp|Ex|NC&chco=0000FF'/></DIV>"

figure 2.4

Although we are not quite finished we are well on our way. In Part I we discussed the process that lead me to the table layout and styles described. In Part 2 we discussed some obstacles and work rounds to get the required results. In our final installment, Part 3, I will describe the CSS and scripts that provided the final presentation.

Update [06/07/2010]: screenshots updated.

Case study: course compliance report (Part I)

A couple weeks ago, Larry Pfaff shared a screenshot that impressed me. First, it made full use of my “HTML Calculated Column” method, including in a same report color coding, a pie chart and a progress bar. But also it offered an elegant, condensed layout, different from the traditional “one row per item” tables.
I asked Larry if he could tell us more about his design. After introducing the business context and providing an overview in part I,  in part II and III we’ll dive into the formulas behind this customization.

 This past year I was given a set of requirements to develop a reporting mechanism that needed to track course compliance for all company employees. Although our Learning Management System (LMS) has some reporting functionality it requires a higher permission level to run some of the basic vanilla reports and the vanilla report data is for specific groups or teams, not the entire organization. With over 70k employees I needed to find a simple useable way to report out on the course compliance (Complete, Exempt and Not Complete).

Some of the key features I kept in mind were usability, simplicity and functionality. From inception I knew I wanted a condensed view that would display the data across multiple rows that included color coding and inline charts. From a usability standpoint a table output would be easier to interpret then the standard SharePoint views and would be very helpful in meeting the simplicity and usability requirements.

For the last six plus years I have been actively using SharePoint (SP). I utilize SP whenever I can with the understanding that SP is not the solution for every request. Considering the end user population and usage of this data, SP was the solution for this project. I know SP offers multiple views and the views could include numerous sorts and filters that can be applied, but even the Table view SP offered could not organize the data on multiple rows. Once I was able to get my LMS data into SP I could manipulate the output into a table format grouping the data across multiple rows.

First I needed to automate data migration into SP. Then I needed to organize my output to obtain a table result. For the past six years I struggled on my own learning SP trying to stay ahead of the curve. One of the toughest obstacles I’ve come across is the lack of shared knowledge from within my company. When I first came across the Path to SharePoint blog this opened doors to a whole new world, starting with how to convert text to HTML by adding script to a Content Editor Web Parts (CEWP). This provided a way to change the standard SP pages without damaging or unghosting them.

Once my data automation was implemented I began trying different outputs (views). In one of my initial views (Default) I was able to display a simple colored progress bar and inline charts through the use of the HTMLcc script. This put me on the way to achieving the visual effects I was looking for. 

Figure 1.1

 This view was a simplistic, but not the table output I needed. It returned the data with colors and charts, but the results are still on one row and not easy to read. I began investigating other view types.

Implementing the Boxed view condensed the data some, but it was confusing because the data had no delimitation.

Figure 1.2

The “Boxed view, no labels” returned a cleaner results, but still missing organization.

Figure 1.3

 So I began thinking how this data could be displayed in a way that would provide simple results across multiple rows of a table. I began with a simple table design for the data. With the HTMLcc script already in use I knew I could convert any HTML I put into a calculated field. My first attempt to create a table with some test data was successful.

As I began testing my table across the different view types (Basic Table view) my results were less satisfying even with the addition of CSS and “HTMLcc” scripts it resulted in missing formatting. 

Figure 1.4

In figure 1.4, every other table was missing borders. Looking through the page source I realized the TR class “.ms-alternating” was causing a conflict. Initially I did not pursue a fix. When I presented this to Christophe he agreed the issue was the TR class and modified the table styles for a successful solution.

 Even without all the borders the results were much more manageable and easier to read. I continued testing the table layout across multiple views. Initially, during development, I was only using Internet Explorer 7, but once loaded into Firefox the results were identical.

Figure 1.5

Wrap up
Today we were able to achieve extraordinary results that displayed data across multiple rows in a table format. The tables contained color and inline charts something the OOB SharePoint could not achieve. The output was tested across multiple view types and across multiple browsers with identical results.  In Part 2, I will discuss the steps to creating a table output incorporating color and Google charts through the use of scripts and calculated columns, with an identical look and feel across multiple browsers.

I am a long time reader of Christophe’s blog and have always been amazed at the work he has put out. This article could not be possible if not for the work and scripts of Christophe Humbert at Path to SharePoint.

About Larry
Larry Pfaff, Sr, Systems Analyst working for Convergys Corp, a global company, based out of Jacksonville Florida delivering training and outsourcing for major corporations. I have been working with SharePoint for almost 7 years and manage many internal projects using SharePoint as the collaboration tool. I enjoy new challenges and automating existing process. I seek out cookie cutter solution and mold them to fit for “Non-Developers” working with SharePoint by developing and providing training for end user and sharing my knowledge with as many as I can.”

Update [06/07/2010]: Screenshots updated.