Low Tech SharePoint Hack: Project Status Indicator

I’m doing a little hacking in SharePoint that is pulling together a few ideas from the past:

• Keeping my tech skills sharp
• Knowing when to search for the answer (vs. stubbornly sticking with DIY)
• Communicating status tersely but effectively

Apparently, I’m also trying to answer a question that is meaningful to many others, as it is used as an example in the Help files for Microsoft’s online SharePoint guides, the add-on Web Parts we use here, and many other places. Why doesn’t Microsoft make something like this a standard feature?

Note that I had some fairly specific requirements in mind for something that I would consider “low tech”. this should be an approach that the average (read: non-IT) SharePoint site admin could use. I don’t want to require third-party controls, nor do I want to require the use of SharePoint Designer. I’m also shying away from image files – little GIFs to show red, green, and yellow icons; I have established a style for displaying project status in that works great with PowerPoint and Excel, and I want to use the same images consistently on the collaboration space.

Of course, I wanted to get to a solution in a reasonable amount of time (ie. Speed to Value, or being proactively lazy). A few Google searches turned up a number of resources with different approaches. The best resource was this site, loaded with excellent SharePoint hacks – including one simple concept that requires me to tweak my ground rules, just a bit. To get this to work, I have to include a JavaScript routine on the page; however, I learned a nifty trick, one of those things that is fairly straightforward, but still has to be pointed out to you before you “get it”.

The JavaScript Trick

You don’t need SharePoint Designer to install JavaScript routines or special CSS on the page. All you need to do is install a Content Editor Web Part (CEWP) somewhere on the same page as your list. You can bring up the Source Editor and insert any scripts, you want, nothing will display about the routines will be available to the other controls / web parts on the same page. I’m not going to copy the code here – these guys deserve the web traffic for their work, so, go to this page to copy the script.

A Lot of HTML for a Little Indicator

The actual HTML for the “green” indicator (~) looks like this:

Unfortunately the font changes based on which indicator you need – this table shows the various components for all of the status indicators.

Status	Code	Font	Color	Result
Green	˜	Wingdings 2	green
Yellow	p	Wingdings 3	#FFCC00
Red	n	Wingdings	#DC143C
Complete	ü	Wingdings	black

My solution adds three calculated columns to the list. Why three? Well, yes, you could do the whole thing with one computed column, but the nested IF statements would be brutally complex, and I was hoping for something “self-documenting” (ie. clear and simple).

The SharePoint list in question is a typical Issue Tracker – and the first step is to define what the different statuses (statii?) are going to be. Here, I am only allowing Open (Red, Yellow, Green) and Closed – nothing like resolved or in process or proposed – we’ll keep it simple.

I then added three Calculated columns, and defined the formulas like this:
Status_Char = IF([Issue Status]="Open - Green","˜", IF([Issue Status]="Open - Yellow","p", IF([Issue Status]="Open - Red","n","ü")))
Status_Font = IF([Issue Status]="Open - Green","Wingdings 2", IF([Issue Status]="Open - Yellow","Wingdings 3", IF([Issue Status]="Open - Red","Wingdings","Wingdings")))
Status_Color = IF ([Issue Status]="Open - Green","green", IF([Issue Status]="Open - Yellow","#FFCC00", IF([Issue Status]="Open - Red","#DC143C","black")))

I’ve tweaked the colors – standard yellow and red don’t look good with a white background. (I burned at least 30 minutes fiddling with the color tones, to make something that was visibly yet subtle. Gotta know when to go fast and when to dither over the details …).

Add a fourth column for the actual status indicator; we use the CONCATENATE function to build the HTML string as specified above. The surrounding <DIV> is used by our borrowed JavaScript function to signal the browser to turn this little bit into true HTML.
Status_Color = CONCATENATE("<DIV><font size=4 color=", Status_Color," face='", Status_Font,"'>", Status_Char,"</font></DIV>")

Effectiveness Testing

Yes, this could have been done with a single calculated field – it’s just a little bit easier to debug this way. In any event, it s a relatively large amount of code for a fairly simple effect – was it worth the effort? The ultimate test came when reviewing the list of open issues with the project team – and folks understood what was being communicated immediately. No explanation necessary – the conversation focused on the item marked “red” right away. If we just displayed the words “green”, “yellow”, or “red”, it would take a bit more mental effort to understand what was being communicated. I really want folks to think about the solutions, and not waste brain power trying to understand the problems. A little extra effort in the code is just right.