HTML Calculated Column – Updated script v2.1

I have updated the TextToHTML script to add support for preview panes. You can get the new version (v 2.1) on the download page.

SharePoint uses a function called “showpreview1″ to render preview panes. My script appends the “text to HTML” transformation to this default function.

Note that since version 2.0, you don’t need DIV tags anymore in your HTML formulas. The script will identify any HTML string, with upper case tags (DIV, SPAN, IMG, A, etc.) or lower case (div, span, img, a, etc.). For this, the script relies on regular expressions, like this one used for list views:
RegExp("^\\s*<([a-zA-Z]*)(.|\\s)*/\\1?>\\s*$")

If you haven’t heard about the HTML Calculated Column yet, check out these links:
- the method, explained (includes version 1.0 of the script)
- an example: apply color coding to lists
- a formula generator for color coding
- a troubleshooting page

…Or read the whole series (~30 posts).

More jQuery demos: scrolling news, fisheye menu

I have added two jQuery animation demos to my SharePoint site:
- Scrolling news on the home page, based on the jQuery Tools plugin (its Scrollable feature to be precise)
- Dock menu with fisheye effect, based on the Interface plugin

At this point, these are “proofs of concept”, not ready yet for a production environment.

I have also transferred the Star rating demo from my old site. It is based on the star rating widget. For the occasion I have upgraded it with the June 2009 release.

For the record, the following jQuery demos are already present on the site:
- Sparklines (minicharts)
- Slider for the Gantt view (animation)

JavaScript – the language jQuery is based on - is becoming a more and more powerful tool for animations and other visual effects, and is slowly gaining ground in areas where Flash was previously considered the only option. This trend is driven by the development of new libraries/frameworks/plugins, the rapid progress of JavaScript engines in modern browsers, and new standards like the HTML canvas element and CSS3.
If you are interested in this topic, in addition to jQuery take a look at these 10 Impressive JavaScript Animation Frameworks and 10 excellent JavaScript solutions to graphing/charting data.

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

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"/>

to:

<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"/>

Once 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[@Style='Dashboard']" mode="itemstyle">
        <xsl:variable name="SafeLinkUrl">
            <xsl:call-template name="OuterTemplate.GetSafeLink">
                <xsl:with-param name="UrlColumnName" select="'LinkUrl'"/>
            </xsl:call-template>
        </xsl:variable>
        <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:call-template>
        </xsl:variable>
        <xsl:variable name="LinkTarget">
            <xsl:if test="@OpenInNewWindow = 'True'" >_blank</xsl:if>
        </xsl:variable>
        <div>
            <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"/>
            </a>
            <xsl:value-of disable-output-escaping="yes" select="substring-after(@Progress,'#') "/>
        </div><br/>
    </xsl:template>

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.

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

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.

What I’m working on

Just a quick heads-up on what you can expect on my blog and demo site in the weeks to come:

Image rotator: there’s been a lot of interest around my Image Rotator, and I have received several enhancement requests. I am going to push a couple of them into the script generator, and for the rest I’ll publish some advice on how to tweak the code. I am also thinking about creating an XSLT version for the XML Web Part.

Easy Tabs: also a big success. Well, I love them too, and I put them all over the place in my demo site! I’ll publish some explanations on how to customize their look.

Calendars: I need to work on my demo page, to showcase all the customizations described in my blog, and add some more. I am also thinking about building a script generator.

jQuery: several upcoming demos – mostly proofs of concept for now, the tutorials may come later.

HTML calculated column: this method opens up endless possibilities, and I’ll publish a few more examples. Also, people tend to restrict its scope to color coding, so I’ll try to show other applications.

iframes: displaying a list or calendar in another site/site collection/SharePoint server, resizing the Page Viewer Web Part or the Excel Web Access Web Part, all these are popular but difficult topics. I have answers, but some more testing is needed.

Guest posts: if one of my articles helped create a breakthrough in the way you use SharePoint, I’d love to hear your story, and I’ll be happy to work with you on a guest post for my blog. Thanks for sharing!