JavaScript/jQuery PivotGrid - Fields and Areas

The structure of data displayed by the pivot grid is defined in the fields array. The pivot grid UI component is designed to group and summarize field values within areas and allows you to analyze large data. This article will help you get familiar with the field and area concepts.

Fields configuration is required to describe data from the store for the pivot grid data source. In most cases, the field configuration object contains the data field name, an area to which this field belongs and other optional configurations.

If the retrieveFields property is set to true, the fields array is generated automatically according to data from the store. In this case, the fields array defined in the configuration is merged with fields generated automatically. If the fields array is not configured, the automatically generated fields will be available in the Field Chooser.

The area is a place in the pivot grid UI component where you can put fields. Each field whose data is displayed in the pivot grid is bound to a particular area. There are four different areas.

• Row area — the first pivot grid dimension, which is used to group data. The categories generated from fields in this area are displayed in the row header. Dimensional areas should contain dimensional fields only (fields describing categories that can be used to group or filter data).

• Column area — the second pivot grid dimension, which is used to group data. Its characteristics are similar to the row area, except that its fields are displayed in the column header.

• Filter area — the third dimensional area. It has no visual representation, although you can perform filtering based on this area fields' data.

• Data area — the non-dimensional area containing fields with data and aggregation methods. Fields in this area are called data fields (measures in OLAP terminology). Data from the fields of this area is used to calculate summary values displayed in the pivot grid cells.

With the PivotGrid UI component you can organize fields related to a single dimension into groups. To learn more about this functionality, refer to the Fields Grouping article.

The order of fields in an area is significant, because the area is hierarchical and its fields define different hierarchy levels. Changing the order of hierarchy levels will change the meaning of your area. For example, consider the "time" dimension: if an area is configured as follows, data will be grouped by years and within each year - by months.

fields: [{
    area: 'column',
    dataField: 'OrderDate',
    dataType: 'date',
    groupInterval: 'year'
}, {
    area: 'column',
    dataField: 'OrderDate',
    dataType: 'date',
    groupInterval: 'month'
},
// ...
]

Alternatively, you can group data by months and within months — by years.

fields: [{
    area: 'column',
    dataField: 'OrderDate',
    dataType: 'date',
    groupInterval: 'month'
}, {
    area: 'column',
    dataField: 'OrderDate',
    dataType: 'date',
    groupInterval: 'year'
},
// ...
]

In this case, you will see data for each month summarized across all years. If you expand a month, you will see years (only those containing data of the expanded month) and the data will be split more precisely.

The ordering of fields in an area is defined either by their position in the fields array or by the areaIndex property (if it is set). Note that the fields whose areaIndex property is set to a value (any value) will be displayed before fields with no areaIndex defined.

An example of the areaIndex property is presented in the Get or Set Field Options section.

You can interact with the pivot grid fields using the API methods of pivot grid data source. If you did not create a separate PivotGridDataSource object, use the getDataSource() method of the PivotGrid UI component to acquire the pivot grid's current data source. Tasks that can be solved using the API are presented below.

You can either get all fields from the data source or get fields that belong to a specific area. The array returned by these functions has the following structure.

[  
    {  
        "area":/*...*/,
        "dataField":/*...*/,
        "groupInterval":/*...*/,
        "dataType":/*...*/,
        "displayFolder":/*...*/,
        "index":/* the index in the fields array */,
        "caption":/*...*/,
        "allowSorting":/*...*/,
        "allowSortingBySummary":/*...*/,
        "allowFiltering":/*...*/,
        "allowExpandAll":/*...*/,
        "areaIndex":/*...*/,
        //...
    },
//...
]

Virtually all properties available in this object are the same as in the fields configuration reference section, except for the index field. This is a unique field index in the fields array that can be used to identify this field (for example, when calling the field(id) method).

To get fields of a particular area, use the getAreaFields(area, collectGroups) method. The structure of the elements in the array returned by this method is similar to the one presented above. If the collectGroups parameter is set to true, this method will return groups as separate items without going into their structure.

To define an entire fields list at runtime, use the fields(fields) API method. The properties available for each field are described in the fields configuration reference section.

Use the field(id) method to get properties of a specific field. The id parameter accepts one of the following values:

• the index of a field in the fields array;
• the dataField value of a field;
• the caption of a field.

To set one or more field parameters, use the field(id, options) method. The id parameter format is similar to the one described above and the options parameter is an option/value pairs object. The properties available are described in the fields configuration reference section.