Sharepoint Dataview: show sum of calculated column when grouping applied

Article
1/17/2024

Introduction

Will create java script variable outside the xsl:for-each give it to "0"
write xsl:foreach to get every row, and then add that value in above java script variable , final value of that variable will give us Total or Sum of that column.
print above java script variable

Code

<td class="ms-vh" nowrap="nowrap">sum :$
<script type="text/javascript">var totalrev=0;</script>
<xsl:for-each select="$nodeset">                           
<script type="text/javascript">
var rev= '<xsl:value-of select="translate(@Total,',','')" />';
totalrev = totalrev + parseFloat(rev);
</script>
 </xsl:for-each>
 <script type="text/javascript">document.write(totalrev);</script>
 </td>

In depth step-by-Step

I have created list like below. Screenshot is Self-Explanatory.

Total is Calculated column = [Quantity]*[Price]

Here I have created Brand as Choose Column, but you can take it as Text column as well.

I have added some dummy data like below

Now create new Webpart page and add dataview webpart and apply grouping on Brand Column, it will show data like below (In dataview show data in Multiple view, and select column which you want to show in view, I selected following columns)

As you can see , webpart does not show sum of price , even it is number column.

But it has shown sum of Quantity column ,Reason which I think is "," Quantity do not have commons and point values, so SharePoint done the sum. Now here we have two problems

1)Sum of price is not showing even though it is Number column

2)Sum of Total(Calculated) column.

Solutions Starts here

1) Sum of price is not showing even though it is Number column

This is a bit easy. Whenever you create Number column, SharePoint create 2 columns (background)

in above case for Price, SharePoint created 2 column "Price" and "Price." (there is "." at the end)

"Price." stores it as Number, so go to xslt and go to line where actually sum is showing

go to <xsl:template name="dvt_1.groupfooter0"> template you will get following line

and add just "." to @Price. save it.

Open the page, Bingo you done it.

2) Sum of Total(Calculated) column.

you must have seen below screen when you were applying grouping and select "Show Column totals per group"

This will add footer xslt template in your data-view web-part. Which will be used to show group footer to every group and will show sum or total etc.

So we will make use of that group footer template

<xsl:template name="dvt_1.groupfooter0">

if you give close look to that Template you will understand that this Templated takes

<xsl:param name="nodeset" /> parameter which will be the nodes or rows of respective group.

whatever sum or total we want show we have to use this nodeset only.

So what we need to do in this Template is that, firstly we will declare JavaScript variable then we will access every node of that nodeset as xsl:foreach and whatever column value we want to sum, that value we will add in JavaScript variable. finally we will make use of that JavaScript variable to get out final Total.

Nutshell

Will create JavaScript variable outside the xsl:for-each give it to "0"
write xsl:foreach to get every row, and then add that value in above java script variable , final value of that variable will give us Total or Sum of that column.
print above java script variable

Now add new Sum: column just below of Sum columns which SharePoint added for other columns.

and paste the code which I have given at the Top.

Explanation about the code is below (Line by line)

<script type="text/JavaScript">var totalrev=0;</script> This is to just declare main variable which will store our final sum.
<xsl:for-each select="$nodeset"> this is to travel every node
add var rev= '<xsl:value-of select="translate(@Total,',','')" />';totalrev = totalrev + parseFloat(rev); in xsl:foreach and in javascript access xsl:value of @Total column(Column which you want to do sum) , put it in local variable of javascript, and then add that variable to Totalrev variable which we created above for-each. See I have applied translate to @Total , just to translate "," to "" empty value(So javascript will treat this as number)
<script type="text/javascript">document.write(totalrev);</script> finally print that value.

Final screenshot.

That code is generic, just change the variable names and use it in other places.

Share via