Skip to content

Instantly share code, notes, and snippets.

@stephanvd
Created October 31, 2013 09:33
Show Gist options
  • Save stephanvd/7246890 to your computer and use it in GitHub Desktop.
Save stephanvd/7246890 to your computer and use it in GitHub Desktop.
Aggregate over multiple fact fields. Result looks like this: http://imgur.com/fI636rq The aggregator returns first field for renderers without multifield support. The renderer is a modified version of the built-in table to support multifield. Quick and dirty but it gets the job done. For https://github.com/nicolaskruchten/pivottable.
multifactSumAggregator = function() {
return function(facts) {
return function() {
var summedFacts = {};
for (_i = 0, _len = facts.length; _i < _len; _i++) {
summedFacts[facts[_i]] = 0
}
return {
push: function(record) {
for ( _i = 0, _len = facts.length; _i < _len; _i++) {
summedFacts[facts[_i]] += parseFloat(record[facts[_i]]);
}
},
multivalue: function() {
return summedFacts;
},
// return the first element for unsupported renderers.
value: function() { return summedFacts[facts[0]]; },
format: function(x) { return x; },
label: "Facts"
};
};
};
}
multifactTableRenderer = function(){
return function(pivotData) {
var aggregator, c, colAttrs, colKey, colKeys, i, j, r, result, rowAttrs, rowKey, rowKeys, th, totalAggregator, tr, txt, val, x;
colAttrs = pivotData.colAttrs;
rowAttrs = pivotData.rowAttrs;
rowKeys = pivotData.getRowKeys();
colKeys = pivotData.getColKeys();
result = $("<table class='table table-bordered pvtTable'>");
for (j in colAttrs) {
c = colAttrs[j];
tr = $("<tr>");
if (parseInt(j) === 0 && rowAttrs.length !== 0) {
tr.append($("<th>").attr("colspan", rowAttrs.length).attr("rowspan", colAttrs.length));
}
tr.append($("<th class='pvtAxisLabel'>").text(c));
tmpAggregator = pivotData.getAggregator([], []);
if (tmpAggregator.multivalue) {
col_colspan = Object.keys(tmpAggregator.multivalue()).length;
col_rowspan = 1
} else {
col_colspan = 1
col_rowspan = 2
}
for (i in colKeys) {
colKey = colKeys[i];
th = $("<th class='pvtColLabel'>").text(colKey[j]).attr("colspan", col_colspan);
if (parseInt(j) === colAttrs.length - 1 && rowAttrs.length !== 0) {
th.attr("rowspan", col_rowspan);
}
tr.append(th);
}
if (parseInt(j) === 0) {
tr.append($("<th class='pvtTotalLabel'>").text("Totals").attr("colspan", col_colspan).attr("rowspan", col_rowspan));
}
result.append(tr);
}
if (rowAttrs.length !== 0) {
tr = $("<tr>");
for (i in rowAttrs) {
r = rowAttrs[i];
tr.append($("<th class='pvtAxisLabel'>").text(r));
}
tmpAggregator = pivotData.getAggregator([], []);
if (tmpAggregator.multivalue) {
if (colAttrs.length > 0) {
th = $("<th>");
tr.append(th);
}
val = tmpAggregator.multivalue();
for (i in colKeys) {
for (v in val) {
tr.append($("<th class='pvtColLabel'>").text(v).data("value", v));
}
}
for (v in val) {
tr.append($("<th class='pvtColLabel'>").text(v).data("value", v));
}
} else {
th = $("<th>");
if (colAttrs.length === 0) {
th.addClass("pvtTotalLabel").text("Totals");
}
tr.append(th);
}
result.append(tr);
}
for (i in rowKeys) {
rowKey = rowKeys[i];
tr = $("<tr>");
for (j in rowKey) {
txt = rowKey[j];
th = $("<th class='pvtRowLabel'>").text(txt).attr("rowspan", x);
if (parseInt(j) === rowAttrs.length - 1 && colAttrs.length !== 0) {
th.attr("colspan", 2);
}
tr.append(th);
}
for (j in colKeys) {
colKey = colKeys[j];
aggregator = pivotData.getAggregator(rowKey, colKey);
if (aggregator.multivalue) {
val = aggregator.multivalue();
for (v in val) {
tr.append($("<td class='pvtVal row" + i + " col" + j + "'>").text(aggregator.format(val[v])).data("value", val[v]));
}
} else {
val = aggregator.value();
tr.append($("<td class='pvtVal row" + i + " col" + j + "'>").text(aggregator.format(val)).data("value", val));
}
}
totalAggregator = pivotData.getAggregator(rowKey, []);
if (totalAggregator.multivalue) {
val = totalAggregator.multivalue();
for (v in val) {
tr.append($("<td class='pvtTotal rowTotal'>").text(totalAggregator.format(val[v])).data("value", val[v]).data("for", "row" + i));
}
} else {
val = totalAggregator.value();
tr.append($("<td class='pvtTotal rowTotal'>").text(totalAggregator.format(val)).data("value", val).data("for", "row" + i));
}
result.append(tr);
}
tr = $("<tr>");
th = $("<th class='pvtTotalLabel'>").text("Totals");
th.attr("colspan", rowAttrs.length + (colAttrs.length === 0 ? 0 : 1));
tr.append(th);
for (j in colKeys) {
colKey = colKeys[j];
totalAggregator = pivotData.getAggregator([], colKey);
if (totalAggregator.multivalue) {
val = totalAggregator.multivalue();
for (v in val) {
tr.append($("<td class='pvtTotal colTotal'>").text(totalAggregator.format(val[v])).data("value", val[v]).data("for", "col" + j));
}
} else {
val = totalAggregator.value();
tr.append($("<td class='pvtTotal colTotal'>").text(totalAggregator.format(val)).data("value", val).data("for", "col" + j));
}
}
totalAggregator = pivotData.getAggregator([], []);
if (totalAggregator.multivalue) {
val = totalAggregator.multivalue();
for (v in val) {
tr.append($("<td class='pvtGrandTotal'>").text(totalAggregator.format(val[v])).data("value", val[v]));
}
} else {
val = totalAggregator.value();
tr.append($("<td class='pvtGrandTotal'>").text(totalAggregator.format(val)).data("value", val));
}
result.append(tr);
result.data("dimensions", [rowKeys.length, colKeys.length]);
return result;
};
};
// Putting the pieces together.
var custom_renderers = {
"Multifact Table": multifactTableRenderer()
};
var custom_aggregators = {
"Multifact aggregator": multifactSumAggregator()
};
$("#declarations_report #pivottable").pivotUI(data, {
aggregators: $.extend(custom_aggregators, $.pivotUtilities.aggregators),
renderers: $.extend(custom_renderers, $.pivotUtilities.renderers, $.pivotUtilities.gchart_renderers),
rows: ["person"],
cols: ["company"],
vals: ["hours", "turnover", "profit_margin"],
hiddenAttributes: ["id"]
});
@Austinb
Copy link

Austinb commented Aug 20, 2020

Just to let others know trying to use this to render multiple values but the latest version by @park896 is incomplete. There are internal function(s) missing that are using in rendering that are not defined like getRenderer(). Working to see how to work around.

@ilitty
Copy link

ilitty commented Jan 20, 2021

Hello, this solution looks great!
Since I am new to this, could you please tell me how to implement it in my current project based on pivottable.js?
Should I just add these files here and include them in mu index HTML or I should add the code in the pivottable.js?

Many thanks

@diego-weber
Copy link

To fix the problem with colspan, you just need to change:

          val = aggregator.value();
          tr.append($("<td class='pvtVal row" + i + " col" + j + "'>").text(aggregator.format(val)).data("value", val));

to

val = aggregator.value();
          if (val) {
            tr.append($("<td class='pvtVal row" + i + " col" + j + "'>").text(aggregator.format(val)).data("value", val));
          }
          else {
            tmpAggregator = pivotData.getAggregator([], []);
            var cols_length = 1;
            if (tmpAggregator.multivalue) {
              cols_length = Object.keys(tmpAggregator.multivalue()).length;
            }
            for (var cl = 0; cl < cols_length; cl++) {
              tr.append($("<td class='pvtVal row" + i + " col" + j + "-" + cl + "'>").text("").data("value", null));
            }
          }

on line 99 of multifact_table_renderer.js

Is it right @zuk38 ?

Thanks for this solution.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment