Use sap.ui.table.Table like PowerPivot table

Question

I've got a table with these values:

var aTableData = [
                  {Product: "A", Sales:120, Group:"001"},
                  {Product: "A", Sales:82, Group:"002"},
                  {Product: "B", Sales:10, Group:"002"},
                  {Product: "C", Sales:14, Group:"001"},
                  {Product: "A", Sales:8, Group:"001"},
                  {Product: "A", Sales:39, Group:"001"},
                  {Product: "B", Sales:3, Group:"002"},
                  {Product: "C", Sales:2, Group:"001"},
                  {Product: "D", Sales:1002, Group:"003"},
];

and I want the user to select through a facetFilter grouping-options like in a PowerPivot table. For example if the user chooses Product the returned table should look like this:

var aTableData = [
                  {Product: "A", Sales:249},
                  {Product: "B", Sales:13},
                  {Product: "C", Sales:16},
                  {Product: "D", Sales:1002},
];

But if the user chooses Product and Group the table should look like this:

var aTableData = [
                      {Product: "A", Sales:167, group: "001"},
                      {Product: "A", Sales:82, group: "001"},
                      {Product: "B", Sales:13, group: "002"},
                      {Product: "C", Sales:16, group: "001"},
                      {Product: "D", Sales:1002, group: "003"},
    ];

At the moment I only can filter for one facet and the filter works only once (because I'm overwriting the table data). So how should the logic behind this "PowerPivot" be to filter for more than one or two values and is it possible to do it with filter and groupings (so I do not need to overwrite the table data)?

I've copied my code to a jsbin.

JSBIN

Answer 1

The grouping of aggregationbindings do not support any aggregate functions like sum. So i guess you have to do it all yourself.

I would recommed to load the unaggregated data with a $.ajax call (or with a dedicated Model) and save it in a member of your controller or component. Then aggregate your data with some lines of JavaScript into a copy and assign that to a JSONModel to which you bind your table.

With an algorithm like this, you can reuse the groupBy function:

var fnAggregate = function(aItems){
  var result = aItems[0];
  result.Sales = aItems.reduce(function(prev, curr){ return prev+curr.Sales;},0);
  return result;
};
var groupBy = function(aData, fnKey, fnAggregate){
  var map = {};
  aData.forEach(function(item){
    var key = fnKey(item);
    if (!(key in map)){
      map[key] = [];
    }
    map[key].push(item);
  });
  var result = [];
  for(key in map){
    result.push(fnAggregate(map[key]));
  }
  return result;
};

And in your handleSelect function you can use them

var fnKey = function(item){ 
  return selectedKeysArray.map(function(key){ return item[key]; }).join(";");
};                               
var groupedTableData = groupBy(aTableData, fnKey, fnAggregate);
var oTable = sap.ui.getCore().byId("Table_ID");
var oTableModel = oTable.getModel().setProperty("/modelData", groupedTableData);

Here's your modified jsbin .