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:


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

#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());
string paramValues = sb.ToString();
paramValues = paramValues.Substring(0, paramValues.Length - 1);

// 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….

Excel Service REST API Ribbon Button

On of my favourite areas of SharePoint BI to demonstrate is the Excel Services REST API. It’s a great way to reuse content and logic embedded inside existing spread sheets but needs to be accessed via a ‘special’ URL. The Office team has got a great blog article on how to construct the required URL to access your Excel assets via the REST API.

However, constructing these URLs requires a little cutting and pasting so I’ve created a SharePoint solution that constructs the required URL for you. When a single XLSX based spread sheet is selected in a document library, a ‘Excel REST’ ribbon button is enabled:


Clicking on the ribbon button displays the URL required to access the select spread sheet via the REST API:


Here’s how the solution is structured:


Firstly there is a Elements file that is used to define a ScriptLink and a ribbon button. The ScriptLink is used to inject the JavaScript library that contains the logic for the ribbon button into the page:


The ribbon button is defined by a second CustomAction in the Elements file and most importantly defines the JavaScript functions to be called to determine if the ribbon button should be enabled (EnabledScript) and what happens when the button is clicked (CommandAction):


The above two functions are contained in the excelRest.js JavaScript library which is deployed to the SharePoint root via a mapped folder:


This JavaScript library contains the functions required to determine if the button should be enabled (based on selected item type) and then builds and displays the REST API URL for the selected item when the ribbon button is clicked:

var _site;
var _web;
var _file;
var _selectedItemType;
var _selectedItemId;

// this function is called when the ribbon button is pressed
function invokeExcelREST() {

    // construct the rest URL for our dialog
    var webUrl = _web.get_serverRelativeUrl();
    if (webUrl == '/') {
        webUrl = '';
    var restUrl = _site.get_url() + webUrl + '/_vti_bin/ExcelRest.aspx' + _file.get_serverRelativeUrl().replace(webUrl, '') + '/Model';
    // build the html content for our dialog
    var htmlContent = document.createElement('div');
    htmlContent.setAttribute('style', 'padding-top:10px; padding-left: 10px');
    var htmlIcon = document.createElement('img');
    htmlIcon.setAttribute('src', '/_layouts/images/icxlsx.png');
    var htmlSpan = document.createElement('span');
    htmlSpan.setAttribute('style', 'padding-left: 10px;');
    var htmlLink = document.createElement('a');
    htmlLink.setAttribute('href', restUrl);
    htmlLink.innerHTML = restUrl;
    // build the options for our dialog
    var options = {
        html: htmlContent,
        autoSize: true,
        title: 'Excel REST URL for ' + _file.get_name(),
        allowMaximize: false,
        showClose: true

    // call our dialog

// this function is used to determine if the ribbon button 
// should be active or not based on the selected document type
function enableExcelREST() {
    // get the collection of selected items
    var items = SP.ListOperation.Selection.getSelectedItems();
    // check that only one item is selected
    if (items.length == 1) {

        // get the first (only) selected item
        var item = items[0];

        // get the listid of selected item
        var listID = SP.ListOperation.Selection.getSelectedList();

        // check to determine if the current execution of this function
        // is due to a RefreshCommandUI call
        if (_selectedItemId == null && _selectedItemType == null) {
            // this is the first execution of this function

            // store the selected item id
            _selectedItemId = item['id'];
            // prepare a CSOM query to get the selected item
            _selectedItemType = null;
            var listGuid = SP.ListOperation.Selection.getSelectedList();
            getStatus(_selectedItemId, listGuid);
        else {
            // this path is called post a RefreshCommandUI that
            // is initiated from a successful CSOM query
            if (_selectedItemType == 'xlsx') {
                // we have an xlsx file type so enable the ribbon button
                _selectedItemId = null;
                _selectedItemType = null;
                return true;
            else {
                // we do not have an xlsx file type so disable the ribbon button
                _selectedItemId = null;
                _selectedItemType = null;
                return false;
    else {
        // more than one item was selected
        return false; // disable the ribbon button

// this function gets called when the CSOM query has completed
function onStatusQuerySucceeded(sender, args) {

    // remember the selected item file type and title
    _selectedItemType = IssueItem.get_item('ows_File_x0020_Type');

    // this causes the enabledScript function to be re-executed
    // but this time we've already set the _selectedItemId so
    // a different logic path will be followed in enableExcelREST

// this function is called is the CSOM query bombs
function onQueryFailed(sender, args) {
    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());

// this function builds and execute a CSOM query to fetch the selected item
function getStatus(ItemId, listGuid) {
    var clientContext = new SP.ClientContext();
    _site = clientContext.get_site();
     _web = clientContext.get_web();
    var lists = _web.get_lists();
    var list = lists.getById(listGuid);
    this.IssueItem = list.getItemById(ItemId);
    _file = IssueItem.get_file();

    // specify the columns we wish to return
    clientContext.load(IssueItem, 'Title', 'ows_File_x0020_Type');
    // execute the query
    clientContext.executeQueryAsync(onStatusQuerySucceeded, onQueryFailed);

If you want to download the completed solution, it can be found here:

I hope this helps…

Track SharePoint Content Database Growth via Central Admin

Following on from a recent post I made about a SharePoint health analyzer rule that can be used to automatically expand a SharePoint content database outside of normal working hours, I wanted to create a solution for monitoring content databases growth over time via central admin. Here’s what I came up with:


The solution consists of four parts, the first part is the Review Databases Sizes page shown above. The page is accessed from a custom action under Application Management > Databases:


The Review Databases Sizes page lists each content database present in the farm, plus a spark line that shows the database data file size and log file size over time. Clicking on the database name or either of the spark lines shows the second part of the solution, the Database Size Details application page. This page will be displayed to you inside an SP.UI.ModalDialog:


imageThe chart shown in the modal dialog (and the spark lines) are created via the jqPlot jQuery extension and allow for some nifty features such as data point highlighting, animated rendering and zooming. Note: You may need to check the jqPlot browser requirements to ensure this will work in your environment.

To zoom into an area on the chart simply click and then drag a rectangle that contains the data to be explored:

The chart will be re-rendered to display just the data points contained in the area you selected.

After you’ve zoomed in, you can examine individual values by hovering your mouse over a data point or you can zoom back out to the full chart by double clicking anywhere on the chart.


The third part of the solution is the deployment of the jqPlot JavaScript libraries themselves. The required libraries are deployed by a SharePoint feature and use ScriptLinks to add themselves to the master page of central admin without updating the master page itself. I’ve used this simple and powerful method to deploy jQuery libraries before and more details about can be found here: Use jQuery in SharePoint without updating your MasterPage

The fourth and final part of the solution is a custom timer job that is set to run once a day sometime between midnight and 1am. Its called ‘SPHealth Database Size Collection’:


The timer job finds each content database in the farm and demines the size of the database data file and log files for each. The sizes are then stored in the property bag for each content database.

That’s it – two application pages, a timer job, and a couple of module files. Smile

I’ve published the source code to the solution at if you want to have a poke around and try it out for yourself. Caveat: Before you deploy this in a production farm, just like any other third party solution, I would recommend you review and understand what the code is doing before you use it. Also note the following, it may take a couple of days before you see any charts as the timer job will need to have run twice to have collected enough data points to plot!


Autogrowth of SharePoint Content Databases – an Automated Solution

During a recent presentation at the London SharePoint User Group by Steve Smith of Combined Knowledge about SharePoint administration (and many other things), he discussed the issues surrounding the auto-growth of SharePoint content databases and the possible performance ramifications these can have when they are triggered during business hours.

As Steve pointed out, the default auto-growth settings for a newly created content databases are to grow in 1MB increments:

imageClearly for a content database that is used off the bat with this configuration, a lot (and I mean a lot) of auto-growths will be performed on the database as users load content and even access the site collections that the content database contains. The recommendations from Microsoft are to pre-grow data and log files and to set the auto-growth to 10% – see Storage and SQL Server capacity planning and configuration (SharePoint Server 2010) for further details:


These recommendation rightly point out that database growth should be proactively managed. So Steve’s presentation and this article got me thinking about a repairable SharePoint health analyzer rule that could warn when content databases are filling up and, if required, grow them automatically. What makes this a practical solution I believe is the ability to configure the rule so that database growths performed by the repair action of the health rule are only executed within a specified time window.

The health rule derives from SPRepairableHealthAnalysisRule so it can be configured to automatically repair (for repair read grow) a database once it has exceeded a configurable capacity threshold. The rule supports four custom configurable parameters:

  <!-- Enter the database capacity percentage that is used to trigger -->
  <!-- a warning and potentially a scheduled database expansion. Values -->
  <!-- should be between 0.0 and 1.0. -->
  <Property Key="CapacityThreshold" Value="0.8" />
  <!-- Enter the BeginHour for the time period in which database -->
  <!-- expansions should occur. -->
  <Property Key="BeginHour" Value="1" />
  <!-- Enter the EndHour for the time period in which database -->
  <!-- expansions should occur. -->
  <Property Key="EndHour" Value="3" />
  <!-- Enter the percentage of growth the database should undertake -->
  <!-- during an expansion. Values should be between 0.0 and 1.0. -->
  <Property Key="GrowBy" Value="0.3" />

The CapcityThreshold property is used to set the level at which warnings about database capacities are raised. Once a database exceeds 80%  (the default threshold for the rule) a health analyzer warning is raised and is visible in central admin.

The BeginHour and EndHour properties are used to define a time window in which, for database that have exceeded their capacity threshold, growths should be executed by the rule. These growths will not occur if the ‘Repair Automatically’ button is pressed outside of this window. Ideally you should review the properties and behaviour of this rule and if appropriate, set the rule to repair automatically. Please note, in order for the rule to repair automatically during the specified time window, the rule schedule should remain hourly:


Lastly, the GrowBy property is used by the repair method to determine the amount of expansion a database should undertake. The default option is 30% – this means that if a database is 100MB in size and 90% full, the database will be grown to 130MB. The total database size is used to calculate the new database size and not the amount of space currently used.

The rule is packaged as part of the SharePoint Health Analyzer Rules project on

The source code for the rule can be reviewed here:

BTW: There is a quicker way to solve this entire auto-growth problem – make the content database read-only! Winking smile

Metro Style SharePoint Search Results

As a proof of concept I created recently I wanted to see what can be quickly done to alter the out of the box search results layout. For the project I was working on, the context of where a keyword appears in a result was important so I wanted to increase the amount of preview text that the search core results web part returned by default. However, I didn’t want the user to be overwhelmed by too much text on screen so I decided to experiment with delivering the search results in a metro style live tile. My inspiration came from the Microsoft MSDN tiles I see virtually everyday when I visit their site:

Here’s what I came up with:


Each result consists of two panels, the result panel simply contains an expanded preview of the document content with the out of the box hit highlighting. Over the top of the result panel is a semi transparent title panel that floats up when the results panel is hovered over. The title panel contains the standard search result details. In my environment I’ve set this up so that a single result only takes up as much height as an out of the box result does but clearly more pimagereview text is visible.

To implement this yourself, you’ll need to do four things:

1. Edit the search results page and expand the ‘Characters In URL’ parameter from 180 to 600. This will ensure that more preview text is returned as part of the search results response and that the results panel gets filled (in most cases). However, please note that this means the amount of data being transferred across the system for each search will increase and this might not be an appropriate thing to do in every environment.

2. Next, disable the ‘Use Location Visualization’ option. This will then allow you to override the XSL used by the search core results web part.

3. Override the default XSL used to render the results with the XSL used in my sample above. This can be downloaded from!397&parid=941D17ECA8C6632D!396&authkey=!ADsqmTHJv3MFfmk. The XSL I use overrides some of the default styles used by SharePoint but also relies on jQuery being available within the SharePoint site (see point #4).

4. Enable jQuery in the site collection. You can do this without having to write any managed code or change any of the master pages etc. by using ScriptLinks. I’ve posted a walkthrough of how to do this over here:

With the web part properties set and the XSL overridden, save the results page and you’ll see your search results displayed in metro style – live tiles (assuming you’ve already enabled jQuery).

If you want to adjust the look and feel of the tiles or their behaviour – float height, speed etc. simply update the XSL and reapply this to the search core results web part.


Disappearing Silverlight controls and SharePoint modal dialogs

If you’ve spent anytime developing Silverlight controls for SharePoint that utilise SP.UI.ModalDialog dialogs you may have noticed a idiosyncrasy with the way Silverlight behaves when a modal dialog is thrown from the page that hosts the Silverlight control. The behaviour in IE is that the Silverlight is hidden whilst the modal is visible:

Before throwing a modal – Silverlight control is visible on a page:


During – modal is thrown and Silverlight is hidden:


Once the modal dialog has been closed, the control is made visible again. This presents two issues as far as I can tell, firstly the Silverlight control is actually reloaded when the modal is closed due to a bug. I’ve blogged about this issue including a workaround on this post. Secondly, user context is broken. Depending on the operation your controls is performing, it could be important that the Silverlight controls remain visible whilst a modal dialog is open.

One thing to note is that the behaviour described above is different in Firefox. Here, the Silverlight control remains visible but is not masked by the opaque div that is used to dim the background:

During – Firefox keeps the Silverlight control visible:


So why is the Silverlight control hidden by IE but not by Firefox? The answer is in the out-of-the-box SP.UI.Dialog.js file, or in this example, the SP.UI.Dialog.debug,js file:


The out-of-the-box JavaScript  checks the browser agent (line 1158) and, if you’re running IE, it loops through every object tag on the page and sets their style visibility attribute to hidden (line 1164). It certainly looks like the disappearing Silverlight controls in IE is the intended behaviour – more about is in a moment. In the meantime, if you need to work around this issue we can use a little additional JavaScript after we’ve opened our modal to show the Silverlight control in IE again.

The script extract below is actually taken from lines 1180 to 1190 of the SP.UI.Dialog.debug.js file and is what gets executed out-of-the-box when a modal dialog is closed. We’re simply running the same JavaScript immediately after opening the modal dialog. Walking through the code below we can see just before we open the modal, we set the browser focus away from our Silverlight control (this stops the control reloading when the modal is closed – see this post for details). Next, we show our modal as usual via a call to SP.UI.ModalDialog.showModalDialog. Finally we immediately loop through all the object tags on the page and set them to back visible:


Why do this? Well, we’ve worked around the out-of-the-box behaviour of SharePoint modals, Silverlight and IE that hides object tags and our user experience in IE now behaves just like Firefox:

So now in IE and Firefox we see the same behaviour – the Silverlight control remains visible but is not covered by the opaque div used to dim the modal background. 1 line of XAML and 2 lines of C# will fix that.

Next, open the source code of your Silverlight control and append to the end of your control stack (so that it appears over the top of all other controls), a canvas that is the same size as your entire usercontrol:


The important attributes of this canvas are highlighted. The opacity and background colour attributes match those of the out of the box opaque div and the initial visibility is set to collapsed (hidden). Next, in our Silverlight control just before we throw the modal dialog, we make our new canvas visible:


This approach assumes you’re initiating the modal dialogs from the Silverlight control. If not, it would be easy enough to call a ScriptableMember from the JavaScript that calls a Silverlight method that changes the canvas visibility to visible.

Lastly, once the modal is closed, I’m using another ScriptableMember  that is called from the JavaScript that collapses the canvas:


The end result is a visible Silverlight control that has a canvas applied over the top of it that matches the opacity and colour of the out-of-the-box modal background. This ‘fix’ works in both IE and Firefox:


Now the downside, and possibly why objects tags are hidden in IE by the out-of-the-box JavaScript shipped with SharePoint. With the Silverlight control visible, they can be clicked on and when you do so, they gain focus and are displayed above the modal dialog (this only happens in IE). In my environment, this is less of an issue than the whole control being hidden but you need to know about this side effect:


Luckily by adding a canvas to the top of the control stack of the Silverlight control I’m stopping any Silverlight buttons or controls from being clicked. I’m looking at a possible fix for this last issue and will blog further if it works.

I hope this helps someone…

Create custom SharePoint Health Analyzer rules

Have you got a SharePoint farm that has a unique set-up, special monitoring requirements, particular SLAs that it must meet or a farm that needs to provide your operations team with pro-active monitoring. If so, create your own SharePoint Health Analyzer rules, it’s super easy!

I’m sure we’ve all worked on deployments that fall into one or more of the categories above or have tons of other requirements than would benefit from monitoring. Perhaps the monitoring you need is nothing to do with the farm deployment and it’s operating environment but instead monitoring of a custom application you’ve built. Either way, creating your own SharePoint Health Analyzer rules could be a good idea.

Here’s how you create them…

Start a new Visual Studio 2010 Empty SharePoint Project and add to it a new class. The class must inherit from SPHealthAnalysisRule:


Next, you need to override the Category and ErrorLevel your rule will be reported under:


Next, override the Explanation, Remedy and Summary strings the rule returns. These are what the user see when the rule is displayed in the Review problems and solutions list from within Central Administration.


Next, override the SPHealthAnalysisRuleAutomaticExecutionParameters, these control how, where and when the rule is checked.


The interesting option here is the Scope. The Scope allows the rule to be executed on ‘Any’ or ‘All’ servers in the farm. Depending on what your rule is designed to do, running it on one server might be enough but you may need to run it on every server. For example, a health rule than check the size of a content database could be run on any server (SPHealthCheckScope.Any) as it doesn’t matter from which server you interrogate your database for its size. However, a rule that checks for available disk space will need to be executed on every server (SPHealthCheckScope.All).

Now the last part, the rule logic itself. To implement this, simple override the Check() method:


The check method must return a SPHealthCheckStatus:


As you can see, creating rules is simple. Installing the rules is a little more involved but still only a few lines of code. To deploy the rules, you’ll need to add a farm scoped feature to your project, add to the farm scoped feature an event receiver and override the FeatureActivated and FeatureDeactivating events. The FeatureActivated event will install the rules contained in the assembly produced by the project by calling the RegisterRules method of the SPHealthAnalyzer class:


Lastly, the code to remove the rules on feature activation is just as simple:


Now deploy your feature and watch it fail…

There’s on last trick to getting this working. It appears that there is a issue with deploying the solution and activating the feature all in one step (just like Visual Studio tries to do). The RegisterRules method call fails if you attempt this, I suspect this is due to timing of the DLL becoming available in the GAC but I haven’t got to the bottom of this one yet. To work around this issue, update the farm feature manifest.xml to include the ActivateOnDefault=”False” attribute:


Now you can deploy your solution, manually activate your farm feature and begin testing your custom rules.Smile

If you want a complete sample solution that includes the rule I’ve used as an example in this post and many more you can download the source code and WSP at