HELP!:Summing separate sections of an XML file

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



j_young_80Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kjayaramanCommented:
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
kjayaramanCommented:
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
kjayaramanCommented:
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

dualsoulCommented:
or you have always oportunity to switch to XSLT 2.0 , and stop doing magic ;)
0
j_young_80Author Commented:
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
jkmyoungCommented:
? 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
j_young_80Author Commented:
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
rdcproCommented:
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
j_young_80Author Commented:
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
rdcproCommented:
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
j_young_80Author Commented:
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
rdcproCommented:
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
rdcproCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
j_young_80Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.