About Summing Calculated Columns

Calculated columns are great, but if you expect to be able to total calculated columns using the Totals feature in the View Settings, think again.  To sum calculated columns in a list you need SharePoint Designer 2010, a web part page, a data source, and a line of code.

Example Overview

This example will total the results of a calculated field in a custom list. The list will be inserted as a data source on a web part page.  The list and web part page have been previously created and the creation of these items is not documented in the demo.

On a web part page called Demo Calculated Columns.aspx, an empty data source will be added with four columns inserted from the calculatedcolums list as the data source:  Title, column1, columns2, and 1and2.

Creating a Totals Field for a Calculated Column

 1.       Create the list

This demo does not include screenshots or instructions for building the list used in this example.  To recreate this demo, create a new custom list and add the three columns outlined in the Example Overview section.

2.       Create a web part page

This demo does not include screenshots or instructions for creating the web part page used in this example.  To recreate this demo, create a web part page somewhere in your test site. 

3.       Add an empty data source and configure

4.       Add XSL Value of Select data

Add an Empty Data Source and Configure

 1.    Open the Demo Calculated Columns.aspx page in Advanced Mode in SharePoint Designer in Split mode.  In the design portion, click and place your cursor in the PlaceHolderMain section of the page.  From the Ribbon, on the Insert tab, select Data View, Empty Data View. 

2.       Select Click here to select a data source and choose calculatedcolumns.  The Data Source details pane will open on the right.  Insert the following fields into the new data source:  Title, column1, column2,and 1and2.

3.  The totals information should be included in a new row at the bottom of the list.  To insert a new row, in the last cell of the table, right-click and select Insert, Row Below.

4.        Your cursor will now be in the last cell on the bottom row of the table in the design pane.  In the code pane, the cursor will be placed in its current cell position. 

 Add “XSL Value of Select” Data

1.     Continuing from step 4 above, we want to delete the current code of the new cell.  For the active cell, copy the code snippet <td><xsl:text xmlns:ddwrt=”http://schemas.microsoft.com/WebParts/v2/DataView/runtime” ddwrt:nbsp-preserve=”yes” disable-output-escaping=”yes”>&amp;nbsp;</xsl:text> and delete it. 

2.       Input the following code (replace the highlighted text with your column name) and Save your page: 

Note – Designer is precise when it comes to code.  If you copy and paste the row of code below, after pasting into Designer, delete the “ in the code pane, then re-add them.   Otherwise, due to font differences, a XSLT style sheet error will display.

<td ><xsl:value-of select=“sum(/dsQueryResponse/Rows/Row/@_x0031_and2)“ /></td>

3.       The calculated column is now totaled. 

 

Additional Modifications

Add the Toolbar

So that users can interact with the data source by adding new items, setting alerts, and filtering and sorting on column headers, add the SharePoint Toolbar. 

1.     To add the Toolbar, from the Ribbon, on the Design tab, in the Toolbar section, click Options, then select SharePoint Toolbar.  Save the page in Designer.  Note:  Alternatively to only enable sorting and filtering on column headers, leave the Toolbar setting as is and in the Show/Hide section check Sort & Filter on Headers.

2.       The data source now displays with the Toolbar.

Add the Quick Launch to the page

So that users can navigate easily around the site, add the left hand naviation, the Quick Launch, to the web part page.

1.  Delete the following lines of code:

<asp:Content ContentPlaceHolderId=”PlaceHolderNavSpacer” runat=”server”></asp:Content>

<asp:Content ContentPlaceHolderId=”PlaceHolderLeftNavBar” runat=”server”></asp:Content>

<style type=”text/css”>body #s4-leftpanel { display:none;}.s4-ca { margin-left:0px;}</style>

 2.       The Demo Calculated Columns page now displays with the Quick Launch.