This chapter is from the book
This chapter is from the book
This chapter is from the book
The Cross Tab Stylesheet
In the next example, we'll change the stylesheet in order to create a cross tab report on the same dataset. Unlike the previous stylesheet, this one is not completely generic, but depends upon a particular dataset. However, with the addition of more parameters set within the JSP, this stylesheet can be converted into a more generic form.
The CrossTab.xsl stylesheet begins with a parameter called NumClinics, whose default value is 5. This parameter will be used as the counter in the looping template used to iterate the rows of the resulting cross tab report:
<xsl:param name="NumClinics" select="5"/>
Next are two templates. The first begins by matching on the root of the XML document and creating the resulting table labels:
<xsl:template match="/">
<table border="1">
<!-- crosstab labels of first row-->
<tr><th> </th><th>Blue</th><th>Green</th><th>Orange</th>
<th>Purple</th><th>Red</th><th>Yellow</th><th>Totals:</th>
</tr>
The rest of this template calls the crosstab template, and our looping begins. Notice that the call-template tag contains the with-param tag, which will set the parameter denoted by the name attribute value found in the template. In this case, the template is called with the parameter Clinics set to 1:
<!-- begin loop to create crosstab body -->
<xsl:call-template name="crosstab" >
<xsl:with-param name="Clinics" select="1"/>
</xsl:call-template>
</table>
</xsl:template>
Our looping template called crosstab then begins with the Clinic parameter creation and output via the value-of tag, surrounded by some HTML table-related tags:
<xsl:template name="crosstab">
<xsl:param name="Clinics"/>
<tr><th><xsl:value-of select="$Clinics" /></th>
Next is the output of the count() of the number of elements meeting the criteria as stated in the XPath statement. This particular statement counts the number of child elements (that is, records) that match the following criteria: The statement requires the RCD_color element to contain the text data blue, and the RCD_clinic element must contain the text data that $Clinics resolves to. In this case, the $Clinics parameter was set to 1 upon the start of this template. As a result, this value-of will return the number of records with data blue, and 1:
<td><xsl:value-of select="count(*/*[RCD_color='blue' and
RCD_clinic=$Clinics])" /></td>
The following lines also follow the same format as the previous fragment, except with a different color value:
<td><xsl:value-of select="count(*/*[RCD_color='green' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='orange' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='purple' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='red' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='yellow' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_clinic=$Clinics])" /></td>
</tr>
This closes the creation of one row of the cross tab report being created. The only thing left is to iterate through the template the correct number of times to create the remaining rows.
This is done by using an if tag and making sure $Clinics is less than $NumClinics. If the condition is true, then the crosstab template is called again with the incremented value of $Clinics:
<!-- continue looping when condition met -->
<xsl:if test="$Clinics < $NumClinics">
<xsl:call-template name="crosstab">
<xsl:with-param name="Clinics" select="$Clinics + 1"/>
</xsl:call-template>
</xsl:if>
And thus the template is recursive, and any recursive template can be created using the same method.
Finally, the last row of the table is output when the two parameters are equal, and the recursion is complete:
<xsl:if test="$Clinics=$NumClinics">
<tr><th>Totals:</th>
<td><xsl:value-of select="count(*/*[RCD_color='blue'])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='green'])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='orange'])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='purple'])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='red'])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='yellow'])" /></td>
<td><xsl:value-of select="count(*/*)" /></td>
</tr>
</xsl:if>
Besides some closing tags, this completes the cross tab stylesheet. The complete stylesheet can be seen in Listing 11.7 and should be saved as \webapps\xmlbook\chapter11\CrossTab.xsl.
Listing 11.7 CrossTab.xsl
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:param name="NumClinics" select="5"/>
<xsl:output method="html" />
<xsl:template match="/">
<table border="1">
<!-- crosstab labels of first row-->
<tr><th> </th><th>Blue</th><th>Green</th><th>Orange</th>
<th>Purple</th><th>Red</th><th>Yellow</th><th>Totals:</th>
</tr>
<!-- begin loop to create crosstab body -->
<xsl:call-template name="crosstab" >
<xsl:with-param name="Clinics" select="1"/>
</xsl:call-template>
</table>
</xsl:template>
<xsl:template name="crosstab">
<xsl:param name="Clinics"/>
<tr><th><xsl:value-of select="$Clinics" /></th>
<td><xsl:value-of select="count(*/*[RCD_color='blue' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='green' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='orange' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='purple' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='red' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='yellow' and
RCD_clinic=$Clinics])" /></td>
<td><xsl:value-of select="count(*/*[RCD_clinic=$Clinics])" /></td>
</tr>
<!-- continue looping when condition met -->
<xsl:if test="$Clinics < $NumClinics">
<xsl:call-template name="crosstab">
<xsl:with-param name="Clinics" select="$Clinics + 1"/>
</xsl:call-template>
</xsl:if>
<!-- table foot -->
<xsl:if test="$Clinics=$NumClinics">
<tr><th>Totals:</th>
<td><xsl:value-of select="count(*/*[RCD_color='blue'])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='green'])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='orange'])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='purple'])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='red'])" /></td>
<td><xsl:value-of select="count(*/*[RCD_color='yellow'])" /></td>
<td><xsl:value-of select="count(*/*)" /></td>
</tr>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
The last thing that we need to do is to change the stylesheet being used in DBtoXML.jsp in Listing 11.5 to CrossTab.xsl. This can be achieved by changing the value of the variable stylesheet from
String stylesheet = "TableSort.xsl"
to
String stylesheet = "CrossTab.xsl"
After this has been done, the results will look like those in Figure 11.2. Note that the actual counts will vary depending on the data in your database.
)
Figure 11.2. Results of DBtoXML.jsp, RStoXML.java, and CrossTab.xsl.