Fields and Areas

The structure of data displayed by the pivot grid is defined in the fields array. The pivot grid widget 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

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 option 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.

Areas

The area is a place in the pivot grid widget 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 widget you can organize fields related to a single dimension into groups. To learn more about this functionality, refer to the Fields Grouping article.

NOTE: Fields with no area defined will not be displayed in the pivot grid, although, they will be available in the Field Chooser, which can be used to put a field into an area.

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.

JavaScript
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.

JavaScript
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 option (if it is set). Note that the fields whose areaIndex option is set to a value (any value) will be displayed before fields with no areaIndex defined.

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

API

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 widget to acquire the pivot grid's current data source. Tasks that can be solved using the API are presented below.

Get or Set Fields List

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.

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

Virtually all options 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 options available for each field are described in the fields configuration reference section.

NOTE: After making changes in fields array, apply them by calling the load() method of the PivotGridDataSource.

Show Example:
AngularJS
Knockout
jQuery

This example allows you to populate different areas and see how the widget changes depending on the field set. Check corresponding checkboxes at the top of the pivot grid to create a different set of fields and then load this set into the widget.


                                    

                                    

                                    

This example allows you to populate different areas and see how the widget changes depending on the field set. Check corresponding checkboxes at the top of the pivot grid to create a different set of fields and then load this set into the widget.


                                    

                                    

                                    

This example allows you to populate different areas and see how the widget changes depending on the field set. Check corresponding checkboxes at the top of the pivot grid to create a different set of fields and then load this set into the widget.


                                    

                                    

                                    

Get or Set Field Options

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

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 options available are described in the fields configuration reference section.

NOTE: After making changes in the fields array, you should apply them by calling the load() method of the PivotGridDataSource.

Show Example:
AngularJS
Knockout
jQuery

This example allows you to see the difference between two locations of the "Year" field within the column area. Click the "Relocate the Year field" button to change the area index of the "Year" field and move it from the first place in the area to the last and vice versa.


                                    

                                    

This example allows you to see the difference between two locations of the "Year" field within the column area. Click the "Relocate the Year field" button to change the area index of the "Year" field and move it from the first place in the area to the last and vice versa.


                                    

                                    

This example allows you to see the difference between two locations of the "Year" field within the column area. Click the "Relocate the Year field" button to change the area index of the "Year" field and move it from the first place in the area to the last and vice versa.