xsl-list
[Top] [All Lists]

Re: sub total issue

2004-12-15 10:46:00
Hi,

> I have a  problem with sub totals in my transformation.
>
> I am getting the sub totals correctly if I do not specify a filter in the
> apply templates. I am using the
> following-sibling construct to produce totals on breaks.

Do you mean that the totals do not sum the amount of the shown subtotals but more, when using the filter?

You say you are using following-sibling, note that it is based on document order, not on the context list from the last apply-templates. The filter nor the sort of the apply-templates influence the behaviour of this axis, it simply gives access to all following-siblings that occur after the current node in the source tree.

> This does not produce sub totals at all:

I am seeing 4 numbers in the result, nicely formatted, but these aren't the sub totals you are looking for?

Cheers,
Geert

Srinivasan, Sundar wrote:


FOR EXAMPLE:

============================

<xsl:apply-templates select="MyTable[curr_code='AUD']" >
                <xsl:sort select="0" data-type="text" order="ascending" />
</xsl:apply-templates>

This produces sub totals correctly:
=========================

<xsl:apply-templates select="MyTable" >
                <xsl:sort select="0" data-type="text" order="ascending" />
</xsl:apply-templates>

I am attaching the xml and xslt documents. The xml is sorted on bs_group,
bs_categ.


This is the XML:
======================
<MyDataSet>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>01. Cash &amp; Due from Banks</bs_categ>
                <curr_code>AUD</curr_code>
                <ps_download>487637.750</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>01. Cash &amp; Due from Banks</bs_categ>
                <curr_code>AUD</curr_code>
                <ps_download>-587788.493</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>01. Cash &amp; Due from Banks</bs_categ>
                <curr_code>CAD</curr_code>
                <ps_download>73108.752</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>01. Cash &amp; Due from Banks</bs_categ>
                <curr_code>GBP</curr_code>
                <ps_download>4262274.160</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>01. Cash &amp; Due from Banks</bs_categ>
                <curr_code>GBP</curr_code>
                <ps_download>-2956402.499</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>01. Cash &amp; Due from Banks</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>10514518.380</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>02.Securities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>18294190.340</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>02.Securities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-18206224.390</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>04.Loans</bs_categ>
                <curr_code>AUD</curr_code>
                <ps_download>93587.020</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>04.Loans</bs_categ>
                <curr_code>AUD</curr_code>
                <ps_download>5802.950</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>04.Loans</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-742934.750</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>04.Loans</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-15000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>05.Trading Assets</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>17878.430</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>05.Trading Assets</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>11952104.300</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>05.Trading Assets</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>53801336.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>05.Trading Assets</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>450000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>06.Other Assets</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-4786154.220</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>06.Other Assets</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-6098133.630</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>06.Other Assets</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-6976731.810</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>06.Other Assets</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-8804476.850</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>07.Due To/From Related Parties</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-188018136.380</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>07.Due To/From Related Parties</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-195306355.530</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>07.Due To/From Related Parties</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-2635857513.620</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Assets</bs_group>
                <bs_categ>07.Due To/From Related Parties</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-36672230504.810</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-340000000.000</ps_download>
        </MyTable>
        <MyTable>\r\n
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-350000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-400000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-400000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-400000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-400000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-400000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-450000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-455000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-500000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-500000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-500000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-500000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-600000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-604000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-620500000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-660000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-990000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-1058000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-1150000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-1299000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>08.Deposits</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-1500000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>09.FFP &amp; REPOS</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-5064000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>09.FFP &amp; REPOS</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-250000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>09.FFP &amp; REPOS</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-1000000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>09.FFP &amp; REPOS</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-1030000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>10.Other Borrowings</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-365000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>10.Other Borrowings</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-365000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>10.Other Borrowings</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-400000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>10.Other Borrowings</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-600000000.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>11.Trading Liabilities</bs_categ>
                <curr_code>EUR</curr_code>
                <ps_download>219496.600</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>11.Trading Liabilities</bs_categ>
                <curr_code>JPY</curr_code>
                <ps_download>-0.010</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>11.Trading Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>0.010</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>11.Trading Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>0.010</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>11.Trading Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>93193.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>11.Trading Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>206472.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>11.Trading Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-0.030</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>11.Trading Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-5888.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>11.Trading Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-305686.520</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>11.Trading Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-1788707.000</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>12.Other Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-32.850</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>12.Other Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-67.260</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>12.Other Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-21991446.060</ps_download>
        </MyTable>
        <MyTable>
                <bs_group>Liabilities</bs_group>
                <bs_categ>12.Other Liabilities</bs_categ>
                <curr_code>USD</curr_code>
                <ps_download>-22959632.690</ps_download>
        </MyTable>
</MyDataSet>


This is the XSLT:
==============

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
version="1.0">



<xsl:key name="key-group"                  match="MyTable" use="bs_group"/>
<xsl:key name="key-group-bs_categ" match="MyTable"
use="concat(bs_group,'+',bs_categ)"/>

<xsl:variable name="ps_download_asset_sub_total"
select="sum(key('key-group','Assets')/ps_download)"></xsl:variable>

<xsl:variable name="ps_download_liab_sub_total"
select="sum(key('key-group','Liabilities')/ps_download)"></xsl:variable>

<xsl:template match="/">
<xsl:apply-templates select="MyDataSet" />
</xsl:template>

<xsl:template match="MyDataSet">

<div>
                <div id="CmnSpanSearchCriteria" ></div>
        <div id="CmnListOverFlowDiv"
style="width:820px;position:absolute;display:inline">
        <TABLE   width='800px'  >
                 <tr>
                        <th  id="bs_group"         style="WIDTH:3%;"        
align='center' >BS Group                          </th>
                        <th  id="bs_categ"         style="WIDTH:5%;"        
align='center' >BS category
</th>
                        <th id="curr_code"         style="WIDTH:3%;"        
align='center' > Currency                 </th>
                        <th id="ps_download"       style="WIDTH:4%;"        
align='center' > Amount                    </th>
                </tr>
        </TABLE>

                <TABLE   width='800px'   >
                        <TBODY>
                                <xsl:apply-templates 
select="MyTable[curr_code='AUD']" >
                                                <xsl:sort select="0" data-type="text" 
order="ascending" />
                                </xsl:apply-templates>
                        </TBODY>
                </TABLE>
        </div> <!-- end divDetail -->
</div>
</xsl:template>

<xsl:template match="MyTable">

                                <tr>
                                        <td style="WIDTH:3%;" > <xsl:value-of 
select="bs_group"                    />   </td>
                                        <td style="WIDTH:5%"> <xsl:value-of 
select="bs_categ"                      />   </td>
                                        <td style="WIDTH:3%;"> <xsl:value-of 
select="curr_code"            />   </td>
                                        <td align="right" style="WIDTH:4%;">
                                                <xsl:value-of
select="format-number(ps_download,'$#,###,###,###,###,###.000')"/>
                                        </td>
                                </tr>

                <!-- Categ total -->
                        <xsl:if test="(following-sibling::MyTable[1]/bs_categ 
!= bs_categ) or
(following-sibling::MyTable[1]/bs_group != bs_group)  or (position() =
last())">
                                <xsl:variable name="ps_download_categ_sub_total"
select="sum(key('key-group-bs_categ',concat(bs_group,'+',bs_categ))/ps_download)"></xsl:variable>

                                <tr >
                                        <td  colspan="3">Categ Total</td>
                                        <td align="right" >
                                                <xsl:value-of
select="format-number($ps_download_categ_sub_total,'$#,###,###,###,###,###.000')"/>
                                        </td>
                                </tr>
                        </xsl:if>

                <!-- Asset total -->
                        <xsl:if test="(following-sibling::MyTable[1]/bs_group 
!= bs_group) and
(bs_group='Assets')">
                                <tr >
                                                <td colspan="3">Total <xsl:value-of 
select="bs_group" /></td>
                                                <td align="right">
                                                        <xsl:value-of
select="format-number($ps_download_asset_sub_total,'$#,###,###,###,###,###.000')"/>
                                                </td>
                                </tr>
                        </xsl:if>

                <!-- Liability total -->
                        <xsl:if test="position() = last()">
                                <tr>
                                                <td colspan="3">Total <xsl:value-of 
select="bs_group" /></td>
                                                <td align="right">
                                                        <xsl:value-of
select="format-number($ps_download_liab_sub_total,'$#,###,###,###,###,###.000')"/>
                                                </td>
                                </tr>


                </xsl:if>
</xsl:template>
</xsl:stylesheet>



==============================================================================
This message is for the sole use of the intended recipient. If you received
this message in error please delete it and notify us. If this message was
misdirected, CSFB does not waive any confidentiality or privilege. CSFB
retains and monitors electronic communications sent through its network.
Instructions transmitted over this system are not binding on CSFB until they
are confirmed by us. Message transmission is not guaranteed to be secure.
==============================================================================


--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--




--
Geert(_dot_)Josten(_at_)Daidalos(_dot_)nl
IT-consultant at Daidalos BV, Zoetermeer (NL)

http://www.daidalos.nl/
tel:+31-(0)79-3316961
fax:+31-(0)79-3316464

GPG: 1024D/12DEBB50

--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--



<Prev in Thread] Current Thread [Next in Thread>
  • sub total issue, Srinivasan, Sundar
    • Re: sub total issue, Geert Josten <=