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

Posted on by

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.

CQWPsource 

CQWPfilter

Here is the result:

CQWPtitle

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

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:

CQWPstrings

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:

CQWPfinal

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: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>

18 thoughts on “Case study: KPI roll-up in MOSS (Part II)”

  1. Pingback: Case study: KPI roll-up in MOSS (Part I) « Path to SharePoint

  2. Pingback: SharePoint Links for 2009-06-27 | MarkSimon.de

  3. Pingback: Links (6/28/2009) « Steve Pietrek – Everything SharePoint and Office

  4. I am stuck here:
    “We need a new template in the ItemStyle.xsl file in the Style Library – I’ll call it “Dashboard””

    How do i do this? Where is the Style Library? Is it unique to the CQWP?

    Otherwise- accomplishing this!

  5. I can’t get the business unit level dashboard to display the KPI columns.

    I have the executive dashboard displaying correctly per the instructions in this article.

    It gathers content from list in subsites. The lists in the subsites display the KPIU columns per Part One of this article.

    On the business unit level dashboard page, I have one CQWP, just like the division level dashboard page.

    Source: show items fro the following site and subsites: (it pulls the correct content from the lists.)

    List Type: Tasks

    Additional Filters: (I have three ‘or’ criteria to get all of the values of ‘Visibility’.)

    In Styles, I have:
    Group Style: Default
    Item Style: Dashboard (both the same as the division level CQWP)

    What is displayed title and it is rendering the from the XSLT.

    No KPI columns.

    any thoughts?

    • Solved. I forgot to export the CQWP for the business unit portal, edit it and add the KPI fields to the CommonViewFields property. Works now.

      Thanks for this series of articles, very helpful!

  7. Sorted it 🙂
    Edited the webpart to read…
    Progress;Indicator;StartDate, DateTime

    and added the following to ItemStyle.xsl
    Age:

  8. A great example that I have worked through and reproduced. I do have 2 questions though:

    1. In the image at the top of the page you are displaying a “grid” type of dashboard. I got close to this using the “Group By” and selecting a number of columns though mine is still displaying the column header in the detail as well – is there any way to remove this or have I done it incorrectly?

    2. There seems to be a limitation on the CQWP of 5 columns. This may be restrictive for a dashboard – do you know if it is possible to have more than 5 columns?

    Thanks

    Dave

  9. Kudos for the case study. I have it working and mostly great!

    One smalish problem. Occasionally the “eye candy” (indicator and progress bar) don’t render for a prived account, and never seem to render for non prived accounts.

    On the prived account, the action of going into the modify web part and exiting causes it to render again. Non prived users don’t have this option…

    Any clues?

  10. prived account = SharePoint administrators account

    nonprived = (not exhaustively tested, but anybody else)

    The default view of the KPI page showing the rolled up task items marked “executive” visability show up with color indicators and progress bars when logged in as admin. If I then sign in as a different (nonprived) user, the indicator and progress bar just go away. They are not replaced with html code text or anything…

    I’m confused.

  11. figured it out! the difference was not having published the ItemStyle.xsl file in the Style Library, duh!

  12. Pingback: HTML Calculated Column: solutions for SP 2010 (Part II) « Path to SharePoint

  13. This is fantastic! I’m not a developer but just figured out how to use the HTML Calculated Columns and look forward to trying this out as well. One thing I’m wondering is if this actually not a “roll up” in the sense of having one metric that is based on a bunch of “sub metrics”, such as an overall Business Unit “% Complete” that is calculated based on all of the % Complete values for all Business Unit visible projects. I only raise this point because a true “roll up” is something I’m interested in, though I imagine it gets into some fairly heavy lifting to do the required calculations. I just figured I would post in case you are ever looking for new topics to tackle. Regardless, thank you so much for sharing all of this information; it is really helping me take my sites and lists to the next level!

    • You’re correct, this is a content roll-up, not data aggregation. The live example actually does a simple aggregation (counting the number of items). This is done in xslt in the Data View Web Part.

Comments are closed.