Configuring Pivot

Getting and Setting Configuration Object

You can get and set the pivot configuration object as follows:

//get current configuration
var config = pivot.getStructure();
 
//set configuration
pivot.setStructure(config);

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

Defining Operations on Data

Operations are set within Pivot structure object in the values array. Name refers to 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:

  • sum - default, shows the sum of values of this property;
  • max - shows the max value of this property found in the dataset;
  • min - shows the min value of this property found in the dataset;
  • count - shows the number of occurrences of this property in the dataset.

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:

  • name - (string) the operation's name
  • function - (function) the function which will be called for each cell in the column with this operation. It takes an array with values of child cells
  • options - (object) object with operation options (leavesOnly or ids, see details below)

Besides adding an operation, the addOperation method allows specifying two properties:

  • leavesOnly - (boolean) if set to true, the operation function will take only values of child rows that don't contain sub-rows.
  • ids - (boolean) if set to true, the operation function will take ids of ungrouped data items as the 4th 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

Sum Counters for Columns and Rows

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

Filters are set within 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:

  • select - filters by options automatically gathered from a dataset (all unique values of this property). For instance, if you choose Continents, then all unique continent names from the dataset become options;
  • text - filters by symbols in the text field. It supports base math comparison operators, so you can type something like "< 100", "> 2.5" or "= 12"
    If there is no operator, filter will use text match for filtering.

Renaming Fields

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

Setting Readonly Mode

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",
    //...
});

Related sample:  Readonly mode

Specifying Header Sorting

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"}]
}

Related sample:  Sorted Header

Back to top