SPD. Joined list, grouped by a field in the Parent List. Cannot get subtotals for the Child list per group.
Mittwoch, 11. Mai 2011 22:14
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]
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), "$#,##0.00;-$#,##0.00")" />
Any help would be much appreciated.
Mittwoch, 11. Mai 2011 23:01
Donnerstag, 12. Mai 2011 13:47
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)" />
Samstag, 10. März 2012 14:12
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
- Bearbeitet ahwall Samstag, 10. März 2012 14:12
Montag, 12. März 2012 14:46Well, 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.