Sparklines and other charts in SharePoint lists

sparkline2

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:
http://8002.freesharepoint2007.com/BlogStats/Forms/AllItems.aspx
http://8002.freesharepoint2007.com/BlogStats/Forms/sparkline.aspx

The method

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:
http://chart.apis.google.com/chart?cht=lfi&chco=4d89f9&chs=50×30&chm=B,E6F2FA,0,0,0&chd=t:2239,2150,1822,1923,2002,2570,2318,2532,2992,4287,5345,4720&chds=0,7000

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)
– etc.

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:"&&#91;Weeks <43&#93;&","&&#91;Week 43&#93;&","&&#91;Week 44&#93;&","&&#91;Week 45&#93;&","&&#91;Week 46&#93;&","&&#91;Week 47&#93;&","&&#91;Week 48&#93;&"&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)
– You comply with the service’s Terms of Use

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.

Another example

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).

Have fun!

Advertisements

16 thoughts on “Sparklines and other charts in SharePoint lists

  1. Pingback: Sparklines and other charts in SharePoint lists « Path to SharePoint | Email Marketing Tool

  2. Agreed, very interesting. Looking breifly over the post and images it looks as if the graphs are pulling there data from your columns. Is there a way to possibly to a countif so I do not have to update my totals?

    I have a project where they have been asking for visuals according to status. I have not found a way to do a countif to come up with multiple totals.

    Anyone?

  3. Great help! Thank you! However, any way to get this to work by implementing in a KPI Indicator List (All Items View) and referring to the KPI Value? I cant seem to find the actual KPI value column … just tuns up as 0. Yes i’ve tried formatting as %, multiplying by 100 or 1000, etc… just cant find the column…. listed all of them and the value never shows.

    Example: KPI List View shows a KPI Value of 71.54% and a Goal of 90%
    Switch to all items view, add KPI of the KPI, KPI Value, KPI Formatted Indicator, etc… and 71.54% never is displayed…

    Wierd and perhaps yet another shortcoming of Sharepoint?

    Thanks for any thoughts!

    SCB

  4. Christophe, I have a form library consisting of several hundred infopath forms. Multiple fields are promoted for reporting. I have been trying to figure out a way to track total number of documents in each status, by week or at any given time. is there a way to use my library as a web service, count the number of a specific status and populate a second list with that data?

  5. Pingback: 7+1 live demos: widgets, REST, jQuery in SharePoint lists « Path to SharePoint

  6. Pingback: Live demo: jQuery sparklines « Path to SharePoint

  7. Hey Christophe,
    Well it as been some time. Hope you are well. I began playing around with this and the hardest thing I found was formating and modifying this, not being very familiar.Through my search I found this page, which I thought I would share with you and your readers. Was very helpful for me. real time GUI for customization.

    http://www.clabberhead.com/googlechartgenerator.html

    enjoy!

  8. Pingback: Finally: Dynamic charting in WSS, no code required! | End User SharePoint

  9. Pingback: Sparklines, more information in less space « Path to SharePoint

  10. Very cool, but you are injecting code from a remote source into your site which could be quite dangerous. If the chart server was compromised to return a buffer overflow condition, you just put every MOSS user at risk.

    Outside of the risk, extremely cool idead… REST/Cloud services are going to open some doors to do amazing stuff like this.

    • Well, in these examples, only pictures are injected, no code. So it seems to me that the only risk is to get a red cross if the returned information is not in the expected format.

      I have other sparkline examples on my blogs that rely on code (jQuery). In those examples, the code is stored locally.

  11. Is there another way to get the graphs instead of using google? Our company cannot access the internet so therefore I am unable to get the graphics needed. Any suggestions?

    • Jeff, you’ll need a tool that generates the charts locally. There are plenty of options, free or not, based on JavaScript/jQuery, Flash or Silverlight. You’ll find some examples on my blog, like the jQuery Sparklines or FusionCharts.

Comments are closed.