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

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:

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:

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

I hope this helps…

SharePoint Topology Data Collection Walkthrough

Here’s a walkthrough of how to install, configure and use the great SharePoint Topology Data Collection and Visio SharePoint Network Topology Add-in provided by one very clever AravindKS.

The tool is designed to automatically collect topology data about from your SharePoint farm and to automate the creation of Visio diagrams that illustrate this topology via data connected diagrams – very cool!

Timer Job Installation

Step 1.

Download the SharePoint Topology Data Collection and Visio SharePoint Network Topology Add-in

Step 2.

Install the SharePoint Topology Data Collection Tool. Note: This tool required the .Net Framework v4 to be present on the farm:

Once this tool has installed, check Central Admin for the following items. Under Monitoring > Timer Jobs you should see a new option appear on the monitoring home page called ‘SharePoint Topology Data Job Settings’:

Additionally, under Monitoring > Review job definitions, you should see a new timer job entitled ‘SharePoint Topology Data Collection Timer Job’:

Step 3.

Before we run the SharePoint Topology Data Collection Timer Job, we need to configure the timer job settings, so back to Monitoring > Timer Jobs > SharePoint Topology Data Job Settings. From here, enter a valid

From here, pick a Web Application, Site Collection, Site, and List name to store the collected topology data. You don’t have to use central admin like I have done but you might want to so that access to the topology data is restricted. Additionally, note that the list name entered does not have to already exist, the timer job will create the list the next time it runs.

Step 4.

From Monitoring > Review job definitions, run the SharePoint Topology Data Collection Time Job. Once this job has run, you should notice a new list appeared in the location you specified in Step 3 and it will be populated with a number of different rows that the timer jobs has created:

In my single server development farm the timer job created 45 rows.

If you see this list and it contains data, then the installation and configuration of the timer job is complete and you can move onto the Visio add-in installation

Visio Installation and Data Connection

Step 1.

Now that the timer job is installed and working, we can configure the Visio add-in. Install the Visio SharePoint Network Topology Add-in and note the installation directory. Once the installation is complete, navigate to the installation directory and locate the SPNetworkTopology.vsto file:

Open this file and the Microsoft Office Customization Installer will prompt you to accept the package. Continue by accepting the package.

Step 2.

Once the package is installed, start up Visio and check the add-in is installed, File > Options > Add-ins:

Step 3.

Create a new Visio diagram from the SharePoint Network Topology template the add-in has enabled. If this template is not immediately available to you, you should find it under the Add-Ins folder under the File > New options:

Step 4.

When you open a diagram that has been created from the SharePoint Network Topology template, a new ribbon will appear called SharePoint Topology:

We will use this ribbon to create our diagram based on the data collected via the timer job. From the SharePoint Topology ribbon, select the Link Data to SharePoint action, to access the SharePoint Network Topology Data Selector:

Click Next, and then enter the SharePoint site url and link name we configured in the timer job settings:

Click Next.

On the Column Selection screen, choose the properties (columns) you would like available in your diagram (I’ve chosen ‘Select All’):

Click Next

Finally, confirm your selections and click Finish:

Diagram Creation

Now that we have completed the Link Data to SharePoint action, our Visio diagram should have a data connection to the SharePoint list that contains our topology data. Now it is time to use this data.

Step 1.

Navigate to the first page that is created for us, ‘SharePoint Network Topology’:

Step 2.

From the ribbon, select Generate Diagram:

Now the magic begins! The add-in will use the topology data collected to automatically generate a diagram of your SharePoint Topology that is data bound to the topology data collected from the timer job. This means that once the diagram has been created you can refresh it at any time to review changes in your topology and its performance – very nice!

Step 3.

Navigate to the Service Details page and repeat Step 2.

Step 4.

The diagrams that were created in steps 2 & 3 for my single server farm are shown below. Obviously in a larger farm these diagrams will contain more servers and more details:

 

I’ll publish some more complex diagrams once I’ve run the tool in my test lab.

Step 5.

Now the diagrams are available you can review the diagrams and decorate them with further data visualisations. See Display a data-connected Visio drawing in a SharePoint web part for more details on how to do this and how you can publish this dynamic diagram to SharePoint.

Enjoy!

Create SharePoint list view sparklines from version history

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

Unlike some of the other methods out there, this approach uses a single value column for the source of the spark line and the list item version history to plot the changes to the value over time.

Here’s how you do it.

Step1 – Create a custom list, in my example I’ve called this ‘SparkLines’

Step 2 – Add a column to contain the numeric value you wish to plot. In my example I’ve called this ‘Value’

Step 3 – Add a column to host the sparkline image. This needs to be created as a ‘Hyperlink or Picture’ column and ensure the display option for the column is set to ‘Format URL as: Picture’.

Step 4 – This step is vital – enable versioning for the list

Now that the list is set up is should look something like this:

Now we need to add an Event Receiver to the list to general the sparkline. The code below uses the Google Chart API to process and render the sparkline as an image that is then displayed in the browser. This technique has a few advantages and a few disadvantages. Firstly, processing for rendering the sparkline is performed by Google and not SharePoint which is good news for performance, however, it does rely on the client browser have access to the internet. Secondly, to use this technique you should note the Google Chart API terms of service: http://code.google.com/apis/chart/image/terms.html

Event Receiver Code:

/// <summary>
/// An item was updated.
/// </summary>
public override void ItemUpdated(SPItemEventProperties properties)
{
    base.ItemUpdated(properties);
    if (properties.ListTitle == "SparkLines")
    {
        string url = "https://chart.googleapis.com/chart?chs=100×25&cht=ls&chco=0077CC&chd=t:{0} SparkLine from Google Charts API";
        string values = "";

        // get the item
        SPListItem i = properties.ListItem;

        // get the ‘Value’ history
        SPListItemVersionCollection history = i.Versions;
        foreach (SPListItemVersion version in history)
        {
            if (version["Value"] != null)
            values = version["Value"].ToString() + "," + values;
        }

        // update the item
        EventFiringEnabled = false;
        i["SparkLine"] = String.Format(url, values);
        i.SystemUpdate(false); // false = do not create a new version of the item
        EventFiringEnabled = true;
    }
}

Once the event receiver is built, deployed and attached to the SparkLines list you are all set to plot sparklines via the Google Chart API.

To test the plotting I’ve used the following Powershell script that updates the first few list items with random numbers:

## Load the SharePoint Snapin so the script can be executed from PowerShell editor
Add-PSSnapin Microsoft.SharePoint.PowerShell –erroraction SilentlyContinue

## Create a random number
$random = New-Object system.random

## get the default web app
$web = Get-SPWeb http://bc01

## get the list
$l = $web.Lists | where { $_.Title -eq "SparkLines" }

$i = $l.items[0]; $i["Value"]
$i["SparkLine"].ToString()

## update the item
$i = $l.items[0]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[1]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[2]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[3]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[4]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[5]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[6]; $i["Value"] = $random.next(0,100);$i.Update();

Run the script or manually update your list items a few times to build up some version history. The resultant list view should look something like this:

The Google Chart API is very flexible and powerful and this technique can be used to render all sorts of different effect. Examples of the types of other charting you can create can be found at http://code.google.com/apis/chart/index.html

Enjoy!

Build a Composite Application and Integrate Google Maps with SharePoint data

One of the great new capabilities of SharePoint 2010 is the amount of disparate information and services you can pull together to build mash up applications or Composites as Microsoft calls them.

In this post I’ll show you how to integrate a SharePoint list with Google maps to provide real-time mapping of records stored in SharePoint. If you prefer to use other mapping services you can, the principal is just the same.

In this example we’ll use out of the box web parts and a zero code approach, (if you count XSL as non-code). The example uses a SharePoint list, two web part views of the list and an XSLT file uploaded to SharePoint. The XSL file I use in this example can be seen below.

To begin with, create a new SharePoint custom list (I’ve called mine Branches) with the usual default Title field and then add a new single line of text column to your list called ‘PostCode’. Once you’ve created your list, add it to your web part page and it should look something like this:

In my example I’ve added a few more columns to the list but the important one is the ‘PostCode’ column. Additionally, I’ve renamed my list view web part ‘Branches’.

Now below the ‘Branches’ web part, add another list view web part for your custom list and set the following web part properties:

· Title: Branch Location

· Height: 310

· Width: 310

You page should now look something like the image below with two similar web parts on it:

Next we are going to connect the web parts so that the Branches (Red) web part filters the Branch Location (Green) web part. To do this, edit the Branches (Red) web part and select the Connections > Send Row of Data To > Branch Location option:

Next, when prompted, set the Connection Type = Get Filter Values From, Provider Field Name = PostCode, Consumer Field Name = PostCode. Once the connection is set up, you should see that selecting a row from the Branches (Red) web part filters the Branch Location (Green) web part. So far, all standard SharePoint behaviour!

Next we’re going to take advantage of one of the new List View web part properties that SharePoint 2010 provides – that’s the ability to override the web part is rendered by specifying an alternative XSL. For those of you not familiar with XSL, it simply allows you to alter the way the default HTML or XML the web part would have displayed to something you can specify.

The XSL I’m using in this example and it’s very straight forward:

<?xml version=”1.0″ encoding=”utf-8″ ?>
<xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform&#8221; xmlns:ddwrt2=”urn:frontpage:internal”>
<xsl:output method=”html” indent=”no”/>
<xsl:template match=”/” xmlns:ddwrt=”http://schemas.microsoft.com/WebParts/v2/DataView/runtime”&gt;
<xsl:call-template name=”dvt_1″/>
</xsl:template>
<xsl:template name=”dvt_1″>
<xsl:variable name=”Rows” select=”/dsQueryResponse/Rows/Row” />
<xsl:call-template name=”dvt_1.body”>
<xsl:with-param name=”Rows” select=”$Rows” />
</xsl:call-template>
</xsl:template>
<xsl:template name=”dvt_1.body”>
<xsl:param name=”Rows” />
<xsl:for-each select=”$Rows”>
<xsl:call-template name=”dvt_1.rowview” />
</xsl:for-each>
</xsl:template>
<xsl:template name=”dvt_1.rowview”>
<xsl:element name=”img”>
<xsl:attribute name=”src”>
http://maps.google.com/maps/api/staticmap?zoom=15&amp;size=300×300&amp;maptype=roadmap&amp;center=
<xsl:value-of select=”@PostCode”></xsl:value-of>
,United Kingdom&amp;sensor=true&amp;markers=color:red|color:red|label:Coffee|
<xsl:value-of select=”@PostCode”></xsl:value-of>
,United Kingdom&amp;key=INSERT_YOUR_KEY_HERE
</xsl:attribute>
</xsl:element>
</xsl:template>
</xsl:stylesheet>

Basically, the above XSL replaces the standard view with an image element (img) that is sourced from the Google Maps API. The Google Maps API knows what address we’ve selected as the PostCode value is passed into the Google Maps image URL via the <xsl:value-of select=”@PostCode”></xsl:value-of> parameters.

Finally, if you use the above example, you’ll need to replace the ‘INSERT_YOUR_KEY_HERE’ value with your own key obtained from Google Maps here: http://code.google.com/apis/maps/signup.html.

Once you’ve configured your XSL with your own key, upload the XSL file to SharePoint – in my example, I’ve used the SiteAssets library. Now edit the properties of you Branch Location (Green) web part and set the XSL Link property to point to you XSL file:

Press OK to save you changes and test that selecting a row from the Branches (Red) web part displays the map of the selected PostCode value:

Using exactly the same principals you can integrate many other examples to create rich mash up applications. Here we’ve integrated Google Maps and Google Charts plus added a custom InfoPath Form to edit the Branch list items to build a simple application to manage and report of branches of one my favourite coffee vendors:

Congratulations! You’ve now created a composite application that merges SharePoint data with Google Maps.