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>