Loading Data

Pivot supports both inline and external (including server-side) data in any of the Supported Data Types: XML, JSON, JsArray and CSV.

Inline Data

Inline Data (JSON)

var pivot_dataset = [
    {"name": "China", "year": 2005, "form": "Republic", "gdp": 181.357, "oil": 1.545 },
    {"name": "China", "year": 2006, "form": "Republic", "gdp": 212.507, "oil": 1.732 },
    //...
]

To load inline data during initialization of a component, make use of data property:

webix.ui({
    view: "pivot",
    id: "pivot",
    data: pivot_dataset
});

To load inline data after the component initialization, for instance, on some event, use the parse function:

$$("pivot").parse(pivot_dataset);

Related sample:  Loading from Inline Data Source

External Data

Either you get data from an external file or by a server side script, you should use the following pattern.

If you load data during the component initialization, specify the path to this file/script as the value of url

{
    view: "pivot",
    url: "../load.php" // or "../data.json"
}

If you load data after the component has been initialized (e.g. on some event), use the load function:

$$("pivot").load("../load.php");
//or
$$("pivot").load("../data.json");

In essence, Pivot complies to standard Webix Data Loading rules.

Related sample:  Loading from URL

Data Export

You can export the result to PDF, Excel, CSV and PNG formats by using the related methods - toPNG, toExcel, toCSV and toPDF:

webix.toPDF($$("myPivot"));
webix.toExcel($$("myPivot"));
webix.toPNG($$("myPivot"));
webix.toCSV($$("myPivot"));

Customizing data export to Excel

In addition to standard export options, the Pivot API allows customizing the output data and render separate columns for data on different hierarchy levels, thus rendering data in a table:

webix.toExcel($$("myPivot"),{
    flatTree:{
        id:"value",
        columns:[
            { header:"Form" },
            { header:"Country" },
        ]
    }
});

The flatTree property object includes the following options:

• id (string) - the data property which will be rendered in columns
• columns (array) - an array with columns, to which the above data property will be distributed according to the hierarchy level
• fill (boolean) - optional. If true, allows filling child records with parent data for each child node

Related sample:  Export Data from Pivot

External Data Processing

It is possible to configure Pivot Table in such a way that its data are processed by a custom server-side script. In this case Pivot loads data that were grouped on the server and provides the ability to customize Pivot configuration.

To define such a processing, you need to enable the externalProcessing property in the Pivot configuration.

webix.ui({
    view: "pivot",
    externalProcessing: true,
    ...
});

The loaded data source should be a JSON that contains the following properties:

• data - an object that includes "columns" and "data" configuration.
  • columns - property is an array of columns properties. It should correspond to the columns definition of TreeTable
  • data - a JSON data source for TreeTable
• structure - an object with the initial Pivot structure
• fields - an array of all pivot fields. It is necessary for the configuration popup to show all the fields there
• options - (optional) a JSON object with options for select filters.

There are some rules for the Pivot columns headers:

• bottom headers (the headers that display formatted value names: e.g "oil (min)") should contain two properties:
  • operation - the name of operation ("min", "sum",...)
  • name - the name of the calculated field
• other headers (that display categories) should contain the "name" property with the text to display

Here is an example of a data source for a Pivot with select filter for the "continent" field:

{
    "fields" :  ["balance", "continent", "form", ...],
    "options" : {
        "continent" : [ "Africa", "Asia", ... ]
    },
    "structure": {
        "filters" : [{ name: "continent", type: "multicombo"}],
        "rows" : ["year"],
        "columns" : ["continent"],
        "values" : [{ "name":"oil", "operation":["min","sum"]}]
    },
    "data" : {
        "columns" : [
            {
                "id" : "column0",
                "template" : "{common.treetable()} #column0#",
                "header" : {}
            },
            {
                "id" : "column1",
                "header" : [
                    { "name" : "Africa", "colspan": 2},
                    { "operation": "min", "name": "oil"}
                ]
            },
            ...
        ],
        "data": [
            {
                "column0": "2014",
                "column1" : 0.778,
                ...
            },
            ...
        ]
    }
}

Back to top