KPI roll-up in SharePoint (Part I)

Do teams in your organization need to report on the status of their projects or action items? Are managers and executives  looking for a way to aggregate and synthetize this information, to help them focus on key issues?

On January 23rd, at the SharePoint Saturday EMEA event, I’ll present a session about “KPI roll-up in SharePoint 2007”.

Last year, I already published a series about KPI roll-up, but it only applied to MOSS, and relied on the Content Query Web Part. This time, I’ll show you how a similar result can be achieved with the Data View Web Part and applied to any SharePoint 2007 configuration (wss v3 and MOSS).

As usual, no action is required on the server side. All the customizations will be done via the SharePoint UI. We’ll also use SharePoint Designer to configure the Data View Web Part, although this is not mandatory and could be one with a text editor.

Our tool for building the visual indicators is my “HTML Calculated Column” method. If you’ve already used it before, you know that it is usually associated with a client side script (“Text to HTML”). Well, here is some good news: in this specific case, we don’t even need the script, SharePoint will do all the work for us! btw this is also how it worked with the CQWP in last year’s series.

In this article (part I), I am going to describe the business scenario, with the support of a live demo.
In part II, I’ll provide the templates I used for the live demo. This will allow you to test them in your own environment (wss or MOSS).
In parts III and IV, I’ll explain how I did it, using calculated columns and the Data View Web Part.
In parts V to X… you tell me how you’ve taken advantage of the method in your own environment, and share your findings and customizations with me and the other readers!

The scenario

An organization is divided in business units, each one gathering multiple program teams. Each program team manages several projects.

Each level of the hierarchy needs visibility on the projects under its supervision. So for example:
– the program team 1.2 monitors all projects 1.2.x
– Business Unit 1 monitors all projects 1.x.y
– the top management monitors all projects

Information architecture

The SharePoint architecture follows the organizational structure: the collaborative space is a site collection, where each business unit is a sub-site of the top level, and each program is a sub-site of the business unit site.

Program team level

Note: click on a screenshot to access the live demo.

In my example, I use a custom list with 5 indicators to monitor the projects.

The budget, quality and schedule indicators track the project health: good (green), average (amber), or poor (red).

The overall status is a global indicator based on the 3 others. For example:
– Green+Amber+Amber –> Amber
– Red+Amber+Amber –> Red

To make the table easier to read, the text is converted into visual indicators: progress bar for the % complete, and traffic light for the health indicators.


Business unit level

 The business unit dashboard gathers all the projects under its responsibility:

For the demo, I have shown all the indicators. At this level, we could actually have restricted the view to the progress bar and the overall status, for a lighter display.

Top level

At the top level, we are collecting information from all the projects in the organization. To avoid an overwhelming amount of data, the list is filtered to only display the projects we want to focus on (in my example the ones with a red overall status).

In the next episode, I’ll share the list template and Web Part I used for the demo. A key point with this method is that it is the SAME Web Part that is used at all levels of the hierarchy to render the visual indicators. At each level, the Web Part is smart enough to only select the relevant information, i.e. the projects in the sub-tree.

Using calculated columns to write scripts

In the past 6 months, I have proposed many examples using calculated columns to write HTML. If you are not familiar with this method, I strongly recommend that you first check out the post that started it all before reading the rest of this article.

Warning! This article doesn’t offer any plug and play example. Wait for my next article to see the method at work.

The HTML calculated column method has allowed us to include “div”, “a”, “img”, “iframe”,”table” and other tags in our formulas. Now, have you tried to use “script” tags? If it worked, this would open up a whole new world of opportunities! Continue reading

Issue: filters in calculated columns


I am about to release some scripts to improve the filter drop-down box for the “HTML calculated column”.

As I worked on this, I came across an issue with calculated columns: the filter doesn’t work if the content of the calculated field is longer than 256 characters.

If you have more information on this issue please let me know!

This will impact you if you use calculated columns and create HTML strings longer than 256 characters. You may also experience this issue in other situations, for example if you use calculated columns to aggregate text strings.

Color coding: more examples

I already published an article about color coding for SharePoint lists three months ago, and its sibling for calendar views last month. Both rely on the same client side method, the “HTML calculated column“.

Color coding involves 3 steps:

1/ Select the color

2/ Apply the formatting: traffic light, font color, background color, etc.

3/ Include the magic script that renders the formatting (SharePoint OOTB would just display text)

People are usually comfortable with step 3, which is just a copy/paste of a generic script. But some users are struggling with the first two steps, because they are not so familiar with SharePoint calculated columns.

So I have decided to publish a list of examples based on the default SharePoint lists. They can be used as is and should cover a wide range of use cases. For each example, you can grab the formula by clicking on “copy to clipboard”.

If you are a seasoned list designer, skip this post…but forward the link to your friends!

Continue reading

Sparklines and other charts in SharePoint lists


Look at the chart in the above picture. Isn’t that cute? Such little, text size graphics are called sparklines. They have become very popular, and you can see them all over the place, for example in Excel spreadsheets.

This post will show you how sparklines and other charts can easily be inserted in SharePoint lists.

If you checked out the 7+1 live demos, this post refers to examples 3 and 4.

Continue reading

Live demo: holiday calendar

I got a couple questions about the holiday calendar displayed on my demo site, so here is the recipe.

The ingredients are already on this blog:
– how to shrink the calendar month view
– how to display HTML in a calendar view
– how to include a hover effect

So as usual add a CEWP to the page, under the calendar, where you’ll paste the CSS to shrink the calendar and the script to render the HTML.

Here is the formula I used for my demo:

="<DIV style='position: relative;margin:-4px;' onMouseOver='document.getElementById("""&Title&""").style.display=""""' onMouseOut='document.getElementById("""&Title&""").style.display=""none""'><DIV style='height:10px;overflow-y:hidden;' ><IMG style='width:20px;' src='"&Picture&"' /></DIV><DIV id='"&Title&"' style='display:none;position: absolute; top:-60px; left:-110px;background-color:navajowhite;border-width:thick;border-style:ridge;'><IMG style='width:86px;' src='"&Picture&"' />

Where [Picture] is a column of type text containing the link to the displayed picture.

Note that the hover effect works well in Internet Explorer, but there are rendering issues in Firefox.

SharePoint calendars: color coding, hover effects, etc.


Update [5/15/2009]: some readers have reported issues in specific cases (e.g. intraday events in calendar lists). To fix this, I have modified the post and now use “SPAN” tags instead of “DIV” tags. I’ll publish a more complete update in a couple weeks.

Update [6/8/2009]: A new version of the script is now available, more details here.

Two months ago, I introduced a method to display html in list views. The purpose of this post is to extend it to calendar views.

Continue reading