I'm looking for assistance with setting up the HTML output generated from an XSL file in order to give row results alternating background colors based on changes in a specific node element value. The data is sourced from SQL Server 2008 and the query results are sorted by the "DivisionName" element.
SELECT
d.DivisionName,
CASE
WHEN t.ActualFinish IS NOT NULL
THEN CONVERT(varchar, DATEPART(mm, t.ActualFinish))+ '/' + CONVERT(varchar, DATEPART(dd, t.ActualFinish)) + '/' + CONVERT(varchar, DATEPART(yyyy, t.ActualFinish))
ELSE ' '
END AS "Date",
r.RegionName,
t.Name,
CASE
WHEN t.ScheduleByElement IS NOT NULL
THEN t.ScheduleByElement
ELSE ' '
END AS "ScheduleByElement",
'' AS "Activity",
'' AS "Team",
CASE
WHEN t.WatchStatus IS NOT NULL
THEN CONVERT(varchar, DATEPART(mm, t.WatchStatus))+ '/' + CONVERT(varchar, DATEPART(dd, t.WatchStatus)) + '/' + CONVERT(varchar, DATEPART(yyyy, t.WatchStatus))
ELSE ' '
END AS "Status"
FROM
dbo.Tasks AS t
INNER JOIN dbo.Division AS d
ON t.fk_DivisionID = d.DivisionID
INNER JOIN dbo.Region AS r
ON t.fk_RegionID = r.RegionID
WHERE
(t.OutlineLevel = 3)
AND (t.ScheduleByElement <> '')
AND (t.FileName LIKE 'NVDE%')
AND (t.ActualFinish IS NOT NULL)
ORDER BY d.DivisionName, t.ActualFinish, r.RegionName
FOR XML PATH('DivisonName'), ROOT('CCDN')
Below is the full content of the XSL file:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" indent="yes"/>
<xsl:template match="/ROOT">
<table border="1" cellpadding="6">
<tr bgcolor="#9acd32">
<td>Division</td>
<td>Date</td>
<td>Region</td>
<td>System Name</td>
<td>Schedule by/Plan by Element</td>
<td>Activity</td>
<td>Deployment Engineering Team</td>
<td>Status</td>
</tr>
<xsl:for-each select="CCDN/DivisonName [not(preceding-sibling::DivisionName[1]/@DivisionNameID = @DivisionNameID)]">
<xsl:variable name="DivisionNameID" select="@DivisionNameID" />
<xsl:variable name="bgcolor">
<xsl:choose>
<xsl:when test="position() mod 2 = 1">#FFFFCC</xsl:when>
<xsl:otherwise>#CCFFFF</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:for-each select=".|following-sibling::DivisionName[@DivisionNameID = $DivisionNameID]">
<tr bgcolor="{$bgcolor}">
<xsl:for-each select="./*">
<td>
<xsl:value-of select="." />
</td>
</xsl:for-each>
</tr>
</xsl:for-each>
</xsl:for-each>
</table>
</xsl:template>
I've reviewed similar questions on your site but haven't found one where color is defined based on changes in a specific node element's value. Currently, every other row in the table alternates between #FFFFCC and #CCFFFF regardless of the DivisionName value.
The following HTML example better illustrates the desired outcome. Rows are sorted ascending by divisions A, B, C, and D. Each division has its own distinct background color that alternates per division change.
<style type="text/css">
table.mystyle
{
border-width: 0 0 1px 1px;
border-spacing: 0;
border-collapse: collapse;
border-style: solid;
}
.mystyle td, .mystyle th
{
margin: 0;
padding: 4px;
border-width: 1px 1px 0 0;
border-style: solid;
}
tr.oddcolor
{
Background-color:#FFFFFF;
}
tr.evencolor
{
Background-color:#CCCCCC;
}
</style>
<table class="mystyle">
<tr>
<b>
<td>Division</td>
<td>Date</td>
<td>Region</td>
<td>System Name</td>
<td>Schedule by/Plan by Element</td>
<td>Activity</td>
<td>Deployment Engineering Team</td>
<td>Status</td>
</b>
</tr>
<tr bgcolor="#FFFFCC">
<td>A</td>
<td>3/2/2012</td>
<td>region</td>
<td>xxx</td>
<td>xxxx</td>
<td></td>
<td></td>
<td></td>
</tr>
... <!-- Additional rows removed for brevity -->
</table>
Thank you.