Summing a Column in a Data View Web Part (DVWP)
I have a question for you…
In a DVWP is there a way to total a column?
Absolutely.
The most common approach would be to emit a table row in the dvt_1.body template (or equivalent) which uses the sum() function. An example would be something like this.
Let’s assume that you have a column called Potential Value, that it’s numeric, and that you’re displaying that column and the Title. When you set up the DVWP, your XSL will probably look something like this:
<xsl:template match="/">
<xsl:call-template name="dvt_1"/>
</xsl:template>
<xsl:template name="dvt_1">
<xsl:variable name="dvt_StyleName">Table</xsl:variable>
<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/>
<table border="0" width="100%" cellpadding="2" cellspacing="0">
<tr valign="top">
<xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
<th class="ms-vh" width="1%" nowrap="nowrap"></th>
</xsl:if>
<th class="ms-vh" nowrap="nowrap">Title</th>
<th class="ms-vh" nowrap="nowrap">Potential Value</th>
</tr>
<xsl:call-template name="dvt_1.body">
<xsl:with-param name="Rows" select="$Rows"/>
</xsl:call-template>
</table>
</xsl:template>
<xsl:template name="dvt_1.body">
<xsl:param name="Rows"/>
<xsl:for-each select="$Rows">
<xsl:call-template name="dvt_1.rowview"/>
</xsl:for-each>
</xsl:template>
<xsl:template name="dvt_1.rowview">
<tr>
<xsl:if test="position() mod 2 = 1">
<xsl:attribute name="class">ms-alternating</xsl:attribute>
</xsl:if>
<xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
<td class="ms-vb" width="1%" nowrap="nowrap">
<span ddwrt:amkeyfield="ID" ddwrt:amkeyvalue="ddwrt:EscapeDelims(string(@ID))" ddwrt:ammode="view"></span>
</td>
</xsl:if>
<td class="ms-vb">
<xsl:value-of select="@Title"/>
</td>
<td class="ms-vb">
<xsl:value-of select="@Potential_x0020_Value"/>
</td>
</tr>
</xsl:template>
You can add a new row in your XSL to emit the total like this:
<xsl:template name="dvt_1.body">
<xsl:param name="Rows"/>
<xsl:for-each select="$Rows">
<xsl:call-template name="dvt_1.rowview"/>
</xsl:for-each>
<tr>
<td>
TOTAL
</td>
<td>
<xsl:value-of select="format-number(sum($Rows/@Potential_x0020_Value),'#,##0')"/>
</td>
</tr>
</xsl:template>
Or you could add the XSL in the dvt_1 template:
<xsl:template name="dvt_1">
<xsl:variable name="dvt_StyleName">Table</xsl:variable>
<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/>
<table border="0" width="100%" cellpadding="2" cellspacing="0">
<tr valign="top">
<xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
<th class="ms-vh" width="1%" nowrap="nowrap"></th>
</xsl:if>
<th class="ms-vh" nowrap="nowrap">Title</th>
<th class="ms-vh" nowrap="nowrap">Potential Value</th>
</tr>
<xsl:call-template name="dvt_1.body">
<xsl:with-param name="Rows" select="$Rows"/>
</xsl:call-template>
<tr>
<td>
TOTAL
</td>
<td>
<xsl:value-of select="format-number(sum($Rows/@Potential_x0020_Value),'#,##0')"/>
</td>
</tr>
</table>
</xsl:template>
Either placement will work, and it will work exactly the same. As with any programming language, you can choose the style you prefer, but be consistent!
Of course, you can also ask SharePoint Designer to add the XSL for the total in the Common Dialogs under Sorting and Grouping, but as usual, the XSL will be a bit bloated and you won’t have as much control over the formatting.
Finally, since this is simple editing of XSL. it works exactly the same in SharePoint 2010 and 2007.
Hi Marc Thanks for Sharing Knowledge.Your formula works fine in my case but it doesnt show Total for big number like 1,500,4,500.My field type is number with 0 decimals.
If i enter 100,200 it works fine please advise.
Thanks
Ronak
Hi Marc it doesn’t work for 4 digit Number.
It sounds like Known bug in SPD 2010 but cant find any work around.
ROnak:
I’m not sure what’s happening in your case. Using sum on a numeric column ought to work, but it’s possible that you need to reformat it to remove the comma.
M.
I desperately need help with this…I have searched and tried everything I know.
I have come across a group count error. I have created a data view web part using a linked source. The DVWP has only one level of grouping: Date.
My Group Totals are incorrect (with the exception of the last group on the bottom). And my Webpart total count is also correct.
I know very little about the code. But I have played with it to make some sense of it. I would appreciate if you can propose a solution.
I can’t display a picture but my Data Veiw Web Part presently breaks down is something like this:
Date: 1/15/2012
Entry 1
Entry 2
Group Count: 0 (Wrong Count)
Date: 1/31/2012
Entry 1
Group Count: 0 (Wrong Count)
Date: 2/15/2012
Entry 1
Entry 2
Group Count: 2 (Right Count)
Total Count: 5 (Right Count)
What is frustrating is that all the group counts are based on the same formula then why are the first group counts wrong when the last one generates the correct value.
Group Count Code:
Count :
Total Web Part Total: This portion shows the correct value and uses:
I would greatly appreciate your help.
ssvv:
It’s a little hard to say. It looks like you are using the dialogs to create the groupings, and that often doesn’t get you what you wany, unfortunately. I usuallu build my own grouing logic in the XSL because then I can get exactly what I want.
On thing which may be causing the issue is that you are grouping on dates. Since the dates are in the m/d/yyyy format, then calculations may not be correct. Try creating a calculated column which hjas the dates in YYYYMMDD format and grouping on that. It may work.
M.
Marc,
Thanks.
I tried play with the date format, but it is not the date format that is causing the problem because:
1. Changing the date format had no affect on the calculation
2. I also added a calculation for a one of the numeric columns and it has the same issue (all group counts return a zero value with the exception of the last one).
3. I deleted the data for the last group. So the intial second to last group which initially returned zero value now retruns accurate values.
I am wondering since the Data View Total returns the correct count, is there a way I can use dsQueryResponse to return the group totals?
Please let me know you think.
You can adapt the XSL however you want. The other thing I just realized is that you are using the Web Services to retreive the data. That means that all data is in text format, so the format of the values is even more important.
M.
Marc,
Since my WebPart Total returns the correct value using this code:
count(/dsQueryResponse/soap:Envelope/soap:Body/ddw1:GetListItemsResponse/ddw1:GetListItemsResult/ddw1:listitems/rs:data/z:row)
How can I modify it to return the count for each group? (I have no expereince with coding so this may seem like a very elementary question.)
This is the last piece of my puzzle.
I really appreciate your help.
Marc,
You are right. For some reason you cannot get an accurate calculation if your web part is grouped by date. That is very strange.
Now I did create a calculated column as you suggested however, when I go to “Group & Sort” the calculated column does not show in ‘available fields’.
Please advise.
Thanks a lot for your help.
You’re running into some of the reasons that I just write my own XSL most of the time. Only certain column types can be used in each option, and you’re running into one of those restrictions.
M.
Marc,
Problem solved. Thanks for yoru help!!!
You’re welcome!
M.
I noticed the code didnt’ appear on my previous comments. I’ll give it another try without the angular brackets:
Group Count Code:
xsl:template name=”dvt_1.groupfooter0″
xsl:param name=”fieldtitle” /
xsl:param name=”fieldname” /
xsl:param name=”fieldvalue” /
xsl:param name=”fieldtype” /
xsl:param name=”nodeset” /
xsl:param name=”groupid” /
xsl:param name=”displaystyle” /
xsl:param name=”showfooter” /
xsl:param name=”showfootercolumn” /
xsl:if test=”$showfootercolumn” ddwrt:cf_ignore=”1″> <t r valign="top" style="display:none"
xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1"
t h class="ms-vh" width="1%" nowrap="nowrap" /t h
/xsl:if
t d class="ms-gb2 ms-altering" nowrap="nowrap"
Count : xsl:value-of select="count($nodeset)" / /t d
Total Web Part Total: This portion shows the correct value and uses:
xsl:value-of select="count(/dsQueryResponse/soap:Envelope/soap:Body/ddw1:GetListItemsResponse/ddw1:GetListItemsResult/ddw1:listitems/rs:data/z:row)" /
Thanks.