Wrox Programmer Forums
Go Back   Wrox Programmer Forums > XML > XSLT
| Search | Today's Posts | Mark Forums Read
XSLT General questions and answers about XSLT. For issues strictly specific to the book XSLT 1.1 Programmers Reference, please post to that forum instead.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XSLT section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 15th, 2010, 06:34 AM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default Totals Grouping Problem

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>
 
Old June 15th, 2010, 06:49 AM
mhkay's Avatar
Wrox Author
Points: 18,487, Level: 59
Points: 18,487, Level: 59 Points: 18,487, Level: 59 Points: 18,487, Level: 59
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2004
Location: Reading, Berks, United Kingdom.
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default

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.
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
 
Old June 15th, 2010, 07:05 AM
Friend of Wrox
Points: 6,676, Level: 34
Points: 6,676, Level: 34 Points: 6,676, Level: 34 Points: 6,676, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: Germany
Posts: 1,243
Thanks: 0
Thanked 245 Times in 244 Posts
Default

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.
__________________
Martin Honnen
Microsoft MVP (XML, Data Platform Development) 2005/04 - 2013/03
My blog
 
Old June 15th, 2010, 07:51 AM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

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="

Last edited by pallone; June 15th, 2010 at 07:56 AM.. Reason: the code was cut
 
Old June 15th, 2010, 07:57 AM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

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>
 
Old June 15th, 2010, 08:15 AM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

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>
 
Old June 15th, 2010, 08:45 AM
mhkay's Avatar
Wrox Author
Points: 18,487, Level: 59
Points: 18,487, Level: 59 Points: 18,487, Level: 59 Points: 18,487, Level: 59
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2004
Location: Reading, Berks, United Kingdom.
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default

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".
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
The Following User Says Thank You to mhkay For This Useful Post:
pallone (June 15th, 2010)
 
Old June 15th, 2010, 09:03 AM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

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>

Last edited by pallone; June 15th, 2010 at 09:07 AM..
 
Old June 15th, 2010, 09:20 AM
mhkay's Avatar
Wrox Author
Points: 18,487, Level: 59
Points: 18,487, Level: 59 Points: 18,487, Level: 59 Points: 18,487, Level: 59
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2004
Location: Reading, Berks, United Kingdom.
Posts: 4,962
Thanks: 0
Thanked 292 Times in 287 Posts
Default

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

http://www.codeproject.com/KB/XML/groupxml.aspx
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
The Following User Says Thank You to mhkay For This Useful Post:
pallone (June 15th, 2010)
 
Old June 15th, 2010, 09:35 AM
Friend of Wrox
Points: 6,676, Level: 34
Points: 6,676, Level: 34 Points: 6,676, Level: 34 Points: 6,676, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: Germany
Posts: 1,243
Thanks: 0
Thanked 245 Times in 244 Posts
Default

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>
__________________
Martin Honnen
Microsoft MVP (XML, Data Platform Development) 2005/04 - 2013/03
My blog
The Following User Says Thank You to Martin Honnen For This Useful Post:
pallone (June 15th, 2010)




Similar Threads
Thread Thread Starter Forum Replies Last Post
SSRS 2005 Matrix Totals Problem!!!!!!!!!!!!!!!!! dba123 Reporting Services 1 February 10th, 2006 10:00 AM
Grouping problem aware Access 11 August 4th, 2005 11:00 AM
Grouping and totals btado XML 4 July 1st, 2005 03:08 AM
Grouping and totals btado XSLT 3 June 22nd, 2005 01:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.