markwilson.it

I’ve been having some fun over the last couple of weeks, developing a dashboard in SharePoint to track a number of activities that I’m co-ordinating.

Within our company there are some people who criticise SharePoint as a platform, largely because of some of the confusing messaging around some of the collaboration features and the way in which it gets mis-used as a document store (complete with folders!) but, whilst it can certainly be infuriating at times, I have worked on quite a few sites over the years where it’s struck me just how powerful the concept of a list is (I should know that really – I remember spending quite a bit of time discussing linked lists in my Computer Studies degree, but that was 20 years ago…).

I wanted to create a list with a red/amber/green status for each item – but I didn’t just want the words – I wanted colour to jump out of the page and say – this is the stuff that’s on track… and this isn’t…

Some calculated values and a script to generate HTML from text

A bit of digging around on the ‘net turned up a method that works for me – and the details are in Anjali Bharadwa’s post on colour coding custom lists and tasks:

Firstly, I  interrogate the contents of my text column (in my case this is called RAG Status, with pre-defined choices or Red, Amber and Green) and convert that to an HTML colour code in another column (called Color). The formula is  as follows:

I created a page that includes a Web Part that displays a view on my list and used a hidden Content Editor Web Part below this, containing Anjali’s script (there’s an alternative script available from Christophe Humbert at PathToSharePoint but the download link wasn’t working on the day I needed it…), to read this and to generate HTML in another column with a calculated value (called RAG).  For SharePoint 2007, the script can be included within the HTML source for the webpart but, for SharePoint 2010, it’s necessary to link to a separate file (Adam Preston explains how to do this in his post on inserting JavaScript into a Content editor Web Part) in the This time, the formula is:

Using a particular font is risky (how do you know that everyone has it?) but in this case I could be sure that all of my audience would be running our corporate build with Windows and Internet Explorer. I later changed the formula to use a different symbol:

Sorting the RAG status

One annoyance with this approach was that the list was sorted based on my RAG Status column, but I wanted Red-Amber-Green and the alphabetical order was Amber-Green-Red. My fix for this was to change the RAG Status choices to include a number so they become: (1) Red; (2) Amber; and (3) Green.

I also tweaked the calculated value for the Color column:

With this change in place, my column ordering works – and it gave me the opportunity to change the way I represent the status too…

Alternative visualisations

Path to SharePoint has some ideas for alternative visualisations for colour-coding (and a follow-up post). I particularly like the KPI indicator version, which is based on some of SharePoint’s standard images:

_layouts/images/kpidefault-0.gif
_layouts/images/kpidefault-1.gif
_layouts/images/kpidefault-2.gif

To use these, I changed the formula for my RAG column to:

This change also means that the Color column is redundant, although I’ve left it in place in case I want to go back to it later.

What next

So that’s my RAG status sorted. In the next post, I’ll explain how I used this method to show ticks and crosses to indicate activity completeness.