Working with Data

Loading Data

Webix Pivot expects a common URL for loading and saving data:

view:"pivot", url:"remote/data"

Data Structure

Pivot expects a JSON object with the data to aggregate and display. There are no obligatory fields required so data structure can be totally custom. Check the data sample below:

[
  {
    balance: 4.699,
    continent: "South America",
    form: "Republic",
    gdp: 181.357,
    name: "Argentina",
    oil: 1.545,
    year: 2005
  },
  // other data objects
]

Data Services

Pivot has the following services to work with data:

1. Local Data

stores data on the client side
allows redefining limits for data volumes
provides methods for getting and repainting data

const local = $$("pivot").getService("local");
// gets an array of structure columns
const columns = pivot.getColumns();

2. Backend

provides methods for fetching data from the server

const back = $$("pivot").getService("backend");
back.data().then(data => console.log(data));

Study the models folder in the source code for method signatures.

Data Operations

Getting Pivot Data

The Pivot data are accessible via the getData() method of the Local Data service.

const data = $$("pivot").getService("local").getData(false);

The method accepts one parameter:

force (boolean) - if true, data will be reloaded from the server.

Force data reloading

$$("pivot").getService("local").getData(true);

The method returns a data object with the following fields:

data (array) - contains inner arrays with tree data (one array per each data group)
header (array) - an array of header object. Each object contains the following fields:
- id (number) - header ID
- header (array) - contains two objects with the text and operation fields
- format (string) - header value format.
marks (array) - 2D array where each element is either an empty value or an array with CSS styling class for min/max values of the column/row
total (array) - an array of total values. Each element is either an empty value or a total value of a column.

Data example

data: [/* Pivot data */],
header: [
  {
    id: 1,
    header: [{text: "oil"}, {operation: "max"}],
  },
  // other headers
]
marks: [
  ["webix_min_x"]
  // other marks
]
total: [2.946, 292.017, ... ]

Getting Fields

Fields are accessible via the getFields() method of the Local Data service.

const fields = $$("pivot").getService("local").getFields();

The method takes one parameter:

firstRow (object) - first row in the data.

$$("pivot").getService("local").getFields(data[0]);
/*
  "name": "Argentina",
  "year": 2005,
  "continent": "South America",
  "form": "Republic",
  "gdp": 181.357,
  "oil": 1.545,
  "balance": 4.699
*/

The method returns an array of object fields. Each object possesses the following properties:

id (string) - field ID
type (number) - field type. Possible values are:
- "text" - if field value is a string
- "number" - if field value is a number
- "date" - if field value is a date.
value (string) - field value
predicate (string) - predicate of the field
prepare (function) - prepares raw data. Accepts a single parameter - raw value (e.g. "2021").

Accessing Pivot fields can be useful when you need to redefine fields (e.g. change field type, value, etc.).

Getting Chart Palette

To get built-in chart palette call the getPalette() method of the Local Data service.

const palette = $$("pivot").getService("local").getPalette();

The method returns a 2D array where each inner array is a row of colors:

[
  ["#e33fc7", "#a244ea", "#476cee", "#36abee", "#58dccd", "#a7ee70"],
  ["#d3ee36", "#eed236", "#ee9336", "#ee4339", "#595959", "#b85981"],
  // other rows
]

Accessing chart palette can be useful when you need to redefine predefined color sets.

Clearing data

You can call clearAll to reset all stored data and settings.

$$("$pivot").clearAll();

Reloading data

To reload data after calling clearAll, you need to call the related method of the Local Data service: getData(true). Boolean parameter true will force reloading from the server.

$$("$pivot").getService("local").getData(true);

getData returns a promise that is resolved when the data is received. Once the data is received, you can also set the initial structure to display it:

$$("$pivot").getService("local").getData(true).then(() => {
  $$("$pivot").setStructure({ /* structure params */ });
});

Use Case: Loading Edited Data from DataTable into Pivot

If you need to edit the source data displayed in Pivot, you can integrate Pivot with Datatable. Pivot is primarily designed for data aggregation, while the Datatable is a tool that allows editing, deleting, and adding data. In this use case, all modifications will be made within Datatable, and the updated data will then be loaded into Pivot for display.