HTML Calculated Column: solutions for SP 2010 (Part II)

This series assumes that you are familiar with the “HTML Calculated Column” .

Part I: fallback to SP 2007
Part II: edit in SharePoint Designer

In this part II, I am going to show a straightforward method that can be applied via SharePoint Designer.

In SharePoint 2010, lists are rendered through a new component, the XSLT List View (XLV) .

Let’s add to a page a list with an HTML Calculated Column. Naturally, this column will be rendered as text. If we edit the page in SharePoint Designer, in split mode, it will look like this (click picture to enlarge):

The XSLT hierarchy is displayed at the bottom of the page. The tag that renders the field reads as follows:

<xsl:value-of  select="$thisNode/@*[name()=current()/@Name]">

This is a complicated expression, but for our purpose we don’t need to understand it. What we are going to do is tell SharePoint to interpret the field as HTML instead of just text, by adding the disable-output-escaping attribute (see picture below):

<xsl:value-of select="$thisNode/@*[name()=current()/@Name]" disable-output-escaping="yes">

 And here we go, the field is now rendered as HTML:

 The final result in the browser:

Note that the HTML rendering is done in the XSLT view itself. So if you have asynchronous updates enabled, all content refreshes will be rendered as HTML.

How about SP 2007?

We’ve actually already used this method before, in cross-list views:
– with Content Query Web Parts, as demonstrated by Eric Proshuto
– with Data View Web Parts, in my KPI roll-up series

Dealing with a standard Data View Web Part is more difficult, as explained here.

Conclusion: the HTML Calculated Column plays better with the XSLT List View in SP 2010 than it did with the Data View Web Part in SP 2007.

Your feedback and suggestions are welcome, they will guide me in my investigations. Also, let me know if you need more details on the step shown in this post.

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).

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.

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.

Live demo: SharePoint, XML files, and Flash charts


XML driven components are a favorite among designers of dynamic Web sites. They allow to dissociate content from rendering, and greatly facilitate the site maintenance. The most popular are Flash charts, although other technologies are available, like Silverlight.

You can use such components in SharePoint: upload the flash component (e.g. PieFlash.swf) and the data (e.g. PieData.xml) to a document library, then insert the html/JavaScript code in the source editor of a Content Editor Web Part.

In addition, and it is actually the point of this post, SharePoint offers a convenient way to update xml files: the Data Form Web Part. Note that setting up a Data Form Web Part requires SharePoint Designer.

To see it in action, go to my live demo. Note that I am not making credentials publicly available, please contact me by e-mail if you want to play with the form.

Maybe you didn’t know that XML files could be edited directly from within SharePoint pages? If so, the demo page also includes a tutorial on how to set up the Data Form Web Part using SharePoint Designer.

XML files vs. SharePoint lists

Instead of an XML file, another standard approach is to store the data in a SharePoint list. You can then pull the list content in XML format to render the chart.

The advantage of XML files is that they are easier to set up. It just takes a couple minutes to upload the file to SharePoint and link it to a Data Form Web Part. With a SharePoint list, you would have to write the code that grabs the list XML and transforms it into a format that the component can read.

On the other hand, the list approach will be better if you want to benefit from specific SharePoint features (e.g. alerts, version history and permissions at the item level).

My live demo is sponsored by FusionCharts (version 3). Note that the previous version, FusionCharts v2, is available for free (22 chart types).

Maybe time for an update on SharePoint Designer backup/restore?

All day, we’ve been talking about SharePoint Designer becoming available for all. We’ve been talking about all these great opportunities to create Data View Web Parts and workflows, or blow up our sites.

But here is a question to which I haven’t found any clear answer: the backup/restore feature.

From the SharePoint Designer help:

Suppose that your team has just posted their latest quarterly reports to various document libraries on the team site, and you want to back up the site at this critical juncture. Or suppose that your team has been moved to a different group in your organization, so you need to move your team site to another server. You can use Microsoft Office SharePoint Designer 2007 to create a backup copy of a site or subsite, and then re-create that site on the same server or on another server.

Sounds great. FrontPage also had this feature, and I used it quite a lot.

However, when I started using it with SharePoint Designer, I quickly reached a “limit”: the backup/restore feature only worked for sites smaller than 26 MB. The tricky part is that SPD won’t alert you during backup, you’ll only discover the issue when trying to restore the site.

An Internet search confirms that others have had similar issues, but I haven’t seen any mention of this from Microsoft.

Maybe now would be a good time for a clear status update on this issue?