Can I display data within a grouped section for a table

[levyuk]

Hi

I have data which contains the the name of an office, the staff name,
and a count

data = new google.visualization.DataTable();
data.addColumn('string', 'Staff Name');
data.addColumn('number', 'Count');
data.addColumn('string', 'Office');
data.addRows([
['Jon', 15,'London'],
['Kev', 29, 'London'],
['Dave', 4, 'Edinburgh'],
['Rosie', 11, 'Edinburgh'],
['Kim', 6, 'Cardiff']
]);

var table = new
google.visualization.Table(document.getElementById('table_div'));
var result = google.visualization.data.group(data, [2], [
{ 'column': 1, 'aggregation':
google.visualization.data.sum, 'type': 'number' }
]);

Now what I want to do is group by the office, which the code above
does but under each office I want to display the names and individual
counts of the staff. Is this possible and how do I go about it?

cheers
Jon

[asgallant]

What do you mean by "under each office"?  Do you want another cell in the table with this info, or do you want it added to the Office cell, or something else?

[levyuk]

Hi

In effect I want to group by two pieces of information, the office and
the staff name. But I only want to show the office name once. So the
table would have a row for the office name, then under this it would
show all the staff names with their individual count and then a
further row with the office total. This would repeat for each office.

Does that make more sense?

Thanks
Jon

[asgallant]

Something like this:

    Office
-------|------
Staff  | count
-------|------
Staff  | count
-------|------
Staff  | count

-------|------

Total  | count
-------|------
    Office
-------|------
Staff  | count
-------|------
Staff  | count
-------|------
Staff  | count
-------|------
Staff  | count
-------|------

Total  | count

-------|------
    Office
-------|------
Staff  | count
-------|------
Staff  | count

-------|------

Total  | count

or this?

       | Staff | Count |

Office | Staff | Count | Total

       | Staff | Count |

-------|-------|-------|------

       | Staff | Count |

Office | Staff | Count | Total

       | Staff | Count |

       | Staff | Count |

-------|-------|-------|------

Office | Staff | Count | Total

       | Staff | Count |

[levyuk]

The 1st example is more like it but with a sub-total for each office
along with an final total at the end.

Jon

[asgallant]

There's no built-in functionality to handle anything like this, you'll have to code something that transforms the source data table into one containing data in the structure you want (or organize the initial data differently).  Perhaps something like this would do:

/*  assumes data is the dataTable object to work from
 *  groups by column 2, then meshes the original table with the group and adds a totals row
 */
var groupName, groupVal, rows, total = 0;
var group = google.visualization.data.group(data, [2], [{

    'column': 1,
    'aggregation': google.visualization.data.sum,
    'type': 'number'
}]);

var newData = new google.visualization.DataTable();
newData.addColumn('string', 'Location/Name');
newData.addColumn('number', 'Count');
for (i = 0; i < group.getNumberOfRows()) {
    groupName = group.getValue(i, 0);
    groupVal = group.getValue(i, 1);
    newData.addRow([groupName, groupVal]);
    total += groupVal;
    
    rows = data.getFilteredRows([{column: 2, value: groupName}]);
    for (j = 0; j < rows.length; j++) {
        newData.addRow([data.getValue(rows[i], 0), data.getValue(rows[i], 1)]);
    }
}
newData.addRow(['Total', total]);