SPD. Joined list, grouped by a field in the Parent List. Cannot get subtotals for the Child list per group.

Question

 

In SPD, I have set up a Linked Source, created a page w/a data view, and successfully managed to get the resulting information to appear as one row, and been able to group by a parent field. I can get subtotals for all the parent fields in the group footer, and I've been able to get a grand total of the subview field, but not the subtotal. 

 

Group Header -District A

    Acct 6809 |  Mary Smith      | 7 Units  | $40 (Child field) | 5/1/2011

    Acct 7895 |  George Jetson | 12 Units | $20 (Child field) | 5/1/2011

Group Footer: Count: 2, 19 Units, Child field will not subtotal.

Group Header -District B

    Acct 7458 |  Fred McMartin  | 8 Units    | $10 (Child field) | 5/1/2011

    Acct 6548 |  George Jetson | 20 Units  | $10 (Child field) | 5/1/2011

    Acct 2514 |  George Jetson | 9 Units    | $10 (Child field) | 5/1/2011

Group Footer: Count: 3, 37 Units, Child field will not subtotal.

Report Footer: Count: 5, 56 Units, $90 [Dang field will calc here]

 

So, how do I get the sub-total for a joined subview when the items are grouped by a field on the parent?

This is the code I used to get the grand total at the very end of the grouped list:

<xsl:value-of select="format-number(sum(/dsQueryResponse/ChildList/Rows/Row[@FieldInChildThatContainsParentID = /dsQueryResponse/ParentList/Rows/Row/@ID]/@Price), &quot;$#,##0.00;-$#,##0.00&quot;)" />

Any help would be much appreciated.

 

 

Answer 1

What code are you using to get the Units and Count?  

It may make sense to drop the format-number until you can get a good result back.

---

Cheers, Matt Bramer
My Blog
Send me a tweet

Answer 2

The "Unit" and "Count" are using fields from the parent list. And I added the number format once I had the grand total working.

 

Subtotal, Units: <xsl:value-of select="sum($nodeset/@Units)" />  This does not work for the Price field, because it is part of a joined subview.

Subtotal, Count:  <xsl:value-of select="count($nodeset/@Account_Number)" />

 

Grand Total, Units:<xsl:value-of select="sum(/dsQueryResponse/ParentList/Rows/Row[@ID = /dsQueryResponse/ChildList/Rows/Row/@FieldInChildThatContainsParentID]/@Units)" /> [As you can see, a modified version of this did work for getting the grand total of the Price field]

Grand Total, Count: <xsl:value-of select="count($Rows)" />

Answer 3

You were so close, it should look like this:

<xsl:value-of select="sum(/dsQueryResponse/ParentList/Rows/Row[@ID=$nodeset/@FieldInChildThatContainsParentID]/@Units)" />

There are more details to the solution here: http://www.thesug.org/Blogs/annw/archive/2012/3/10/MysterySolvedSumandSubtotalGroupsonaDataviewcontainingJoinedSubviews.aspx

---

=^..^=

Answer 4

Well, I have a new job now, and I'm not using SPD, so I can't confirm whose answer is correct. Thanks for the input.