Grouping and subtotals

Suite and Optimus Suite 5
#1

Is it possible to add in a row at the end of a group of data to sum the columns of data?

It appears using forEachRowInGroup could assist in a manual solution.
I’ve attempted to do so but it unfortunately fails because when it reaches a row that it claims it cannot read the properties of, however the row does exist. I wonder if it is because I think the row is a grouping header rather than a row of data the following error occurs:
Uncaught TypeError: Cannot read property ‘4’ of undefined
Where ‘4’ is the index of a column of data I want to sum for the group.

#2

Here are some code examples:

var data = {
	rows: [
			{id:1, data:[1, "A", "12/31/2014", "12/31/2014", 0, 3187.53, 0, "Group A"]},
			{id:2, data:[85, "A", "12/31/2014", "12/31/2014", 0, 2992.85, 204.56, "Group A"]},
			{id:3, data:[32, "A", "12/31/2014", "12/31/2014", 0, 2992.85, 204.56, "Group A"]},
			{id:4, data:[85, "B", "12/31/2014", "12/31/2014", 0, 2992.85, 204.56, "Group B"]}
		]
};
report = new dhtmlXGridObject('report');
report.setImagePath("/scripts/dhtmlx/dhtmlxGrid/codebase/imgs/");
report.setSkin("dhx_skyblue");
report.enableAutoWidth(true);
report.enableAutoHeight(true);
report.setHeader("Shares,Tickers,Sales Date,Purchase Date,Cost,Proceeds,Gain/Loss,Group");
report.setInitWidthsP("10,10,15,15,10,20,20,0");
report.setColAlign("center,center,center,center,center,center,center,center");
report.setColTypes("ron,ro,ro,ro,ro,ron,ron,ro");
report.setColSorting("int,str,date,date,int,int,int,str");
report.init();
report.parse(data,"json");
report.groupBy(7);

The above will setup a nice table that is grouped by the last column named, surprisingly, Group.
I’d like to sum the following columns of each grouping: Cost, Proceeds, Gain/Loss
I do not see a way to accomplish this with the Grid API, so I’ve begun a manual solution:

var groups = ["Group A", "Group B"];
for (var i = 0; i<groups.length; i++) {
	var cost = 0, proceeds = 0,	gain_loss = 0;
	report.forEachRowInGroup(groups[i],function(id){
		console.log("On row ID "+id);
		if(report.isItemExists(id)) {
			cost += parseFloat(report.cellByIndex(id,4).getValue());
			proceeds += parseFloat(report.cellByIndex(id,5).getValue());
			gain_loss += parseFloat(report.cellByIndex(id,6).getValue());
		}
	});
}

This iterates through the first group, Group A, and sums the columns as expcted.
Unfortunately when the loop iterates to the 4th row, which would be in Group B, I receive the error I stated above:
Uncaught TypeError: Cannot read property ‘4’ of undefined
Which is where I attempt to getValue() on the 4th column of the current row.

#3

Unfortunately, there is no way to add extra “footer” row to the grouped data.
If you still have some problem with forEachRowInGroup - please share the code snippet, where issue occurs.

#4

I’d like to sum the following columns of each grouping: Cost, Proceeds, Gain/Loss

You can add sum into the top-group row
docs.dhtmlx.com/grid__grouping_r … uiltinmath

As for error - change in your code cellByIndex with cellById ( as you are using id rows, not indexes here )

#5

Thanks Stanislav, adding the sum into the top-group row should be acceptable.

#6

Hmm, with the same example code above I’m trying to get the #stat_total of the 4th, 5th, and 6th columns and leave the rest empty like so:

report.groupBy(7,["","","","","#stat_total","#stat_total","#stat_total",""]);

However, the following error is occurring:
Uncaught TypeError: Cannot set property ‘innerHTML’ of undefined

#7

I double checked and each column is appropriately set to type “ron”.
I’m using setNumberFormat() against those columns anyways.

#8

It appears it’s because #title is required in one of the columns, although the documentation doesn’t state it.
Might I recommend updating the groupBy API reference?
I see it references the #title as the group key but that wasn’t enough of an indicator that it is required.

Here is the working code:

report.groupBy(7,["#title","#cspan","#cspan","#cspan","#stat_total","#stat_total","#stat_total",""]);
#9

You’re right. Unfortunately it is a mandatory to define a “title” for a group row.