Customizing the MS Multiple Budget and Project tem

[oscarb]

I am not a scripting person, so I need some help! I am hoping one of you can help me with this.

I have been using one of the MS fab 40 free site templates to produce a site that can track multiple projects. It is very useful but needed some customization for us since we don't do much with cost at the task level and dont track budgets through the project this way.

I have been able to modify it so it useful to us, but I cant seem to get the dashbaord the way I want it. I started with the buit-in dashboard and stripped out the xls code that put the budget vs cost stuff in. That left with KPI's showing how many overdue tasks and how many overdue issues I had for each projecct. Perfect!

Now i just wanted to add a 3rd column for to show how many overdue Milestones there are. (See attached screen capture for what I am trying to accomplish) All three tasks, issues and mielstones are lists that are built as part of the site from the template. I have tried to add the milestones to the xls script, but can not get the count to work. I get tthe column to show the KPI image to show, but can not get the count to count the number of overdue milestones from the milestone list the same way it does for the tasks and issues. It is staying a count of 0 for each item.

Can someone look at my modified code below the screen capture and tell me why it is not working?

avcdhg.bay.livefilestore.com/y1pNOLE9sGsphEWZe3wU8t4iUgrs5YKbaygnvVoAUGQTjNbbMzZ0LoEKAFr6YKQwJA2K6sy6FxCB8YJdCZewYdgCaXuK9YWhGTI/Project%20dashboard%20modified.png?psid=1


Modified Code:
<xsl:stylesheet xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:WebControls="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
<xsl:output method="html" indent="no"/>
<xsl:decimal-format NaN="" grouping-separator="," decimal-separator="."/>
<xsl:param name="dvt_apos">'</xsl:param>
<xsl:variable name="dvt_1_automode">0</xsl:variable>
<xsl:template match="/" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:WebControls="Microsoft.SharePoint.WebControls">
<xsl:call-template name="dvt_1"/>
</xsl:template>
<xsl:template name="dvt_1">
<xsl:param name="ParentPath"/>
<xsl:variable name="dvt_StyleName">Table</xsl:variable>
<xsl:variable name="Rows" select="/dsQueryResponse/Projects/Rows/Row"/>
<SCRIPT type="text/javascript">
function GetSource(defaultSource)
{
return escapeProperly(STSPageUrlValidation(window.location.href));
}
</SCRIPT>
<table border="0" width="100%" cellpadding="2" cellspacing="0">
<tr valign="top">
<xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
<th class="ms-vh" width="1" nowrap="nowrap">
</th>
</xsl:if>
<th class="ms-vh" nowrap="" width="" style="vertical-align: bottom;" > Project Name</th>
<th class="ms-vh" nowrap="" style="text-align: right;vertical-align: bottom;" width="15%"> Overdue Milestones</th>
<th class="ms-vh" nowrap="" style="text-align: right;vertical-align: bottom;" width="15%"> Overdue Issues </th>
<th class="ms-vh" nowrap="" style="text-align: right;vertical-align: bottom;" width="15%"> Overdue Tasks </th>
</tr>
<xsl:call-template name="dvt_1.body">
<xsl:with-param name="ParentPath" select="$ParentPath"/>
<xsl:with-param name="Rows" select="$Rows"/>
</xsl:call-template>
</table>
</xsl:template>
<xsl:template name="dvt_1.body">
<xsl:param name="ParentPath"/>
<xsl:param name="Rows"/>
<xsl:for-each select="$Rows">
<xsl:variable name="SrcPos" select="count(preceding-sibling::*[local-name()=local-name(current()) and namespace-uri()=namespace-uri(current())])+1"/>
<xsl:call-template name="dvt_1.rowview">
<xsl:with-param name="ParentPath" select="$ParentPath"/>
<xsl:with-param name="SrcPos" select="$SrcPos"/>
</xsl:call-template>
</xsl:for-each>
</xsl:template>
<xsl:template name="dvt_1.rowview">
<xsl:param name="ParentPath"/>
<xsl:param name="SrcPos"/>
<xsl:variable name="dvt_ParentRow" select="current()" />
<xsl:variable name="FilteredPeerTasks" select="../../../Project_Tasks/Rows/Row[@project=$dvt_ParentRow/@title]" />
<xsl:variable name="FilteredPeerIssues" select="../../../Project_Issues/Rows/Row[@project=$dvt_ParentRow/@title]" />
<xsl:variable name="FilteredPeerMilestones" select="../../../Project_Milestones/Rows/Row[@project=$dvt_ParentRow/@title]" />

<xsl:variable name="FilterPeerTasksMod">
<xsl:for-each select="$FilteredPeerTasks">

</xsl:for-each>
</xsl:variable>
<tr>
<xsl:if test="position() mod 2 = 0" ddwrt:cf_ignore="1">
<xsl:attribute name="class">ms-alternating</xsl:attribute>
</xsl:if>
<xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
<td class="ms-vb" width="1" nowrap="nowrap">
<span ddwrt:amkeyfield="ID,ID" ddwrt:amkeyvalue="concat(ddwrt:EscapeDelims(string(@id)),',',ddwrt:EscapeDelims(string(@id)))" ddwrt:ammode="view"/>
</td>
</xsl:if>
<td class="ms-vb" style="height: 22px; vertical-align:middle;" >
<a href="Lists/Projects/DispForm.aspx?ID={@id}" onclick="javascript:this.href = unescapeProperly(escape(this.href)); GoToLink(this); return false;" target="_self"><xsl:value-of select="@title" /></a>
</td>

<!--Active Milestones-->
<td class="ms-vb" style="text-align:right; height: 22px; vertical-align:middle;">
<span style="vertical-align:middle">
<xsl:value-of select="count($FilteredPeerMilestones[@complete = 'FALSE' and (substring-before(@duedate,'-') < substring-before(ddwrt:TodayIso(),'-')
or
substring(@duedate,6,2) < substring(ddwrt:TodayIso(),6,2)
or
substring(@duedate,9,2) < substring(ddwrt:TodayIso(),9,2))])"/>
</span>
<xsl:text xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" ddwrt:nbsp-preserve="yes" disable-output-escaping="yes">&nbsp;</xsl:text>  
<xsl:choose>
<xsl:when test="count($FilteredPeerMilestones[@complete = 'FALSE' and (substring-before(@duedate,'-') < substring-before(ddwrt:TodayIso(),'-')
or
substring(@duedate,6,2) < substring(ddwrt:TodayIso(),6,2)
or
substring(@duedate,9,2) < substring(ddwrt:TodayIso(),9,2))]) = 0 ">
<img src="images/KPI-0.gif" align="absmiddle"/>
</xsl:when>
<xsl:when test="count($FilteredPeerMilestones[@complete = 'FALSE' and (substring-before(@duedate,'-') < substring-before(ddwrt:TodayIso(),'-')
or
substring(@duedate,6,2) < substring(ddwrt:TodayIso(),6,2)
or
substring(@duedate,9,2) < substring(ddwrt:TodayIso(),9,2))]) < 5">
<img src="images/KPI-1.gif" align="absmiddle"/>
</xsl:when>
<xsl:otherwise>
<img src="images/KPI-2.gif" align="absmiddle"/>
</xsl:otherwise>
</xsl:choose>
</td>
<!--Active Issues-->
<td class="ms-vb" style="text-align:right; height: 22px; vertical-align:middle;">
<span style="vertical-align:middle">
<xsl:value-of select="count($FilteredPeerIssues[status = 'Active' and (substring-before(@duedate,'-') < substring-before(ddwrt:TodayIso(),'-')
or
substring(@duedate,6,2) < substring(ddwrt:TodayIso(),6,2)
or
substring(@duedate,9,2) < substring(ddwrt:TodayIso(),9,2))])"/>
</span>
<xsl:text xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" ddwrt:nbsp-preserve="yes" disable-output-escaping="yes">&nbsp;</xsl:text>  
<xsl:choose>
<xsl:when test="count($FilteredPeerIssues[status = 'Active' and (substring-before(@duedate,'-') < substring-before(ddwrt:TodayIso(),'-')
or
substring(@duedate,6,2) < substring(ddwrt:TodayIso(),6,2)
or
substring(@duedate,9,2) < substring(ddwrt:TodayIso(),9,2))]) = 0 ">
<img src="images/KPI-0.gif" align="absmiddle"/>
</xsl:when>
<xsl:when test="count($FilteredPeerIssues[status = 'Active' and (substring-before(@duedate,'-') < substring-before(ddwrt:TodayIso(),'-')
or
substring(@duedate,6,2) < substring(ddwrt:TodayIso(),6,2)
or
substring(@duedate,9,2) < substring(ddwrt:TodayIso(),9,2))]) < 5">
<img src="images/KPI-1.gif" align="absmiddle"/>
</xsl:when>
<xsl:otherwise>
<img src="images/KPI-2.gif" align="absmiddle"/>
</xsl:otherwise>
</xsl:choose>
</td>
<!-- Overdue Tasks -->
<td class="ms-vb" style="text-align:right; height: 22px; vertical-align:middle;">
<span style="vertical-align:middle">
<xsl:value-of select="count($FilteredPeerTasks[status != 'Completed' and (substring-before(@duedate,'-') < substring-before(ddwrt:TodayIso(),'-')
or
substring(@duedate,6,2) < substring(ddwrt:TodayIso(),6,2)
or
substring(@duedate,9,2) < substring(ddwrt:TodayIso(),9,2))])"/>
</span>
<xsl:text xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" ddwrt:nbsp-preserve="yes" disable-output-escaping="yes">&nbsp;</xsl:text>  
<xsl:choose>
<xsl:when test="count($FilteredPeerTasks[status != 'Completed' and (substring-before(@duedate,'-') < substring-before(ddwrt:TodayIso(),'-')
or
substring(@duedate,6,2) < substring(ddwrt:TodayIso(),6,2)
or
substring(@duedate,9,2) < substring(ddwrt:TodayIso(),9,2))]) = 0 ">
<img src="images/KPI-0.gif" align="absmiddle"/>
</xsl:when>
<xsl:when test="count($FilteredPeerTasks[status != 'Completed' and (substring-before(@duedate,'-') < substring-before(ddwrt:TodayIso(),'-')
or
substring(@duedate,6,2) < substring(ddwrt:TodayIso(),6,2)
or
substring(@duedate,9,2) < substring(ddwrt:TodayIso(),9,2))]) < 5">
<img src="images/KPI-1.gif" align="absmiddle"/>
</xsl:when>
<xsl:otherwise>
<img src="images/KPI-2.gif" align="absmiddle"/>
</xsl:otherwise>
</xsl:choose>
</td>
</tr>
</xsl:template>
</xsl:stylesheet>

[oscarb]

I should also point out that I orginally tried using the @complete = 'No' in my script and that did not work, but since the Complete column in Milestones is a Yes/No checkbox, and when the data is exported to excel the No shows as FALSE, I switched the script to @complete = 'FALSE' to try to get it to work.

It did not work either way. I also tried creating another column called Completed and made it a choice with my choices Yes and No. Then I switched the 3 areas of the script to @completed = 'No'. This also did not work. I had high hopes of this after reading that a yes/no checkbox maybe my problem. That is why I am stumped.