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…

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.

SharePoint Lorem Ipsum Web Part to display random content

One of the things I like to do when I create SharePoint page layouts is to test them with different content to ensure the layout is robust enough to handle both short content blocks and very long content blocks. Typically layout issues can arise within or around floating DIVs, with page scrolling or overflowed content so its important these (and many other) facets are tested thoroughly.

One of the tools I use to test these page layouts is my Lorem Ipsum web part. The web part uses JavaScript to build a content block of random length. The web part uses no managed code (it is packaged as a dwp web part file) so can be used in sandboxed environments and is simply uploaded via the Add Web Part UI:

image

Once the web part has been uploaded via the ‘Upload a Web Part’ option, it will appear in the Imported Web Parts category. From here you can add it to the page like any other web part:

image

Now with the web part on page, every time the page is refreshed, the amount of content displayed by the web part will change:

image

The maximum number of words that the web part can choose to display can be set by updating the numOfWords value in the script block that is contained inside the dwp web part file.

image

Here’s the complete contents of the dwp web part file, alternatively, you can download the file from the link below:

<?xml version="1.0" encoding="utf-8"?>
<WebPart xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/WebPart/v2">
  <Title>Lorem Ipsum Content</Title>
  <FrameType>Default</FrameType>
  <Description>Use to display random content to aid testing page layouts</Description>
  <IsIncluded>true</IsIncluded>
  <ZoneID>wpz</ZoneID>
  <PartOrder>0</PartOrder>
  <FrameState>Normal</FrameState>
  <Height />
  <Width />
  <AllowRemove>true</AllowRemove>
  <AllowZoneChange>true</AllowZoneChange>
  <AllowMinimize>true</AllowMinimize>
  <AllowConnect>true</AllowConnect>
  <AllowEdit>true</AllowEdit>
  <AllowHide>true</AllowHide>
  <IsVisible>true</IsVisible>
  <DetailLink />
  <HelpLink />
  <HelpMode>Modeless</HelpMode>
  <Dir>Default</Dir>
  <PartImageSmall />
  <MissingAssembly>Cannot import this Web Part.</MissingAssembly>
  <PartImageLarge>/_layouts/images/mscontl.gif</PartImageLarge>
  <IsIncludedFilter />
  <Assembly>Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Assembly>
  <TypeName>Microsoft.SharePoint.WebPartPages.ContentEditorWebPart</TypeName>
  <ContentLink xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor" />
  <Content xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor"><![CDATA[<script>

  // setup our array of available text
  var source = new Array();
  source[0] = "Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat. Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi.";
  source[1] = "Epsum factorial non deposit quid pro quo hic escorol. Olypian quarrels et gorilla congolium sic ad nauseum. Souvlaki ignitus carborundum e pluribus unum. Defacto lingo est igpay atinlay. Marquee selectus non provisio incongruous feline nolo contendre. Gratuitous octopus niacin, sodium glutimate. Quote meon an estimate et non interruptus stadium. Sic tempus fugit esperanto hiccup estrogen. Glorious baklava ex librus hup hey ad infinitum. Non sequitur condominium facile et geranium incognito. Epsum factorial non deposit quid pro quo hic escorol. Marquee selectus non provisio incongruous feline nolo contendre Olypian quarrels et gorilla congolium sic ad nauseum. Souvlaki ignitus carborundum e pluribus unum.";
  source[2] = "Li Europan lingues es membres del sam familie. Lor separat existentie es un myth. Por scientie, musica, sport etc., li tot Europa usa li sam vocabularium. Li lingues differe solmen in li grammatica, li pronunciation e li plu commun vocabules. Omnicos directe al desirabilit? de un nov lingua franca: on refusa continuar payar custosi traductores. It solmen va esser necessi far uniform grammatica, pronunciation e plu sommun paroles.";
  source[3] = "Ma quande lingues coalesce, li grammatica del resultant lingue es plu simplic e regulari quam ti del coalescent lingues. Li nov lingua franca va esser plu simplic e regulari quam li existent Europan lingues. It va esser tam simplic quam Occidental: in fact, it va esser Occidental. A un Angleso it va semblar un simplificat Angles, quam un skeptic Cambridge amico dit me que Occidental es."

  // pick the starting point for our random text
  var text_no = Math.floor((4)*Math.random());

  // setup our array of available text
  var lorem = new Array();
  switch(text_no)
      {
              case 0: {
            lorem[0] = source[0] + source[1] + source[2] + source[3]
            break;
            }
          case 1: {
            lorem[0] = source[1] + source[2] + source[3] + source[0]
               break;
            }
          case 2: {
            lorem[0] = source[2] + source[3] + source[0] + source[1]
               break;
            }
          case 3: {
            lorem[0] = source[3] + source[0] + source[1] + source[2]
               break;
            }
    }

  // pick the number of words
  var numOfWords = Math.floor((500)*Math.random()) + 20;

  var list = new Array();
  var wordList = new Array();
  wordList = lorem[0].split( ‘ ‘ );
  var iParagraphCount = 0;
  var iWordCount = 0;

  while( list.length < numOfWords )
    {
         if( iWordCount > wordList.length )
            {
                iWordCount = 0;
                iParagraphCount++;
                if( iParagraphCount + 1 > lorem.length ) iParagraphCount = 0;
                wordList = lorem[ iParagraphCount ].split( ‘ ‘ );
                wordList[0] = "<br/><br/>" + wordList[ 0 ];
            }
        list.push( wordList[ iWordCount ] );
        iWordCount++;
    }
 
  var out_str = list.join(‘ ‘)
  out_str = out_str + "…"

  document.write(out_str);

</script>]]></Content>
  <PartStorage xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor" />
</WebPart>

download The dwp web part file used in this post can be downloaded from here: https://skydrive.live.com/redir.aspx?cid=941d17eca8c6632d&resid=941D17ECA8C6632D!378&parid=941D17ECA8C6632D!376

The JavaScript used in the web part contains portions of a JavaScriptBank script that can be found here: http://www.javascriptbank.com/javascript/utility/generator/random-text-generator/print/en/

Enjoy…

Insert Excel REST chart into Word documents–an alternative approach

The most frequently documented way to embed an Excel chart object into a Word document via the Excel REST API is to insert into the Word document a new Quick Part via the Insert > Quick Parts > Field… > IncludePicture command and ensure the ‘Data not stored with document’ option is ticked. This option ensures that the chart is dynamically refreshed each time the Word document is opened:

image

However, there is I believe a better way to achieve the same (and arguably a more usable) result in a much more user friendly and demo friendly manner. Rather than use the Insert > Quick Parts > Field… > IncludePicture option, insert the picture from the standard Insert > Picture option:

image 

Here’s the important step – don’t simply click on the Insert button. Instead, from the Insert button drop down, select Insert and Link:

image

When you do this, your Excel chart is dynamically fetched and inserted into the Word document as you would expect.

But here’s the bonus…

If you use the first method, you need to close and reopen the Word document for the chart to refresh or wait several minutes and then press Ctrl+A, F9 to refresh the embedded chart – in my experience you have to wait 5 to 10 minutes, and sometimes even longer, for the chart to refresh. Consequently it’s usually quicker to close and reopen the Word document.

However, if you use the second method to embed a chart into your Word document, Ctrl+A, F9 updates the chart immediately. Not only does this method make the insertion of charts look more elegant but you can demonstrate dynamic data refreshes without have to shutdown and reopen Word.

Happy demoing….

Bill Gates lives in all our SharePoint installations

Here’s something I learnt from an Office 365 presentation by Mark Kashman I watched today. Bill Gates is alive and well and living in all our SharePoint installations!

You know the placeholder image for a user profile, this one:

image

Well it transpires that it is based on Bill Gates mugshot:

Here’s the ‘proof’…

image

Enjoy…

Graphical Search Refiners for SharePoint

Here’s my proof of concept for graphical search refiners for SharePoint 2010. It is based on the Google chart tools API, specifically the treemap library: http://code.google.com/apis/chart/interactive/docs/gallery/treemap.html.

I simply override the XSL of the refiners web part so that the item counts for each refiners are used to display an area in the treemap proportional to the number of items found. There is no need to override the filter category definition xml to include counts (via the ShowCounts=”Count” attribute) as the XSL accesses the count values directly:

image

This is just a proof of concept so there are some (many) rough edges but if you want to grab the xsl I used it’s available here: RefinersXSL. Feel free to extend, enhance or use this as you wish.

Finally, you need to include a reference to the Google JavaScript API on your page somewhere:

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

…perhaps via a content editor web part or via a delegate control.

Enjoy.