Hey everyone,
Todd Klindt is like the guru of SharePoint and he is at the leading edge of all this stuff.
If you have a lab or test environment with some VM’s somewhere not doing anything then have a go at doing what Todd recommends in his blog.
Its a really long URL so I shortened it with Bitly but I swear I don’t get anything for sending you Todd’s way he is just a great teacher.
If you need dynamically generated visualizations for your SharePoint data, have you considered leveraging the power of the Calculated Column? This Tuesday at 1:00pm EST, Mark Miller and I will give you all the keys to master this simple yet powerful technique. At the end of the two hour entry level workshop, you’ll be able to add color coding, KPIs and other effects – like the one described in this post – to your SharePoint lists.
Green/Yellow/Red is a standard color palette for dashboards. You can just use 3 colors to visualize discrete states, for example the status of a project (on track – drifting - late). But if your purpose is to communicate progress, or a measure on a scale, you need a larger color palette. This is for example the case in my screenshot, where the color reflects the level of completion (in %), in a tasks list.
So, how can I do this in SharePoint? Of course, my plan is to use a calculated column that will determine the color, based on the value in the [% Completed] column.
Method 1: nested IFs
This is the most basic approach:
if [% Completed]>90, select green
else if [% Completed]>80, etc.
I am not going to detail it, as we can do much better.
Method 2: CHOOSE function
The CHOOSE function is more elegant than nested IFs, and is a natural choice when dealing with multiple options. You’ll find all the explanations to achieve a color gradient in this post.
Method 3: pure calculation
So, how can we go even further? Well, colors can be identified by their name, but also by theirrgb code, as each color can be generated from a combination of red, green and blue. For example:
red: rgb(255,0,0)
green: rgb(0,255,0)
blue: rgb(0,0,255)
yellow: rgb(255,255,0)
white: rgb(255,255,255)
Using these values, we can “easily” create our red/yellow/green gradient:
rgb(255,0,0) –> rgb(255,255,0) –> rgb(0,255,0)
The following formula, entered in a calculated column, will give you the rgb value for each value of the [% Completed] column:
=”rgb(“&INT(MIN(510-[% Complete]*255*2,255))&”,”&INT(MIN([% Complete]*255*2,255))&”,0)”
To obtain the visual effect as in the screenshot, use the HTML Calculated Column method, with the following formula:
1="<span style='display:inline-block;position:relative; width:60px; height:14px;border:1px solid;'><span style='display:inline-block;position:relative;background-color:rgb("&INT(MIN(510-[% Complete]*255*2,255))&","&INT(MIN([% Complete]*255*2,255))&",0); width:"&([% Complete]*100)&"%;height:14px;'><span style='position:absolute; top:0px;'> "&TEXT([% Complete],"0%")&"</span></span></span>"
