?
Solved

HELP!:Summing separate sections of an XML file

Posted on 2004-11-15
14
Medium Priority
?
220 Views
Last Modified: 2013-11-19
I currently have a value in an XML file that I want to sum all the values with the same title together with each other and output these to the screen for each different title that exists in the xml.

Below is a simplified version of the XML file I am currently using:

- <reports>
  <ID>50</ID>
  <Title>Title1</Title>
  <Total1>10</Total1>
  </reports>
- <reports>
  <ID>60</ID>
  <Title>Title1</Title>
  <Total1>20</Total1>
  </reports>
- <reports>
  <ID>70</ID>
  <Title>Title1</Title>
  <Total1>30</Total1>
  </reports>
- <reports>
  <ID>80</ID>
  <Title>Title2</Title>
  <Total1>50</Total1>
  </reports>
- <reports>
  <ID>90</ID>
  <Title>Title2</Title>
  <Total1>70</Total1>
  </reports>
- <reports>
  <ID>100</ID>
  <Title>Title2</Title>
  <Total1>90</Total1>
  </reports>
- <reports>
  <ID>110</ID>
  <Title>Title2</Title>
  <Total1>100</Total1>
  </reports>

and here is the xslt code I am trying to use to output the correct values:

<xsl:value-of select="sum(//Total1)"/>

I have tried using the preceding sibling statement to test the Title but the way this template is structured I need to use the ../Title format to access the node and the statement errors if I use that throughout.

Does anyone have any other ideas?
thanks,
j



0
Comment
Question by:j_young_80
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 2

Expert Comment

by:kjayaraman
ID: 12590726
Use a grouping strategy as below

<xsl:key name="amount-key" match="head/reports" use="Title"/>
-- Assumption: head is the head of your xml

<xsl:template match="/">
        <xsl:for-each select="page/row[generate-id() = generate-id(key('amount-key', Title)[1])]">
            <xsl:sort select="Title"/>
            <xsl:variable name="current-group" select="/page/row[Title = current()/Title]"/>
            Title: <xsl:value-of select="Title"/>
            Total: <xsl:value-of select="sum($current-group/Total1)"/>
        </xsl:for-each>
</xsl:template>


0
 
LVL 2

Expert Comment

by:kjayaraman
ID: 12590728
Change
<xsl:variable name="current-group" select="/page/row[Title = current()/Title]"/>
to
<xsl:variable name="current-group" select="/head/reports[Title = current()/Title]"/>
0
 
LVL 2

Expert Comment

by:kjayaraman
ID: 12590762
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:dualsoul
ID: 12591187
or you have always oportunity to switch to XSLT 2.0 , and stop doing magic ;)
0
 

Author Comment

by:j_young_80
ID: 12597762
I have tried the method you have suggested but I am getting no data returned - this is being called within another template - will that cause any problems? Currently I am passing in one node to the template and if I need to reference outside that node i have to use ../Title for example.

 I am also a little confused about what you refer to as the head of my xml this is the first line;<root xmlns:sql="urn:schemas-microsoft-com:xml-sql"> and the rest is as I have included - so do I need to use root/reports?

thanks,
j
0
 
LVL 12

Expert Comment

by:jkmyoung
ID: 12628813
? I don't see why you need the head part at all in the key.
use root/reports if reports are children of the root, eg one level below root.

Assuming you're in the node which contains the reports nodes:

<xsl:key name="amount-key" match="reports" use="Title"/>

<xsl:for-each select="reports[count(. | key('amount_key', Title)[1]) = 1]">
<xsl:sort select="Title" />
      <xsl:value-of select="Title" />,<br />
                <xsl:value-of select="sum(../reports[Title=current()/Title]/Total1)"><br />
</xsl:for-each>

Not the greatest code, would need more structure info to simplify.
0
 

Author Comment

by:j_young_80
ID: 12651643
hi jkmyoung,

i have tried the code you suggested but this doesn't give any results back.

i'm trying to work out the totals inside a template that is called via this xml call: <xsl:apply-templates select="FormGuide" mode="formationheader"/> where FormGuide is a section of my XML. To reference the other nodes I need to use ../ in most of my calls.

let me know if you require any further info....

many thanks,
j
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12671138
It would help if you posted the XSLT.  We're all guessing here.  You can't simply paste the previous code in, you have to look at it to see how it works, and use the technique in your XSLT, or post your XSLT.  Somewhere or other in your XSLT you're going to have to use Muenchian grouping:

http://rdcpro.com/zones/xml/xslt/faqroot/faq-grouping-1.1
http://rdcpro.com/zones/xml/xslt/faqroot/faq-grouping-1.2

Regards,
Mike Sharp
0
 

Author Comment

by:j_young_80
ID: 12692402
Hi Mike,

Thanx for those hyperlinks - it has cleared up some of the mystery for me but I still have a problem getting the unique sum totals to show up at their correct levels - I am getting the totals showing up concatenated at each level now. I have attached a modifed xslt and xml with a lot of the extra stuff taken out but I think it's enough to make it a bit clearer to everyone the problem that is occuring....

XSLT FILE:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sql="urn:schemas-microsoft-com:xml-sql" exclude-result-prefixes="sql">
<xsl:output omit-xml-declaration="yes"/>

<!--XSLT Version Control Header-->
<!--Any Changes made to this file must be recorded below stating Change Author, Data of the Change, Feedback ID (if relevant) and also Reason for the Change-->

            <xsl:template match="/">
                  <xsl:apply-templates select="*" mode="table"/>
            </xsl:template>

            <xsl:template match="root"      mode="table">
                        <div style="overflow:scroll;height=91%">
                                    <table class="report_Critical" style="border-right: 1pt outset; border-top: 1pt outset; font-weight: normal; font-size: 8pt; border-left: 1pt outset; border-bottom: 1pt outset; font-family: tahoma, verdana, arial" cellSpacing="0" width="100%" border="1">                  
                                                <tr>
                                                      <th>Group</th>
                                                      <th>Name</th>
                                                      <th><xsl:apply-templates select="/" mode="tableheader"/></th>
                                                      <th><font color="green"><xsl:value-of select="./GroupTitle"/></font></th>
                                                </tr>
                                          <xsl:apply-templates select="*" mode="tablecontent"/>
                                    </table>
                        </div>
            </xsl:template>
            
            <xsl:template match="*" mode="tableheader">
                  <xsl:value-of select="reports/ReportHeader"/>
            </xsl:template>
            <xsl:key name="kGroupTitle" match="reports" use="GroupTitle"/>
            
            <xsl:template match="//reports/FormGuide" mode="header">
            
            <!--This template will build the entire Formation row including Total/Alloc and Crit, Ess and RR Totals-->
                                    <td><b>Sum</b></td>
                                    <td><xsl:value-of select='format-number(., "#,##0.00;(#,##0.00)")'/></td>
                              
                        <tr>
                                    <td></td>
                                    <td>+/-</td>
                                    <td></td>      
                                    <td></td>
                                    <td></td>
                                    <td></td>
                                    <td></td>
                                    <td></td>
                                    <td></td>
                                    <td></td>                                                                                                                        
                        </tr>
                        
                        <tr>
                                    <td></td>
                                    <td><b>Total</b></td>
                                    <td>
<xsl:for-each select="//reports[count(. | key('kGroupTitle', GroupTitle)[1]) = 1]">
     <xsl:value-of select="sum(../reports[GroupTitle=current()/GroupTitle]/Total)" />
</xsl:for-each>
</td>
                                    <td></td>
                                    <td></td>
                                    <td></td>

                        </tr>
            </xsl:template>      
            
            <xsl:template match="reports" mode="tablecontent">
                        
                                                <td>
                                                            <xsl:choose>
                                                                        <xsl:when test="(preceding-sibling::reports/GroupTitle = GroupTitle)">
                                                                              <!--Do Nothing-->
                                                                        </xsl:when>
                                                                        <xsl:otherwise>
                                                                              <font color="blue"><xsl:value-of select="./GroupTitle"/></font>
                                                                              <xsl:apply-templates select="FormGuide" mode="header"/>
                                                                        </xsl:otherwise>      
                                                            </xsl:choose>
                                                      </td>
<tr>
<td></td>
                                                      <td>
                                                            <font color="green"><xsl:value-of select="./Title"/></font>
                                                      </td>

                                                </tr>
                              
            </xsl:template>

</xsl:stylesheet>

XML FILE:

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<reports>
  <Title>John1</Title>
  <ID>1</ID>
  <GroupTitle>Title1</GroupTitle>
  <Total>45500</Total>
  <FormGuide>13221227</FormGuide>
  <ReportHeader>Guideline</ReportHeader>
</reports>
<reports>
  <Title>Fred</Title>
  <ID>1</ID>
  <GroupTitle>Title1</GroupTitle>
  <Total>17783</Total>
  <FormGuide>13221227</FormGuide>
  <ReportHeader>Guideline</ReportHeader>
  </reports>
<reports>
  <Title>Barry</Title>
  <ID>2</ID>
  <GroupTitle>Title2</GroupTitle>
  <Total>144830</Total>
  <FormGuide>13221227</FormGuide>
  <ReportHeader>Guideline</ReportHeader>
</reports>
</root>

many thanks,
j
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12701301
I teach beginners that when they desire a fairly complex HTML layout, it helps to create the HTML first, paste that into the XSLT, and move the repeating sections down to secondary templates....ie:

Step 1
      <xsl:template match="root">
            <table>
                  <tbody>
                        <tr>
                              <th>Column Heading 1</th>
                              <th>Column Heading 2</th>
                              <th>Column Heading 3</th>
                        </tr>
                        <tr>
                              <td>Data Row1 Column 1</td>
                              <td>Data Row1 Column 2</td>
                              <td>Data Row1 Column 3</td>
                        </tr>
                  </tbody>
            </table>
      </xsl:template>


Step 2

      <xsl:template match="root">
            <table>
                  <tbody>
                        <tr>
                              <th>Column Heading 1</th>
                              <th>Column Heading 2</th>
                              <th>Column Heading 3</th>
                        </tr>
                        <xsl:apply-templates select="row"/>
                  </tbody>
            </table>
      </xsl:template>
      <xsl:template match="row">
                        <tr>
                              <td>Data Row1 Column 1</td>
                              <td>Data Row1 Column 2</td>
                              <td>Data Row1 Column 3</td>
                        </tr>
      </xsl:template>


Step 3

      <xsl:template match="root">
            <table>
                  <tbody>
                        <tr>
                              <th>Column Heading 1</th>
                              <th>Column Heading 2</th>
                              <th>Column Heading 3</th>
                        </tr>
                        <xsl:apply-templates select="row"/>
                  </tbody>
            </table>
      </xsl:template>
      <xsl:template match="row">
                        <tr>
                              <xsl:apply-templates select="column"/>
                        </tr>
      </xsl:template>
      <xsl:template match="column">
                              <td><xsl:value-of select="."/></td>
      </xsl:template>

The next step will add things like grouping to the existing apply-templates.

      <xsl:key name="kRow" match="row" use="column"/>
      <xsl:template match="root">
            <table>
                  <tbody>
                        <tr>
                              <th>Column Heading 1</th>
                              <th>Column Heading 2</th>
                              <th>Column Heading 3</th>
                        </tr>
                        <xsl:apply-templates select="row[count( . | key('kRow', column[1])[1]) = 1]"/>
                  </tbody>
            </table>
      </xsl:template>
      <xsl:template match="row">
                        <tr>
                              <xsl:apply-templates select="column"/>
                        </tr>
      </xsl:template>
      <xsl:template match="column">
                              <td><xsl:value-of select="."/></td>
      </xsl:template>


And so on.  Using this approach until you're familiar with how to do it "top down" helps keep your HTML well-formed, and the goal is always in sight.  In looking at your example, I can't tell what your desired output actually is.  Can you post just the HTML of how you want it to turn out?  

Regards,
Mike Sharp
0
 

Author Comment

by:j_young_80
ID: 12710820
Hi Mike,

Thanks for your patience in this - the section that is giving me the problems is where I am trying to sum all records with a similar GroupTitle. In the XML I supplied there are only two GroupTitles ('Title1' and 'Title2') Currently it seems to be concatenating the Total for 'Title1' (which is 63283) with the total for all records with GroupTitle of 'Title2' (which is 144830) . So the row that has the text "Total" is showing a figure of 63283144830.

So the HTML resuklt set that I need returned for this is going to look like the following:

<table border="1">
<tr>
<th>Group</th>
<th>Name</th>
<th>Guideline</th>
<th><font color="green"></font></th>
</tr>

<tr>
<td><font color="blue">Title1</font><td><b>Sum</b></td><td>13,221,227.00</td>
</tr>
<tr>
<td></td><td><b>Total</b></td><td>63283</td><td/><td/><td/>
</tr>
<tr>
<td></td><td><font color="green">John1</font></td>
</tr>
<tr>
<td></td><td><font color="green">Fred</font></td>
</tr>
<tr>
<td><font color="blue">Title2</font><td><b>Sum</b></td><td>13,221,227.00</td>
</tr>
<tr>
<td></td><td><b>Total</b></td><td>144830</td><td/><td/><td/>
</tr>
<tr>
<td></td><td><font color="green">Barry</font></td>
</tr>
</table>

The results you see against the Total Row are obviously fudged as  I cannot get this to work but all the other data I can get back in the format as you see it.
many thanks,
jamie

0
 
LVL 26

Expert Comment

by:rdcpro
ID: 12711692
Well, the HTML is still not well formed; each row seems to have a different number of <td> tags...But I think I see what you're after.  I'll set up an example for you, and post it shortly.

Regards,
Mike Sharp
0
 
LVL 26

Accepted Solution

by:
rdcpro earned 600 total points
ID: 12711800
Ok, this XSLT:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sql="urn:schemas-microsoft-com:xml-sql" exclude-result-prefixes="sql">
      <xsl:output method="html" encoding="utf-16"/>

      <xsl:key name="kGroupTitle" match="reports" use="GroupTitle"/>

      <xsl:template match="root">
                  <table border="1" cellpadding="4px" cellspacing="0">
                        <tr>
                              <th>Group</th>
                              <th>Name</th>
                              <th>
                                    <xsl:value-of select="reports[1]/ReportHeader"/>
                              </th>
                        </tr>
                        <xsl:apply-templates select="reports[count( . | key('kGroupTitle', GroupTitle)[1]) = 1 ]" mode="tablecontent"/>
                  </table>
      </xsl:template>
      <xsl:template match="reports" mode="tablecontent">
            <tr>
                  <td>
                        <font color="blue">
                              <xsl:value-of select="GroupTitle"/>
                        </font>
                  </td>
                  <td>Sum</td>
                  <td>
                        <xsl:value-of select="format-number(FormGuide, '#,##0.00;(#,##0.00)')"/>
                  </td>
            </tr>
            <tr>
                  <td>&#x00A0;</td>
                  <td>Total</td>
                  <td>
                        <xsl:value-of select="sum(key('kGroupTitle', GroupTitle)/Total)"/>
                  </td>
            </tr>
            <xsl:apply-templates select="/root/reports[GroupTitle = current()/GroupTitle]" mode="detail"/>
      </xsl:template>
      
      <xsl:template match="reports" mode="detail">
            <tr>
                  <td>&#x00A0;</td>
                  <td><xsl:value-of select="Title"/></td>
                  <td><xsl:value-of select="Total"/></td>
            </tr>
      </xsl:template>
</xsl:stylesheet>


Produces this output:

<table border="1" cellpadding="4px" cellspacing="0">
      <tr>
            <th>Group</th>
            <th>Name</th>
            <th>Guideline</th>
      </tr>
      <tr>
            <td>
                  <font color="blue">Title1</font>
            </td>
            <td>Sum</td>
            <td>13,221,227.00</td>
      </tr>
      <tr>
            <td> </td>
            <td>Total</td>
            <td>63283</td>
      </tr>
      <tr>
            <td> </td>
            <td>John1</td>
            <td>45500</td>
      </tr>
      <tr>
            <td> </td>
            <td>Fred</td>
            <td>17783</td>
      </tr>
      <tr>
            <td>
                  <font color="blue">Title2</font>
            </td>
            <td>Sum</td>
            <td>13,221,227.00</td>
      </tr>
      <tr>
            <td> </td>
            <td>Total</td>
            <td>144830</td>
      </tr>
      <tr>
            <td> </td>
            <td>Barry</td>
            <td>144830</td>
      </tr>
</table>

It shows the basics of Muenchian grouping here.

Regards,
Mike Sharp
0
 

Author Comment

by:j_young_80
ID: 12758629
Excellent that has cleared things up for me and I am getting the required results now!

my apologies for the delay in reply - I have been away from my computer for the past couple of days.

many thanks for all your help on this!

j
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question