Subject: [xsl] Grouping distinct and summing their cost values From: GORDIST@xxxxxxxxxxxxxx Date: Tue, 24 Feb 2004 08:07:35 -0500 |
I had sent this out a little while back and have had no response. Not sure if the email actually reached everyone so I am sending it again. Let me know if anyone has any ideas on how to make this occur. Thanks, Todd. ----- Forwarded by Kenneth T Gordish/Nationwide/NWIE on 02/24/2004 08:06 AM ----- |---------+--------------------------------------> | | Todd Gordish | | | Programmer Analyst | | | FIMS-Cost Management | | | System | | | Phone: 677-5913 | | | Pager 720-0109 | | | Fax: 677-7634 | | | 1-31-01 | | | 02/13/2004 06:34 AM | | | | |---------+--------------------------------------> >--------------------------------------------------------------------------------------------------------------------| | | | To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx | | cc: | | bcc: | | Subject: Grouping distinct and summing their cost values | >--------------------------------------------------------------------------------------------------------------------| OK. Here is my problem. I have been trying everything I can find to get the distinct attribute within a particular tag and at the same time sum the costs that are related to that attribute. The difficulty that I am having is that the way we are building the xml file, the cost tag is a sibling of the tag where the attribute needs to be grouped and displayed. This is to be done for each Activity that is in the XML file. I have included an example of the XML, the XSLT that I am currently trying to use, and a sample output of how the data should look. Just to let you know, the Product, Customer, Channel, and Company tags are treated as separate tables when being displayed. So basically getting one of these to work will make them all work. Thanks in advance for any help given to me on this. Todd Gordish XML Data <cmsParent xmlns="http://cms.nwie.net/CostObject" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation=" http://cms.nwie.net/CostObject file:/R:/docs/schema/CMSColReports.xsd" parentShortDesc="PARENT 1" timestamp="2004-01-14T08:30:01Z"> <node nodeShortDesc="NODE 1"> <actv actvShortDesc="ACTIVITY 1" actvDrvShortDesc="ACTIVITY DRIVER 1" expgrpShortDesc="EXPENSE GROUP 1"> <dimensions> <prod prodShortDesc="PRODUCT 1"/> <cust custShortDesc="CUSTOMER 1"/> <chan chanShortDesc="CHANNEL 1"/> <comp compShortDesc="COMPANY 1"/> <mthCost>15.901</mthCost> <ytdCost>31.802</ytdCost> </dimensions> <dimensions> <prod prodShortDesc="PRODUCT 1"/> <cust custShortDesc="CUSTOMER 1"/> <chan chanShortDesc="CHANNEL 1"/> <comp compShortDesc="COMPANY 2"/> <mthCost>47.703</mthCost> <ytdCost>95.406</ytdCost> </dimensions> </actv> <actv actvShortDesc="ACTIVITY 2" actvDrvShortDesc="ACTIVITY DRIVER 1" expgrpShortDesc="EXPENSE GROUP 1"> <dimensions> <prod prodShortDesc="PRODUCT 1"/> <cust custShortDesc="CUSTOMER 1"/> <chan chanShortDesc="CHANNEL 1"/> <comp compShortDesc="COMPANY 1"/> <mthCost>143.109</mthCost> <ytdCost>143.109</ytdCost> </dimensions> <dimensions> <prod prodShortDesc="PRODUCT 1"/> <cust custShortDesc="CUSTOMER 1"/> <chan chanShortDesc="CHANNEL 1"/> <comp compShortDesc="COMPANY 2"/> <mthCost>429.327</mthCost> <ytdCost>429.327</ytdCost> </dimensions> </actv> </node> </cmsParent> Template for building the Company data <!-- This is for one template to build the Company information only per Activity tag --> <xsl:template name="compGroup"> <table class="cmsBcrSummary"> <tr class="cmsBcrSummary"> <th class="cmsBcrSummary">Company</th> <th class="cmsBcrSummary">Current Month</th> <th class="cmsBcrSummary">%</th> <th class="cmsBcrSummary">Year To Date</th> <th class="cmsBcrSummary">%</th> </tr> <tr> <xsl:for-each select ="set:distinct(//cms:dimensions/cms:comp/@compShortDesc)"> <tr class="cmsBcrSummary"> <td class="cmsBcrSummary"><xsl:value-of select ="."/></td> <xsl:for-each select ="set:distinct(//cms:dimensions/cms:comp/@compShortDesc)"> <xsl:variable name="compMthCost"> <xsl:value-of select ="sum(//cms:mthCost)"/> </xsl:variable> <xsl:variable name="compYtdCost"> <xsl:value-of select ="sum(//cms:ytdCost)"/> </xsl:variable> <td class="cmsBcrSummary"><xsl:value-of select="format-number(sum(//following-sibling::cms:dimensions/cms:mthCost), '#,##0.00')"/></td> <td class="cmsBcrSummary"><xsl:value-of select="format-number(sum(./cms:dimensions/cms:mthCost)div$compMthCost*100, '#,##0.00')"/>%</td> <td class="cmsBcrSummary"><xsl:value-of select="format-number(sum(./cms:dimensions/cms:ytdCost), '#, ##0.00')"/></td> <td class="cmsBcrSummary"><xsl:value-of select="format-number(sum(./cms:dimensions/cms:ytdCost)div$compYtdCost*100, '#,##0.00')"/>%</td> </xsl:for-each> </tr> </xsl:for-each> </tr> <tr> <td class="bcrSummaryTotal">Total Expenses</td> <td class="cmsBcrSummary"><xsl:value-of select ="format-number(sum(./cms:dimensions/cms:mthCost), '#,##0.00')"/></td> <td class="cmsBcrSummary"><xsl:value-of select ="$pctMthCost"/></td> <td class="cmsBcrSummary"><xsl:value-of select ="format-number(sum(./cms:dimensions/cms:ytdCost), '#,##0.00')"/></td> <td class="cmsBcrSummary"><xsl:value-of select ="$pctYtdCost"/></td> </tr> </table> </xsl:template> How the data should be displayed <!-- Output of the data from the XML and XSLT above --> Activity: ACTIVITY 1 Expense Group: EXPENSE GROUP 1 Activity Driver: ACTIVITY DRIVER 1 Product Current Month % Year To Date % PRODUCT 1 15.90 100.00% 31.80 100.00% Total Expenses 63.60 100.00% 127.21 100.00% Customer Current Month % Year To Date % CUSTOMER 1 15.90 100.00% 31.80 100.00% Total Expenses 63.60 100.00% 127.21 100.00% Channel Current Month % Year To Date % CHANNEL 1 15.90 100.00% 31.80 100.00% Total Expenses 63.60 100.00% 127.21 100.00% Company Current Month % Year To Date % COMPANY 1 636.04 0.00% 0.00 0.00% COMPANY 2 636.04 0.00% 0.00 0.00% Total Expenses 63.60 100.00% 127.21 100.00% Activity: ACTIVITY 2 Expense Group: EXPENSE GROUP 1 Activity Driver: ACTIVITY DRIVER 1 Product Current Month % Year To Date % PRODUCT 1 15.90 100.00% 31.80 100.00% Total Expenses 63.60 100.00% 127.21 100.00% Customer Current Month % Year To Date % CUSTOMER 1 15.90 100.00% 31.80 100.00% Total Expenses 63.60 100.00% 127.21 100.00% Channel Current Month % Year To Date % CHANNEL 1 15.90 100.00% 31.80 100.00% Total Expenses 63.60 100.00% 127.21 100.00% Company Current Month % Year To Date % COMPANY 1 636.04 0.00% 0.00 0.00% COMPANY 2 636.04 0.00% 0.00 0.00% Total Expenses 63.60 100.00% 127.21 100.00% XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
[xsl] Grouping distinct and summing, GORDIST | Thread | [xsl] Sorting XML Elements alphabet, Peter Loh Yoon Chao |
[xsl] Updating attributes and node , Arulraj | Date | Re: [xsl] question count some nodes, Markus Hanel |
Month |