Showing Subtotals in a DVWP, Sorted by the Subtotals – Part 1

ByMarc D Anderson

I ran across a post over at the MSDN SharePoint – Design and Customization forum which intrigued me enough to want to figure it out.  The question was about showing subtotals for a given column’s values, with the results sorted by the subtotal, descending.

In other words, for a list with these items:

Title Amount
Bob 20
Chrissy 10
Bob 5
Mabel 50

Return this:

Title Amount
Mabel 50
Bob 25
Chrissy 10

Here’s the code to do it.  The tricky parts were to get the xsl:sort value right and then to display the items with duplicates removed using ddwrt:NameChanged.  I’m sure that there are more efficient ways to do this, but this one works!

<xsl:template name="dvt_1.body">
    <xsl:param name="Rows"/>
    <xsl:for-each select="$Rows">
        <xsl:sort select="sum($Rows&#91;current()/@Title=@Title&#93;/@Amount)" data-type="number" order="descending" />
        <xsl:call-template name="dvt_1.rowview">
            <xsl:with-param name="Rows" select="$Rows"/>
        </xsl:call-template>
    </xsl:for-each>
</xsl:template> 

<xsl:template name="dvt_1.rowview">
  <xsl:param name="Rows"/>
  <xsl:variable name="NewTitle" select="ddwrt:NameChanged(string(@Title), 0)"/>
  <xsl:if test="string-length($NewTitle) > 0">
<tr>
<td class="ms-vb">
       <xsl:value-of select="@Title"/>
    </td>
<td class="ms-vb">
       <xsl:value-of select="sum($Rows&#91;current()/@Title=@Title&#93;/@Amount)"/>
    </td>
   </tr>
  </xsl:if>
 </xsl:template>

UPDATE 2009-05-21: I just found another approach over at Christian’s Pampigt blog.  By changing the xsl:for-each to test for unique Titles, we don’t need to do the work in the dvt_1.rowview template below.  I don’t know that this would be any more efficient (I’m sure the XSL gurus out there would know), but it’s a nice alternate approach.

<xsl:template name="dvt_1.body">
  <xsl:param name="Rows"/>
  <xsl:for-each select="$Rows&#91;not(@Title = preceding-sibling::*/@Title)&#93;">
   <xsl:sort select="sum($Rows&#91;current()/@Title=@Title&#93;/@Amount)" data-type="number" order="descending" />
   <xsl:call-template name="dvt_1.rowview">
    <xsl:with-param name="Rows" select="$Rows"/>
   </xsl:call-template>
  </xsl:for-each>
 </xsl:template>
 
 <xsl:template name="dvt_1.rowview">
  <xsl:param name="Rows"/>
   
<tr>
    
<td class="ms-vb">
     <xsl:value-of select="@Title"/>
    </td>
    
<td class="ms-vb">
     <xsl:value-of select="sum($Rows&#91;current()/@Title=@Title&#93;/@Amount)"/>
    </td>
   </tr>
 </xsl:template>

Similar Posts

30 Comments

  1. Pingback: XSL Alternative to Eliminating Duplicates Using ddwrt:NameChanged « Marc D Anderson’s Blog
  2. Pingback: Showing Subtotals in a DVWP, Sorted by the Subtotals – Part 2 « Marc D Anderson’s Blog
  3. Pingback: Showing Subtotals in a DVWP, Sorted by the Subtotals – Part 3 « Marc D Anderson’s Blog

  4. Hi Marc,

    Nice! I have a similar problem that I hope you can help me with. I have this DVWP where I want to calculate the % that someone is present.

    Student Present
    Kim Yes
    Kim No
    Kim No
    Lisa Yes
    Lisa No
    Lisa Yes

    Do you have any idea how I can calculate this to get the following result:
    Student Present
    Kim 33%
    Lisa 67%

    Thanks!

    Maaike

    Reply

    1. Maaike:

      This isn’t too hard. ;+) This should get you started.

      <xsl:value-of select="format-number(count($Rows[@Student = current()/@Student and @Present = 'Yes']) div count($Rows[@Student = current()/@Student]), &quot;#,##0.%;-#,##0.%&quot;)"/>

      You’ll then want to use the ddwrt:NameChanged function to decide when to output a row with this calculation.

      M.

      Reply

      1. Hi Marc,

        Thanks a lot for your quick response. It works great. The page loads a bit slow with 2400 records, but the result is just what I needed! Someday I will agree that it isn’t to hard ;-). Thanks for helping me out with this one.

        For those who have trouble implementing the code. I found out that you need to sort the DVWP first before pasting Marc’s code in the DVWP (I did a sort on Student) . Else the NameChanged function isn’t recognized. Then I replaced @Title and @Amount with @Student and @Present and replaced line 20 of the code with the alternate codeline to get the percentage. I filtered the DVWP on Begindate smaller or equal to Today, to get the current percentage.

        Best regards, Maaike

        Reply

  5. I am a new bee to SP, can you tell me where to put this code?
    I dont understand where exactly this code will go..I have inserted the DVWP and then?

    Thanks in advance….

    Reply

    1. kirthi:

      It all depends on what you want to accomplish. You *could* just replace your dvt_1.body and dvt_1.rowview templates with mine, but your column names are undoubtedly different.

      You’ll need to figure out how to leverage the logic I show based on your particular situation.

      M.

      Reply

    1. kirthi:

      If you mean in the sense of a Web Part Connection, no. You should post more specifics about what you are trying to accomplish to Stump the Panel or SPOverflow with some of your code and try to get help. (It’s difficult to post code in comments here at WordPress unless you know the tricks.)

      M.

      Reply

  7. I have a req similar to this:
    Title Value
    A 1
    B 2
    A 3
    B 2

    and I used your code to generate

    Title Value
    A 4
    B 4

    Now that I found these values in DVWP. I want to put them another List called Totals

    Name Value Percentage FInalTotal
    A — 2%
    B — 98%

    Now I need to fill teh Value column in here with the values I calculated in DWVP, so that I can perform a calculation on the A and B to get the FinalTotal value.

    Thanks…

    Reply

    1. kirthi:

      I’m not sure I totally get it, but if you really need to stuff the values into a list, you can use SPServices for that. However, I think you can do what you’re describing entirely in the DVWP, which would be a better way to go unless you need the totals in the totals list for some reason you haven’t mentioned.

      M.

      Reply

  8. Hi,

    I haven’t been able to do this with 2 columns but I’m sure I’ll figure it out eventually. Can I do this with multiple columns? There’s a title then there are 3 other columns I need to do a sum on. i.e. Quantity, Quantity on Order, Quantity on Hold.

    Each Title has these 3 columns and I’d like to sum the columns for the unique items.

    Thanks.

    Reply

    1. Latte:

      Yes, it’s definitely possible. I just worked on something similar over the last few weeks. The XSL can get a little messy, but it’s definitely possible.

      M.

      Reply

  9. Thanks Marc but I don’t have a clue as to how to go about this. If you have any sample code I’d really appreciate it.

    Thanks again.

    Reply

    1. Latte:

      Well, this post shows you how to go about it for one column. You’d build out the additional XSL for the additional two columns using the same logic. It’s hard to give more “sample code” for something specific to your needs.

      M.

      Reply

  10. Hi Marc,

    As I’m sure you’ve guessed I’m not a developer at all, whatever I can do in design view I’d rather do there. I managed to get a working configuration of this by grouping by the Title column; editting the group row to span fewer cells then adding columns beside that and then using calculations in SPD in these columns. Works quite fine for me.

    Thanks for starting me off on this and letting me know it can be done.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.