You can get and set the pivot configuration object as follows:
// getting the current configuration
var config = pivot.getStructure();
// setting a new configuration
pivot.setStructure(newConfig);
The format of the config object is the same as "structure" parameter of the constructor:
var config = {
rows: ["form", "name"],
columns: ["year"],
values: [
{ name: "gdp", operation: "sum"},
{ name: "oil", operation: "sum"}
],
filters:[]
}
Related sample: Setting/Getting Structure
Operations are set within the Pivot structure object in the values array. The name property in the operation object refers to the data item property:
view: "pivot",
structure: {
values: [
{ name: "gdp", operation: "sum" }, // GDP values will be summed up
{ name: "oil", operation: "max" } // max oil value will be shown
]
}
There are 4 prebuilt operations over data:
Please note that Pivot Chart has a different logic of adding custom data operations. Check the related article for details.
If needed, you can add your own operation in one of the following ways:
1) through the operations property
pivot.operations.abssum = function(data) {
// data - array of values which need to be processed
var sum = 0;
for (var i = 0; i < data.length; i++) {
var num = window.parseFloat(data[i], 10);
if (!window.isNaN(num))
sum += Math.abs(num);
}
return sum;
};
And use it as:
values:[ name:"oil", operation:"abssum"]
Related sample: Defining Custom Functions
2) with the help of the addOperation method
The method takes three parameters:
Besides adding an operation, the addOperation method allows specifying two options in the third parameter:
grida.addOperation("avr", function(data) {
var sum = 0;
for (var i = 0; i < data.length; i++) {
if( data[i] )
sum += window.parseFloat(data[i], 10);
}
return data.length?(sum/data.length):0;
}, {leavesOnly: true});
Related sample: Defining Custom Functions
Related sample: Weighted Average
The default logic of processing values is not always suitable for calculating results in the total column or footer. In this case you can create a custom total operation with the necessary logic for processing values with the help of the addTotalOperation method. It takes two parameters:
Besides adding an operation, the method allows specifying two options in the third parameter:
$$("pivot").addTotalOperation("wAver", function(values, key, data) {
// make the neccessary calculations and return the resulting value
}, {leavesOnly: true});
Related sample: Weighted Average
To calculate the total sum for each row, total column can be set via the totalColumn property
webix.ui({
view:"pivot",
totalColumn:"sumOnly",
//...
});
To calculate the total sum per column, footer can be defined via the footer property:
webix.ui({
view:"pivot",
footer: "sumOnly",
//...
});
The "sumOnly" value of both properties forces the summing function to process only rows/columns that contain sums already.
Related sample: Total Column and Footer
Filters are set within the Pivot structure object in filters array. Name refers to data item property used for filtering:
view:"pivot",
structure: {
filters: [
{ name: "name", type:"select" },
{ name: "continent", type: "text" }
]
}
There are two types of filters:
By default, Pivot uses data item properties for column names. But if you have dataset structured like below, the default pattern becomes unsuitable:
[{ a1:100, a2:1.34 }, ...]
In this case, you can use the fieldMap property to set beautiful names for columns instead of a1 and a2:
webix.ui({
view: "pivot",
fieldMap: { "a1" : "GDP", "a2" : "Grow ratio" },
//...
});
Related sample: Custom Field Titles
In order to disable changing of the Pivot configuration settings, you can specify the readonly config property with the "true" value:
webix.ui({
view: "pivot",
readonly:true,
//...
});
You can also set a readonly title which will be displayed instead of the link which opens a popup with Pivot configuration settings. Use the readonlyTitle for this purpose:
webix.ui({
view: "pivot",
readonlyTitle:"New Title",
//...
});
You can enable header sorting in Pivot by specifying the sort property with the "string" value in the column configuration:
webix.ui({
view: "pivot",
id: "pivot",
structure:{
rows: ["year"],
columns: [{id:"form",sort:"string"}, {id:"continent",sort:"string"}],
values: [{name:"balance", operation:"sum"}]
}
});
It's also possible to define the sorting direction in the column. By default, the direction is ascending. You can change it by setting the sortDir property with the "desc" value in the column configuration:
structure:{
rows: ["year"],
columns: [
{id:"form",sort:"string", sortDir:"desc"},
{id:"continent",sort:"string"}
],
values: [{name:"balance", operation:"sum"}]
}
It is possible to configure the order of Pivot columns headers. For this purpose you need to set the columnSort property in the Pivot structure object. Inside of this property you can specify the following objects:
webix.ui({
id:"pivot",
view:"pivot",
structure: {
columnSort: {
"$default": {
dir: "desc"
},
"year": {
dir: "asc"
}
},
rows: ["name"],
columns: ["form", "continent", "year"],
values: [
{ name:"gdp", operation:"sum", sort:"string"},
{ name:"oil", operation:"sum"},
]
}
});
In the above example we have set the "desc" order of sorting for all operation columns and the "asc" order for the "year" columns headers. So all the columns headers will be sorted in the descending order, except for the headers of the "year" columns. They will be sorted in the ascending order.
There is a possibility to get the source data of Webix Pivot in case you need to check that it includes all the necessary rows and columns. You can do it in the following way:
$$("pivot").$$("data").attachEvent("onItemClick", function(id){
var item = this.getItem(id);
var pivot = this.getTopParentView();
var source = item.$source;
if (source){
var raw = pivot.data.getItem(source[0]);
webix.message("Based on "+source.length+"raw records. Base country: " + raw.name);
} else {
//group row, collect all sources
var source = [];
this.data.eachSubItem(id, function(obj){
if (obj.$source) source.push.apply(source, obj.$source);
});
}
var data = [].concat(source).map(id => pivot.data.getItem(id));
$$("raw").clearAll();
$$("raw").parse(data);
})
Related sample: Access Raw Data
Back to top