eraskin
February 26, 2021, 3:17pm
#1
I am building a Pivot Table using the Reporting addon. I would like to build a custom aggregator that takes two arguments. The first is a value to sum. The second is a value to make unique.
I will implement an aggregator that will add the sum the first time a value is found. To make this clearer, I have customers, orders and detail lines in my data. The order amount field is repeated in each row for the same order. When I want to sum up the order amounts, I have to take one candidate row from each order and add the amount. Hence, I want to sum up the amount column each time I find a unique order id.
I can’t figure out how to write it. The help does not give any hints for this. The following is my first attempt.
function(data, rowKey, colKey) {
return {
uniq: [],
sum: 0,
push: function(record) {
if (record[attributeArray[1]] not in this.uniq && ! isNAN(parseFloat(attributeArray[0]))) {
this.uniq.push(record[attributeArray[1]]);
this.sum += parseFloat(attributeArray[0]);
}
},
value: { return this.sum; },
format: function(x) { return x; },
numInputs: 2,
};
}
It fails with Javascript errors (attributeArray is unknown, I believe). Heres the error I see in the Javascript console:
eraskin
February 26, 2021, 3:49pm
#2
Small update - I’ve been using the original source as an example, but that is in coffeescript. I have decided to try a smaller version, converting to Javascript and hard-coding the fields that will be used:
function(data, rowKey, colKey) {
return {
uniq: [],
sum: 0,
push: function(record) {
if ( ! this.uniq.includes(record["orderid"]) && ! isNaN(parseFloat(record["amount"])) ) {
this.uniq.push(record["orderid"]);
this.sum += parseFloat(record["amount"]);
}
},
value: { return this.sum; },
format: function(x) { return x; },
};
}
Still no luck. Now I get:
com.google.gwt.event.shared.UmbrellaException: Exception caught: (SyntaxError) : Unexpected token 'this'
I’m hoping somebody can see my syntax error?
Pinyazhin
March 2, 2021, 9:07am
#4
Hello!
PivotTable JS Aggregators wiki has examples of simple PivotTable and PivotTable with UI. In CUBA it will look like the following examples:
Simple PivotTable
You can do it completely in the descriptor:
<chart:pivotTable id="pivotTable"
dataContainer="goodsDc"
width="100%">
<chart:properties>
<chart:property name="count"/>
<chart:property name="name"/>
<chart:property name="price"/>
</chart:properties>
<chart:aggregation custom="true">
<chart:function>
function(data, rowKey, colKey) {
return {
count: 0,
push: function(record) {
this.count++;
},
value: function() {
return this.count;
},
format: function(x) {
return x; },
};
}
</chart:function>
</chart:aggregation>
<chart:columns>
<chart:column value="name"/>
</chart:columns>
<chart:rows>
<chart:row value="price"/>
</chart:rows>
</chart:pivotTable>
Or you can try to do it from Java:
@Inject
private PivotTable pivotTable;
@Subscribe
public void onInit(InitEvent event) {
pivotTable.setAggregation(new Aggregation()
.setCustom(true)
.setFunction(new JsFunction(
"function(data, rowKey, colKey) {\n" +
" return {\n" +
" count: 0,\n" +
" push: function(record) {\n" +
" this.count++;\n" +
" },\n" +
" value: function() {\n" +
" return this.count;\n" +
" },\n" +
" format: function(x) {\n" +
" return x; },\n" +
" };\n" +
"}"
)));
}
Editable PivotTable
Custom aggregation function in the screen descriptor:
<layout expand="pivotTable"
spacing="true">
<chart:pivotTable id="pivotTable"
dataContainer="goodsDc"
editable="true"
width="100%">
<chart:properties>
<chart:property name="count"/>
<chart:property name="name"/>
<chart:property name="price"/>
</chart:properties>
<chart:aggregations>
<chart:aggregation custom="true" caption="My custom aggregation">
<chart:function>
function(attributeArray) {
var attribute = attributeArray[0];
return function(data, rowKey, colKey) {
return {
uniq: [],
push: function(record) {
console.log('My custom logic of unique values');
this.uniq.push(record[attribute]);
},
value: function() { return this.uniq.length; },
format: function(x) { return x; },
numInputs: 1
};
};
}
</chart:function>
</chart:aggregation>
</chart:aggregations>
<chart:columns>
<chart:column value="name"/>
</chart:columns>
<chart:rows>
<chart:row value="price"/>
</chart:rows>
</chart:pivotTable>
</layout>
And from Java:
@Inject
private PivotTable pivotTable;
@Subscribe
public void onInit(InitEvent event) {
Aggregations aggregations = new Aggregations();
aggregations.addAggregations(
new Aggregation()
.setCustom(true)
.setCaption("My custom aggregation")
.setFunction(new JsFunction(
"function(attributeArray) {\n" +
" var attribute = attributeArray[0];\n" +
" return function(data, rowKey, colKey) {\n" +
" return {\n" +
" uniq: [],\n" +
" push: function(record) {\n" +
" console.log('My custom logic of unique values');\n" +
" this.uniq.push(record[attribute]);\n" +
" },\n" +
" value: function() { return this.uniq.length; },\n" +
" format: function(x) { return x; },\n" +
" numInputs: 1\n" +
" };\n" +
" };\n" +
"}"
)));
pivotTable.setAggregations(aggregations);
}
eraskin
March 2, 2021, 2:28pm
#6
Thanks. I did look at your examples, but I did not notice the extra Javascript in the Editable pivot table.
How does this translate to the Report Generator screens?
Pinyazhin
March 3, 2021, 6:13am
#7
To use it with “Reports” you should create a report template with PivotTable output and specify properties, aggregations, etc. See 4.2.8. Pivot Table Formatter .
For instance:
eraskin
March 3, 2021, 2:17pm
#8
Yes, this is the example that you get when you click the “?” icon. If you notice, it does not have any way to pass in variables. That’s where I got confused. Is it:
function(arrayvars) {
var myvar = arrayvars[0];
return function(data, rowKey, colKey) {
... do whatever referencing myvar ...
}
}
If not, how is it done?
Pinyazhin
March 4, 2021, 6:26am
#9
You can use myvar in the returning function like this:
function(arrayvars) {
var myvar = arrayvars[0];
return function(data, rowKey, colKey) {
return {
values: [],
push: function(record) {
if (myvar) {
this.values.push(myvar);
console.log('attribute name:' + myvar);
}
},
value: function() { return this.values.length; },
format: function(x) { return x; },
numInputs: 1
};
};
}
arrayvars - is an array of attribute names which the user can specify in the UI due to to the numInputs parameter (see wiki pivotUI(): aggregators and aggregatorName parameters ).
eraskin
March 4, 2021, 2:08pm
#10
OK, thank you. That’s what I thought but there really is no documentation to indicate this.
