Starting to Add Hierarchy to Google Table Visualization
The primary user of a new web page I created came back to me with a nightmare request: take the table and make it a tiered drill-down table. The table is the Google Visualization Table, and it's not got a single provision for handling roll-ups of the data, or hiding/expanding groups - or even of groups at all. This was going to have to be something I implemented from scratch.
Ick.
I had all the data, and I could get the organization, but how to store the organization? How to expand/collapse it? There were a lot of things I needed.
The thing I wanted to get started today was the aggregation into groups. It's a pretty simple idea: there's a new row in the table that is a very simple attribution of the data on the rows of it's members. So I needed to be able to identify the groups in some order so that they would be calculated in a consistent manner. If there were going to be several levels in this new system, we needed to be able to correctly aggregate the data for all groups.
What hit me was a simple use of the JavaScript objects and arrays to organize the groups. Each group would have a name and list of members (for now), and they would be placed into an array in the order of processing, which would guarantee that the lowest-level groups were calculated first, and then those depending on these next, and so on.
Breakthrough.
The group definitions started out looking like:
var g = 0; var groups = new Array(); groups[g++] = { name: 'Housing', members: ['ABC', 'DEF', 'GHI'] }; groups[g++] = { name: 'Tech', members: ['AAPL', 'IBM', 'GOOG'] }; groups[g++] = { name: 'Retail', members: ['BBUY', 'HD', 'LOW'] };
which means that if you want to re-order the calculation, just move the definitions in the JavaScript file - the g++ takes care of putting them in the right order. Also, with this we can then look at writing something like this to add the aggregated value rows to the end of the table:
// create all the groups - in the right order for (var g = 0; g < groups.length; ++g) { createGroup(answer, groups, portMap); } /** * This function creates the aggregate row in the table */ function createNewGroup(tbl, grp, map) { // create a new row at the end of the table - sort later var row = tbl.addRow(); // add it to the map map[grp.name] = row; // ...set the name of the group in the right spot tbl.setValue(row, 0, grp.name); // now get the values for the group from the members present var val = 0.0; var colCnt = tbl.getNumberOfColumns(); for (var c = 1; c < colCnt; ++c) { // reset the value for each column val = 0.0; // aggregate the numerics values - pick first non-numeric if (tbl.getColumnType(c) == 'number') { // sum up all the available numeric values for (var e = 0; e < grp.members.length; ++e) { if (map[grp.members[e]] != undefined) { val += tbl.getValue(map[grp.members[e]], c); } } } else { // get just the first available value for (var e = 0; e < grp.members.length; ++e) { if (map[grp.members[e]] != undefined) { val = tbl.getValue(map[grp.members[e]], c); break; } } } // save the value we have for the column tbl.setValue(row, c, val); // ...and set the formatted value as well if (tbl.getColumnType(c) == 'number') { if (val != null) { tbl.setFormattedValue(row, c, val.numberFormat('#,##0;(#,##0)'); } } } }
The trick here is the mapping of the portfolio (or member) to row in the table. I created a function that did this for me:
function mapNamesToIndex(tbl) { var map = new Array(); var port = null; for (var i = 0; i < tbl.getNumberOfRows(); ++i) { // get the portfolio for this row port = tbl.getValue(i, 0); // map it into the array map[port] = i; } return map; }
With this, I'm able to make one scan through the table and map the row indexes for all values - making it much faster to do the aggregations.
With this, I'm able to get the aggregations created in the table. With the previous posting about how to sort on an arbitrary key, I can then pass this into that function to have the groups above their contents in the table.
Big day... not done, but a good start to what I need to get done.