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:
=IF([RAG Status]="Red","red",IF([RAG Status]="Amber","orange","green"))
="<DIV style='font-family:Wingdings; font-weight:bold; font-size:24px; color:"&Color&";'>n</DIV>"
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:
="<DIV style='font-family:Arial Unicode MS; font-size:36px; color:"&Color&";'>?</DIV>"
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:
=IF([RAG Status]="(1) Red","red",IF([RAG Status]="(2) Amber","orange","green"))
With this change in place, my column ordering works – and it gave me the opportunity to change the way I represent the status too…
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:
To use these, I changed the formula for my RAG column to:
="<DIV><IMG src='/_layouts/images/KPIDefault-"&(3-RIGHT(LEFT([RAG Status],2),1))&".gif' /></DIV>"
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.
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.