XSLT 1. 0 Grouping by two attributes and creating table

Narendra.Patil · September 23rd, 2009, 02:28 PM

Just Great...

Thanks a lot...

Narendra.Patil · September 29th, 2009, 09:13 AM

Dear Martin,

I have a XML file like
<?xml version="1.0" encoding="UTF-8"?>
<Body>
<Data Continent="Africa" Country="A" Region="North" Remarks="" DueStatus="Sold"/>
<Data Continent="Africa" Country="A" Region="North" Remarks="Good" DueStatus="Sold"/>
<Data Continent="Africa" Country="A" Region="South" Remarks="Excellent" DueStatus="Sold"/>
<Data Continent="Africa" Country="A" Region="South" Remarks="" DueStatus="Not Sold"/>
<Data Continent="Africa" Country="B" Region="East" Remarks="" DueStatus="Not Sold"/>
<Data Continent="Africa" Country="B" Region="East" Remarks="" DueStatus="Sold"/>
<Data Continent="Africa" Country="B" Region="East" Remarks="" DueStatus="Sold"/>
<Data Continent="Africa" Country="B" Region="East" Remarks="" DueStatus="Sold"/>
</Body>

I want to generate a report for the product 'Shampoo' based on common country and region BUT one criteria i have to add for Sold and Not Sold. The report is like

Following is the report for
Shampoo Sold

A - North
2 rows will come here
A - South
1 row here
B - East
3 rows here

B]Shampoo Not Sold[/B]
A - South
1 row here
B- East
1 Row here

Please let me know how should be the xls.

If the "Remarks" field is blank how should form the proper cell? with table border=1

Thanks in advance...

Martin Honnen · September 29th, 2009, 09:47 AM

Here is a sample stylesheet:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">
  
  <xsl:output method="html" indent="yes"
    doctype-system="http://www.w3.org/TR/html4/strict.dtd" doctype-public="-//W3C//DTD HTML 4.01//EN"/>
  
  <xsl:key name="k1" match="Data" use="@DueStatus"/>
  <xsl:key name="k2" match="Data" use="concat(@DueStatus, '|', @Country, '|', @Region)"/>
  
  <xsl:template match="/">
    <html lang="en">
      <head>
        <title>Example</title>
        <style type="text/css">
        table { empty-cells: show; }
        </style>
      </head>
      <body>
        <h1>Example</h1>
        <xsl:apply-templates/>
      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="Body">
    <xsl:for-each select="Data[generate-id() = generate-id(key('k1', @DueStatus)[1])]">
      <h2>Shampoo <xsl:value-of select="@DueStatus"/></h2>
      <table border="1">
        <thead>
          <th>Country</th>
          <th>Region</th>
          <th>Remarks</th>
        </thead>
        <tbody>
          <xsl:for-each select="key('k1', @DueStatus)[generate-id() = generate-id(key('k2', concat(@DueStatus, '|', @Country, '|', @Region))[1])]">
            <xsl:variable name="group" select="key('k2', concat(@DueStatus, '|', @Country, '|', @Region))"/>
            <tr>
              <th rowspan="{count($group) + 1}"><xsl:value-of select="@Country"/></th>
              <th rowspan="{count($group) + 1}" align="left"><xsl:value-of select="@Region"/></th>
            </tr>
            <xsl:for-each select="$group">
              <tr>
                <td>
                  <xsl:choose>
                    <xsl:when test="normalize-space(@Remarks)">
                      <xsl:value-of select="@Remarks"/>
                    </xsl:when>
                    <xsl:otherwise> </xsl:otherwise>
                  </xsl:choose>
                </td>
              </tr>
            </xsl:for-each>
          </xsl:for-each>
        </tbody>
      </table>
    </xsl:for-each>
  </xsl:template>

</xsl:stylesheet>

Result looks as follows:

Code:

<!DOCTYPE html
  PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
   <head>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
   
      <title>Example</title><style type="text/css">
        table { empty-cells: show; }
        </style></head>
   <body>
      <h1>Example</h1>
      <h2>Shampoo Sold</h2>
      <table border="1">
         <thead>
            <th>Country</th>
            <th>Region</th>
            <th>Remarks</th>
         </thead>
         <tbody>
            <tr>
               <th rowspan="3">A</th>
               <th rowspan="3" align="left">North</th>
            </tr>
            <tr>
               <td>&nbsp;</td>
            </tr>
            <tr>
               <td>Good</td>
            </tr>
            <tr>
               <th rowspan="2">A</th>
               <th rowspan="2" align="left">South</th>
            </tr>
            <tr>
               <td>Excellent</td>
            </tr>
            <tr>
               <th rowspan="4">B</th>
               <th rowspan="4" align="left">East</th>
            </tr>
            <tr>
               <td>&nbsp;</td>
            </tr>
            <tr>
               <td>&nbsp;</td>
            </tr>
            <tr>
               <td>&nbsp;</td>
            </tr>
         </tbody>
      </table>
      <h2>Shampoo Not Sold</h2>
      <table border="1">
         <thead>
            <th>Country</th>
            <th>Region</th>
            <th>Remarks</th>
         </thead>
         <tbody>
            <tr>
               <th rowspan="2">A</th>
               <th rowspan="2" align="left">South</th>
            </tr>
            <tr>
               <td>&nbsp;</td>
            </tr>
            <tr>
               <th rowspan="2">B</th>
               <th rowspan="2" align="left">East</th>
            </tr>
            <tr>
               <td>&nbsp;</td>
            </tr>
         </tbody>
      </table>
   </body>
</html>

Narendra.Patil · September 29th, 2009, 10:52 AM

Hi Martin,

Sorry i missed the date attribute
<?xml version="1.0" encoding="UTF-8"?>
<Body>
<Data Continent="Africa" Country="A" Region="North" Date="01-01-2008" Remarks="" DueStatus="Sold"/>
<Data Continent="Africa" Country="A" Region="North" Date="12-01-2008" Remarks="Good" DueStatus="Sold"/>
<Data Continent="Africa" Country="A" Region="South" Date="21-02-2008" Remarks="Excellent" DueStatus="Sold"/>
<Data Continent="Africa" Country="A" Region="South" Date="01-01-2007" Remarks="" DueStatus="Not Sold"/>
<Data Continent="Africa" Country="B" Region="East" Date="01-01-2007" Remarks="" DueStatus="Not Sold"/>
<Data Continent="Africa" Country="B" Region="East" Date="24-01-2008" Remarks="" DueStatus="Sold"/>
<Data Continent="Africa" Country="B" Region="East" Date="01-02-2008" Remarks="" DueStatus="Sold"/>
<Data Continent="Africa" Country="B" Region="East" Date="03-01-2008" Remarks="" DueStatus="Sold"/>
</Body>

and the output expected in HTML is
<html>
<head>
<title>Example</title>
</head>
<body>
<p>Shampoo Sold</p>
<table border="1">
<tr>
<th colspan="3">A- North</th>
</tr>
<tr>
<th>Date</th>
<th>Remarks</th>
<th>Status</th>
</tr>
<tr>
<td>01-01-2008</td>
<td></td>
<td>Sold</td>
</tr>
<tr>
<td>12-01-2008</td>
<td>Good</td>
<td>Sold</td>
</tr>
</table>
<table border="1">
<tr>
<th colspan="3">A- South</th>
</tr>
<tr>
<th>Date</th>
<th>Remarks</th>
<th>Status</th>
</tr>
<tr>
<td>21-02-2008</td>
<td>Excellent</td>
<td>Sold</td>
</tr>
</table>
<table border="1">
<tr>
<th colspan="3">B- East</th>
</tr>
<tr>
<th>Date</th>
<th>Remarks</th>
<th>Status</th>
</tr>
<tr>
<td>24-01-2008</td>
<td>Excellent</td>
<td>Sold</td>
</tr>
<tr>
<td>01-02-2008</td>
<td></td>
<td>Sold</td>
</tr>
<tr>
<td>03-01-2008</td>
<td></td>
<td>Sold</td>
</tr>
</table>
<p>Shampoo NOT Sold</p>
<table border="1">
<tr>
<th colspan="3">A- South</th>
</tr>
<tr>
<th>Date</th>
<th>Remarks</th>
<th>Status</th>
</tr>
<tr>
<td>01-01-2007</td>
<td></td>
<td>Not Sold</td>
</tr>
</table>
<table border="1">
<tr>
<th colspan="3">B- East</th>
</tr>
<tr>
<th>Date</th>
<th>Remarks</th>
<th>Status</th>
</tr>
<tr>
<td>01-01-2007</td>
<td></td>
<td>Not Sold</td>
</tr>
</table>

</body>
</html>

And how to show the empty cells with border

Shampoo Sold

A- North
Date Remarks Status
01-01-2008 Sold
12-01-2008 Good Sold
A- South
Date Remarks Status
21-02-2008 Excellent Sold
B- East
Date Remarks Status
24-01-2008 Excellent Sold
01-02-2008 Sold
03-01-2008 Sold

Shampoo NOT Sold

A- South
Date Remarks Status
01-01-2007 Not Sold
B- East
Date Remarks Status
01-01-2007 Not Sold

Thanks...

Martin Honnen · September 29th, 2009, 11:07 AM

Here is an adapted stylesheet:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">
  
  <xsl:output method="html" indent="yes"
    doctype-system="http://www.w3.org/TR/html4/strict.dtd" doctype-public="-//W3C//DTD HTML 4.01//EN"/>
  
  <xsl:key name="k1" match="Data" use="@DueStatus"/>
  <xsl:key name="k2" match="Data" use="concat(@DueStatus, '|', @Country, '|', @Region)"/>
  
  <xsl:template match="/">
    <html lang="en">
      <head>
        <title>Example</title>
        <style type="text/css">
        table { empty-cells: show; }
        </style>
      </head>
      <body>
        <h1>Example</h1>
        <xsl:apply-templates/>
      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="Body">
    <xsl:for-each select="Data[generate-id() = generate-id(key('k1', @DueStatus)[1])]">
      <h2>Shampoo <xsl:value-of select="@DueStatus"/></h2>
      <table border="1">
        <tbody>
          <xsl:for-each select="key('k1', @DueStatus)[generate-id() = generate-id(key('k2', concat(@DueStatus, '|', @Country, '|', @Region))[1])]">
            <xsl:variable name="group" select="key('k2', concat(@DueStatus, '|', @Country, '|', @Region))"/>
            <tr>
              <th colspan="3"><xsl:value-of select="concat(@Country, '- ', @Region)"/></th>
            </tr>
            <tr>
              <th>Date</th>
              <th>Remarks</th>
              <th>Status</th>
            </tr>
            <xsl:for-each select="$group">
              <tr>
                <td><xsl:value-of select="@Date"/></td>
                <td>
                  <xsl:choose>
                    <xsl:when test="normalize-space(@Remarks)">
                      <xsl:value-of select="@Remarks"/>
                    </xsl:when>
                    <xsl:otherwise> </xsl:otherwise>
                  </xsl:choose>
                </td>
                <td><xsl:value-of select="@DueStatus"/></td>
              </tr>
            </xsl:for-each>
          </xsl:for-each>
        </tbody>
      </table>
    </xsl:for-each>
  </xsl:template>

</xsl:stylesheet>

Martin Honnen · September 29th, 2009, 11:13 AM

As for preventing empty cells, both stylesheets posted have the code in the xsl:otherwise but unfortunately the forum is not correctly rendering that: it should be

Code:

<xsl:otherwise>& # 1 6 0;</xsl:otherwise>

without the spaces between the characters and digits. So the xsl:otherwise will output a non-breaking space character with the help of a numeric character reference.

Narendra.Patil · September 30th, 2009, 01:35 AM

Thank You Martin... :)

Narendra.Patil · October 1st, 2009, 06:19 AM

Dear Martin,

Can we appy two templates? I have this XMl file and I want out put with grouping with columns and rows. I tried your earlier XSL files but could not succeed in the proper output. Group Country & Region as Header and Subregions as row level grouping

<?xml version="1.0" encoding="utf-16"?>
<Body>
<Data Country="India" Region="North" SubRegion="A" SalesDate="01-01-2007" Remarks="Done"/>
<Data Country="India" Region="North" SubRegion="A" SalesDate="21-11-2007" Remarks="Done... Expected"/>
<Data Country="India" Region="North" SubRegion="B" SalesDate="02-02-2009" Remarks="Done"/>
<Data Country="India" Region="North" SubRegion="C" SalesDate="03-11-2008" Remarks="Done"/>
<Data Country="India" Region="North" SubRegion="C" SalesDate="21-11-2007" Remarks="Done... Carried Forward"/>

<Data Country="India" Region="South" SubRegion="D" SalesDate="01-01-2007" Remarks="Done"/>
<Data Country="India" Region="South" SubRegion="D" SalesDate="21-11-2007" Remarks="Done"/>
<Data Country="India" Region="South" SubRegion="D" SalesDate="02-02-2009" Remarks="Done... Expected"/>

<Data Country="India" Region="East" SubRegion="E" SalesDate="21-11-2007" Remarks="Done... Stopped"/>
<Data Country="India" Region="East" SubRegion="E" SalesDate="02-02-2009" Remarks="Done"/>
<Data Country="India" Region="East" SubRegion="E" SalesDate="03-11-2009" Remarks="Done"/>

</Body>

Expected Output
<html>
<head>
<title></title>
</head>
<body>
<table border="1">
<tr><th colspan="3">India - North</th></tr>
<tr>
<th>Sub Region</th>
<th>Sales Date</th>
<th>Remarks</th>
</tr>
<tr>
<td rowspan="2">A</td>
<td>01-01-2007</td>
<td>Done</td>
</tr>
<tr>
<td>21-11-2007</td>
<td>Done... Expected</td>
</tr>
<tr>
<td>B</td>
<td>01-01-2007</td>
<td>02-02-2009</td>
</tr>
<tr>
<td rowspan="2">C</td>
<td>03-11-2008</td>
<td>Done</td>
</tr>
<tr>
<td>21-11-2007</td>
<td>Done... Carried Forward</td>
</tr>
</table>
<br/>
<table border="1">
<tr><th colspan="3">India - South</th></tr>
<tr>
<th>Sub Region</th>
<th>Sales Date</th>
<th>Remarks</th>
</tr>
<tr>
<td rowspan="3">D</td>
<td>01-01-2007</td>
<td>Done</td>
</tr>
<tr>
<td>21-11-2007</td>
<td>Done</td>
</tr>
<tr>
<td>02-02-2009</td>
<td>Done... Expected</td>
</tr>
</table>

<br/>
<table border="1">
<tr><th colspan="3">India - East</th></tr>
<tr>
<th>Sub Region</th>
<th>Sales Date</th>
<th>Remarks</th>
</tr>
<tr>
<td rowspan="3">E</td>
<td>21-11-2007</td>
<td>Done... Stopped</td>
</tr>
<tr>
<td>02-02-2009</td>
<td>Done</td>
</tr>
<tr>
<td>03-11-2009</td>
<td>Done... Expected</td>
</tr>
</table>
</body>
</html>
Please let me know the XSL how it should be?
Thanks...

Martin Honnen · October 1st, 2009, 08:29 AM

Here is a stylesheet that should do what you want:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">
  
  <xsl:output method="html" indent="yes"/>
  
  <xsl:key name="k1" match="Data" use="concat(@Country, '|', @Region)"/>
  <xsl:key name="k2" match="Data" use="concat(@Country, '|', @Region, '|', @SubRegion)"/>
  
  <xsl:template match="/">
    <html lang="en">
      <head>
        <title>Example</title>
      </head>
      <body>
        <xsl:apply-templates select="Body/Data[generate-id() = generate-id(key('k1', concat(@Country, '|', @Region))[1])]" mode="table"/>
      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="Data" mode="table">
    <table border="1">
      <thead>
        <tr>
          <th colspan="3"><xsl:value-of select="concat(@Country, ' - ', @Region)"/></th>
        </tr>
        <tr>
          <th>Sub Region</th>
          <th>Sales Date</th>
          <th>Remarks</th>
        </tr>
      </thead>
      <tbody>
        <xsl:apply-templates select="key('k1', concat(@Country, '|', @Region))"/>      
      </tbody>
    </table>
    <xsl:if test="position() != last()"><br /></xsl:if>
  </xsl:template>
  
  <xsl:template match="Data">
    <tr>
      <xsl:if test="generate-id() = generate-id(key('k2', concat(@Country, '|', @Region, '|', @SubRegion))[1])">
        <td rowspan="{count(key('k2', concat(@Country, '|', @Region, '|', @SubRegion)))}">
          <xsl:value-of select="@SubRegion"/>
        </td>
      </xsl:if>
      <td><xsl:value-of select="@SalesDate"/></td>
      <td><xsl:value-of select="@Remarks"/></td>
    </tr>
  </xsl:template>

</xsl:stylesheet>

But a forum like this is meant to be help for you to learn XSLT. So you should study solutions and suggestions posted to be able to write your own stylesheets. By now you should have an idea how Muenchian grouping works and how to apply it to further problems you face.

Narendra.Patil · October 1st, 2009, 09:15 AM

Thanks a lot. It is just what I wanted. :)