SharePoint list view Sparkline with Excel REST API

In a previous post I showed how to create a Sparkline in a list view by using a ‘Hyperlink or Picture’ column. This previous example used the Google Chart API to render the Sparkline but this requires an active connection to the Internet. If you want to create something similar but an Internet connection is not available, why not use Excel Services and the REST API. Here’s what it can look like:

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

These are the same steps from my last post but here is where the technique now differs. Next we need to create an Excel spread sheet that we’ll use to host our Sparkline (it’s actually a chart not an Excel Sparkline!). Here’s what my simple sheet looks like:

It really doesn’t matter what the spread sheet looks like but it will need a few key elements. Firstly, we’ll need a few cells that will be used to hold our plotted values, in my example I’m using 10 values (Value0 to Value9). The cells that hols these values must be named ranges:

I’ve named my value cells _0 to _9 – it’s important to keep the names short (more on this later). Next, create a chart that you want to use as your Sparkline, I’ve used a simple line chart and removed the axis, title, border and background. It important you know the name of your chart and in my example I’ve given my chart the name ‘c1’:

 

Now we need to save our workbook to SharePoint but when we do, choose ‘Publish Options’

From the Publish Options dialog, we need to ensure that our chart object is select, plus we need to add each named range (our cell values) as a parameter:

Having set the publish options, save the spread sheet to a document library somewhere in SharePoint.

Finally, using a similar technique to my previous post, we add an event receiver to our custom list that is used to calculate the required URL to render our Sparkline. The URL the code builds references the Excel REST URL, our workbook URL, our chart object (‘c1’ in my example) and then specifies the parameter values that will be passed to the named ranges. Example URL:

http://bc01/_vti_bin/ExcelRest.aspx/Documents/Sparkline.xlsx/Model/Charts(‘c1’)?Ranges(‘_9’)=143&Ranges(‘_8’)=24&Ranges(‘_7’)=12&Ranges(‘_6’)=12&Ranges(‘_5’)=99&Ranges(‘_4’)=17&Ranges(‘_3’)=76&Ranges(‘_2’)=53&Ranges(‘_1’)=45&Ranges(‘_0’)=78

The code required in the event receiver to construct the URL is pretty simple:

// get the item
SPListItem i = properties.ListItem;
SPListItemVersionCollection history = i.Versions;

#region Build the 'Google' sparkline
#endregion

#region Build the 'Excel REST' sparkline
string src = "/_vti_bin/ExcelRest.aspx/Documents/Sparkline.xlsx/Model/Charts('c1')?";
string range = "Ranges('_{0}')={1}&";
int valCounter = 9;
StringBuilder sb = new StringBuilder();

// make sure we read only the first 10 values from the history
// this will actually be the last 10 values chronologically
for (int x = 0; x < (history.Count > 10 ? 10 : history.Count); x++)
{
    SPListItemVersion version = history[x];
    sb.AppendFormat(range, valCounter, version["Value"].ToString());
    valCounter--;
}
string paramValues = sb.ToString();
paramValues = paramValues.Substring(0, paramValues.Length - 1);
#endregion

// update the item
EventFiringEnabled = false;
i["ExcelSparkLine"] = String.Format("{0}{1}{2}{3}", 
    properties.WebUrl, src, paramValues, ", SparkLine from Excel REST API");
i.SystemUpdate(false); // false = do not create a new version of the item
EventFiringEnabled = true;

Note: This technique does have some limitations; SharePoint Enterprise is required, URL length passed into the ‘Hyperlink or Picture’ column cannot be too long – that’s why I’m using very short parameter and chart object names and limiting the number of values passed to 10, it has a performance impact on your SharePoint environment where as the Google Chart API technique offloads this to the cloud, etc, etc.

I hope this helps….

Create SharePoint list view sparklines from version history

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

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:

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:

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!