In my the first two articles of this series, Visual Indicators for the Masses and Embed KPIs as a List View, I discussed ways to create simple KPIs in WSS. This article will show how to make calculations on top of date-time fields.

Calculated fields do not update themselves until the item itself is updated. Dessie Lunsford proposed a workaround, so we cannot use calculated columns nor built in KPI web parts. The approach below does not have that limitation.

Dashboard based on date-time fields (relative to Today)

Let’s say we have a requirement that states: Show a list of active issues with a status indicator that alerts you if a task is overdue.

Create the following:

1. An Issues list based on the default Issues list template
2. A list view for issues list
   • Convert it XSTL view as described in Embed KPIs as a List View

The date must be transformed to the date numeric format – Julian Day in order to calculate the difference between two dates . It is the interval of time in days and fractions of a day, since 4713 BC January 1, Greenwich noon. The original function is delivered with WSS Time Track Template.

Use the following XLST code to calculate the Due Date. It calculates the difference (as an integer) between two parameters: StartDate and TodayDate. Add the code to your list view stylesheet.

  <xsl:template name="DateDiff">
    <xsl:param name="StartDate"></xsl:param>
    <xsl:param name="TodayDate"></xsl:param>

    <xsl:variable name="JulianToday">
      <xsl:call-template name="calculate-julian-day">
        <xsl:with-param name="Year" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),0,5)"/>

        <xsl:with-param name="Month" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),5,2)"/>

        <xsl:with-param name="Day" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),7,2)"/>
    </xsl:call-template>
    </xsl:variable>
    <xsl:variable name="JulianStartDate">
      <xsl:call-template name="calculate-julian-day">
        <xsl:with-param name="Year" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),0,5)"/>

        <xsl:with-param name="Month" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),5,2)"/>

        <xsl:with-param name="Day" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),7,2)"/>
    </xsl:call-template>
    </xsl:variable>

    <xsl:value-of select="$JulianStartDate - $JulianToday"></xsl:value-of>
  </xsl:template>

  <xsl:template name="calculate-julian-day">
    <xsl:param name="Year"/>
    <xsl:param name="Month"/>
    <xsl:param name="Day"/>    

    <xsl:variable name="JulianDay" select="floor((14 - $Month) div 12)"/>
    <xsl:variable name="JulianYear" select="$Year + 4800 - $JulianDay"/>
    <xsl:variable name="JulianMonth" select="$Month + 12 * $JulianDay - 3"/>    

    <xsl:value-of select="$Day + floor((153 * $JulianMonth + 2) div 5) + $JulianYear * 365 + floor($JulianYear div 4) - floor($JulianYear div 100) + floor($JulianYear div 400) - 32045"/>
  </xsl:template>

In order to show the indicator add the following code to your output. It will show a red icon for overdue task, a yellow icon for a task that will be overdue next week, and a green icon for other tasks.

<td class="ms-vb">
    <xsl:variable name="DueDateDiff">
        <xsl:call-template name="DateDiff">
            <xsl:with-param name="StartDate" select="@DueDate"></xsl:with-param>
            <xsl:with-param name="TodayDate" select="ddwrt:TodayIso()"></xsl:with-param>
        </xsl:call-template>
    </xsl:variable>

    <img alt="Indicator" >
        <xsl:attribute name="src">
            <xsl:choose>
                <xsl:when test="$DueDateDiff &lt; 0">/_layouts/images/ewr218m.gif</xsl:when>
                <xsl:when test="$DueDateDiff &lt;= 7">/_layouts/images/ewr219m.gif</xsl:when>
                <xsl:otherwise>/_layouts/images/ewr217m.gif</xsl:otherwise>
            </xsl:choose>
        </xsl:attribute>
    </img>
</td>

The final output will look like this one.

Conclusion

WSS has limitations when it comes to creating KPIs, With these three articles, I have demonstrated a few ways to generate KPI type indicators within WSS. I hope it has given you some new ideas and you’ll let me know how your experiments turn out .

Author: Toni Frankola
SharePoint Use Cases
Toni started his Web adventure in late 90’s and has been working with various web technologies ever since.

Toni is leading project engagements and managing a team of consultants specializing in Microsoft technologies. His primary focus is on Microsoft Office SharePoint and Dynamics CRM. He works at Perpetuum Mobile, a Microsoft Gold Partner from Croatia.