Trick or Treat? Text to html, the wicked no-code way (Part II)

At the end of the previous post, we had this aha moment when we realized that rendering a field as rich text might not be that difficult, after all.

I’m back, after taking the time to set up a brand new demo to confirm our assumptions:



It works! As you guessed, just using a SharePoint Designer workflow to copy the content of the calculated column to a rich text field did the trick. For the record, here are the detailed steps:

  1. Create a list (I used the Tasks template here).
  2. Create a calculated column called cc, with the html formula. For the above demo I used calculated gradients (just did a copy/paste of the formula in the post).
  3. Create a column of type “Multiple lines of text” called HTMLcc, and specify that the type of text is “Enhanced rich text”.
  4. Create a one step workflow that copies the cc column to the HTMLcc column, every time an item is created or modified (screenshot below)
  5. We’re done!


The benefit of this approach is that we now have a clean field that renders our graphic – no ugly code around. I left the cc field visible for the purpose of the demo, but in production I would naturally make it a hidden field.

Although this looks great and easy, there are a couple gotchas to be aware of, if you decide to use this trick.

First, it is rather heavy: two fields and one workflow! And of course it won’t work for you if SharePoint Designer is not allowed in your environment (or you’ll need another way to copy cc to HTMLcc).

Second, it won’t help in views such as calendars, as these views don’t render rich text fields.

Third, as with any workflow action there’s a latency. The HTMLcc field will not refresh as quickly as other fields.

One last, important point: SharePoint is going to “sanitize” the html you copy to the HTMLcc field, and might decide to remove some of the content. This can actually be considered a good thing, as it addresses a security concern I reported in a previous post. On the other hand, it will block some script-based enhancements, like hover effects or countdowns.

Enjoy, and see you for next year’s trick: how to sort a list by month name. Happy Halloween!


Trick or Treat? Text to html, the wicked no-code way

As I am actively working on a new training program called SPELL, it recently occurred to me that I should make Halloween a special day. So let’s start the tradition, the SPELL program annual trick or treat!

I have described many times my HTML Calculated Column technique on this blog. The idea is to use a calculated column to build html content. You have certainly already tried them out, for example for color coded calendars, progress bars, or KPIs.

The catch is that SharePoint won’t naturally interpret the string as html, so extra work is needed to do the conversion – usually done using JavaScript or xslt.

Now, rendering an html string as actual html is something any rich text editor can do, why is it so complicated with SharePoint? Oh yes, here is the thing: the output of calculated columns is just plain text. Ah, if only I could change it to rich text! Or find a way to transfer the content of my calculated column to a field that understands rich text!


[To be continued]

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