Totals Grouping Problem

pallone · June 15th, 2010, 06:34 AM

Hi,

I am using xslt 1.0 and msxml

I am struggling to get my xslt to group correctly to create the total cost and total number of insertions and I would appreciate if someone could help me.

This is the ouput I need:

Media - Pub Type - Ad Unit - # Insertions - Total Cost
Blood 2 6562.00
Blood Cover 4 1 3614.62
Blood Cover 2 1 3253.16AMR Cover 2 1 11806.50

The insertion totals and total cost should be grouped by Position/@name

To get the total insertions I need to use the <Insertion /> nodes and to get the total cost I need to use the <RateCardRate value="2409.75" /> nodes
However, in my xslt I was only able to get the total insertion and cost for the Vehicle nodes. I was not able to group it by Position/@name.

This is the xml

<Schedule projectId="2354">
<Media detail="1">
<Vehicle key="BLOOD" AgencyDiscount="15" CashDiscount="0" insCount="1">
<RateCards>
<RateCard id="7962" name="2011p" detail="1">
<EarnedBW value="1" />
<EarnedColour value="1" />
<Ads>
<Ad key="545:5922" manufac="">
<RateCardRate value="2409.75" />
<ManualRate value="" AdjustedValue="" />
<Position id="0" name="" />
<Issues>
<Issue date="2010-05-06">
<Insertion />
</Issue>
<Issue date="2010-05-13">
<Insertion />
</Issue>
</Issues>
</Ad>
<Ad key="545:5922" manufac="">
<RateCardRate value="3614.62" />
<ManualRate value="" AdjustedValue="" />
<Position id="531" name="Cover 4" />
<Issues>
<Issue date="2010-05-06">
<Insertion />
</Issue>
<Issue date="2010-05-13">
<Insertion />
</Issue>
</Issues>
</Ad>
<Ad key="545:5922" manufac="">
<RateCardRate value="3253.16" />
<ManualRate value="" AdjustedValue="" />
<Position id="534" name="Cover 2" />
<Issues>
<Issue date="2010-05-06">
<Insertion />
</Issue>
<Issue date="2010-05-13">
<Insertion />
</Issue>
</Issues>
</Ad>
<Ad key="300:5922" manufac="">
<RateCardRate value="4152.25" />
<ManualRate value="" AdjustedValue="" />
<Position id="0" name="" />
<Issues>
<Issue date="2010-05-06">
<Insertion />
</Issue>
<Issue date="2010-05-13">
<Insertion />
</Issue>
</Issues>
</Ad>
</Ads>
</RateCard>
</RateCards>
</Vehicle>
<Vehicle key="AMR" insCount="1" AgencyDiscount="15" CashDiscount="0">
<RateCards>
<RateCard id="7897" name="2011p">
<EarnedBW value="1" />
<EarnedColour value="1" />
<Ads>
<Ad key="300:5922" manufac="">
<RateCardRate value="11806.50" />
<ManualRate value="" AdjustedValue="" />
<Position id="0" name="Cover 2" />
<Issues>
<Issue date="2010-06-01">
<Insertion />
</Issue>
<Issue date="2010-07-01">
<Insertion />
<Insertion />
</Issue>
</Issues>
</Ad>
</Ads>
</RateCard>
</RateCards>
</Vehicle>
</Media>
</Schedule>

My xslt

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" version="4.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
<html>
<head>
<title>Schedule</title>
</head>

<body>

<xsl:apply-templates/>

</body>
</html>
</xsl:template>

<xsl:template match="Vehicle">

<table border="2">
<tr>
<th>Media</th>
<th>Pub Type</th>
<th>Ad Unit</th>
<th>#Ins</th>
<th>Cost</th>
</tr>
<xsl:apply-templates select="RateCards/RateCard/Ads/Ad/Position">
<xsl:with-param name ="media" select ="@key"/>
</xsl:apply-templates>

</table>
</xsl:template>
<xsl:template match ="text()">

</xsl:template>
<xsl:template match="Position">
<xsl:param name ="media"/>
<tr>
<td>
<xsl:value-of select="$media"/>
</td>
<td>
<xsl:value-of select="@name"/>
</td>
<td>
<xsl:value-of select="@name"/>
</td>
<td align="right">

<xsl:value-of select="count(../../Ad)"/>
</td>
<td align="right">
<xsl:call-template name="AdTotal">
<xsl:with-param name="AdItems"
select="../../Ad"/>
</xsl:call-template>
</td>

</tr>
</xsl:template>
<xsl:template name="AdTotal">
<xsl:param name="AdItems"/>
<xsl:choose>
<xsl:when test="$AdItems">
<xsl:variable
name="FirstInList"
select="$AdItems[1]"/>
<xsl:variable name="TotalOfRest">
<xsl:call-template name="AdTotal">
<xsl:with-param
name="AdItems"
select="$AdItems[position()!=1]"/>
</xsl:call-template>
</xsl:variable>
<xsl:value-of
select="format-number($FirstInList/RateCardRate/@value + $TotalOfRest,'#.00')"/>
</xsl:when>
<xsl:otherwise>
0
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>

This is the wrong HTML table produced:

<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Schedule</title>
</head>
<body>
<table border="2">
<tr>
<th>Media</th>
<th>Pub Type</th>
<th>Ad Unit</th>
<th>#Ins</th>
<th>Cost</th>
</tr>
<tr>
<td>BLOOD</td>
<td>
</td>
<td>
</td>
<td align="right">4</td>
<td align="right">13429.78</td>
</tr>
<tr>
<td>BLOOD</td>
<td>Cover 4</td>
<td>Cover 4</td>
<td align="right">4</td>
<td align="right">13429.78</td>
</tr>
<tr>
<td>BLOOD</td>
<td>Cover 2</td>
<td>Cover 2</td>
<td align="right">4</td>
<td align="right">13429.78</td>
</tr>
<tr>
<td>BLOOD</td>
<td>
</td>
<td>
</td>
<td align="right">4</td>
<td align="right">13429.78</td>
</tr>
</table>
<table border="2">
<tr>
<th>Media</th>
<th>Pub Type</th>
<th>Ad Unit</th>
<th>#Ins</th>
<th>Cost</th>
</tr>
<tr>
<td>AMER_FAMILY_PHYSICIAN</td>
<td>
</td>
<td>
</td>
<td align="right">1</td>
<td align="right">11806.50</td>
</tr>
</table>
</body>
</html>

mhkay · June 15th, 2010, 06:49 AM

I don't see any code in your stylesheet that's attempting to do the grouping.

Grouping in XSLT 1.0 should always be done using the Muenchian method. It takes a bit of time to understand it at first, but it's very stereotyped once you get used to it. You'll find it described in all XSLT textbooks and tutorials, for example Jeni Tennison's site is often cited.

Martin Honnen · June 15th, 2010, 07:05 AM

Can you post the HTML document you want to create for the XML sample you posted?
And please make use of the forum's markup for code samples http://p2p.wrox.com/misc.php?do=bbcode#code and consider to indent your code samples so that we can better read them.

pallone · June 15th, 2010, 07:51 AM

Hi Michael,

Thanks for your reply.

I did try to group it using a variable but I am not sure how to use it to produce the totals

<xsl:variable name="UniqueItems" select="

pallone · June 15th, 2010, 07:57 AM

Hi Michael,

Thanks for your reply.

I did try to group it using a variable but I am not sure how to use it to produce the totals

<xsl:variable name="UniqueItems" select="Schedule/Media/Vehicle/RateCards/RateCard/Ads/Ad/Position/@name[not(.=preceding::Position/@name)]"/>

<xsl:for-each select="$UniqueItems">
<xsl:for-each select="/Schedule/Media/Vehicle/RateCards/RateCard/Ads/Ad[Position/@name=current()/@name]">
<xsl:apply-templates select="."/>
</xsl:for-each>
</xsl:for-each>

pallone · June 15th, 2010, 08:15 AM

Hi Martin,

Many thanks for your reply.

This is the HTML output I need with the total insertions and cost based on Position/@name.

Thanks

Code:

 
<html>
  <head>
    <title>Schedule</title>
  </head>
  <body>
    <table border="2">
      <tr>
        <th>Media</th>
        <th>Pub Type</th>
        <th>Ad Unit</th>
        <th>Total #Ins per Position name</th>
        <th>Total Cost per Position name</th>
      </tr>
      <tr>
        <td>BLOOD</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td align="right">4</td>
        <td align="right">6562.00</td>
      </tr>
      <tr>
        <td>BLOOD</td>
        <td>Cover 4</td>
        <td>Cover 4</td>
        <td align="right">2</td>
        <td align="right">3614.62</td>
      </tr>
      <tr>
        <td>BLOOD</td>
        <td>Cover 2</td>
        <td>Cover 2</td>
        <td align="right">2</td>
        <td align="right">3253.16</td>
      </tr>
      <tr>
        <td>AMR</td>
        <td>Cover 2</td>
        <td>Cover 2</td>
        <td align="right">3</td>
        <td align="right">11806.50</td>
      </tr>
    </table>
  </body>
</html>

mhkay · June 15th, 2010, 08:45 AM

Using the x[not(.=[preceding::x])] technique should work, but it is incredibly inefficient compared with Muenchian grouping.

It's not working for you because your two nested for loops are saying "for each unique value, process all the items with that value", which is another way of saying "process all the items".

pallone · June 15th, 2010, 09:03 AM

Hi Michael,

I am really not sure how to get this to work. Could you please point out what I have to change in my xslt below?

Cheers

Code:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html" version="4.0" encoding="UTF-8" indent="yes"/>

  <xsl:variable name="UniqueItems" select="Schedule/Media/Vehicle/RateCards/RateCard/Ads/Ad/Position/@name[not(.=preceding::Position/@name)]"/>

  <xsl:template match="/">
    <html>
      <head>
        <title>Schedule</title>
      </head>

      <body>
        <xsl:apply-templates/>
      </body>
    </html>
  </xsl:template>

  <xsl:template match="Vehicle">
    <table border="2">
      <tr>
        <th>Media</th>
        <th>Pub Type</th>
        <th>Ad Unit</th>
        <th>#Ins</th>
        <th>Cost</th>
      </tr>

      <xsl:for-each select="$UniqueItems">
        <xsl:for-each select="/Schedule/Media/Vehicle/RateCards/RateCard/Ads/Ad[Position/@name=current()/@name]">
          <!--<xsl:sort select=""/>-->
          <xsl:apply-templates select="."/>
        </xsl:for-each>
      </xsl:for-each>
      
<!--<xsl:apply-templates select="RateCards/RateCard/Ads/Ad/Position">
        <xsl:with-param name ="media" select ="@key"/>
      </xsl:apply-templates>-->

    </table>
  </xsl:template>

  <xsl:template match ="text()">
  </xsl:template>

  <xsl:template match="Position">
    <xsl:param name ="media"/>
    <tr>
      <td>
        <xsl:value-of select="$media"/>
      </td>
      <td>
        <xsl:value-of select="@name"/>
      </td>
      <td>
        <xsl:value-of select="@name"/>
      </td>
      <td align="right">
        <!--<xsl:value-of select="count(../Ad)"/>-->
        <xsl:value-of select="count(../../Ad)"/>
      </td>
      <td align="right">
        <xsl:call-template name="AdTotal">
          <xsl:with-param name="AdItems"
                     select="../../Ad"/>
        </xsl:call-template>
      </td>
    </tr>
  </xsl:template>

  <xsl:template name="AdTotal">
    <xsl:param name="AdItems"/>
    <xsl:choose>
      <xsl:when test="$AdItems">
        <xsl:variable
                 name="FirstInList"
                 select="$AdItems[1]"/>
        <xsl:variable name="TotalOfRest">
          <xsl:call-template name="AdTotal">
            <xsl:with-param
                         name="AdItems"
                         select="$AdItems[position()!=1]"/>
          </xsl:call-template>
        </xsl:variable>
        <xsl:value-of
                 select="format-number($FirstInList/RateCardRate/@value + $TotalOfRest,'#.00')"/>
      </xsl:when>
      <xsl:otherwise>
        0
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
</xsl:stylesheet>

mhkay · June 15th, 2010, 09:20 AM

Your problem is essentially the same as the one that is solved here:

http://www.codeproject.com/KB/XML/groupxml.aspx

Martin Honnen · June 15th, 2010, 09:35 AM

Here is an XSLT 1.0 stylesheet using Muenchian grouping that should output what you described:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">
  
  <xsl:output method="html" indent="yes"/>
  <xsl:strip-space elements="*"/>
  
  <xsl:key name="k1" match="Ad" use="concat(ancestor::Vehicle/@key, '|', Position/@name)"/>
  
  <xsl:template match="/">
    <html>
      <head>
        <title>Schedule</title>
      </head>
      <body>
        <xsl:apply-templates/>
      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="Media">
    <table border="2">
      <thead>
        <tr>
          <th>Media</th>
          <th>Pub Type</th>
          <th>Ad Unit</th>
          <th>Total #Ins per Position name</th>
          <th>Total Cost per Position name</th>
        </tr>
      </thead>
      <tbody>
        <xsl:apply-templates select="descendant::Ad[generate-id() = generate-id(key('k1', concat(ancestor::Vehicle/@key, '|', Position/@name))[1])]"/>
      </tbody>
    </table>
  </xsl:template>
  
  <xsl:template match="Ad">
    <xsl:variable name="current-group" select="key('k1', concat(ancestor::Vehicle/@key, '|', Position/@name))"/>
    <tr>
      <td><xsl:value-of select="ancestor::Vehicle/@key"/></td>
      <td><xsl:value-of select="Position/@name"/></td>
      <td><xsl:value-of select="Position/@name"/></td>
      <td><xsl:value-of select="count($current-group/Issues/Issue/Insertion)"/></td>
      <td><xsl:value-of select="format-number(sum($current-group/RateCardRate/@value), '#.00')"/></td>
    </tr>
  </xsl:template>

</xsl:stylesheet>