Dearest Professionals,
I have some joint billing XML code that has Time Card / Timekeeper summaries within. I can easily group by a specific Matter, then by Timekeeper and get totals per matter. But I need to get a sum of all hours and billed amounts by timekeeper for the entire joint bill, not just per matter.
I have all code referenced below, but updated from @Tim-C 's comment. The XSL Transform link is HERE
INPUT:
<?xml version="1.0" encoding="utf-8"?>
<superbill>
<invoice type="P" id="562845" number="562845">
<matters>
<matter number="014592-000007">
<timecard-summary-by-timekeeper>
<timekeeper-summary timekeeper-id="NC1">
<timekeeper>
<initials>NC1</initials>
<billingname>Nicholas J. Collins</billingname>
</timekeeper>
<timekeeper-summary-totals>
<timekeeper-summary-total type="billed">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="worked">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="standard">
<total type="rate">525.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate1">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate2">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
</timekeeper-summary-totals>
</timekeeper-summary>
<timekeeper-summary timekeeper-id="BJB">
<timekeeper>
<initials>BJB</initials>
<billingname>Billie J. Bob</billingname>
</timekeeper>
<timekeeper-summary-totals>
<timekeeper-summary-total type="billed">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="worked">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="standard">
<total type="rate">575.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate1">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate2">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
</timekeeper-summary-totals>
</timekeeper-summary>
</timecard-summary-by-timekeeper>
</matter>
<matter number="014592-000091">
<timecard-summary-by-timekeeper>
<timekeeper-summary timekeeper-id="NC1">
<timekeeper>
<initials>NC1</initials>
<billingname>Nicholas J. Collins</billingname>
</timekeeper>
<timekeeper-summary-totals>
<timekeeper-summary-total type="billed">
<total type="rate">525.00</total>
<total type="hours">1.00</total>
<total type="amount">525.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="worked">
<total type="rate">525.00</total>
<total type="hours">1.00</total>
<total type="amount">525.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="standard">
<total type="rate">525.00</total>
<total type="hours">1.00</total>
<total type="amount">525.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate1">
<total type="rate">525.00</total>
<total type="hours">1.00</total>
<total type="amount">525.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate2">
<total type="rate">0.00</total>
<total type="hours">1.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
</timekeeper-summary-totals>
</timekeeper-summary>
<timekeeper-summary timekeeper-id="BJB">
<timekeeper>
<initials>BJB</initials>
<billingname>Billie J. Bob</billingname>
</timekeeper>
<timekeeper-summary-totals>
<timekeeper-summary-total type="billed">
<total type="rate">575.00</total>
<total type="hours">11.00</total>
<total type="amount">6325.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="worked">
<total type="rate">575.00</total>
<total type="hours">11.00</total>
<total type="amount">6325.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="standard">
<total type="rate">575.00</total>
<total type="hours">11.00</total>
<total type="amount">6325.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate1">
<total type="rate">575.00</total>
<total type="hours">11.00</total>
<total type="amount">6325.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate2">
<total type="rate">0.00</total>
<total type="hours">11.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
</timekeeper-summary-totals>
</timekeeper-summary>
</timecard-summary-by-timekeeper>
</matter>
<matter number="014592-000092">
<timecard-summary-by-timekeeper>
<timekeeper-summary timekeeper-id="NC1">
<timekeeper>
<initials>NC1</initials>
<billingname>Nicholas J. Collins</billingname>
</timekeeper>
<timekeeper-summary-totals>
<timekeeper-summary-total type="billed">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="worked">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="standard">
<total type="rate">525.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate1">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate2">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
</timekeeper-summary-totals>
</timekeeper-summary>
<timekeeper-summary timekeeper-id="BJB">
<timekeeper>
<initials>BJB</initials>
<billingname>Billie J. Bob</billingname>
</timekeeper>
<timekeeper-summary-totals>
<timekeeper-summary-total type="billed">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="worked">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="standard">
<total type="rate">575.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate1">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate2">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
</timekeeper-summary-totals>
</timekeeper-summary>
</timecard-summary-by-timekeeper>
</matter>
<matter number="014592-000095">
<timecard-summary-by-timekeeper>
<timekeeper-summary timekeeper-id="NC1">
<timekeeper>
<initials>NC1</initials>
<billingname>Nicholas J. Collins</billingname>
</timekeeper>
<timekeeper-summary-totals>
<timekeeper-summary-total type="billed">
<total type="rate">525.00</total>
<total type="hours">4.00</total>
<total type="amount">2100.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="worked">
<total type="rate">525.00</total>
<total type="hours">4.00</total>
<total type="amount">2100.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="standard">
<total type="rate">525.00</total>
<total type="hours">4.00</total>
<total type="amount">2100.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate1">
<total type="rate">525.00</total>
<total type="hours">4.00</total>
<total type="amount">2100.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate2">
<total type="rate">0.00</total>
<total type="hours">4.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
</timekeeper-summary-totals>
</timekeeper-summary>
<timekeeper-summary timekeeper-id="BJB">
<timekeeper>
<initials>BJB</initials>
<billingname>Billie J. Bob</billingname>
</timekeeper>
<timekeeper-summary-totals>
<timekeeper-summary-total type="billed">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="worked">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="standard">
<total type="rate">575.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate1">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate2">
<total type="rate">0.00</total>
<total type="hours">0.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
</timekeeper-summary-totals>
</timekeeper-summary>
</timecard-summary-by-timekeeper>
</matter>
<matter number="014592-000096">
<timecard-summary-by-timekeeper>
<timekeeper-summary timekeeper-id="NC1">
<timekeeper>
<initials>NC1</initials>
<billingname>Nicholas J. Collins</billingname>
</timekeeper>
<timekeeper-summary-totals>
<timekeeper-summary-total type="billed">
<total type="rate">525.00</total>
<total type="hours">20.00</total>
<total type="amount">10500.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="worked">
<total type="rate">525.00</total>
<total type="hours">20.00</total>
<total type="amount">10500.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="standard">
<total type="rate">525.00</total>
<total type="hours">20.00</total>
<total type="amount">10500.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate1">
<total type="rate">525.00</total>
<total type="hours">20.00</total>
<total type="amount">10500.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate2">
<total type="rate">0.00</total>
<total type="hours">20.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
</timekeeper-summary-totals>
</timekeeper-summary>
<timekeeper-summary timekeeper-id="BJB">
<timekeeper>
<initials>BJB</initials>
<billingname>Billie J. Bob</billingname>
</timekeeper>
<timekeeper-summary-totals>
<timekeeper-summary-total type="billed">
<total type="rate">575.00</total>
<total type="hours">4.00</total>
<total type="amount">2300.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="worked">
<total type="rate">575.00</total>
<total type="hours">4.00</total>
<total type="amount">2300.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="standard">
<total type="rate">575.00</total>
<total type="hours">4.00</total>
<total type="amount">2300.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate1">
<total type="rate">575.00</total>
<total type="hours">4.00</total>
<total type="amount">2300.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
<timekeeper-summary-total type="timerate2">
<total type="rate">0.00</total>
<total type="hours">4.00</total>
<total type="amount">0.00</total>
<total type="adjustment">0.00</total>
</timekeeper-summary-total>
</timekeeper-summary-totals>
</timekeeper-summary>
</timecard-summary-by-timekeeper>
</matter>
</matters>
</invoice>
</superbill>
CURRENT CODE:
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
version="2.0">
<xsl:output method="xml" indent="yes" />
<xsl:template match="/">
<xsl:call-template name="XJ_TKSummary_02" />
</xsl:template>
<xsl:template name="XJ_TKSummary_02">
<xsl:variable name="TW" select="1440" />
<xsl:for-each-group select="//superbill/invoice/matters/matter/timecard-summary-by-timekeeper/timekeeper-summary/timekeeper" group-by="../@timekeeper-id">
<w:p>
<w:r>
<w:t><xsl:value-of select="initials" /> - <xsl:value-of select="billingname" /></w:t>
</w:r>
</w:p>
<w:p>
<w:r>
<xsl:variable name="groupTotals" select="current-group()/timekeeper-summary-totals/timekeeper-summary-total[@type='billed']/total" />
<w:t>
Hours: <xsl:value-of select="format-number(sum($groupTotals[@type='hours']), '###,##0.00')" />
for $ <xsl:value-of select="format-number(sum($groupTotals[@type='amount']), '###,##0.00')" />
</w:t>
</w:r>
</w:p>
</xsl:for-each-group>
</xsl:template>
</xsl:stylesheet>
OUTPUT:
The desired totals should read...
NC1 - Nicholas J. Collins
Hours: 25.00 for $13,125.00
BJB - Billie J. Bob
Hours: 15.00 for $8,625.00
Any and all assistance would be appreciated.
Regard,
-Nick
The main thing you need to do is change the xsl:for-each-group to this...
<xsl:for-each-group select="//invoice/matter/timecard-summary-by-timekeeper/timekeeper-summary/timekeeper" group-by="../@timekeeper-id">
So, you are grouping timekeeper records by their parent timekeeper-id attribute.
You also need to include the timekeeper-summary-totals in the xpath to get your total hours and amount.
<w:t>
Hours:
<xsl:value-of select="format-number(sum(current-group()/timekeeper-summary-totals/timekeeper-summary-total[@type='billed']/total[@type='hours']), '###,##0.00')" />
for $
<xsl:value-of select="format-number(sum(current-group()/timekeeper-summary-totals/timekeeper-summary-total[@type='billed']/total[@type='amount']), '###,##0.00')" />
</w:t>
Or, better still, use a variable to cut down on the code repitition of the xpath
Try this abridged XSLT
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
version="2.0">
<xsl:output method="xml" indent="yes" />
<xsl:template match="/">
<xsl:call-template name="XJ_TKSummary_02" />
</xsl:template>
<xsl:template name="XJ_TKSummary_02">
<xsl:variable name="TW" select="1440" />
<xsl:for-each-group select="//invoice/matter/timecard-summary-by-timekeeper/timekeeper-summary/timekeeper" group-by="../@timekeeper-id">
<w:p>
<w:r>
<w:t><xsl:value-of select="initials" /> - <xsl:value-of select="billingname" /></w:t>
</w:r>
</w:p>
<w:p>
<w:r>
<xsl:variable name="groupTotals" select="current-group()/timekeeper-summary-totals/timekeeper-summary-total[@type='billed']/total" />
<w:t>
Hours: <xsl:value-of select="format-number(sum($groupTotals[@type='hours']), '###,##0.00')" />
for $ <xsl:value-of select="format-number(sum($groupTotals[@type='amount']), '###,##0.00')" />
</w:t>
</w:r>
</w:p>
</xsl:for-each-group>
</xsl:template>
</xsl:stylesheet>