Advent Calendar Door 5: Data Aggregation in SharePoint: Running totals across the lists
Data Aggregation in SharePoint: Running totals across the lists
Here is another one from the from the eternal “how do I” SharePoint questions: the data aggregation. In the example from yesterday’s advent cookie blog, we had Purchase Orders and Ordered items lists, where in Purchase Order, we always have to keep accurate total number of items in the purchase order, and the total price. In SharePoint, that is not possible out of the box, but it is a very simple task with skybow Solution Studio.
In order to create a total, or an aggregation, go to the “Things in background” section of the list where you want the aggregation or total to be stored. That list has to have a sublist, in which the aggregation data are stored.
The next step is to open the settings icon (gears) of the column where you want to store the total:
In the values tab of the column properties, first choose the aggregation type (sum, count, average, maximum, minimum, function/custom), and then the sublist and a column from the sublist which you want to aggregate. You can even filter the selection, if you don’t want to aggregate all subitems.
Now, give it a few seconds to recalculate, since it is a background, asynchronous operation. Very soon, your set totals and aggregations will appear in place.
Please note: the fields where totals are stored are normal, SharePoint fields. If you include those fields in new or edit forms, and set the values manually, your manually entered values will be overwritten immediately after saving the item, since the aggregation calculation in background has the priority over that.
You can watch the video tutorial here: https://youtu.be/olF8WRPaAoY