Case study: KPI roll-up in MOSS (Part I)

Posted on by

Eric Proshuto is a Sr. Application Engineer for Siemens PLM Software, where he manages the intranet/extranet MOSS farm. He has been supporting SharePoint since early 2000 using SharePoint Team Services.
A couple weeks ago, Eric posted a very useful tip on my blog. I have built a case study to show how to put it to work. I sincerely hope that Eric can share a more complete, real life case study after he implements this in his company.

Note that this case study relies on the Content Query Web Part, only available in MOSS.

The scenario

Let’s consider the case of a division in a large organization, with dozens of business units that report project status on a daily basis. The Management needs visibility across business units on certain critical projects.

The figure below shows the organization structure. The SharePoint sites structure replicates it, with “Division” as the top-level site in the site collection.

Site Collection Structure

To make everyone go to a central list to report projects health would make that list unwieldy at best. Instead, we’ll keep the reporting at the team level, and use SharePoint’s aggregation capabilities to build the Executive dashboard.

Update [6/27/2009] For more details on this information architecture choice, see this timely article from Bob Mixon:
SharePoint IA: Store Information Close to the Point of Ownership

Team dashboard

Team Dashboard
We are going to build a list template that each program or project team will use to report their project’s health. We’ll do this in 3 steps:
1/ Create site columns, at the site collection level
2/ Create the SharePoint list that hosts the dashboard
3/ Save the list as a template for reuse across the organization

Note: in a real life scenario, I would definitely consider using content types for added flexibility.

Site columns

For my example, I have chosen to track two indicators for each project: progress and health. In addition, we’ll have a column that precises the level of visibility of the project.

To reach the Site Column Gallery, follow this path:
Site Actions | Site Settings | Galleries | Site Columns

The % Complete column is already present in the list. Let’s add two choice columns:
– Health, with three options: (1) Red, (2) Yellow, (3) Green.
– Visibility, with three options: Team, Business Unit, Division.

And two calculated columns for the indicators:
– Progress, based on the % Complete:
="<div style='position:relative; width:50px; border: 1px solid;'><div style='background-color:"&CHOOSE(INT([% Complete]*10)+1,"red","red","OrangeRed","OrangeRed","DarkOrange","Orange","Gold","yellow","GreenYellow","LawnGreen","Lime")&"; width:"&([% Complete]*100)&"%;'> </div><div style='position:absolute; top:0px;'> "&TEXT([% Complete],"0%")&"</div></div>"

– Indicator, based on the Health:
="<img style='float:left;' src='/_layouts/images/KPIDefault-"&(3-RIGHT(LEFT(Health,2),1))&".gif' />"

SharePoint list

We’ll use a Tasks List type:

Site Actions | Create | Tracking | Tasks

The % Complete column is already there. Let’s add the 4 other site columns we created:
Settings | List Settings | Columns | Add from existing site columns.

The final touch is to add the script for the HTML calculated column (the instructions were initially posted here, but make sure you grab the updated version of the script). Your tasks list should now look like the above screenshot.

The list is ready, we can now save it as a template that all teams will use for their project:
Settings | List Settings | Save list as template

Note: the “Text to HTML” script will also be saved in the template.

In Part II, we’ll see how to roll up projects with executive visibility to the top level, and how the HTML calculated column plays well with the Content Query Web Part.

29 thoughts on “Case study: KPI roll-up in MOSS (Part I)”

  2. Your timing with this is amazing. Just started looking at this subject again today and working on a mock up over the weekend.

  3. Pingback: Case study: KPI roll-up in MOSS (Part II) « Path to SharePoint

  4. Pingback: SharePoint Links for 2009-06-27 | MarkSimon.de

  6. Wanda: in wss you don’t have these OOTB aggregation capabilities, so you would have to build them from scratch (using Web Services for example) – or look for third party tools.

    An alternate option would be to use a central list. As explained in this post(and in Bob Mixon’s article), it is not the preferred choice, but it could work for smaller organizations.

  7. re: “in WSS”…”build them from scratch” – well, all the tools are there if you can use SharePoint Designer to setup a Data Source to call the _vti_bin\Lists.asmx web service and then use the Data View Web Part. I have seen some blog posts that explain how to modify the CAML queries to do cross site queries. tedious, but possible.

    • Funny to read this thread again, as I am about to publish a series that shows how to do it in wss. Well Larry, all you said was correct (including the tedious part).

  8. Thanks for this great column, but

    I seem to be missing something but I can’t figure out where.
    I can display the Progress column correctly, but the Indicator column appears as HTML:

    for the first item in the list and:

    for the second item in the list and so on.

    I have the TextToHTML Ver 2.0 linked in a CWEP at the bottom of the page, and I know it is working, as the Progress column displays properly.

    The HTML not rendering correctly seems to me like there is a missing opening tag.

    Any ideas?

  9. yes, that fixed it. I put in a “” before: <img style=…

    Wonder why no one else had this problem?

    • Bill, there was an error in the formula, and I think this happened when I last updated the post. The div tags are not necessary when you use the TextToHTML v2. The formulas should now work fine.

  11. This is wonderful. The Indicator formula gives a great effect.

    I also have a “% change” value I would like to use an icon to represent. Is there a similar formula that will return the default KPI up/down/neutral arrow images as icons in the same way?

  12. Adding- I assume this would require a column which noted whether the % change value was either an increase, decrease or neutral result. For my list, such a column would be called Result.

  13. Pingback: Case study: KPI roll-up in MOSS (Part II) « Path to SharePoint | Rickey Whitworths Blog

  14. I’m having a problem with the calculated column, Progress, in Firefox 3.x.

    It displays fine in IE 7 & 8. The problem seems centered in the part that draws the box. In FF, the style is collapsed to a 1 pixel wide box.

    My code is:

    =” “&TEXT([% Complete],”0%”)&””

    I have some screen shots, but can’t seem to post them here in the comments.

    Any ideas?

  15. Here is something cool.

    With inspiration from this great site, I’ve been able to build colour coded icons that have a different shape. Not all stop lights, but rather a variety of shapes.

    It is driven all by “Overall” which is an assessment of the project.
    Our company uses Red, Yellow, Green, Black = cancelled project, White = not started.

    Field = Key
    =” “&Icon&””

    Field = Icon
    =CHOOSE(FIND(LEFT(Overall,1),”GYRWB”),”•”,”♦”,”█”,”[]”,”X”)

    These formulas are somewhat simpler to debug than the nexted if statements.

    Somebody might have a use for this.

    Thanks for a great site and source of inspiration.
    Cheers

    Bob

    • Bob,

      I am a complete beginner in SharePoint. But I want to use the formula that you have mentioned. I have a Project Status column that has 3 options Alert, Warning, Satisfatory. Depending on what is selected for Project Status, I want to display red ”•”, Yellow ”♦” and green”█”.
      I cannot understand Field = Key and Field = Icon. Please can you help me?

  16. Pingback: KPI roll-up in SharePoint (Part I) « Path to SharePoint

  17. Pingback: Smart TextToHTML « Path to SharePoint

  18. Hello Sir ,

    I have created site columns and lists as you mentioned . and finally i added JQuery as follow

    var theTDs = document.getElementsByTagName(“TD”);

    var i=0;

    var TDContent = ” “;

    while (i < theTDs.length) {

    try {

    TDContent = theTDs[i].innerText || theTDs[i].textContent;

    if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("”) >= 0)) {

    theTDs[i].innerHTML = TDContent;

    }

    }

    catch(err){}

    i=i+1;

    }

    function ExpGroupRenderData(htmlToRender, groupName, isLoaded)
    {

    var tbody=document.getElementById(“tbod”+groupName+”_”);

    var wrapDiv=document.createElement(“DIV”);

    wrapDiv.innerHTML=””+htmlToRender+””;

    var theTBODYTDs = wrapDiv.getElementsByTagName(“TD”); var j=0; var TDContent = ” “;

    while (j < theTBODYTDs.length) {

    try {

    TDContent = theTBODYTDs[j].innerText || theTBODYTDs[j].textContent;

    if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("”) >= 0)) {

    theTBODYTDs[j].innerHTML = TDContent;

    }

    }

    catch(err){}

    j=j+1;

    }

    tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);

    }

    But i am not able to show progress and indicator result as i m getting html text instead of that which is exact , there is no wrong syntax , but i guess there wud be javascript error …i added javascript in CEWP at bottom of list , but still not rendering exact result .

    could you please help me out to complete task u mentioned in this article !!!

    i am waiting for your favorable reply.

    Thanks & Regards,
    Dipti Chhatrapati

  19. Hello Sir ,

    I have created site columns and lists as you mentioned . and finally i added JQuery as follow

    var theTDs = document.getElementsByTagName(“TD”);

    var i=0;

    var TDContent = ” “;

    while (i < theTDs.length) {

    try {

    TDContent = theTDs[i].innerText || theTDs[i].textContent;

    if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("”) >= 0)) {

    theTDs[i].innerHTML = TDContent;

    }

    }

    catch(err){}

    i=i+1;

    }

    function ExpGroupRenderData(htmlToRender, groupName, isLoaded)
    {

    var tbody=document.getElementById(“tbod”+groupName+”_”);

    var wrapDiv=document.createElement(“DIV”);

    wrapDiv.innerHTML=””+htmlToRender+””;

    var theTBODYTDs = wrapDiv.getElementsByTagName(“TD”); var j=0; var TDContent = ” “;

    while (j < theTBODYTDs.length) {

    try {

    TDContent = theTBODYTDs[j].innerText || theTBODYTDs[j].textContent;

    if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("”) >= 0)) {

    theTBODYTDs[j].innerHTML = TDContent;

    }

    }

    catch(err){}

    j=j+1;

    }

    tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);

    }

    But i am not able to show progress and indicator result as i m getting html text instead of that which is exact , there is no wrong syntax , but i guess there wud be javascript error …i added javascript in CEWP at bottom of list , but still not rendering exact result .

    could you please help me out to complete task u mentioned in this article !!!

    i am waiting for your favorable reply.

    Thanks & Regards,
    Dipti Chhatrapati

    • I’m very new at this, but when I try to create the calculated “Indicator” site column I am receiving the following error:

      The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.

      I have already created the site column called Health, and I see it as available, and I’ve triple checked for typos. Not sure what I’m doing wrong.

      Thanks,
      Andy

  20. This is great, thanks for this. However although it works great on the site, whenever a task is assigned or updated an email is sent out. In the email I see the HTML Code which I have been asked to remove. I tried editing the alerttemplate.xml file in the Bin directory by adding the Text to HTML code but it did not work. Can you provide any help on this. It would be nice to have the visiual indicators in the Alert Emails as well.

    Thanks, Jovi

  21. Hi

    I am trying this in SP2010 and the Javascript does not seem to work. Do we have a different approach for SP2010. Please help out

  22. Hi,
    I am having the same error as posted by “Andy” Followed up with your instructions ditto but the error is kind or irritating.

    Can you please post your suggesstion on the error.

    Regards & Thanks
    Jaleel

  23. Thank you for sharing this great script Christophe!

    First filed Progress showed me only the text e.g.’ 80%’ but no color. Then I found that I have to change the part
    “Lime”)&”; width: “&
    to
    “Lime”)&”; height: 13px; width: “&

    (Just add height: 13px; to it)
    Thanks a lot,
    Stefan on Sharepoint 2010

Comments are closed.