Create SharePoint list view sparklines from version history

Here’s my simple process to create dynamic spaklines in a SharePoint list view.

image

Unlike some of the other methods out there, this approach uses a single value column for the source of the spark line and the list item version history to plot the changes to the value over time.

Here’s how you do it.

Step1 – Create a custom list, in my example I’ve called this ‘SparkLines’

Step 2 – Add a column to contain the numeric value you wish to plot. In my example I’ve called this ‘Value’

Step 3 – Add a column to host the sparkline image. This needs to be created as a ‘Hyperlink or Picture’ column and ensure the display option for the column is set to ‘Format URL as: Picture’.

Step 4 – This step is vital – enable versioning for the list

Now that the list is set up is should look something like this:

image

Now we need to add an Event Receiver to the list to general the sparkline. The code below uses the Google Chart API to process and render the sparkline as an image that is then displayed in the browser. This technique has a few advantages and a few disadvantages. Firstly, processing for rendering the sparkline is performed by Google and not SharePoint which is good news for performance, however, it does rely on the client browser have access to the internet. Secondly, to use this technique you should note the Google Chart API terms of service: http://code.google.com/apis/chart/image/terms.html

Event Receiver Code:

/// <summary>
/// An item was updated.
/// </summary>
public override void ItemUpdated(SPItemEventProperties properties)
{
    base.ItemUpdated(properties);
    if (properties.ListTitle == "SparkLines")
    {
        string url = "https://chart.googleapis.com/chart?chs=100×25&cht=ls&chco=0077CC&chd=t:{0} SparkLine from Google Charts API";
        string values = "";

        // get the item
        SPListItem i = properties.ListItem;

        // get the ‘Value’ history
        SPListItemVersionCollection history = i.Versions;
        foreach (SPListItemVersion version in history)
        {
            if (version["Value"] != null)
            values = version["Value"].ToString() + "," + values;
        }

        // update the item
        EventFiringEnabled = false;
        i["SparkLine"] = String.Format(url, values);
        i.SystemUpdate(false); // false = do not create a new version of the item
        EventFiringEnabled = true;
    }
}

Once the event receiver is built, deployed and attached to the SparkLines list you are all set to plot sparklines via the Google Chart API.

To test the plotting I’ve used the following Powershell script that updates the first few list items with random numbers:

## Load the SharePoint Snapin so the script can be executed from PowerShell editor
Add-PSSnapin Microsoft.SharePoint.PowerShell –erroraction SilentlyContinue

## Create a random number
$random = New-Object system.random

## get the default web app
$web = Get-SPWeb http://bc01

## get the list
$l = $web.Lists | where { $_.Title -eq "SparkLines" }

$i = $l.items[0]; $i["Value"]
$i["SparkLine"].ToString()

## update the item
$i = $l.items[0]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[1]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[2]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[3]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[4]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[5]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[6]; $i["Value"] = $random.next(0,100);$i.Update();

Run the script or manually update your list items a few times to build up some version history. The resultant list view should look something like this:

image

The Google Chart API is very flexible and powerful and this technique can be used to render all sorts of different effect. Examples of the types of other charting you can create can be found at http://code.google.com/apis/chart/index.html

Enjoy!

Advertisements

2 Responses to Create SharePoint list view sparklines from version history

  1. Pingback: SharePoint list view Sparkline with Excel REST API « SharePint Blog… Hic!

  2. Generally, I like this. You mention performance implications. Have you had a chance to see the effect in the real world? I’m wondering how much of an effect that is.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: