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>"
You can trick SharePoint into creating a column for today’s date which you can then compare today’s date to another date (in this case revised date).
As a result, I created a column for “Today”, with single line of text and then created another calculated column “CalcToday” and set it equal to the “Today” column.
Once I was done I deleted the “Today” column and voila! You have a column which is set to the current date!
Then I just created the “Days Overdue” column which compares date information from certain items to be addressed/completed (a.k.a”Revised Date”) to “CalcToday”.
I know there is now Office 365 accounts but sometimes you don’t want all the clutter of that product.
So here is a free website you can play with. Enjoy
If you haven’t used this method yet, you’ll need to learn it to take advantage of these tutorials. For the latest information on the HTML Calculated Column, start with this post… or attend one of our live online workshops.
Most examples are about color coding backgrounds or text. But what if you want to take it a little further? For example display:
- up/down arrows ➘➙➚
- check marks ✗✓
- star ratings ✭✭✭✭✭
- traffic lights ✹✹✹ ✹✹✹ ✹✹✹
- etc. ✉☎☀☁
What immediately comes to mind is to use a set of icons. But the above examples offer a lighter solution: welcome to the world of unicode graphics!
Unicode is an international standard that references character sets. This includes some graphics, see for example the page below:
http://www.alanwood.net/unicode/index.html
For the graphics, scroll down to the “Symbols” category.
Some benefits of unicode, compared to icons:
- unlimited choice of colors, for both the graphic and the background.
- the rendering is not bound to an external image. This means better performance. Also, it makes it easier to save the SharePoint list as template for reuse in other environments (I’ll provide such a template in an upcoming post).
An example: traffic lights
As an example, here is the formula I used in a calculated column to generate the above traffic lights:
1="<span style='background-color:black;font-size:24px;'><span style='padding:-10px;color:"&IF([Color]="Green","green;'>✹","gray;'>✹")&"</span><span style='padding:-10px;color:"&IF([Color]="Amber","RGB(255, 191, 0);'>✹","gray;'>✹")&"</span><span style='padding:-10px;color:"&IF([Color]="Red","red;'>✹","gray;'>✹")&"</span></span>"
Where the [Color] column can take the values Green, Amber or Red.
How about Wingdings?
Why use unicode characters, and not simply fonts like Webdings, Wingdings, or Zapf Dingbats? Those too offer graphics, but there is a downside: these fonts are not standard, and they don’t work cross-browser (and never will, from what I read). Such graphic fonts could still work for you if you are in a corporate environment where your internal policy enforces the use of Internet Explorer.
Unicode seems to work in all modern browsers. I tested it in IE7, IE8, Firefox, Chrome and Safari.
