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. Great post, Marc. This solved a problem for me. It was easy to implement, I was able to incorporated the bar chart from Part 3, and I learned a lot in the process. I tried to do this with Google visualization charts with no success.

    Thank you.

    Reply

  2. Hi Marc,

    I have a similar requirement. Could you please help me out.
    I have people picker field(Assigned To) and Status field with values Open,InProgress and Completed. Now i want a report which displays the count of Open items,InProgress items and Completed items for each person.
    Waiting for your reply

    Reply

  4. Hi Marc – this is great!! I had been struggling to get something like this and came upon your post. :)

    One additional question related to this:

    How would I filter the results to just show Amounts greater than 10 so the resulting list would just show

    Title Amount
    Mabel 50
    Bob 25

    Reply

    1. Liz:
      It would be something like this (untested). As you can see, I’ve simply added a new clause to the xsl:if in line 14.

      <xsl:template name="dvt_1.body">
    <xsl:param name="Rows"/>
    <xsl:for-each select="$Rows">
        <xsl:sort select="sum($Rows[current()/@Title=@Title]/@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) &gt; 0 and sum($Rows[current()/@Title=@Title]/@Amount) &gt; 10">
<tr>
<td class="ms-vb">
       <xsl:value-of select="@Title"/>
    </td>
<td class="ms-vb">
       <xsl:value-of select="sum($Rows[current()/@Title=@Title]/@Amount)"/>
    </td>
   </tr>
  </xsl:if>
 </xsl:template>

      M.

      Reply

  7. Marc, I am not a total noob on SPDesigner stuff, but I haven’t done a ton with XSLT. I have a DVWP in SP2013 that groups by Vendors and Counts the number of items under each vendor grouping. I want to sort by those numbers.

    I wasn’t 100% sure where to put your code. No matter where I placed it in the page in SP Designer it corrupts the view, so I decided to put it in it’s own XSL file in Site Assets and reference it, but I still get the same “unable to display this web part” error.

    Here is where I included it

    Here is the begining of the XSLT View Web Part that shows the order/group settings:

    Reply

  8. Marc –

    How do I incorporate this code into my newly added DVWP. (a list DVWP)

    I know I have to change column names. I am trying to replicate this example you gave… I’ve never worked with DVWP before – farthest I’ve gotten is just adding in the web part in designer just now.

    Thanks.
    Kevin

    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.