Last night, I wanted to sum a calculated column with groups on the page with the groups closed. SharePoint does not do this OOB and my previous attempt at a solution needed data rendered (groups expanded).

To accomplish this, I did two things to my OWS API. First, I added a getType method that can check the schema’s datatype tag and return the type attribute. Next, I upgraded the getValues method to check type for “variant” and return the appropriate value. In the OWS world, calculated columns are called variant data types and they are stored with a type;#value syntax.

Since all of the other calculations rely on getValues, I don’t need to change anything else. Now I can call my API and write the sum of my calculated column in like three lines:

$(function(){
	//example on a custom Tasks list with grouping
	$.fn.ows({},function(o){
		var p = ows.getPosition(o.ctx,'calcNumbers');
		var s = ows.getSum(o.ctx,'calcNumbers');
		$("#aggr > tr > td:not(.ms-vh-group):eq("+p+")").html("<b>Sum = "+s+"</b>");
	});
});

The above script calls OWS to build the ows object. In the callback function, I use the getPosition and getSum methods to get a value and where it should be written. Since I’m using a list view, all of the CTX information is already on the page, I don’t need any custom options. The last line is an array of td’s in the aggregation row (#aggr) excluding the grouping column. The :eq() filter allows me to pick one of the array members by position (same as the schema position, hence why I can just ask OWS what the position is).

Click “Read more…” to get the updated OWS API and this example. To configure your list for this example, create a calculated column in a standard Task list called “calcNumbers”. Use a simple calculation like =[% Complete]*10. Set at least one row to use a total function (like Count), this creates the aggr row. Turn on grouping if you like, it should work either way.

<script type="text/javascript">
//?PageView=Shared&ToolPaneView=2
if(typeof jQuery=="undefined"){
	var jQPath="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/";
	document.write("<script src='",jQPath,"jquery.min.js' type='text/javascript'><\/script>");
}
</script>
<script type="text/javascript">
(function(){
	Array.max = function(array){
		return Math.max.apply( Math, array );
	};
	Array.min = function(array){
		return Math.min.apply( Math, array );
	};
	var defaults = {
		ctx: "ctx1",
		baseUrl: "",
		list: "",
		view: "",
		command: ""
	},
	isNumber = function(val){
		return (/^-?((\d+\.?\d*)|(\.\d+))$/).test(val);
	},
	ows_url = function(o){
		o.baseUrl = (!o.baseUrl)?L_Menu_BaseUrl:o.baseUrl;
		o.list = (!o.list)?window[o.ctx].listName:o.list;
		o.view = (!o.view)?window[o.ctx].view:o.view;
		return o.baseUrl+"/_vti_bin/owssvr.dll?XMLDATA=1&List="+
				o.list+"&View="+o.view+o.command;
	},
	create_ows = function(){
		ows = {
			getName : function(ctx,rsname){
				var n = $.grep($(ows[ctx].schema),function(e,i){
					return ($(e).attr("rs:name")==rsname);
				});
				return $(n).attr("name");
			},
			getType : function(ctx,rsname){
				var n = $.grep($(ows[ctx].schema),function(e,i){
					return ($(e).attr("rs:name")==rsname);
				});
				return $(n).find("[nodeName='s:datatype']").attr("dt:type");
			},
			getNumber : function(ctx,rsname){
				var r = $.grep($(ows[ctx].schema),function(e,i){
					return ($(e).attr("rs:name")==rsname);
				});
				return $(r).attr("rs:number");
			},
			getPosition : function(ctx,rsname){
				var pos = ows.getNumber(ctx,rsname);
				return parseInt(pos)-1;
			},
			getValues : function(ctx,rsname){
				var v = [],
					vname = ows.getName(ctx,rsname);
				$(ows[ctx].data).each(function(i,e){
					var vx = $(e).attr(vname);
					if(vx){
						if(ows.getType(ctx,rsname)=='variant'){
							v.push(vx.split(';#')[1]);
						}else{
							v.push(vx);
						}
					}
				});
				return v;
			},
			getAll : function(ctx,rsname){
				var a = [],
					aname = ows.getName(ctx,rsname);
				$(ows[ctx].data).each(function(i,e){
					a.push($(e).attr(aname));
				});
				return a;
			},
			getSum : function(ctx,rsname){
				var svals = ows.getValues(ctx,rsname),
					sum=0;
				$(svals).each(function(i,e){
					if(e && isNumber(e)){
						sum += parseFloat(e);
					}
				});
				return sum;
			},
			getAvg : function(ctx,rsname){
				var avgsum = ows.getSum(ctx,rsname),
					avgcount = ows.getValues(ctx,rsname).length;
				return avgsum/avgcount;
			},
			getMax : function(ctx,rsname){
				var xvals = ows.getValues(ctx,rsname);
				xvals = $.grep(xvals,function(e,i){
					return (e && isNumber(e));
				});
				return Array.max(xvals);
			},
			getMin : function(ctx,rsname){
				var nvals = ows.getValues(ctx,rsname);
				nvals = $.grep(nvals,function(e,i){
					return (e && isNumber(e));
				});
				return Array.min(nvals);
			},
			getTable : function(ctx){
				var t = $("[id='"+window[ctx].listName+"-"+window[ctx].view+"']");
				return t;
			}
		};
	};
	jQuery.fn.ows = function(options,callback){
		var o = $.extend({},defaults,options);
		if(typeof ows=='undefined'){create_ows();}
		$.get(ows_url(o),function(data){
			ows[o.ctx] = {
				schema:$(data).find("[nodeName='s:AttributeType']"),
				data:$(data).find("[nodeName='z:row']")
			};
			return callback(o);
		});
	};
})();
$(function(){
	//example on a custom Tasks list with grouping
	$.fn.ows({},function(o){
		var p = ows.getPosition(o.ctx,'calcNumbers');
		//console.log(p);
		var s = ows.getSum(o.ctx,'calcNumbers');
		$("#aggr > tr > td:not(.ms-vh-group):eq("+p+")").html("<b>Sum = "+s+"</b>");
	});
});
</script>

Paul Grenier