Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > XML > XSLT
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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>
Reply With Quote
  #2 (permalink)  
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: 100%
Activity: 100% Activity: 100% Activity: 100%
 
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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
Reply With Quote
  #5 (permalink)  
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>
Reply With Quote
  #6 (permalink)  
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>
Reply With Quote
  #7 (permalink)  
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: 100%
Activity: 100% Activity: 100% Activity: 100%
 
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
Reply With Quote
The Following User Says Thank You to mhkay For This Useful Post:
pallone (June 15th, 2010)
  #8 (permalink)  
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..
Reply With Quote
  #9 (permalink)  
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: 100%
Activity: 100% Activity: 100% Activity: 100%
 
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
Reply With Quote
The Following User Says Thank You to mhkay For This Useful Post:
pallone (June 15th, 2010)
  #10 (permalink)  
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
Reply With Quote
The Following User Says Thank You to Martin Honnen For This Useful Post:
pallone (June 15th, 2010)
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:56 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.