Help with this please 2

[sonya9879]

hello,

I am trying to render an xml file but I am having problems trying to get the record count for the categories and the total amount (Pi in the XML) for each category. I am posting the XML and the XSL I have to see if someone could help me. It supposed to be something very simple but I spend 3 days working out solutions but nothing works. Any help will be greatly appreciated, thanks

Report 12/21/2005

Total: 4 11.00
Total A: ? ?
Total AB: ? ?
Total ABC: ? ?

XML:

<?xml version="1.0" standalone="yes"?>
<Reports>
<Entry>
<uniqueID>4</uniqueID>
<date>12/21/2005</date>
<sNo>9000</sNo>
<sID>661</sID>
<Pi>2.0000</Pi>
<sName>Test Test</sName>
<sRName>SMITH</sRName>
<sRNameL>KKL</sRNameL>
<Category>1</Category>
<sInterNo />
<sInterR>0</sInterR>
</Entry>
<Entry>
<uniqueID>5</uniqueID>
<date>12/21/2005</date>
<sNo>9000</sNo>
<sID>622</sID>
<Pi>3.0000</Pi>
<sName>Test Test</sName>
<sRName>POWELL</sRName>
<sRNameL>KKL</sRNameL>
<Category>2</Category>
<sInterNo>445544</sInterNo>
<sInterR>2</sInterR>
</Entry>
<Entry>
<uniqueID>385473</uniqueID>
<date>12/20/2005</date>
<sNo>9000</sNo>
<sID>189</sID>
<Pi>2.0000</Pi>
<sName>Test Test</sName>
<sRName>JIMTON</sRName>
<sRNameL>KKL</sRNameL>
<Category>2</Category>
<sInterNo>123456</sInterNo>
<sInterR>2</sInterR>
</Entry>
<Entry>
<uniqueID>387590</uniqueID>
<date>12/21/2005</date>
<sNo>9000</sNo>
<sID>929</sID>
<Pi>4.0000</Pi>
<sName>Test Test</sName>
<sRName>MAN</sRName>
<sRNameL>KKL</sRNameL>
<Category>4</Category>
<sInterNo />
<sInterR>0</sInterR>
</Entry>
<RecNum>
<TotRec>4</TotRec>
</RecNum>
</Reports>

Here is the XSLT:

<xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform">

<xslutput method="xml" doctype-public="-//W3C//DTD XHTML 1.0 Strict//EN"

doctype-system="

http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"/>

<xsl:key name="Reports" match="Entry" use="Category" />
<xsl:template match="/">
<style type="text/css">
BODY
{
margin-left: 0;
margin-bottom: 0;
margin-right: 0;
FONT-SIZE: 14px;
FONT-FAMILY: Arial, Helvetica, sans-serif
}
caption
{
text-align: left;
font-weight: bold;
font-size: 14px;
FONT-FAMILY: Arial, Helvetica, sans-serif
}
</style>
<xsl:apply-templates select="Reports/Entry[not(sRNameL = preceding-sibling::Entry/sRNameL)]" mode="sRNameL"/>
</xsl:template>
<xsl:template match="Entry" mode="sRNameL">
<TABLE width="600" style='width:6.25in;mso-cellspacing:.7pt;margin-left:.5in;mso-padding-alt:1.5pt 1.5pt 1.5pt 1.5pt'

border="1" cellspacing="1" cellpadding="0">
<TR>
<TD colspan="3" align="middle"><b><font size="+1"><xsl:apply-templates select="sRNameL"/> Facility Report

<xsl:value-of select="//Reports/Entry/date"/></font></b></TD>
</TR>
<TR>
<TD><b>Total:</b></TD>
<TD align="right"><xsl:value-of select="//Reports/RecNum/TotRec"/></TD>
<TD align="right"><xsl:value-of select="format-number(sum(//Reports/Entry/Pi),'$###0.00')"/></TD>
</TR>
<xsl:if test="../Entry[sRNameL = current()/sRNameL and Category = current()/Category]">
<xsl:variable name="count" select="count(uniqueID)"/>
</xsl:if>
<xsl:for-each select="../Entry[sRNameL = not(Category = preceding-sibling::Entry[state =

current()/sRNameL]/Category)]">
<xsl:if test="Category[not(. = following::Category)]">
<TR>
<TD><b>Total <xsl:apply-templates select="Category"/>:</b></TD>
<TD align="right"></TD>
<TD align="right"><xsl:value-of select="format-number(sum(Pi),'$###0.00')" />
</TD>
</TR>
</xsl:if>
</xsl:for-each>
</TABLE>
<br>&#160;</br>
<xsl:apply-templates select="../Entry[sRNameL = current()/sRNameL and not(Category = preceding-sibling::Entry[state =

current()/sRNameL]/Category)]" mode="Category"/>
</xsl:template>
<xsl:template match="Entry" mode="Category">
<dd>
<xsl:if test="Category[not(. = following::Category)]">
<table border="1" width="600" cellspacing="1" cellpadding="2">
<caption>
<center><xsl:apply-templates select="Category"/></center>
</caption>
<thead>
<tr>
<TD align="middle"><B>ID</B></TD>
<TD align="middle"><B>KKL</B></TD>
<TD align="middle"><B>Name</B></TD>
<TD align="middle"><B>Pi Amount</B></TD>
<TD align="middle"><B>Name</B></TD>
<TD align="middle"><B>Add</B></TD>
</tr>
</thead>
<tbody>
<xsl:for-each select="../Entry[sRNameL = current()/sRNameL and Category = current()/Category]">
<tr>
<TD align="middle"><xsl:text> </xsl:text><xsl:value-of

select="uniqueID"/><xsl:text></xsl:text></TD>
<TD align="middle"><xsl:text> </xsl:text><xsl:value-of

select="sID"/><xsl:text></xsl:text></TD>
<TD align="middle"><xsl:text> </xsl:text><xsl:value-of

select="sRName"/><xsl:text></xsl:text></TD>
<TD align="right"><xsl:text> </xsl:text><xsl:value-of

select="format-number(Pi,'$###0.00')"/><xsl:text></xsl:text></TD>
<TD align="middle"><xsl:text> </xsl:text><xsl:value-of

select="sName"/><xsl:text></xsl:text></TD>
<TD align="middle"><xsl:text> </xsl:text><xsl:value-of

select="sName"/><xsl:text></xsl:text></TD>
</tr>
<TR>
<xsl:variable name="sInterNo" select="sInterNo" />
<xsl:choose>
<xsl:when test="sInterNo!=''">
<TD colspan="2" align="right"><B>INTER NO:</B></TD>
<TD><xsl:value-of select="$sInterNo" /></TD>
</xsl:when>
</xsl:choose>
<xsl:variable name="sInterR" select="sInterR" />
<xsl:choose>
<xsl:when test="sInterR!='0'">
<TD><B>INTER R:</B></TD>
<TD><xsl:apply-templates select="$sInterR"/></TD>
</xsl:when>
</xsl:choose>
</TR>
</xsl:for-each>
</tbody>
</table>
</xsl:if>
</dd>
</xsl:template>

<xsl:template name="ShowTransfers">
<xslaram name="transfers" />
<xsl:variable name="lcletters">abcdefghijklmnopqrstuvwxyz</xsl:variable>
<xsl:variable name="ucletters">ABCDEFGHIJKLMNOPQRSTUVWXYZ</xsl:variable>
<xsl:for-each select="//Entry">
<TR>
<TD bgcolor="#eee3e1"><B>TYPE OF CATEGORY:</B></TD>
<TD bgcolor="#eee3e1"><xsl:apply-templates select='Category'/></TD>
</TR>
<TR><TD COLSPAN="2">
<TABLE width="100%" border="0" cellspacing="1" cellpadding="2">
<TR>
<TD><B>ID</B></TD>
<TD><B>KKL</B></TD>
<TD><B>Name</B></TD>
<TD><B>Pi Amount</B></TD>
<TD><B>Name</B></TD>
<TD><B>Add</B></TD>
</TR>
<xsl:variable name="vuniqueID"><xsl:value-of select="uniqueID" /></xsl:variable>
<xsl:variable name="alltransfers" select="//Reports[uniqueID=$vuniqueID]" />
<xsl:for-each select="$alltransfers">
<TR>
<TD><xsl:text> </xsl:text><xsl:value-of select="uniqueID"/><xsl:text></xsl:text></TD>
<TD><xsl:text> </xsl:text><xsl:value-of select="sID"/><xsl:text></xsl:text></TD>
<TD><xsl:text> </xsl:text><xsl:value-of

select="translate(sRName,$lcletters,$ucletters)"/><xsl:text></xsl:text></TD>
<TD><xsl:text> </xsl:text><xsl:value-of

select="format-number(Pi,'$###0.00')"/><xsl:text></xsl:text></TD>
<TD><xsl:text> </xsl:text><xsl:value-of

select="translate(sName,$lcletters,$ucletters)"/><xsl:text></xsl:text></TD>
<TD><xsl:text> </xsl:text><xsl:value-of

select="translate(sName,$lcletters,$ucletters)"/><xsl:text></xsl:text></TD>
</TR>
<TR>
<xsl:variable name="sInterNo" select="sInterNo" />
<xsl:choose>
<xsl:when test="sInterNo!=''">
<TD colspan="2" align="right"><B>INTER NO:</B></TD>
<TD><xsl:value-of select="$sInterNo" /></TD>
</xsl:when>
</xsl:choose>
<xsl:variable name="sInterR" select="sInterR" />
<xsl:choose>
<xsl:when test="sInterR!='0'">
<TD><B>INTER R:</B></TD>
<TD><xsl:apply-templates select="$sInterR"/></TD>
</xsl:when>
</xsl:choose>
</TR>
</xsl:for-each>
</TABLE>
</TD></TR>
</xsl:for-each>
</xsl:template>
<xsl:template match="Category">
<xsl:variable name="CategoryVar" select="." />
<xsl:choose>
<xsl:when test="$CategoryVar='1'">
A
</xsl:when>
</xsl:choose>
<xsl:choose>
<xsl:when test="$CategoryVar='2'">
AB
</xsl:when>
</xsl:choose>
<xsl:choose>
<xsl:when test="$CategoryVar='3'">
DDD
</xsl:when>
</xsl:choose>
<xsl:choose>
<xsl:when test="$CategoryVar='4'">
ABC
</xsl:when>
</xsl:choose>
</xsl:template>
<xsl:template match="sInterR">
<xsl:variable name="sInterRVar" select="." />
<xsl:choose>
<xsl:when test="$sInterRVar='1'">
SND
</xsl:when>
</xsl:choose>
<xsl:choose>
<xsl:when test="$sInterRVar='2'">
IPN
</xsl:when>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>

thanks a lot

 

[sonya9879]

hi, does anyone knows the answer to this? try saving my xml as test.xml and the xsl as test.xsl and add this in the xml file, <?xml-stylesheet href="test.xsl" type="text/xsl"?> and u will see the report rendered and the missing information for the total count and total Pi amount under each category.

i.e. KKL Facility Report 12/21/2005
Total: 4 $11.00 (4 is the total number of records)
Total A: ? (? is the number of records under this category) $2.00 (this amount is wrong, it should be the total for this category)
Total AB: ? (? is the number of records under this category) $2.00 (this amount is wrong, it should be the total for this category)
Total ABC: ? (? is the number of records under this category) $4.00 (this amount is wrong, it should be the total for this category)

the rest of the report is correct. Thanks a lot to whoever can help me with this, i am driving nuts finding a solution.

 

[sonya9879]

hello? can someone help me with this?

 

[tsuji]

It is some long verbose doc's, as all xml/xsl thing!, in particular, you have some style that make it quite difficult to deciphe. I narrow down the problem to the difficulty you must have had in grouping and do the partial sum. (The grand sum, you have made it to a separate child to the root. I have some reserve for this kind of mixing up raw data and derived data. But, you know better your stuff...)

Here is a template where you get the essential steps to make the grouping per Category and partial sum of Pi.
[tt]
<xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform">

<!-- since there is no scoping issue in this problem, use simplified global scope key -->
<xsl:key name="keyidx" match="Category" use="." />
<xsl:template match="Reports">
<xsl:variable name="entry_list" select="Entry" />
<xsl:for-each select="$entry_list/Category[generate-id()=generate-id(key('keyidx',.))]">
<xsl:variable name="x" select="." />
<xsl:text>[Unique Category=</xsl:text>
<xsl:value-of select="." />
<xsl:text>;Partial Sum:</xsl:text>
<xsl:value-of select="sum(//Pi[../Category=$x])" />
<xsl:text>]</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
[/tt]
The text elements are for cosmetic only. You have to re-implant this type of construction into your xsl.

 

[tsuji]

Upon re-reading my posted script, I should add also the partial count of entries. It is the same construction. The functional block is then looked like this with slight change of cosmetic.
[tt]
<xsl:key name="keyidx" match="Category" use="." />
<xsl:template match="Reports">
<xsl:variable name="entry_list" select="Entry" />
<xsl:for-each select="$entry_list/Category[generate-id()=generate-id(key('keyidx',.))]">
<xsl:variable name="x" select="." />
<xsl:text>[Unique Category=</xsl:text>
<xsl:value-of select="." />
<xsl:text>;Count=</xsl:text>
<xsl:value-of select="count(//Pi[../Category=$x])" />
<xsl:text>;Partial Sum=</xsl:text>
<xsl:value-of select="sum(//Pi[../Category=$x])" />
<xsl:text>]</xsl:text>
</xsl:for-each>
</xsl:template>
[/tt]

 

[sonya9879]

Dear tsuji,

THANKS A LOT for helping me with this, I was driving nuts trying to get this count sorted. I have taken your suggestion and added that on my xsl.

basically where I had:

<xsl:for-each select="../Entry[sRNameL = not(Category = preceding-sibling::Entry[state =

current()/sRNameL]/Category)]">
<xsl:if test="Category[not(. = following::Category)]">
<TR>
<TD><b>Total <xsl:apply-templates select="Category"/>:</b></TD>
<TD align="right"></TD>
<TD align="right"><xsl:value-of select="format-number(sum(Pi),'$###0.00')" />
</TD>
</TR>
</xsl:if>
</xsl:for-each>

I replaced that with

<xsl:for-each select="$entry_list/Category[generate-id()=generate-id(key('keyidx',.))]">
<xsl:variable name="x" select="." />
<TR>
<TD><b>Total <xsl:apply-templates select="../Category"/>:</b></TD>
<TD align="right"><xsl:value-of select="count(//Pi[../Category=$x])" /></TD>
<TD align="right"><xsl:value-of select="format-number(sum(//Pi[../Category=$x]),'$###0.00')" /></TD>
</TR>
</xsl:for-each>

and added the key and entry_name variable on top specifiying the full path of each tag and it is working now like a charm.

Regarding your reservations about mixing up raw data and derived data, what alternatives you would take? I also think I got this pretty mixed after attempting several times to get this counting working.

a star for you for your great help! you made my day thanks again.

 

[tsuji]

I'm relieved just for your understanding the essential of the thing I'd put together---it's not a priori easy and that proves you know your stuff!; and thanks for your vote too.

The grand count and sum should be easier than partial count and sum with grouping requirement. Hence, RecNum element for processing a data xml file is really not a good idea because your xml would then be much constricted and a fully consistent data file would then warrant an additinal processing on it.

The grand statistics is easier. (I follow the Pi element as I have had in the above. That's not generic. Feel free to follow entry element for instance.
[tt]
<xsl:text>[All Category;Count=</xsl:text>
<xsl:value-of select="count(//Pi)" />
<xsl:text>;Sum=</xsl:text>
<xsl:value-of select="sum(//Pi)" />
<xsl:text>]</xsl:text>
[/tt]
The block place should be placed outside of the xsl:for-each block and before, for instance, the closing tag </xsl:template>, or anywhere corresponding to it in your renderment.

 

[sonya9879]

I see what you mean, thanks so much for your input. I will follow your sample .