Visual Elements

This guide gives a detailed overview of dxPivotGrid visual elements. Each topic in this guide contains a brief description of an element and its main features, which can be configured.

Below is a pivot grid element map. It may be helpful as you begin learning the capabilities of the dxPivotGrid widget. Hover over an element on the map to discover the name of this element. A click on an element navigates you to a topic devoted to this element.

DevExtreme dxPivotGrid Elements

Show Field Chooser Data Field 1 Data Field 1 Data Field 1 Data Field 1 Data Field 1 Data Field 2 Data Field 2 Data Field 2 Data Field 2 Data Field 2 Column Header (Level 1) Column Header (Level 1) Column Header (Level 2) Total Column Grand Total Column Row Header (Level 1) Row Header (Level 1) Row Header (Level 1) Row Header (Level 2) Total Row Summary Value

Headers

Headers are used to annotate numeric values in the pivot grid. A column header consists of two parts — column area header (the header for Column Fields) and data area header (the header for Data Fields).

DevExtreme PivotGrid Headers

An area defines the type of field and the place where field data will be displayed: values of the 'row' and 'column' area fields are displayed in the headers, 'data' area contains fields whose numeric data will be used for calculating summary values. The 'filter' area is the pivot grid's third dimension — it does not have a visual representation, although fields of this area can be involved in a data selection process along with fields in the 'row' and 'column' areas.

Data Area

A data area contains numeric values to be analyzed. Summary values are calculated based on data area fields.

Row and Column Areas

Row and column areas contain categories that are used to form conditions for calculating summary values in the pivot grid cells.

Fields that are put to the 'row', 'column' and 'filter' (which has no visual representation) areas are called dimensional fields. This type of field provides the categorical view of data. Several dimensional fields in a single area are organized as a hierarchy within a corresponding header. Each hierarchical level is defined by a dimensional field.

Dimension is a set of fields that describes a single entity from different sides or with different precision. For example, the dimension 'Time' can consist of the fields 'Year', 'Quarter', 'Month' and 'Day'; and the dimension 'Product' can contain fields 'Category', 'Subcategory' and 'Name'. In OLAP terminology, dimensional fields are called dimension attributes.

Since total summaries are a part of the pivot grid, they are also present in row and column headers.

Headers are strictly dependent on field configuration. Fields added to 'row' and 'column' areas will appear in the corresponding header. Field configuration can be changed in code or at runtime using the Field Chooser.

There is a number of options related to the context menu of headers:

  • allowExpandAll — allows an end-user to expand/collapse all header items within a header level;
  • allowFiltering — allows an end-user to change filtering options;
  • allowSorting — allows an end-user to change sorting options;
  • allowSortingBySummary — allows an end-user to sort columns by summary values.

Summary Values

Summary values are numbers, displayed in grid cells. In OLAP terminology, fields that are put to the data area are called measures and a single record (usually representing a business transaction) is called fact. Summary (measure) value is calculated by the aggregation of field values from a number of facts taken from the data source. The set of facts to be aggregated is different for each pivot grid cell and depends on three dimensional areas (row, column and filter). The aggregation method is configured using the summaryType option.

There is a number of options related to the display format that can be configured:

  • format — specifies a display format for field values;
  • precision — specifies precision for formatted field values;
  • customizeText — allows you to customize the text to be displayed in the cells of a field.

Totals

Total and Grand Total rows and columns are used to display summary values calculated in more general conditions than the conditions defined by the pivot grid's state. Each total row/column aggregates data of several pivot grid rows/columns in accordance with the summary type.

DevExtreme PivotGrid Headers

Total Rows or Columns

Total Rows and Total Columns display sub-totals calculated for outer row/column fields. For example, if you expand the UK group, you will see the rows characterizing cities and also a UK Total row. This row is equal to the collapsed UK row.

Total rows can be enabled or disabled with the showColumnTotals option, and total columns — the showRowTotals option. You can also change the location of total rows or columns using the showTotalsPrior option.

Grand Total Row and Column

Grand Total Rows and Grand Total Columns aggregate data of the entire pivot grid. They show values summarized across all available data.

You can enable or disable the Grand Total Row with the showColumnGrandTotals option, and Grand Total Column — the showRowGrandTotals option.

Field Chooser

The Field Chooser is a complementary widget integrated in the pivot grid that allows you to manage the displayed data. To invoke the Field Chooser, right-click the row or column header, and choose the "Show Field Chooser" option. Also, the Field Chooser can be invoked by clicking the top-left empty area of the pivot grid.

DevExtreme PivotGrid FieldChooser

To configure the Field Chooser, use the fieldChooser object. It has a number of options, which can be specified:

  • enabled — enables or disables the Field Chooser;
  • layout — specifies the field chooser layout;
  • width, height — specifies the field chooser size;
  • title — specifies the text to display as a title of the Field Chooser popup window.

Although the Field Chooser is already integrated in dxPivotGrid and can be invoked using the context menu, you can add it as a separate widget on your page. In this case, the Field Chooser will be displayed continuously and will not overlay the pivot grid.

View Demo

Field Management

By default, all fields from a data source object are visible in the "All Fields" section of the Field Chooser. To hide a specific field, assign false to its visible property.

JavaScript
var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            dataField: 'ID',
            visible: false // hides this field in the Field Chooser 
        }, {
            // ...
        }]
    }
};

Using the Field Chooser, the end-user moves fields between areas with no restrictions by default. Occasionally, you may need to specify the area to which a field must belong. For example, the field that provides data for summary should not be placed anywhere except the data area. For such a field, set the isMeasure option to true.

JavaScript
var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            // ...
            dataField: 'Amount',
            area: 'data',
            summaryType: 'count',
            isMeasure: true // allows the end-user to place this field to the data area only
        }, {
            // ...
        }]
    }
};

And the reverse can be done if you need to restrict a field from being placed to the data area by setting its isMeasure option to false.

JavaScript
var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            // ...
            dataField: 'Region',
            area: 'row',
            isMeasure: false // disallows the end-user to place this field to the data area
        }, {
            // ...
        }]
    }
};

Foldering

The Field Chooser allows you to organize fields from the "All Fields" section in folders. To display a field in a folder, set the field's displayFolder option to the folder name. The following code displays "Country" and "City" fields in an "Address" folder.

JavaScript
var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            // ...
            dataField: 'Country',
            displayFolder: 'Address'
        }, {
            // ...
            dataField: 'City',
            displayFolder: 'Address'
        }, {
            // ...
        }]
    }
};

DevExtreme PivotGrid FieldChooser