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!
1 | <xsl:template name="dvt_1.body"> |
2 | <xsl:param name="Rows"/> |
3 | <xsl:for-each select="$Rows"> |
4 | <xsl:sort select="sum($Rows[current()/@Title=@Title]/@Amount)" data-type="number" order="descending" /> |
5 | <xsl:call-template name="dvt_1.rowview"> |
6 | <xsl:with-param name="Rows" select="$Rows"/> |
11 | <xsl:template name="dvt_1.rowview"> |
12 | <xsl:param name="Rows"/> |
13 | <xsl:variable name="NewTitle" select="ddwrt:NameChanged(string(@Title), 0)"/> |
14 | <xsl:if test="string-length($NewTitle) > 0"> |
17 | <xsl:value-of select="@Title"/> |
20 | <xsl:value-of select="sum($Rows[current()/@Title=@Title]/@Amount)"/> |
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.
1 | <xsl:template name="dvt_1.body"> |
2 | <xsl:param name="Rows"/> |
3 | <xsl:for-each select="$Rows[not(@Title = preceding-sibling::*/@Title)]"> |
4 | <xsl:sort select="sum($Rows[current()/@Title=@Title]/@Amount)" data-type="number" order="descending" /> |
5 | <xsl:call-template name="dvt_1.rowview"> |
6 | <xsl:with-param name="Rows" select="$Rows"/> |
11 | <xsl:template name="dvt_1.rowview"> |
12 | <xsl:param name="Rows"/> |
17 | <xsl:value-of select="@Title"/> |
21 | <xsl:value-of select="sum($Rows[current()/@Title=@Title]/@Amount)"/> |
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.
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
This is the code i am using
0″>
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
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.
1<xsl:template name="dvt_1.body">2<xsl:param name="Rows"/>3<xsl:for-each select="$Rows">4<xsl:sort select="sum($Rows[current()/@Title=@Title]/@Amount)"data-type="number"order="descending"/>5<xsl:call-template name="dvt_1.rowview">6<xsl:with-param name="Rows"select="$Rows"/>7</xsl:call-template>8</xsl:for-each>9</xsl:template>1011<xsl:template name="dvt_1.rowview">12<xsl:param name="Rows"/>13<xsl:variable name="NewTitle"select="ddwrt:NameChanged(string(@Title), 0)"/>14<xsl:iftest="string-length($NewTitle) > 0 and sum($Rows[current()/@Title=@Title]/@Amount) > 10">15<tr>16<td class="ms-vb">17<xsl:value-of select="@Title"/>18</td>19<td class="ms-vb">20<xsl:value-of select="sum($Rows[current()/@Title=@Title]/@Amount)"/>21</td>22</tr>23</xsl:if>24</xsl:template>M.
Marc you’re awesome! I got that filter to work. :)
Thank you so much!
Liz
Hi,
how to sort group total, it is not working with me.
Hi,
I have group by category and I want to sort group total desc.
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:
@Justin:
You might want to read through my series Unlocking the Mysteries of Data View Web Part XSL Tags. The link is on the left side of the page.
M.
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