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 $(“”) 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”).

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…

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

Executive Dashboard

In Part I, we created project dashboards at the team level. In Part II, we are going to use the Content Query Web Part (CQWP) and follow these steps to build our executive dashboard:
1/ Collect the project status for all teams in the division
2/ Filter the list to only keep critical projects
3/ Customize the look to render our KPIs

Before you start: key references

Warning! You’ll need a good knowledge of the customization options of the CQWP to apply the method presented in this post.

My purpose here is not to provide a tutorial on the CQWP, for this I’ll just relay the impressive work done by other bloggers. See my earlier post, and in particular the “must read” section. For this specific case study, I recommend Heather Solomon’s tutorial.

Project status roll-up

Let’s add a CQWP to a page at the top level (“Division” site), and customize it to gather all the team project dashboards:
– Source: show items from all sites.
– List Type: Tasks.
– Filter: only retain projects that have division level visibility.



Here is the result:


The good news is that we have successfully rolled up the projects with executive visibility to the top level (see screenshot from part I and its visibility column as reference). However we are still missing the KPIs.

We are now reaching a point where the tool pane customization options are not enough, and we need to do some heavy lifting to get our final dashboard (although the next steps don’t require any special tool, and can be done in a text editor like Notepad).

Reference the KPI columns

For this, you need to export the CQWP to your desktop, edit it, and change the following line:

<property name="CommonViewFields" type="string"/>


<property name="CommonViewFields" type="string">Progress;Indicator</property>

Display the KPIs

We need a new template in the ItemStyle.xsl file in the Style Library – I’ll call it “Dashboard” – that includes a call to the Progress and Indicator fields. For example, for Progress:

<xsl:value-of select="@Progress"/>

CQWPstyleOnce the template has been added to ItemStyle.xsl, I can go back to my CQWP tool pane and select it (item style: Dashboard).

Let’s see the result:


OK, our KPIs are now on the page…except that they have two issues:
– each calculated field starts with a “string;#” tag
– the CQWP displays the HTML as a string, not as real HTML.

Fortunately, there’s an easy way to fix this. the “string;#” tag can be removed with the “substring-after” function, and the string can be rendered as HTML by disabling output escaping. So for my Progress field I’ll actually use:

<xsl:value-of disable-output-escaping="yes" select="substring-after(@Progress,'#') "/>

The final result:


Let’s sit back for a moment and appreciate the beauty of Eric’s tip: with a minor tweak in the XSL template (disable-output-escaping=”yes”), the HTML calculated column and the Content Query Web Part become a perfect match!

The same approach allows us to create dashboards at the Business Unit level. We just need to modify two settings:
– Source: Show items from the following site and all sub-sites
– Filter: Visibility is equal to Business Unit

As a reference, here is the XSL template I added to ItemStyle.xsl for this case study:

    <xsl:template name="Dashboard" match="Row&#91;@Style='Dashboard'&#93;" mode="itemstyle">
        <xsl:variable name="SafeLinkUrl">
            <xsl:call-template name="OuterTemplate.GetSafeLink">
                <xsl:with-param name="UrlColumnName" select="'LinkUrl'"/>
        <xsl:variable name="DisplayTitle">
            <xsl:call-template name="OuterTemplate.GetTitle">
                <xsl:with-param name="Title" select="@Title"/>
                <xsl:with-param name="UrlColumnName" select="'LinkUrl'"/>
        <xsl:variable name="LinkTarget">
            <xsl:if test="@OpenInNewWindow = 'True'" >_blank</xsl:if>
            <xsl:value-of disable-output-escaping="yes" select="substring-after(@Indicator,'#') "/>
            <xsl:call-template name="OuterTemplate.CallPresenceStatusIconTemplate"/>
            <a href="{$SafeLinkUrl}" target="{$LinkTarget}" title="{@LinkToolTip}">
                <xsl:value-of select="$DisplayTitle"/>
            <xsl:value-of disable-output-escaping="yes" select="substring-after(@Progress,'#') "/>

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

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.

The Content Query Web Part

SharePoint offers some powerful Web Parts that allow you to share content across sites.

The Content Editor Web Part is a universal plug adapter. Put it on a SharePoint page, and suddenly your page gets connected to the rest of the world: you can add html/CSS/JavaScript/ jQuery, embed videos or widgets, display content from other SharePoint sites, etc. To see it in action, explore my blog, Iain’s site or

The Data View Web Part, SharePoint’ s Swiss Army knife, lets you customize SharePoint lists, aggregate them or display them in another site. It can also connect to other data, like RSS feeds or external applications. One constraint: you need SharePoint Designer to customize it. Microsoft has made it more accessible recently, as since last April SPD is free.

MOSS offers a third powerful tool: the Content Query Web Part.

From Microsoft:
Content Query is a Web Part that displays a dynamic set of items based on a query that you build by using a Web browser. You use the query to specify which items are displayed, and you can set presentation options to determine how those items are displayed on the finished page.

I must say that I was disapointed by Microsoft’ s documentation on the CQWP. For example they don’t state up front that the scope of a CQWP is restricted to a site collection. Fortunately the Web provides many helpful references.

If you’re new to the CQWP, you can start with this screencast: 
(Steps 1 to 132 set the scene, and the CQWP makes an entrance at step 133)

Out of the box, you’ll find that the CQWP has limited customization options. It will only return a handful of fields and display them as a simple list. However, you can go beyond this, and the great news for end users is that all this customization can be done on the client side. Be prepared for a tough ride though, customizing the CQWP is not for the faint of heart. To get what you want, you’ll need a minimum knowledge of XSL, or copy/paste skills.

I am not going to repeat what has already be written by others, so I have collected below a list of the best references I have found. I’ll just highlight a few points:
– the CQWP only comes with MOSS, it is not available in wss.
– activate the publishing feature to make the CQWP available in your site collection (Site Actions | Site Settings | Site Collection features)
– you can only query content that resides within the same site collection.
– after activating the publishing feature, end users have access to the stylesheets. They are stored in a dedicated library called the “Style Library”.
– as an end user, you can customize the CQWP by using the tool pane, or you can export it to your computer and edit it (using Notepad, etc.).

 Note that I haven’t included in my list posts that specifically target developers.

Must read

Configuring and Customizing the Content Query Web Part Microsoft Enterprise Content Management (ECM) Team Blog
Customizing the Content Query Web Part and Custom Item Styles Heather Solomon
Display Content Query Web Part Results in a Grid / Table Paul Galvin

Tips and tricks

Content Query Web Part xsl for showing the xml Ishai Sagi

Showing the description of a page in the Content Query Web Part as rich HTML Ishai Sagi

Teach the Content Query Web Part how to display a link list Ishai Sagi

Quick Tip: Content Query Web Part, Lookup Column Value and XSL Paul Galvin

How to customize the Content Query Web Part XSL to aggregate blog posts Henry Ong

Content Query Web Part (CQWP) with Anonymous Access Mike Geyer

 Other references

How to: Customize XSL for the Content Query Web Part MSDN

Customizing the Content Query Web Part XSL Steven Van de Craen

Display data from multiple lists with the Content Query Web Part Microsoft (from within SharePoint Designer)

Add dynamic content to a page Microsoft’s help page on content query

Waldek Mastykarz

Baris Wanschers

Andy Burns

Filter Content Query Web Part by file type Itay Shakury

List of field types

Kartic’s blog MSDN

Content Query Web Part – Field Types Brian Caauwe


Content Query limitations

Content Query Web Part (CQWP) SharePoint 2007 Performance Ranjan Banerji


The Enhanced Content Query Web Part Codeplex project by Ishai Sagi