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:

image

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:

image

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:

image

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’:

 image

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

image

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:

image

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

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:

image

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

image

Here’s how the solution is structured:

image

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:

image

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

image

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

image

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');
    htmlContent.appendChild(htmlIcon);
    var htmlSpan = document.createElement('span');
    htmlSpan.setAttribute('style', 'padding-left: 10px;');
    var htmlLink = document.createElement('a');
    htmlLink.setAttribute('href', restUrl);
    htmlLink.innerHTML = restUrl;
    htmlSpan.appendChild(htmlLink);
    htmlContent.appendChild(htmlSpan);
            
    // 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
    SP.UI.ModalDialog.showModalDialog(options);
}


// 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
    RefreshCommandUI();
}

// 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();
    clientContext.load(_site);
     _web = clientContext.get_web();
    clientContext.load(_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');
    clientContext.load(_file);
    // execute the query
    clientContext.executeQueryAsync(onStatusQuerySucceeded, onQueryFailed);
}

If you want to download the completed solution, it can be found here: http://sdrv.ms/Juvbc1

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:

image

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:

image

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:

image

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.

image

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’:

image

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 http://sphealthdbsize.codeplex.com 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!

Enjoy…

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: http://msdn.microsoft.com/en-us/gg190735.aspx.

Here’s what I came up with:

image

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 https://skydrive.live.com/redir.aspx?cid=941d17eca8c6632d&resid=941D17ECA8C6632D!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: https://sharepintblog.com/2011/11/30/use-jquery-in-sharepoint-without-updating-your-masterpage/

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.

Enjoy.

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:

image

During – modal is thrown and Silverlight is hidden:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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

image

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.

image

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

image

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:

image

The check method must return a SPHealthCheckStatus:

image

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:

image

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

image

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:

image

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 http://sphealth.codeplex.com

Enjoy.

Silverlight, JavaScript and SharePoint Modal Dialogs

I spent some time recently using Silverlight and SharePoint modal dialogs in anger for a client engagement. For this particular project we’re dealing with lots of hierarchical data structures and the function requirement is to provide a drag and drop user interface to support building and reorganising these hierarchies. When combined with the platform choice of SharePoint, Silverlight seemed to be the obvious technology with which to build these controls. We’re using the drag and drop examples form the Silverlight Toolkit for the basis of our controls and combining them with the SharePoint modal dialog framework via SP.UI.ModalDialog.showModalDialog.

To support the solution, we need to create lots of modal dialogs in SharePoint and test the Silverlight controls functionality within these modals. To help support this testing, I built a SharePoint web part that is used to launch modal dialogs:

image

The web part itself, renders a Silverlight control that is used to capture the options needed to launch the modal. When the ‘Go’ button is clicked, the Silverlight control calls the required JavaScript function that launches the modal. The modal is displayed and once it is closed, the results of the modal are returned to the Callback function. In turn, the Callback function calls a ScriptableMember method in the Silverlight control and the results of the modal are then displayed in the Callback Result Value textbox of the Silverlight control.

The flow of data this web part provides is as follows:

Web part  >  Silverlight  >  JavaScript  >  SP.UI.ModalDialog  >  JavaScript  >  Silverlight

There is however, one big gotcha with this data flow that tripped me up for quite a while. When control is passed back to the Callback function after the modal has been closed, the Silverlight control appears to be reloaded. When this happens, any data or settings your entered into the web part are lost and the control reloads with its initial configuration. Clearly this is far from ideal…

I noticed however, that this behaviour is not replicated in Firefox. With Firefox, the results are passed back to the JavaScript Callback function, the function calls the ScriptableMember method without reloading the Silverlight control and all of our settings are preserved plus the results from the modal are shown to the user as per our intended behaviour.

This observation led me to this post that describes an issue with Silverlight and IE that causes the Silverlight plugin to be unloaded and reloaded when a Silverlight control is hidden that currently has focus. The solution suggested by this post is to set focus to another object in the DOM prior to hiding the object. Hiding all the object tags on the calling page is precisely what the SharePoint modal dialog framework does – thus it causes the Silverlight control to be unloaded and reloaded.

The following additional line of JavaScript added just prior to launching the modal dialog solves our problem when this technique is used with IE:

image

The source code for the web part and Silverlight control are available on codeplex: http://throwamodal.codeplex.com/

I hope this helps…