This week’s workshops: visualization, Gantt and Sparklines

I am presenting three live online workshops this week, with Mark Miller from As usual, each participant receives a sandbox to try out the solutions we provide.

March 23, 2010 – Inline Visualizations in SharePoint
An entry level workshop where you’ll learn how to add color and other effects to your SharePoint lists. Here is a live demo:

March 24, 2010 – SharePoint Gantt Chart Enhancements
The participants will receive 6 snippets of code. The centerpiece is the dynamic timescale (month/week/day), as demonstrated on this mockup:

March 25, 2010 – Dynamic Feedback through SharePoint and Sparkline Charts
Sparklines are a fascinating, relatively new way to communicate. More compact than traditional charts, richer than traditional indicators, they are very popular in dashboards that require to synthesize large amounts of data.
jQuery and Google will come to the rescue to help us integrate these mini-charts in SharePoint. But of course we’ll also share some homemade recipes!

For more information and to register, follow this link:

My slides from SharePoint Saturday EMEA

My slide deck from the SharePoint Saturday EMEA conference  is now online. You can find it on Slideshare, or get it from the download section of my Website (under KPI roll-up). The recording of the session should be available soon, watch out for updates on

SharePoint Saturday EMEA was a fantastic event, kudos to Mark Miller and the organization team! As Mark explained in his post mortem, “there were a couple of roadblocks to making this thing actually work. First was that Europeans treasure their weekends and we had to assume that many people would not attend because it was scheduled to be a Saturday event.” Well, the Europeans proved that they had the motivation to dedicate their Saturday to SharePoint. And the Americans proved that they are always ready for action, even at 3 am!

KPI roll-up in SharePoint (Part II)

After reading about the scenario and watching my live demo in part I, it is now time for you to try out KPI roll-ups for yourself.

Note: You’ll need site owner permissions on your site collection to set up the demo.


I am providing all the templates needed to replicate my demo in the download section. This includes 4 files:

Projects_List.stp (List template)
Each program site has a projects list. This is where the project managers will update their project status: progress and 4 health indicators.

This is a Data View Web Part that displays the visual indicators, progress bar and traffic lights.
Placed on the home page,  it will find all the project lists in the sub-tree. For example, if added to the home page of BU2, it will find all the project lists in BU2, Program 2.1, Program 2.2 and Program 2.3.

Similar to the previous Web Part, but with an additional filter that only selects the projects in poor health (overall status = red).

A site template to create program sites. The template includes both the Projects list and the Project DVWP.

So you have the choice: either use the site template, or use the list template with the DVWP template. See below detailed instructions on how to use the files.

A couple comments:
– I used a site content type to manage centrally the project items. It is excluded from the templates, as the SharePoint UI doesn’t provide a way to export/import content types.
– the DVWP includes two grouping levels that are based on the site collection hierarchy.

What’s next?

I am really looking forward to your feedback. I think it is a very convenient implementation of the  “HTML Calculated Column”, as it doesn’t rely on a Content Editor Web Part. Also, the method works on both wss and MOSS, gives you access to a large choice of visual indicators, and doesn’t require images.

If everything works as advertised, please leave a comment here! If not, you may also leave a comment, but more importantly contact me so that I can help you out.

Also, let me know if you’re not clear about the scenario and the benefits of such an implementation.

In part III I’ll explain how I built the projects list.

Detailed instructions

Start by downloading the templates from the download section, under the topic “KPI roll-up”. To download a file, right click on it and select “save target as”.

To use the Projects list template (you need site owner permissions):
– On your top level site, go to the List template gallery:
Site Actions | Site Settings | Galleries | List templates
– Select Upload
– Upload the Projects_List.stp template

You can now create lists based on the ProjectsList template, on any site of your site collection:
Site Actions | Create | Custom Lists | Projects List

To use the Data View Web Parts:
– On your top level site, go to the Web Part gallery:
Site Actions | Site Settings | Galleries | List templates
– Select Upload
– Upload the two templates Projects.webpart and Projects_with_Overall_Status_=_Red.webpart

You can now add them to your Web Part pages like any other Web Part.

Alternately, you can use the Program_Site.stp site template that contains both the projects list and the Data View Web Part (you need site owner permissions):
– On your top level site, go to the site template gallery:
Site Actions | Site Settings | Galleries | Site templates
– Select Upload
– Upload the ProjectsList template

To create a site based on the template:
Site Actions | Create | Web Pages | Sites and Workspaces

Good luck!

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.

Gantt demos – Update

Update [12/8/2009]: due to the recent events at, we are postponing the live online workshop. The Gantt workshop is now scheduled on Thursday, December 17 at 1 pm EST.

I have updated the Gantt demos on my SharePoint site. All the featured customizations will be detailed in Thursday’s live online workshop. Each participant will get a sandbox and the parts needed to build the views below.

Demo 1:

This demo is based on the OOTB Gantt view, to which I have added some customizations (cf. my previous post):
– month time scale
– variable scale, adjusted via a slider. Three ways to adjust the scale: move the handle / click on the slider bar / use the arrows on your keyboard.
– overdue tasks in red
– [new] on the fly filtering of tasks: tasks will be filtered based on the text you enter in the input field (placed above the task titles).
– [new] tooltip displaying task title, start and end dates: hover over a blue bar to view it.

Demo 2:

The second demo shows a method to add color coding to the OOTB Gantt view. The color is based on the level of priority. More details here.

Demo 3:

The third demo offers a completely different approach: the Gantt view is built from scratch, in a standard SharePoint list view. This provides more flexibility (color coding, annotations, tooltip, etc.), and benefits from the OOTB list features (like grouping).
See this post for more details.

Live demo: dynamic time scale for the Gantt view

After this week’s live online workshop dedicated to sparklines, next week will be the turn of the Gantt view.

I am still working to finalize the customizations, but here is a preview to show you what you can expect: 

On this mockup, play with the slider to change the time scale. There are three levels: the default day and week levels, plus a custom month bar. The day and week levels are only displayed when the scale is big enough (x10 for weeks, x15 for days).

You’ll also notice a red bar in the Gantt, this is to indicate an overdue task (not completed, and due date before today).

Anything else you’d like to see on Gantt charts? Let me know!

Online Workshop – Easy Tabs for Project Dashboard


This Thursday, Mark Miller and I will be delivering our second “Easy Tabs interface” online workshop. One of the examples we’ll review is the use of tabs in dashboards.

In our online workshops, each participant receives his/her own sandbox. Access to the sandboxes is restricted, so to give you a taste of the demos I have created a mockup of the project dashboard:

Note that the original page is built on a standard wss site, using only the SharePoint UI (no SharePoint Designer, no server access).

There are multiple approaches to dashboards, depending on the story to tell and the audience. I am not claiming that tabs are a best practice, I am just suggesting them as one more option in our toolkit.

Tabs allow you to create layers. In my example, the first layer is dedicated to visual indicators, allowing you to assess at a glance the health of the project. At the same time, thanks to the tabs, additional information is not far behind, and can be reached without the need to leave the dashboard or even refresh the page. Under each tab, you can find more details, for example data or analyses supporting the charts. Or on the contrary you can filter information, allowing to focus on specific items (e.g. My Tasks, My Issues).

Interested in the Easy Tabs? Then register for our online workshop this Thursday. See you there!

Credit goes to Craig Lowrie for the color coded Gantt, and Claudio Cabaleyro for the pie chart. An HTML calculated column was used to create the bar chart.

Color Coded Gantt View


Update [10/21/2009]: version 1.1 is available for download (see links below). Remember to set the jQuery path and the pictures path in the script before using it.

Craig Lowrie is a Network administrator at the Ear Science Institute (Australia). A couple days ago, he shared with me a very useful script, allowing to add color coding to SharePoint Gantt views.

Let’s start with the live demo (combined here with the Gantt slider):

A SharePoint Gantt view is made of two sections: the Gantt view itself (not customizable), and underneath a “summary list” that displays the task properties. We have customized this view to display the priority levels.

Craig’s script works as follows:
– first, it scans the summary list to identify for each task the priority level.
– it then goes back to the Gantt view, and for each task it replaces the blue bar with the appropriate color (red/yellow/green). This is done by replacing the dozens of small blue images that make the Gantt view with custom images (yes, SharePoint uses one image per day!).

The files for this customization are available on my site’s download page (to download a file, right click on it and select “save target as”).
[09/15/2010: link updated to the new Toolkit location]

First, you’ll need to unzip, and place the images created by Craig in a library.

Then you’ll need to place the script on the page that contains your Gantt view. You have two choices on the download page:
– either the script (ColorCodedGantt.txt) that you’ll paste in the source editor of a Content Editor Web Part
– or directly the Web Part itself (ColorCodedGantt.dwp) that you can import to your SharePoint page.

The last step consists in minor adjustments to the script:
– change the path to point to the library where you stored the images (PicturesPath in the script)
– The sample code calls the jQuery library available online from Google. You may want to link to your local jQuery library instead. Or remove the call if you already use jQuery on your page.

You can easily adapt the script to work with a choice list other than Priority. Simply replace (High|Normal|low) in the script with your own values.

Thanks for sharing Craig!