locked
SPD: Getting subtotals for items in a sub view. RRS feed

  • Question

  • SPD: I have two lists: Customers and Services. Customers is the "Parent List", and may have multiple values in the "Services" list, but should only have one per year. The entire report is restricted to one year, so that should not be an issue. I have my dvwp all set up, and am able to display one line per customer, and group as needed. My only sticking point is that I cannot get the subtotals or totals for a field in the subview. The field I need to subtotal on is "TotalMoney".

    I am thinking that an XPath Formula field is the way to go, but I am not getting anywhere. I feel I'm on the right track, but am stuck. Something like this:

    sum(/dsQueryResponse/ChildList/Rows/Row[@ParentIDSavedinChildList= /dsQueryResponse/ParentList/Rows/Row/@ID]  AND [/dsQueryResponse/ChildList/Rows/Row[@GroupedField= /dsQueryResponse/ParentList/Rows/Row/@GroupedField)/@TotalMoney

    I know that is not in the correct format. I'm not sure how to throw an "and" clause in there, or even if it's possible. Thoughts? The only ideas that I have kind of stink. May a separate report for each grouping. Create a calculated field for each potential grouping (a total nightmare).

    Thoughts? Please? I'll give you chocolate or beer - your choice :)


     

    Tuesday, May 17, 2011 4:39 PM

All replies

  • Hi,

    Thanks to share your post.

    For the issue, I think you need to learn more about the XPath Syntax:

    http://msdn.microsoft.com/en-us/library/ms256471.aspx.

    http://msdn.microsoft.com/en-us/library/ms256471(v=vs.85).aspx.

    And please followed the correct steps in creating a formula column in a Data View:

    http://office.microsoft.com/en-us/sharepoint-designer-help/create-a-formula-column-in-a-data-view-HA010099138.aspx.

    Hope this could help you!

    Leo

     

     

     

    Friday, May 20, 2011 5:39 AM
  • I don't think I did a good job explaining my problem.

    I have a DVWP, with a joined subview.

    I know that usually when you have a joined subview, it looks like this:

    Row1: Parent FieldA | Parent FieldB

                                 | Child FieldA

                                 | Child FieldA

     

    Row2: Parent FieldA | Parent FieldB

                                 | Child FieldA

                                 | Child FieldA

     

    For my DVWP, however, the parent-to-child is one-to-one, so the results appear as a single row:

     

    Row1: Parent FieldA | Parent FieldB | Child FieldA

    Row2: Parent FieldB | Parent FieldB | Child FieldA

     

    The DVWP also uses a parameter to return results for a specific year, and is grouped by a Parent Field. What I cannot do then is create a subtotal for the Child Fields *per group*. I can create the grand totals (and, yes, I use a formula field, using XPath). I understand how to use an "and" statement properly in an XPath.

     

    For instance, this is the formula I am using to calculate the grand total for a Parent Field:

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

     

    So, what I really don't know is how to create a formula that would include information about the group, so my child records would subtotal correctly. How do I say "and fieldX = CurrentGroup" and have "group" be change automatically to the correct group. "$nodeset" is not an option when making a formula for a child field. I realize I could hard-code this for each group, but that'd be time consuming and painful if we added a new group. 

     

     

     

    Friday, May 20, 2011 1:39 PM