A new location for the SharePoint User’s Toolkit

The SharePoint User’s Toolkit has a new official page:

The old location will remain active but won’t be updated anymore.

The new site is based on SharePoint 2010 and hosted by fpweb.net. Thanks to the support of fpweb.net, I expect the new site to be more reliable and offer a better user experience.

The SharePoint User’s Toolkit is a collection of tools designed to help end users build advanced customizations. It includes for example the Easy Tabs and an Image Rotator. It will continue to grow, with new tools added every month.

Regular users of the Toolkit will notice that several solutions are not in beta anymore. I haven’t actually made any changes to the code, the beta versions are becoming official simply because no issue was reported in the past few months.

Tasks Lists Roll-up beta, available for download

A new addition to the SharePoint User’s Toolkit: a Tasks Lists Roll-up Web Part. When placed on a SharePoint 2007 page (wss or MOSS), this Web Part will aggregate tasks from the current site and all its sub-sites.

After downloading the Web Part, you can add it to your page via the page import option, or include it in your Web Part gallery. This is a regular Data View Web Part, and you can later modify the layout in SharePoint Designer. Without SPD, you could also tweak it directly through the SharePoint UI, or even via a text editor.

Lists roll-ups are available OOTB in SharePoint, thanks to the Data View Web Part – no need for a Content Query Web Part (MOSS only) or third party tools. However setting a DVWP to work in CrossList mode is not easy, even for experienced SharePointers. Hopefully this preconfigured Web Part will give a jumpstart to your cross-list experience.

I’ll publish more explanations in the days to come. I also plan to add more options in the future, as well as roll-ups for other lists – feedback and suggestions are welcome!

In case of emergency
If your customizations break your page, keep in mind the contents=1 trick (explained for example at the end of this article).

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 EndUserSharePoint.com.

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!

Smart TextToHTML

First things first: if you don’t know what I mean by “TextToHTML “, you won’t get much from this post. In this case, I recommend that you start with this introduction.
In short, the TextToHTML script has two roles:
1/ find HTML strings in a SharePoint page
2/ Convert these strings into actual HTML

Yesterday, I came across a discussion between @EUSP, @webdes03 and @ebrackley on Twitter:
“TextToHTML can be a bad performer when tasked with lots of HTML; also research DVWPs”

This is a timely comment, as I am about to present at SharePoint Saturday EMEA. Let me expand on this, and provide a few hints on how to better use the HTML Calculated Column.

Remember: TextToHTML is not the only way

Right, most of the examples you’ll find on my blog rely on the TextToHTML script, embedded in a Content Editor Web Part. But there are other ways to render the HTML. In particular, the Data View Web Part, used in crosslist mode, or the Content Query Web Part can directly do the rendering. For more information, see these series:
– for MOSS
KPI roll-up in MOSS
– for SharePoint 2007 (applies to both wss and MOSS):
KPI roll-up in SharePoint (Part I)
KPI roll-up in SharePoint (Part II)

The latter will be the theme of my presentation at SharePoint Saturday.

Get a faster browser

Not always a choice, but if you can, upgrade your browser to the latest version. These days, browsers are improving by leaps and bounds, with Google leading the charge.
My tests show that the TextToHTML script is four times faster in IE 8 than in IE 7. That’s huge! Some other browsers offer even better response time.

Faster TextToHTML

If you are using the TextToHTML script, you’ve certainly got the current version from the download section. What I am making available over there is a generic script, for both SharePoint 2003 and 2007. If you are on SharePoint 2007, you can make it more specific and thus faster.

Focus on the main content

As is, the script will scan the whole page to find table cells (TD elements):


This is a waste of time, as you just need the main content, excluding header and Quicklaunch. You can easily do this by replacing the above line with the following code:

var theMainContent = document.getElementById("MSO_MainContent");

Use selectors

Instead of grabbing all the cells in the document, try to restrict your scope to the cells that may contain HTML Calculated Columns.

For example, Paul Grenier proposed a jQuery version of my script, which allows you to grab only certain cells. If HTML Calculated Columns are only in List View Web Parts on your page, the simple $(“td.ms-vb2”) selector should be enough. For better performance, you may want to combine it with my first advice, and focus on the main content: $(“#MSO_MainContent td.ms-vb2”).

Here again, a modern browser will give you better performance (for example if it has native tools to find elements by class name).

Place your script close to the point of consumption

Imagine that you are displaying 10 lists on your page, but only the third one is using HTML calculated columns. If you place the CEWP that contains TextToHTML at the bottom of the page, the script will go through the 10 lists. If you place the script right below the third list: when the script runs, it will be faster as it only sees 3 lists, the 7 others being displayed later.
btw placing the scripts close to the point of consumption is a good practice that also applies to other scripts, not just here. In traditional Web design, you would find all the scripts under the head section of the page, but this practice has evolved now that pages are more dynamic.

If you have other ideas, feel free to share them with me and the other readers! I’ll push some of these performance improvements in the next release of the TextToHTML script. I am also looking for volunteers to test the beta versions…

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.

Picture libraries: take advantage of Web friendly formats

When loading Web pages, pictures are most often a performance laggard. SharePoint picture libraries have a nice feature that can help you here: every time you upload a picture, SharePoint actually stores 3 files: the original picture, a version optimized for the Web, and a thumbnail.

A classic trap

You’ll often see that in order to create a thumbnail, people just take the original picture and force its dimensions. For example:

<img src=”MyPicture.jpg” width=”60px” height=”40px” />

Sure, the result looks like a thumbnail. But still, the browser has actually downloaded the full size picture.

Where SharePoint helps

Let’s take an example. Here is the URL of a picture that has a full size of 405 kb:

I have uploaded it to a picture library called PhotoVideo. Automatically SharePoint has generated two more pictures:
– a Web friendly picture of 52 kb, 8 times smaller than the original picture:
– a thumbnail: 5 kb, 80 times smaller than the original picture:

So how does it work? Well, if you upload a picture called CutePic.jpg to a picture library called PictureLibrary, then:
– you can find the thumbnail ïn the “_t” folder: PictureLibrary/_t/CutePic_jpg.jpg
– the Web copy will be in the “_w” folder: PictureLibrary/_w/CutePic_jpg.jpg

As for OtherCutePic.png:
– thumbnail: PictureLibrary/_t/OtherCutePic_png.jpg
– Web copy: PictureLibrary/_w/OtherCutePic_png.jpg

How to automate the retrieval of Web formats and thumbnails?

Let’s assume you have a picture library, and for any given picture you want a script to generate the URL of the derived image – Web optimized or thumbnail. It turns out that SharePoint already has a function for this:

function CreateDerivedImageUrl(originalImageUrl, subdirStr)
	var url=originalImageUrl.replace(/\.([^\.]+)$/, "_$1");
	url=url.replace(/\/([^\/]+)$/, subdirStr+"$1");
	return url;

originalImageUrl is the URL of the full size picture; subdirStr will take the values “/_t/” for thumbnail or “/_w/” for Web preview.

I am using a similar script for my Image Rotator.

How about XSLT, for example if you want to include thumbnails in a Data View Web Part? The expression below is taken from a comment by Drasko (in this post, on novoculus.com) :
 For Web previews, simply replace _t with _w.

So, do your site visitors a favor: save them time by taking advantage of SharePoint’s Web friendly formats!

HTML calculated column and Data View Web Part

This is a technical post that assumes you are familiar with the HTML calculated column and the Data View Web Part.

I recently published a new version of my “HTML calculated column” script. It works fine in list views and calendar views, but not with the Data View Web Part. Let’s take a closer look.

As an example, I’ll use a calculated column called “Indicator” with the following formula:

="<IMG src='/_layouts/images/kpiryg-"&(3-RIGHT(LEFT(Priority,2),1))&".gif' />"

If for example the priority is low, the following result will be displayed in list view:
<IMG src=’/_layouts/images/kpiryg-0.gif’ />

Applying the “HTML calculated column” method on top of this will render the kpiryg-0.gif image.

If however you use a DVWP, here is what you’ll see:
&lt;IMG src=&#39;/_layouts/images/kpiryg-0.gif&#39; /&gt;

Special characters are escaped: &lt; for “<“, &gt; for “>”, &#39; for single quotes.

Let’s take a closer look at the source code behind the DVWP. The code that renders the Indicator field depends on how you created the DVWP:
1/ Directly from the data source library, using the “Insert Selected Fields” option:

<td class="ms-vb">
	<xsl:value-of select="@Indicator"/></td>

2/ By converting a list view:

			<TD Class="{$IDAXOQVC}"><xsl:value-of disable-output-escaping="yes" select="ddwrt:AutoNewLine(string(@Indicator))" /></TD>

Change the code as follows:

<td class="ms-vb">
<xsl:value-of disable-output-escaping="yes" select="@Indicator" />

With the modified code, the field will display the same way as in a list view:
<IMG src=’/_layouts/images/kpiryg-0.gif’ />

You can now apply the usual script, and have your string rendered as HTML.