Look at the chart in the above picture. Isn’t that cute? Such little, text size graphics are called sparklines. They have become very popular, and you can see them all over the place, for example in Excel spreadsheets.
This post will show you how sparklines and other charts can easily be inserted in SharePoint lists.
If you checked out the 7+1 live demos, this post refers to examples 3 and 4.
Before we start, here is another example with more traditional bar charts:
And the links to the live demos:
As SharePoint doesn’t provide charting tools, we are going to subcontract the job to an external service, in this case the Google chart API. We’ll send the data over the Internet to this service, which in return will send back a picture.
For the sparkline example, the URL will be as follows:
As you can see, all the information needed to build the chart is contained in the URL:
– Data itself (chd)
– Chart size (chs)
– scale (chds)
Try changing some values (size for example) in the above URL to test the API.
To build the HTML string, we’ll use a calculated column with this formula:
="<DIV><IMG src='http://chart.apis.google.com/chart?cht=lfi&chco=4d89f9&chs=50x30&chm=B,E6F2FA,0,0,0&chd=t:"&[Weeks <43]&","&[Week 43]&","&[Week 44]&","&[Week 45]&","&[Week 46]&","&[Week 47]&","&[Week 48]&"&chds=0,7000' /></DIV>"
For my example, I have chosen different kinds of fields on purpose:
– [Weeks <43] is a text column where I entered multiple values
– [Week 43] to [Week 47] are numbers (hence the comma, which is just a rendering effect)
– [Week 48] and [Week 49] are text columns
The final touch is to apply the “HTML calculated column” method to change the HTML string into actual HTML that will display the chart in list views or calendar views.
A couple notes
The above examples use an external REST API, which implies that:
– You need internet access
– Your data goes public (it is included in the URL)
As we rely on calculated columns, the data used for a given chart must be contained in a single row. Do not expect to be able to build cross-row charts.
The method works for document libraries and lists. In my example, I used a document library because I wanted the hyperlink to link to the related Web page on my demo site, not to the DispForm page.
You’ll find on my demo site another example, live stock quotes, that use the Yahoo! API:
Here I used the following calculated column:
="<DIV style='position: relative;margin:-4px;text-decoration:none;' onMouseOver='document.getElementById("""&Title&""").style.display=""""' onMouseOut='document.getElementById("""&Title&""").style.display=""none""'><DIV><IMG style='width:60px;' src='http://ichart.finance.yahoo.com/t?s="&Symbol&"' /></DIV><DIV id='"&Title&"' style='display:none;position: absolute; top:-20px; left:-200px;background-color:navajowhite;text-decoration:none;'><IMG style='width:192px;' src='http://ichart.finance.yahoo.com/t?s="&Symbol&"' /></DIV></DIV>"
For further exploration
As we have seen in previous posts, combining HTML objects, widgets and APIs with SharePoint lists open up a universe of possibilities, and charting itself is a whole galaxy.
We have seen how to use REST APIs. If you don’t like the idea of your data going out in the wild, you can use Flash or Silverlight tools. See for example the Flash FusionCharts (and their free version), and Silverlight Visifire.
And if you don’t like the idea of proprietary formats, then you’ll turn to pure DHTML components. For example I found these jQuery sparklines that I haven’t tested yet.
You could also consider using applications that sit in your computer, like the Office Web Components. Note that the OWC are discontinued, although still supported (the last is version 11).