Created
October 31, 2013 09:33
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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" | |
| }; | |
| }; | |
| }; | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; | |
| }; | |
| }; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // 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"] | |
| }); |
I just wanted to share my refactoring of this to allow it to work more generally with the latest version of the pivot.js library.
function buildMultiAggregator(names) {
var numInputs = 0;
var aggregators = $.map(names, function(a) {
var agg = $.pivotUtilities.aggregators[a];
var ni = agg([])().numInputs || 0;
numInputs += ni;
return {
aggregator: agg,
numInputs: ni,
name: a
}
});
return function multiAggregator(facts) {
var offset = 0;
var factsAggs = $.map(aggregators, function(a) {
var next = offset + a.numInputs;
var f = facts.slice(offset, next);
var fa = a.aggregator(f);
offset = next;
return {
aggregator: fa,
name: a.name + ''
};
});
return function (data, rowKey, colKey) {
var aggs = $.map(factsAggs, function(a, i) {
var agg = a.aggregator(data, rowKey, colKey);
if(agg.inner) {
//This is detection that we have a fractionOf aggregator that needs to be "fixed"
agg.value = function() {
var parent = data.getAggregator.apply(data, this.selector);
if(parent.multivalue)
parent = parent.multivalue()[i].aggregator;
return this.inner.value() / parent.inner.value();
}
}
return {
aggregator: agg,
name: a.name
};
});
return {
push: function (record) {
for(var i = 0 ; i < aggs.length; i++) {
aggs[i].aggregator.push(record);
}
},
multivalue: function () {
return aggs;
},
value: function (i) {
i = i || 0;
return aggs[i].aggregator.value();
},
format: function (x, i) {
i = i || 0;
return aggs[i].aggregator.format(x);
},
numInputs: numInputs
};
};
};
}
And the renderer is defined as
function multiTableRenderer(pivotData, opts) {
opts = opts || {
localeStrings: {
totals: 'Totals'
}
};
var colAttrs = pivotData.colAttrs;
var rowAttrs = pivotData.rowAttrs;
var rowKeys = pivotData.getRowKeys();
var colKeys = pivotData.getColKeys();
var tmpAggregator = pivotData.getAggregator([], []);
var multivalue = tmpAggregator.multivalue && tmpAggregator.multivalue();
var aggcount = (multivalue && multivalue.length) || 1;
var rowRenderers = $.map(rowAttrs, function(column, index) {
return getRenderer({
length: rowKeys.length,
getValue: function(i) {
return rowKeys[i][index];
}
}, column);
});
var colRenderers = $.map(colAttrs, function(column, index) {
return getRenderer({
length: colKeys.length,
getValue: function(i) {
return colKeys[i][index];
}
}, column);
});
function addDataCells(tr, i, j) {
var colKey = j === -1 ? [] : colKeys[j];
var rowKey = i === -1 ? [] : rowKeys[i];
var aggregator = pivotData.getAggregator(rowKey, colKey);
var rcClass, v, x;
if(i === -1 && j === -1) {
rcClass = 'pvtGrandTotal';
} else if(i === -1) {
rcClass = 'pvtTotal colTotal';
} else if (j === -1) {
rcClass = 'pvtTotal rowTotal';
} else {
rcClass = 'pvtVal row' + i + ' col' + j;
}
if (aggregator.multivalue) {
var mv = aggregator.multivalue();
for (x = 0; x < mv.length; x++) {
v = mv[x].aggregator.value();
$("<td class='" + rcClass + "'>")
.text(aggregator.format(v, x))
.data("value", v)
.appendTo(tr);
}
} else if(aggcount > 1) {
//This handles data cells where there is no data at all. just make sure to fill it in with blanks
for (x = 0; x < aggcount; x++) {
$("<td class='" + rcClass + "'>")
.data("value", null)
.appendTo(tr);
}
} else {
var val = aggregator.value();
$("<td class='" + rcClass + "'>")
.text(aggregator.format(val))
.data("value", val)
.appendTo(tr);
}
}
var c, colKey, i, j, r, result, rowKey, th, tr, txt, x, v;
result = $("<table class='pvtTable'>");
var colspans = [];
for (i = 0; i < colAttrs.length; i++)
colspans.push(0);
for (j = 0; j < colAttrs.length; j++) {
c = colAttrs[j];
tr = $("<tr>");
if (j === 0 && rowAttrs.length !== 0) {
tr.append($("<th>").attr("colspan", rowAttrs.length).attr("rowspan", colAttrs.length));
}
tr.append($("<th class='pvtAxisLabel'>").text(c));
for (i= 0; i < colKeys.length; i++) {
colKey = colKeys[i];
txt = colKey[j];
x = colspans[j];
if(!x) {
for (x = 1; i + x < colKeys.length; x++) {
//This keeps smaller groupings from spanning past their parent
if(j > 0) {
//This logic is not correct yet. For the column headers we have to compare each
//parent to the next as we go
//isBreak = false;
//for(z = j -1; z >= 0; z--) {
// if(colKeys[i + x])
//}
if(x === colspans[j-1] + 1)
break;
}
//If the next row doesnt match our rows value
if(colKeys[i + x][j] !== txt)
break;
}
th = $("<th class='pvtColLabel'>").text(colRenderers[j](txt === 'null' ? '' : txt));
if(x > 1 || aggcount > 1)
th.attr("colspan", x * aggcount);
tr.append(th);
if (j === colAttrs.length - 1 && rowAttrs.length !== 0)
th.attr("rowspan", aggcount === 1 ? 2 : 1);
}
colspans[j] = x-1;
}
if (j === 0) {
th = $("<th class='pvtTotalLabel'>").text(opts.localeStrings.totals).attr("colspan", aggcount);
th.attr("rowspan", colAttrs.length + (rowAttrs.length === 0 ? 0 : 1) - (aggcount === 1 ? 0 : 1));
tr.append(th);
}
result.append(tr);
}
if (rowAttrs.length !== 0) {
tr = $("<tr>");
for (i = 0; i < rowAttrs.length; i++) {
r = rowAttrs[i];
tr.append($("<th class='pvtAxisLabel'>").text(r));
}
if (aggcount > 1) {
if (colAttrs.length > 0) {
th = $("<th>");
tr.append(th);
}
for (i = 0; i < colKeys.length; i++) {
for (j = 0; j < multivalue.length; j++) {
v = multivalue[j];
tr.append($("<th class='pvtColLabel'>").text(v.name).data("value", v.name));
}
}
for (j = 0; j < multivalue.length; j++) {
v = multivalue[j];
tr.append($("<th class='pvtColLabel'>").text(v.name).data("value", v.name));
}
} else {
th = $("<th>");
if (colAttrs.length === 0) {
th.addClass("pvtTotalLabel").text(opts.localeStrings.totals);
}
tr.append(th);
}
result.append(tr);
}
var rowspans = [];
for (i = 0; i < rowAttrs.length; i++)
rowspans.push(0);
//Adds all the data into the table and the column totals at the end of each row
for (i = 0; i < rowKeys.length; i++) {
rowKey = rowKeys[i];
tr = $("<tr>");
for (j = 0; j < rowKey.length; j++) {
txt = rowKey[j];
x = rowspans[j];
if(!x) {
for (x = 1; i + x < rowKeys.length; x++) {
//This keeps smaller groupings from spanning past their parent
if(j > 0) {
if(x === rowspans[j-1] + 1)
break;
}
//If the next row doesnt match our rows value
if(rowKeys[i + x][j] !== txt)
break;
}
th = $("<th class='pvtRowLabel'>").text(rowRenderers[j](txt === 'null' ? '' : txt));
if(x > 1)
th.attr("rowspan", x);
tr.append(th);
if (j === rowAttrs.length - 1 && colAttrs.length !== 0)
th.attr("colspan", 2);
}
rowspans[j] = x-1;
}
for (j = 0; j < colKeys.length; j++) {
addDataCells(tr, i, j);
}
addDataCells(tr, i, -1);
result.append(tr);
}
//Add the bottom row total row
tr = $("<tr>");
th = $("<th class='pvtTotalLabel'>").text(opts.localeStrings.totals);
th.attr("colspan", rowAttrs.length + (colAttrs.length === 0 ? 0 : 1));
tr.append(th);
for (j = 0; j < colKeys.length; j++) {
addDataCells(tr, -1, j);
}
addDataCells(tr, -1, -1);
result.append(tr);
result.data('numrows', rowKeys.length);
result.data('numcols', colKeys.length);
return result;
}
Then to use all the built in renderers you can do this
var custom_renderers = {
"Table": function(data, opts) {
return multiTableRenderer(data, opts);
},
"Table Barchart": function(data, opts) {
return multiTableRenderer(data, opts).barchart();
},
"Heatmap": function(data, opts) {
return multiTableRenderer(data, opts).heatmap("heatmap", opts);
},
"Row Heatmap": function(data, opts) {
return multiTableRenderer(data, opts).heatmap("rowheatmap", opts);
},
"Col Heatmap": function(data, opts) {
return multiTableRenderer(data, opts).heatmap("colheatmap", opts);
}
};
Then to add the aggregator you would do something like the following
var agg = buildMultiAggregator(['Sum', 'Count']);
var pivotaggs = $.extend({
'Analytics': agg
}, $.pivotUtilities.aggregators);
$p.pivotUI(res, {
aggregators: pivotaggs,
renderers: custom_renderers,
aggregatorName: 'Analytics',
vals: ['SumColumn'],
rows: rows,
cols: cols
});
@park896
Hi park896
Can you have any example?I can't get him to work.
Many thanks
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.
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
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
Anybody knows how to implement a chart renderer for this multi sum?