List view settings - Show SUM and COUNT

by simark at 2013-03-06 18:08:50

Hi “Colleagues”!

I would like to activate the “Show Totals” function on a custom list. On the GUI it can be achieved by first selecting the “DataSheet view” mode and then clicking on the “Show Totals” button.
When I switch back to “Standard view” it still shows the SUM results for each number type columns.
I did not find any SPList property that would activate this function. My guess was the property but that returned “none” as result for a list that represents the “Show Totals”.
Any ideas?
#Get the list
$Web = Get-SPWeb “MySite”
$List = $Web.Lists.get_Item(“MyList”)

#Your code here :slight_smile: - how to activate the Show Totals for the $List? -

Thank you for your support!

by AlexBrassington at 2013-03-07 23:52:07

You’re looking in slightly the wrong place. The Sums value is specified on the view object rather than the list itself (which makes sense, you will want ot specify the totals seperate to the list definition so you can have totals in some views but not all). I’ve created a demo list which has a count on a field called Assistant Name. If you compare the SchemaXML before and after I added the count you can see the following changes:

&lt;Aggregations Value=“Off” /&gt;&lt;/View&gt;</S>
&lt;Aggregations Value=“On”&gt;&lt;FieldRef Name=“AssistantsName” Type=“COUNT” /&gt;&lt;/Aggregations&gt;&lt;/View&gt;</S>
Of course that’s just encoded XML but it’s not the easiest thing to work with. To access the property more directly you can use the following:
Which returns the following
<FieldRef Name=“AssistantsName” Type=“COUNT” />
I’ve been playing about with it and i’ve seen some success with the following:
$testAggregation = @"
<FieldRef Name=“AssistantsName” Type=“COUNT” /><FieldRef Name=“Title” Type=“COUNT” /><FieldRef Name=“Name” Type=“COUNT” />
$SPWeb = Get-SPWeb $siteURL
$list = $SPweb.Lists[$docLibName]
$view = $list.views[“Default”]
$view.Aggregations = $testAggregation
$view.AggregationsStatus = “On”

The code above seems to work partially, i had to edit the view manually and hit ok (no changes but just click ok on the page) to get the changes to take effect the first time. Also the code to add ‘Name’ doesn’t work on my library for some reason.
by simark at 2013-03-08 04:33:18
Hi Alex,

I have checked out what you have suggested and it works fine. I had to make a minor modification on the following line
$view = $list.views[“Default”]
The $view had null value. Maybe there is no view named “default” for my document library. I managed to get the default view with the following line
$view = $list.defaultview
I have changed the SchemaXML to return SUM, not COUNT so it will sum up the numeric values in the column.
$testAggregation = "<FieldRef Name=“Usage_MB” Type=“SUM” />"

Thank you for the solution!