xsl-list
[Top] [All Lists]

Grouping distinct and summing their cost values

2004-02-24 06:07:35
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(_at_)Lists(_dot_)mulberrytech(_dot_)com                      
                                                        |
  | 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



<Prev in Thread] Current Thread [Next in Thread>