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

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

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 preview 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: http://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:

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:

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

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.

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>

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:

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:

 

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

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:

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

Here’s the ‘proof’…

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:

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.

Use jQuery in SharePoint without updating your MasterPage

Frequently I need to use jQuery in SharePoint but don’t always want to have to edit and publish a revised MasterPage to include the necessary script links that will allow me to access the jQuery libraries. This is where the ScriptLink class comes to our rescue. The ScriptLink class allows us to ‘inject’ resources into our pages without having to update the source HTML. What’s even better is we can deploy ScriptLinks via custom actions so we can use the SharePoint feature framework to affect our changes.

Here’s how I go about creating a feature that when deployed and activated will add the jQuery libraries to my site without changing any pages or MasterPages:

First, I create an new Visual Studio 2010 ‘Empty SharePoint Project’ and add to the project a new ‘Layouts’ mapped folder, to this directory I add all the jQuery (and other) resources I want to use in my site:

Note: I’m creating my own jQuery folder underneath the existing _layouts/inc’ folder. This is because the ‘inc’ folder is one of the few directories underneath the _layouts folder that is automatically configured for caching and anonymous access. You don’t need to create your folder underneath the existing ‘inc’ directory but you should at least be creating your own directory below the _layouts folder.

Next, I create an elements file that includes all the resources I wish to include in my pages. Each resource is added as a CustomAction with the Location attribute set to ‘ScriptLink’ and the ScriptSrc attribute set to the relative path of the resource:

Finally I include the elements file in a feature:

Now once this is deployed to my site and activated, the following script links are automatically included in the rendered HTML of the site:

 

Now I can use jQuery on my site and I’ve had to alter or switch MasterPage – yay! Obviously deactivating the feature then removes these script links from the rendered HTML.

P.S. One other thing you can do to streamline the solution that contains this feature is to switch off the inclusion of the default assembly that Visual Studio will include with the project. As this solution contains no managed code whatsoever the assembly that gets created for us by default by Visual Studio is totally redundant. To suppress the inclusion of the default assembly in the solution (and therefore the ability to delete the class file from the solution as well), update the solution property ‘Include Assembly In Package’ to False:

 

Enjoy!

Visual Best Bets with SharePoint Server using jQuery (not FAST)

Purely for demonstration purposes only I needed to quickly mock-up some visual best bets in SharePoint Server without using FAST. Thanks to the power of jQuery it took just 4 lines of script!

Setup

I’m assuming you have jQuery already deployed somewhere accessible to your site collection, if not, download it and upload jquery-X.X.X.min.js somewhere you can reference it, like the master page library for example.

First, create the HTML content you want to use as your new Visual Best Bet, here’s mine:

It’s not my best work but this is only a mock-up….

Upload this html content to SharePoint somewhere.

Note: make sure you save and upload it as an ASPX file and not an HTML or HTM file otherwise you wont be able to access it directly due to the default Browser File Handling setting on the web application.

Next, create another HTML file that includes the following script:

<script type=’text/javascript’ src=’/_layouts/inc/jQuery/jquery-1.4.2.min.js’></script>
<script type=’text/javascript’>
    $(document).ready(function () {
        var u = $(‘.srch-BB-URL2’).html();
        $.get(u, function (data) { $(‘.srch-BestBets’).html(data); });
    });
</script>

Note: The first line should be updated to point to your copy of the jQuery js file.

Upload this file to SharePoint somewhere.

Create standard Best Bet

Next we’re going to create a standard Best Bet. From our site collection (that hosts our Search Center) select Site Actions –> Site Settings –> Site Collection Administration – > Search Keywords. From the Manage Keywords screen create a new keyword:

Assign a new Best Bet to the keyword:

The URL you enter for the Best Bet should be the URL of  your HTML content you uploaded earlier as an ASPX file. Next, access your Search Center and test the Keyword and Best Bet is working:

Now edit your search results page and below the existing Search Best Bets web part, add a new content editor web part. Finally set the Content Link property of the content editor web part to be the URL of the script file you uploaded earlier and set the web part chrome type to ‘None’.

Save the page and execute your search again.

Now when the page loads, the jQuery script finds the URL from the standard Best Bet we defined, loads the HTML content from the URL via asynchronous Ajax and finally replaces the contents of the out of the box Best Bets web part with our HTML:

Please note, this is only a mock-up and not production quality code but it does illustrate the power of jQuery and Ajax quite nicely

Enjoy!

SharePoint claims based authentication with Thinktecture identity server – Walkthrough

This article describes how to setup claims based authentication for SharePoint using the Thinktecture Identity Server. If you don’t know about the Thinktecture identity server, it’s a great open source identity provider (IP-STS) available via codeplex: http://identityserver.codeplex.com

For brevity I’ll be referring to the Thinktecture identity server as the IP-STS and SharePoint as the RP-STS in the remainder of this article. Additionally, this setup is for demonstration purposes only, in a production environment you would probably not want to use self-signed certificates as I do here and you’re web applications should be secured with SSL.

To setup claims based authentication, the following steps need to be completed:

• Create Certificates
• Configure Identity Store
• Create Trust between IP-STS and RP-STS
• Test Authentication

So lets begin with creating certificates.

 

Create Certificates

Before the RP-STS can trust the IP-STS, the IP-STS must be able to prove it’s authority – this is done via certificates. We’ll need to create a certificate for the IP-STS to use to sign the tokens it sends the the RP-STS. These tokens contain the claims about the identities the IP-STS authenticates and the RP-STS will only accept these claims when they are signed with a trusted certificate. Therefore, there are three steps involved in the certificate management:

• Create the certificate
• Register the certificate with the IP-STS
• Register the certificate with the RP-STS

In this demo, I’m using self-signed certificates which you probably wouldn’t want to do in a production environment. To create a self signed certificate, open a Visual Studio command prompt and enter the following:

makecert -r -pe -n CN=idp.bc01.com -ss my -sr localmachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 idp.bc01.com.cer

The above will create a certificate for the “idp.bc01.com” domain and save the certificate to a idp.bc01.com.cer file. This will also install the certificate on local machine’s Personal Certificates store, ready to be assigned to a site in IIS configuration.

 TIP: if you want to make a client browser trust it, run mmc and add the certificates snap-in and choose Local Computer as the account. Then import it into Trusted Root Certificates.

Next, we are going to add the certificate to the RP-STS. To do this, open Central Admin –> Security –> Manage Trust and select ‘New’ from the ribbon. Import the certificate you just created and assign it a name, press OK:

Once the certificate is imported, you can review it’s properties:

At this point, you’ve configured the RP-STS to trust this certificate but we need to get the IP-STS to use this certificate when it signs tokens. I’m assuming you’ve already downloaded the Thinktecture identity server project and built the solution, if not you’ll need to complete the following steps:

• Build the Thinktecture identity server project
• Create a new IIS web site and point it’s root directory at the ‘website’ project directory of the Thinktecture solution.
• Set the App Pool for the new web site to use .NET 4 Framework
• Assign the certificate you created above to the IIS web site – typically in a production environment you would use a separate certificate for the IP-STS site and the actual signing of tokens

 

Configure Identity Store

Now we have the basic IP-STS site configured, we need to create an identity store for the IP-STS to use. the Thinktecture identity server uses a pluggable model to connect to a variety of identity stores or you can create your own. In this example I’ll be using the ASP.NET SQL Server Registration Tool to create a database to store and manage my identities. To create your identity store you use aspnet_regsql.exe, full details of this command can be found here: http://msdn.microsoft.com/en-us/library/ms229862(VS.80).aspx

Note: although this is the same identity store that can be used with SharePoint forms based authentication, this store will be connected to the IP-STS and SharePoint will not directly connect to it.

Once you have your identity store created, I typically create another IIS web site to manage the identities via the IIS Manager UI. To do this, create another IIS web site (that is neither the IP-STS web site nor a SharePoint site) and create a connection string to point to your newly created identity store database:

Now create the following .NET Roles:

The ‘Milkman’ role is optional but you could use this as a claim presented to the RP-STS later. Finally, create some users and assign them to these roles – ensuring you assign at least one user to the IdentityServerAdministrators role:

Lastly, open the Thinktecture solution in Visual Studio and locate the connectionString.config file in the WebSite project:

Update the connection string in this file to point to your identity store database:

In my example this database is called SharePoint_FBA but again, this is nothing to do with FBA in SharePoint and SharePoint never directly connects to this database – I’m just reusing an identity store I already had created for another project.

Now run rebuild the Thinktecture solution and access the IP-STS web site – in my example http://idp.bc01.com and you should see:

Sign into the site with the user credentials you added to the IdentityServerAdministrators role. Once signed in click on the [administration] link and you should see the following menu:

Now we need to configure the IP-STS. First, starting at My Claims, you can view the current claims available for the logged on user:

I’ve highlighted in red the claim we’re going to be using as our identity when we sign into the RP-STS. In green I’ve highlighted the additional claim the IP-STS has added for the current user as this user was also a member of the Milkman role in the identity store.

Next, click view My Token to see the token the IP-STS has issued to the current user in XML format:

This can be useful screen for reviewing and debugging claims based authentication.

Next, under Global configuration, we need to adjust a few setting:

Set the Default Token Type to:

and switch on the following options:

Next, under Certificate Configuration, ensure that the Current SSL and Current Signing certificates are set to the certificate you created earlier:

Lastly, under Relying Parties we need to create an entry for our RP-STS (SharePoint). Set the Relying Party Name, Realm URI and ReplyTo URL (assumes you’ll create a web application with the URL http://claims.bc01):

NOTE: Strictly speaking the Realm URI does not need to be the same as the ReplyTo URL but for to simplify configuration these are set to the same address.

Your IP-STS is all done.

IMPORTANT: Be sure to sign out of the IP-STS site before you continue.

 

Create Trust between IP-STS and RP-STS

Next we are going to configure SharePoint to act as a RP-STS and use the IP-STS we’ve just configured. We do this via PowerShell.

First we create a certificate object from the certificate file we created earlier:

$root = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2("C:\idp.bc01.com.cer")

Next we create a new Trusted Root Authority that uses the certificate:

New-SPTrustedRootAuthority -Name "idp Signing Cert" -Certificate $root

Next we create a claim mapping. In this example we are creating a single claim mapping based on emailaddress:

$map = New-SPClaimTypeMapping -IncomingClaimType "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress" -IncomingClaimTypeDisplayName "EmailAddress" -SameAsIncoming

Next we create a realm, this needs to be exactly the same as the realm URI specified in the Thinktecture Relying Party configuration:

$realm = http://claims.bc01/_trust

Lastly we create Trusted Identity Token Issuer that uses the certificate, claims mappings and realm we just created. Additionally, it specifies which claim is the identifier claim, in this example it’s the emailaddress. The following PowerShell creates a new Trusted Identity Token Issuer called “idp SAML Provider”:

$ap = New-SPTrustedIdentityTokenIssuer -Name "idp SAML Provider" -Description "Thinktecture IDP (SAML)" -Realm $realm -ImportTrustCertificate $root -ClaimsMappings $map -SignInUrl "https://idp.bc01.com/account/signin" -IdentifierClaim http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress

NOTE: the SignInUrl for the command is what redirects the user to the IP-STS to authenticate.

With all that PowerShell configuration complete, we can go ahead and create a new Web application. Be sure to use claims based authentication for the authentication mode and select the new Trusted Identity Provider as the claims authentication type:

…also be sure to leave NTLM authentication for the web app enabled.

With this in place we are ready to test authentication.

 

Test Authentication

To test authentication we need to ensure the user we access the site with has at least view permissions to the web app (I’m assuming you also created a root site collection in the web app you created in the previous step) and is a member of the IdentityServerUsers role within the identity store.

Access the new web application and when prompted to pick the type of credentials used to logon to the SharePoint site, choose Windows Authentication:

NOTE: You now get prompted to choose the authentication provider when you access the site because the web app support both claims based authentication via our new Trusted Identity Provider and NTLM authentication.

Enter valid windows credentials for a user who has Site Collection admin privileges. Once logged on, select Site Actions – > Site Permissions –> Members Group (the actual name of the group will depend on the name of your site collection). Now from the ribbon, select New and enter the email address of a user you created in the identity store earlier, again this user must be a member of the IdentityServerUsers role in the identity store:

NOTE: type the users email address very carefully as by default anything you type will be validated as a valid claim (see Claims Provider – http://msdn.microsoft.com/en-us/library/ee535894.aspx for more details on this default behaviour).

Add the user to the SharePoint group and log off the SharePoint site.

Now we are going to log on again but this time select the trusted identity provider as the authentication type. When we do so we will be redirected to the IP-STS to authenticate:

Enter the credentials of a user you entered into your identity store earlier who is a member of the IdentityServerUsers role and press Sign In. the IP-STS will authenticate your credentials and build you a SAML token. The SAML token will be returned to your browser as a cookie and your browser will be instructed to redirect to the SharePoint site (to the /_trust virtual directory):

This should culminate in you being signed into SharePoint with your claims based identity:

You can verify you are signed into you RP-STS (SharePoint) via claims based authentication by selecting My Settings from your sign in name:

When you do this, you’ll see the account name displayed includes the trusted identity provider:

Finally, the codeplex project http://claimsid.codeplex.com/ contains a useful web part you can drop on any web part page that will display the details of the current claim you’re signed in with. In my example, it looks like this:

 

Summary

Hopefully this has given you a few pointers to setting up claims based authentication with SharePoint. I would recommend checking out the Thinktecture codeplex project – it a great basis for deploying or developing a custom IP-STS.

Enjoy!

UPDATE: My next article shows how to add additional claims to the SharePoint RP-STS: http://sharepintblog.com/2011/10/26/adding-additional-claims-to-a-trusted-identity-token-issuer/