JavaScript/jQuery PivotGrid Options

See Also

• Configure a Widget: Angular | Vue | React | jQuery | AngularJS | Knockout | ASP.NET MVC 5 | ASP.NET Core

With this property enabled, an end-user can right-click a header level and choose the corresponding context menu item to expand or collapse all header items within this level.

A user can click a filter icon in the field chooser or field panel when this property is set to true to invoke a popup menu.

With this property enabled, an end user can sort data by clicking the arrow icons in the field chooser or on the field panel.

With this property enabled, an end-user can use the context menu of a column or row header to apply sorting by summary values.

Data field headers appear only when more than one data field is visible. See the following image to spot the difference between the two settings of this property:

Use the PivotGridDataFieldArea enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: Row and Column.

View Demo

If you use DevExtreme ASP.NET MVC Controls, refer to the Bind Controls to Data article.

The PivotGrid is bound to data via the PivotGridDataSource, a component that allows you to sort, filter, group, and perform other data shaping operations. The PivotGridDataSource's underlying data access logic is isolated in the store. You use different store types for different data sources.

To bind the PivotGrid to data, assign a PivotGridDataSource to the UI component's dataSource property. In the PivotGridDataSource, specify the store property depending on your data source as shown in the following list. In each case, also specify the fields[] array to configure pivot grid fields.

• Data Array
  Assign the array to the store property. View Demo

• OLAP Data
  Implement an XmlaStore. View Demo

• Web API, PHP, MongoDB
  Use one of the following extensions to enable the server to process data according to the protocol DevExtreme UI components use:

  • DevExtreme.AspNet.Data
  • DevExtreme-PHP-Data
  • devextreme-query-mongodb

  Then, use the createStore method to configure access to the server on the client as shown below. This method is part of DevExtreme.AspNet.Data.

  jQuery

  JavaScript

  $(function() {
      let serviceUrl = "https://url/to/my/service";
      $("#pivotGridContainer").dxPivotGrid({
          // ...
          dataSource: new DevExpress.data.PivotGridDataSource({
              store: DevExpress.data.AspNet.createStore({
                  key: "ID",
                  loadUrl: serviceUrl + "/GetAction"
              })
          })
      })
  });

  Angular

  app.component.ts

  app.component.html

  app.module.ts

  import { Component } from '@angular/core';
  import CustomStore from 'devextreme/data/custom_store';
  import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
  import { createStore } from 'devextreme-aspnet-data-nojquery';
  
  @Component({
      selector: 'app-root',
      templateUrl: './app.component.html',
      styleUrls: ['./app.component.css']
  })
  export class AppComponent {
      store: CustomStore;
      constructor() {
          let serviceUrl = "https://url/to/my/service";
          this.pivotGridDataSource = new PivotGridDataSource({
              store: createStore({
                  key: "ID",
                  loadUrl: serviceUrl + "/GetAction"
              })
          })
      }
  }

  <dx-pivot-grid ...
      [dataSource]="pivotGridDataSource">
  </dx-pivot-grid>

  import { BrowserModule } from '@angular/platform-browser';
  import { NgModule } from '@angular/core';
  import { AppComponent } from './app.component';
  
  import { DxPivotGridModule } from 'devextreme-angular';
  
  @NgModule({
      declarations: [
          AppComponent
      ],
      imports: [
          BrowserModule,
          DxPivotGridModule
      ],
      providers: [],
      bootstrap: [AppComponent]
  })
  export class AppModule { }

  Vue

  App.vue

  <template> 
      <DxPivotGrid ...
          :data-source="pivotGridDataSource" />
  </template>
  
  <script>
  import 'devextreme/dist/css/dx.common.css';
  import 'devextreme/dist/css/dx.light.css';
  
  import CustomStore from 'devextreme/data/custom_store';
  import { createStore } from 'devextreme-aspnet-data-nojquery';
  import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
  import { DxPivotGrid } from 'devextreme-vue/pivot-grid';
  
  export default {
      components: {
          DxPivotGrid
      },
      data() {
          const serviceUrl = "https://url/to/my/service";
          const pivotGridDataSource = new PivotGridDataSource({
              store: createStore({
                  key: "ID",
                  loadUrl: serviceUrl + "/GetAction"
              })
          });
          return {
              pivotGridDataSource
          }
      }
  }
  </script>

  React

  App.js

  import React from 'react';
  import 'devextreme/dist/css/dx.common.css';
  import 'devextreme/dist/css/dx.light.css';
  
  import CustomStore from 'devextreme/data/custom_store';
  import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
  import { createStore } from 'devextreme-aspnet-data-nojquery';
  import PivotGrid from 'devextreme-react/pivot-grid';
  
  const serviceUrl = "https://url/to/my/service";
  const pivotGridDataSource = new PivotGridDataSource({
      store: createStore({
          key: "ID",
          loadUrl: serviceUrl + "/GetAction"
      })
  });
  
  class App extends React.Component {
      render() {
          return (
              <PivotGrid ...
                  dataSource={pivotGridDataSource} />
          );
      }
  }
  export default App;

• Any other data source
  Implement a CustomStore.

You can call the getDataSource() method to access the PivotGridDataSource instance associated with the PivotGrid.

When exporting is enabled, the new "Export to Excel file" entity is added to the context menu. You can specify exporting properties using this object.

A field chooser is a pivot grid element that allows an end-user to configure data displayed in the pivot grid. To invoke the field chooser, right-click any pivot grid header and select the Show Field Chooser item. You can also display PivotGridFieldChooser as a separate UI component.

View Demo

The field panel is a component that displays the fields involved in the calculation of grid summaries. It consists of four field areas: column, row, data and filter. Each area holds fields of the corresponding type.

By default, the field panel is hidden. To make it visible, assign true to the visible property. To control the visibility of an individual field area, change the showColumnFields, showRowFields, showDataFields or showFilterFields property respectively.

The field panel partially covers the functionality provided by the field chooser. For example, the user can reorder fields within a single field area or even between them. This capability is controlled by the value of the allowFieldDragging property.

In addition, if the allowSorting and allowFiltering properties are true, the user can apply sorting and filtering to fields directly from the field panel.

View Demo

A header filter allows a user to filter individual field's values by including or excluding them from the applied filter. Clicking a header filter icon in the field chooser or the field panel invokes a popup menu displaying all the unique field values.

Assign true to the allowFiltering property to make the icons visible. To customize a specific field's header filter, use the field's headerFilter object.

The user's filtering preferences are saved in the filterValues property. The header filter's Select All checkbox changes the filterType property.

See Also

• Filtering

This property accepts a value of one of the following types:

• Number
  The height in pixels.

• String
  A CSS-accepted measurement of height. For example, "55px", "80%", "inherit".

• Function
  A function returning either of the above. For example:

  JavaScript

  height: function() {
      return window.innerHeight / 1.5;
  }

When PivotGrid operates with a large number of records or uses a remote storage as a data source, loading data takes time. As data is being prepared, PivotGrid displays a load panel.

The load panel consists of a pane, a loading indicator and a text. You can specify whether the pane or loading indicator must be displayed using the showPane or showIndicator properties respectively. The text displayed by the load panel can be specified using the text property. Also, you can change the height or width of the load panel using the corresponding properties of the loadPanel configuration object.

Since the grid load panel is practically the DevExtreme LoadPanel UI component, you can specify any property belonging to this UI component in the loadPanel object.

This function allows you to customize cells and modify their content. Common use-cases are as follows:

• Use CSS to apply styles to a cellElement. The code below customizes font in a separate cell:

  jQuery

  index.js

  $(function() {
      $("#pivotGridContainer").dxPivotGrid({
          // ...
          onCellPrepared: function(e) {
              if(e.cell.rowPath === "rowName" && e.cell.columnPath === "columnName") {
                  e.cellElement.css("font-size", "14px");
                  e.cellElement.css("font-weight", "bold");
              }
          }
      });
  });

  Angular

  app.component.html

  app.component.ts

  app.module.ts

  <dx-pivot-grid ...
      (onCellPrepared)="onCellPrepared($event)">
  </dx-pivot-grid>

  import { Component } from '@angular/core';
  
  @Component({
      selector: 'app-root',
      templateUrl: './app.component.html',
      styleUrls: ['./app.component.css']
  })
  export class AppComponent {
      onCellPrepared(e) {          
          if(e.cell.rowPath === "rowName" && e.cell.columnPath === "columnName") {
              e.cellElement.style.fontSize = "14px";
              e.cellElement.style.fontWeight = "bold";
          }
      }

  import { BrowserModule } from '@angular/platform-browser';
  import { NgModule } from '@angular/core';
  import { AppComponent } from './app.component';
  
  import { DxPivotGridModule } from 'devextreme-angular';
  
  @NgModule({
      declarations: [
          AppComponent
      ],
      imports: [
          BrowserModule,
          DxPivotGridModule
      ],
      bootstrap: [AppComponent]
  })
  export class AppModule { }

  Vue

  App.vue

  <template>
      <DxPivotGrid ...
          @cell-prepared="onCellPrepared"
      />
  </template>
  
  <script>
  import 'devextreme/dist/css/dx.common.css';
  import 'devextreme/dist/css/dx.light.css';
  
  import DxPivotGrid from 'devextreme-vue/pivot-grid';
  
  export default {
      components: {
          DxPivotGrid
      },
      methods: {
          onCellPrepared(e) {          
              if(e.cell.rowPath === "rowName" && e.cell.columnPath === "columnName") {
                  e.cellElement.style.fontSize = "14px";
                  e.cellElement.style.fontWeight = "bold";
              }
          }
      }
  }
  </script>

  React

  App.js

  import React, { useCallback } from 'react';
  
  import 'devextreme/dist/css/dx.common.css';
  import 'devextreme/dist/css/dx.light.css';
  
  import PivotGrid from 'devextreme-react/pivot-grid';
  
  const App = () => {
      const customizeCells = useCallback((e) {          
          if(e.cell.rowPath === "rowName" && e.cell.columnPath === "columnName") {
              e.cellElement.style.fontSize = "14px";
              e.cellElement.style.fontWeight = "bold";
          }
      }, []);
  
  return (
      <PivotGrid ...
          onCellPrepared={customizeCells}
      />
  );
  
  }
  export default App;

• Add a class to a cellElement. The following code adds a custom class to cells in the Grand Total row and column. This code also adds another class to all cells in the "row" and "column" areas:

  jQuery

  index.js

  $(function() {
      $("#pivotGridContainer").dxPivotGrid({
          // ...
          onCellPrepared: function(e) {
              if(e.cell.columnType === "GT" || e.cell.rowType === "GT")
                  e.cellElement.addClass("your-custom-class");
              if(e.area === "row" || e.area === "column")
                  e.cellElement.addClass("another-custom-class");
          }
      });
  });

  Angular

  app.component.html

  app.component.ts

  app.module.ts

  <dx-pivot-grid ...
      (onCellPrepared)="onCellPrepared($event)">
  </dx-pivot-grid>

  import { Component } from '@angular/core';
  
  @Component({
      selector: 'app-root',
      templateUrl: './app.component.html',
      styleUrls: ['./app.component.css']
  })
  export class AppComponent {
      onCellPrepared(e) {          
          if(e.cell.columnType === "GT" || e.cell.rowType === "GT")
              e.cellElement.addClass("your-custom-class");
          if(e.area === "row" || e.area === "column")
              e.cellElement.addClass("another-custom-class");
      }

  import { BrowserModule } from '@angular/platform-browser';
  import { NgModule } from '@angular/core';
  import { AppComponent } from './app.component';
  
  import { DxPivotGridModule } from 'devextreme-angular';
  
  @NgModule({
      declarations: [
          AppComponent
      ],
      imports: [
          BrowserModule,
          DxPivotGridModule
      ],
      bootstrap: [AppComponent]
  })
  export class AppModule { }

  Vue

  App.vue

  <template>
      <DxPivotGrid ...
          @cell-prepared="onCellPrepared"
      />
  </template>
  
  <script>
  import 'devextreme/dist/css/dx.common.css';
  import 'devextreme/dist/css/dx.light.css';
  
  import DxPivotGrid from 'devextreme-vue/pivot-grid';
  
  export default {
      components: {
          DxPivotGrid
      },
      methods: {
          onCellPrepared(e) {          
              if(e.cell.columnType === "GT" || e.cell.rowType === "GT")
                  e.cellElement.addClass("your-custom-class");
              if(e.area === "row" || e.area === "column")
                  e.cellElement.addClass("another-custom-class");
          }
      }
  }
  </script>

  React

  App.js

  import React, { useCallback } from 'react';
  
  import 'devextreme/dist/css/dx.common.css';
  import 'devextreme/dist/css/dx.light.css';
  
  import PivotGrid from 'devextreme-react/pivot-grid';
  
  const App = () => {
      const customizeCells = useCallback((e) {          
          if(e.cell.columnType === "GT" || e.cell.rowType === "GT")
              e.cellElement.addClass("your-custom-class")
          if(e.area === "row" || e.area === "column")
              e.cellElement.addClass("another-custom-class");
      }, []);
  
  return (
      <PivotGrid ...
          onCellPrepared={customizeCells}
      />
  );
  
  }
  export default App;

View Demo

See Also

• Get a UI component Instance in Angular | AngularJS | Knockout | Vue | React

The following example shows how to subscribe to component property changes:

$(function() {
    $("#pivotGridContainer").dxPivotGrid({
        // ...
        onOptionChanged: function(e) {
            if(e.name === "changedProperty") {
                // handle the property change here
            }
        }
    });
});

<dx-pivot-grid ...
    (onOptionChanged)="handlePropertyChange($event)"> 
</dx-pivot-grid>

import { Component } from '@angular/core'; 

@Component({ 
    selector: 'app-root', 
    templateUrl: './app.component.html', 
    styleUrls: ['./app.component.css'] 
}) 

export class AppComponent { 
    // ...
    handlePropertyChange(e) {
        if(e.name === "changedProperty") { 
            // handle the property change here
        }
    }
}

import { BrowserModule } from '@angular/platform-browser'; 
import { NgModule } from '@angular/core'; 
import { AppComponent } from './app.component'; 
import { DxPivotGridModule } from 'devextreme-angular'; 

@NgModule({ 
    declarations: [ 
        AppComponent 
    ], 
    imports: [ 
        BrowserModule, 
        DxPivotGridModule 
    ], 
    providers: [ ], 
    bootstrap: [AppComponent] 
}) 

export class AppModule { }  

<template> 
    <DxPivotGrid ...
        @option-changed="handlePropertyChange"
    />            
</template> 

<script> 
import 'devextreme/dist/css/dx.common.css'; 
import 'devextreme/dist/css/dx.light.css'; 
import DxPivotGrid from 'devextreme-vue/pivot-grid'; 

export default { 
    components: { 
        DxPivotGrid
    }, 
    // ...
    methods: { 
        handlePropertyChange: function(e) {
            if(e.name === "changedProperty") {
                // handle the property change here
            }
        }
    } 
} 
</script> 

import React from 'react'; 
import 'devextreme/dist/css/dx.common.css'; 
import 'devextreme/dist/css/dx.light.css'; 

import PivotGrid from 'devextreme-react/pivot-grid'; 

const handlePropertyChange = (e) => {
    if(e.name === "changedProperty") {
        // handle the property change here
    }
}

export default function App() { 
    return ( 
        <PivotGrid ...
            onOptionChanged={handlePropertyChange}
        />        
    ); 
} 

Frequently, items in the row header have a hierarchical structure. By default, these items are arranged in a line occupying a significant amount of space. If the area assigned to PivotGrid is limited, use a more compact tree layout. The image below illustrates the difference between standard and tree layouts.

Use the PivotGridRowHeadersLayout enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: Standard and Tree.

View Demo

When this property is set to true, the UI component text flows from right to left, and the layout of elements is reversed. To switch the entire application/site to the right-to-left representation, assign true to the rtlEnabled field of the object passed to the DevExpress.config(config) method.

DevExpress.config({
    rtlEnabled: true
});

See Also

• Right-to-Left Support Demo: DataGrid | Navigation Widgets | Editors

The PivotGrid UI component enables an end-user to scroll grid records. To specify required scrolling behavior, use the mode property of the scrolling configuration object.

View Demo

Grand Total column displays the summary values of an entire row.

Total columns show the summary values calculated for all previous hierarchy levels starting with the deepest expanded one.

Grand Total row displays the summary values of an entire column.

Total rows show the summary values calculated for all previous hierarchy levels starting with the deepest expanded one.

By default, total rows and columns are shown after data (columns at the right side, rows at the bottom). You can place total rows, total columns or both before data using this property.

Use the PivotGridTotalsDisplayMode enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: Rows, Columns, Both, and None.

State storing enables the UI component to save applied settings and restore them the next time the UI component is loaded. Assign true to the stateStoring.enabled property to enable this functionality.

The state is saved with a specified storage key.

State storing saves the following properties:

To specify the time in milliseconds between automatic state saves, set the savingTimeout property. To specify the lifetime of the saved state, set the storage type.

Use the PivotGridDataSource's state method to manage the PivotGrid's state at runtime.

View Demo

The value of this property will be passed to the tabindex attribute of the HTML element that underlies the UI component.

This property accepts a value of one of the following types:

• Number
  The width in pixels.

• String
  A CSS-accepted measurement of width. For example, "55px", "80%", "auto", "inherit".

• Function
  A function returning either of the above. For example:

  JavaScript

  width: function() {
      return window.innerWidth / 1.5;
  }

See Also

• PivotGridDataSource.fields.wordWrapEnabled - applies word wrap to a specific field.