Step two in my creation of a modified version of the Google Visualization Table widget is to add in the indentation, sorting and collapsing of the data so that on each recalculation of the data table it looks like it's supposed to look to the user. Some of these are a lot easier than others, but they all share a common theme that was started with the creation of the aggregate groups in the previous post - that is, there's a list (array) of groups and that dictates how to do each of these steps.
Adding Indentation
The first thing I wanted to tackle is the indentation required when all the groups are expanded. Since I'd calculated the aggregate rows, and sorted them, what I needed to do was to make it look nice when all these groups are expanded. It's going to be a simple manner of putting non-breaking spaces in front of the label on the row, but the question is how to do this with the existing structure I have for the groups? Answer: pretty simply.
In keeping with the portfolio-to-row index mapping, and a simple loop over all the groups in the array, we can use the recursive function:
function indentGroupMembers(tbl, grp, map) {
for (var e = 0; e < grp.members.length; ++e) {
if (map[grp.members[e]] != undefined) {
var row = map[grp.members[e]];
// ...add in the necessary space
tbl.setValue(row, 0, ' ' + tbl.getValue(row, 0));
// see if this member is, in fact, a group itself
for (var g = 0; g < groups.members.length; ++g) {
if (groups[g].name == grp.members[e]) {
indentGroupMembers(tbl, groups[g], map);
break;
}
}
}
}
}
The beauty of this is that we have already defined all the structure we need to do the complete indenting - no matter how deep it goes. All we needed to do is to identify if the member is a group, and if so, call it again. Works perfectly.
Adding Group-Level Sorting
Probably the most difficult part of this was getting the sorting done correctly. Each group had to be sorted properly - with the other groups at the same level. Then within each group, the members had to be sorted - but stay within the limits of the group. All this while still appearing to work with the Google Table widget. I was concerned.
The idea I settled on was a hybrid of the sorter and the indenter - what if I went through the groups - sorted their members and assigned numbers 1, 2, 3, ... for their position in the group. Then, we'd scale up the values based on their "depth" in the scheme.
OK, an example. If we had in the table the following data:
| Tech |
101.00 |
| AAPL |
41.00 |
| MSFT |
-10.00 |
| GOOG |
70.00 |
| Retail |
0.00 |
| HD |
55.00 |
| LOW |
-55.00 |
then we'd add in the 'sorting column', and then scan each group - picking out the values, placing them in a JavaScript array, sorting that, and then assigning values. The trick is to once again use JavaScript objects and take not only the value of the row, but the row index so it's easy to place them in the right order.
| Tech |
101.00 |
2 |
| AAPL |
41.00 |
2 |
| MSFT |
-10.00 |
1 |
| GOOG |
70.00 |
3 |
| Retail |
0.00 |
1 |
| HD |
55.00 |
2 |
| LOW |
-55.00 |
1 |
So that now you can see that each group has it's order assigned. The 'indenting' trick is to then scale up the non-leaf nodes by a factor of 10 for each level and add it to the members so that the members are sorted with the groups. This only works if you have less than 10 members in a group. If you have more, then simply increase the factor so that the largest group's membership is covered.
When you're done applying the scale factor, you'll have something like this:
| Tech |
101.00 |
20 |
| AAPL |
41.00 |
22 |
| MSFT |
-10.00 |
21 |
| GOOG |
70.00 |
23 |
| Retail |
0.00 |
10 |
| HD |
55.00 |
12 |
| LOW |
-55.00 |
11 |
At this point, it's pretty clear that the simple table sort on this column will get us what we're looking for. Pretty neat. It took me a while to figure out what I needed to do here, and the code for the factor application and sorting isn't trivial, but it's not hard, and it's all driven by the group definitions so it's very flexible.
Adding the Collapsing
Once the aggregation and sorting are done, the final step is the collapsing of the non-expanded groups. It's easy to see that we can have a JavaScript array with the names of the expanded groups in it - removed when it's collapsed, added when it's expanded, so that it really comes down to removing the members of the groups that aren't expanded.
Based on the same ideas as the indenting this function does the trick:
function removeGroupMembers(tbl, grp) {
for (var e = 0; e < grp.members.length; ++e) {
// remove this guy (row) from the table
removeRow(grp.members[e], tbl);
// see if this member is, in fact, a group itself
for (var g = 0; g < groups.members.length; ++g) {
if (groups[g].name == grp.members[e]) {
removeGroupMembers(tbl, groups[g]);
break;
}
}
}
}
the difference here being that because the table is dynamic at this stage, we can't use the portfolio-to-row index map, and have to, instead, have the function:
function removeRow(name, tbl) {
var rowCnt = tbl.getNumberOfRows();
for (var i = 0; i < rowCnt; ++i) {
if (name == tbl.getValue(i, 0).replace(/ /g,'')) {
tbl.removeRow(i);
break;
}
}
}
where this function looks in the table for the portfolio name and then removes it from the table. This is going to be an expensive step, but it's the only way I know to do what's needed without doing more than what's needed. After all, the groups may be expanded, and for those, there's nothing to delete.
Put together with a little JavaScript and tags, clicking on the group in the table toggles it's inclusion in the expanded list, updates the data and re-generates the table. It's pretty slick. Pretty fast, and exactly what I needed.
