Use jQuery to Create KPIs out of Calculated Columns
Guest Author: Jason MacKenzie
Intelligence Among Us
MOSS 2007 has the capability of creating KPI lists however you need the Enterprise version in order to use this functionality.
From a project management perspective I like to track the status of key milestones in a list with red, yellow and green prominently beside them to have a very visual way of seeing the status of the milestones. This might be based on due date, status or some other criteria.
Using a calculated column to output the result you want and then manipulating the result with jQuery is also a nice way to get this done very simply with some nice visual effects along the way.
In the past I have used a calculated column and created some fairly complex formulas and outputted the HTML that will render the images directly – i.e. “/_layouts/images/kpiryg-0.gif”. This works fine although it requires you to edit the fldtypes.xml file in the 12 hive of the server and this being EUSP I wanted to come up with an “end user” way to do this.
For this example I have created a simple list with a text column and populated the items with one of three values: "KPI_Green", "KPI_Yellow", "KPI_Red". The formula for your calculated column would simply output one of these three values.
Once that is complete simply add a content editor web part to your page and paste the following script into it. This code will use multiple selectors in jQuery to select all the TD elements with a CSS class of “ms-vb2” that contain “KPI_Green”, “KPI_Yellow” or ”KPI_Red”. It will then loop through those elements, hide them, set the appropriate html and then show the element again with a nice sliding effect.
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('TD.ms-vb2:contains("KPI_Green"),TD .ms-vb2:contains("KPI_Yellow"),TD .ms-vb2:contains("KPI_Red")').each(function()
{
$(this).hide();
$(this).html(SetColour($(this).html()));
$(this).show("slow");
});
});
function SetColour(Value)
{
var strImage = ""
switch(Value)
{
case "KPI_Green" :
strImage = "kpidefaultlarge-0.gif"
break;
case "KPI_Yellow" :
strImage = "kpidefaultlarge-1.gif"
break;
case "KPI_Red" :
strImage = "kpidefaultlarge-2.gif"
break;
}
return "<IMG src='/_layouts/images/" + strImage + "' border='0'>"
}
</script>
Guest Author: Jason MacKenzie
Intelligence Among Us
Jason has been an IT professional for 12 years and has worked in a variety of roles from software development to managing business solutions for a large international automotive supplier. He has developed mission critical software solutions for the manufacturing industry and has experience in the government and educational fields as well.
Jason is a social networking enthusiast and is currently working as an independent SharePoint architect. Jason helps organizations with strategy and implementation guidance related to architecture, governance, processes as well as hand-holding and facilitating a good group cry every now and again. Jason’s goal is to actively participate in the community and share what he has learned in order to help people and business leverage their investment in this critical platform.
- Use jQuery to Create KPIs out of Calculated Columns
- Use jQuery to Create a Tag Cloud
- Build a Search Solution with the Help of jQuery
- Build a Search Solution with the Help of jQuery - Part 2: Nuts and Bolts
- Create a Scrollable Image Gallery with jQuery
i love the calculated columns in SharePoint, however, my users (with edit permissions to the list) keep accidentally overwriting the formulas. Is there a solution to protect the calculated field from such annoying changes?
Probably there is a better way than this but here it is anyways:
1. Enable content management on the list
2. Create another Calculated column which is equal to “main calculated col”
3. Make the “main calculated column” hidden
Not really a solution for end users, but you could attach an event handler to the list (fieldUpdating, fieldUpdated) to protect the field. I use this solution to send me an email if the formula is changed on some of my calculated columns. You could also set it to not allow the change, its really up to you.
David
Since I’m an end user, is there a way to find/identify the names of the KPI graphics if I want to use a smaller image size?
Thanks for this tip…it works great!! Just looking for smaller images.
Hi Tim,
if you had access to the actual server, you could browse through the images at the following location:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\IMAGES
As you probably have not, I think it will not be possible for you to get a listing of these images.
To answer your question: the smaller versions are named “kpidefault-0.gif” etc.
Peter Allen, from BitsOfSharePoint.com, has aggregated the Out-of-the-Box (OOB) images into a single location for easy access:
http://www.bitsofsharepoint.com/BlogPoint/Lists/Posts/Post.aspx?List=96f31f8e%2Dca05%2D4cbe%2D9ad7%2D628b4cad2a36&ID=37
Mark
unfortunately I work for a large bank and only have OOB, fully protected SharePoint instance on the farm without even SharePoint Designer available.
Jason, Thanks! I played with this a little and got it working with my own images based on a choice column, no calculated column needed, but I won’t work in boxed views. Anyone have a work around for boxed views? Also, will this solution work in SP2010? Thanks again!
Kerri, in this case the class ms-stylebody is used, and not ms-vb. As I have both web parts (the “normal view” and the “boxed view”) on my page, I do a selection and replacement for both cases at once (you could just replace all ms-vb ’s in above example with ms-stylebody).
Also, I just saw that a space is added to the value of my output, so I modified
$(this).html(SetColour($(this).html()));
to a trimmed version with spaces at the beginning and end of it:
$(’TD.ms-stylebody:contains(”KPI_Green”),TD .ms-stylebody:contains(”KPI_Yellow”),TD .ms-stylebody:contains(”KPI_Red”), TD.ms-vb2:contains(”KPI_Green”),TD .ms-vb2:contains(”KPI_Yellow”),TD .ms-vb2:contains(”KPI_Red”)’).each(function()
{
$(this).hide();
$(this).html(SetColour($.trim($(this).html())));
$(this).show();
});
});
Rene, I’ve worked on this several different times now and I still can’t get it to work with boxed views. I’ve tried using the replace method of the .ms-vb2 with the .ms-stylebody, and your portion (lines 6 through 12 correct?) replaced in the original (with my additional adjustments), still can’t make it work? I wonder what I might be missing – as I’m sure my values are matching.
I’ll keep working at it. I just wanted to say thanks for offering a solution, as I imagine it is my folly not getting it to work. And I’ll post back if I do get it! Thanks again!
Kerri,
can you verify if your boxes actually use the ms-stylebody class? E.g., press F12 in IE to open the Developer Tools, use the selector to select one of the boxes, and check if the table cell (TD) containing the relevant value (KPI_Red, … in the example here) uses this class or another one.
Jason,
This is a very elegant, easy to use solution. We had been using a workflow to replace an indicator graphic when a status changed, but no more!
I love all your posts – particularly those that are all client side (large utility=no SharePoint Designer :D )
Thanks for posting your efforts! I look forward to future posts!
Hi. I got this to work and it looks awesome! Except when I go in and group, the symbols do not show up. Is there a way to fix them. I have tried grouping by several different things and it seems like it is the grouping that is giving me a problem. Any help would be greatly appreciated! Thanks.
Hi Lisa,
I’ll check this out when I am back from vacation. I imagine its nothing more than finding the CSS class associated with that TD element when you group it.
Jason, as far as I can see it uses the ms-vb2 as well. However, jQuery doesn’t seem the select those items with $(’TD .ms-vb2′), not sure why not.
Sorry – I just looked at this. I believe the issue is that the content in each group is loaded dynamically and is not rendered as part of the html output.