DevExtreme Angular - PivotGridDataSource - fields
This property accepts an array of objects where each object configures a single field. Each pivot grid field must be associated with a data field using the dataField property. Fields with the specified area property are displayed in the pivot grid; other fields' headers are displayed in the field chooser.
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ dataField: "region", area: "row" }, { dataField: "date", dataType: "date", area: "column" }, { dataField: "sales", summaryType: "sum", area: "data" }] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: "region", area: "row" }, { dataField: "date", dataType: "date", area: "column" }, { dataField: "sales", summaryType: "sum", area: "data" }] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: 'region', area: 'row' }, { dataField: 'date', dataType: 'date', area: 'column' }, { dataField: 'sales', summaryType: 'sum', area: 'data' }] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: 'region', area: 'row' }, { dataField: 'date', dataType: 'date', area: 'column' }, { dataField: 'sales', summaryType: 'sum', area: 'data' }] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
If the retrieveFields property is true, fields configured in the fields array are accompanied by auto-generated fields that do not belong to any area. However, a user can move them to any area using the field chooser.
See Also
allowCrossGroupCalculation
Specifies whether to take neighboring groups' summary values into account when calculating a running total and absolute or percent variation.
For example, a pivot grid row contains the following summary values split into three groups:
[ [1, 1, 1], [2, 2, 2], [3, 3, 3] ]
This row can be displayed differently depending on the allowCrossGroupCalculation property's value, provided that the running total is calculated by rows:
[ [1, 2, 3], [2, 4, 6], [3, 6, 9] ] // allowCrossGroupCalculation: false [ [1, 2, 3], [5, 7, 9], [12, 15, 18] ] // allowCrossGroupCalculation: true
allowExpandAll
Allows users to expand/collapse all header items within the same header level. Ignored if the PivotGridDataSource's paginate property is true.
See Also
allowFiltering
When this property is set to true, the field's header in the field chooser and on the field panel is supplied with a filter icon. Clicking it invokes the header filter displaying all unique field values. A user applies filtering by selecting or clearing the selection of the values.
See Also
allowSorting
When this property is set to true, clicking the field's header in the field chooser and on the field panel changes the sorting direction. An arrow icon in the header indicates the current sorting direction.
See Also
allowSortingBySummary
Allows users to sort the pivot grid by summary values instead of field values. Ignored if the PivotGridDataSource's paginate property is true.
When this property is set to true, a user can use a column header's or row header's context menu to sort the pivot grid by this column's or row's summary values.
See Also
area
The field does not belong to any area if this property is undefined. However, the field's header is displayed in the field chooser, which enables the user to move this field to any area. In other cases, the field is displayed in the specified area.
See Also
areaIndex
Specifies the field's order among the other fields in the same area. Corresponds to the field's order in the fields array by default.
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ dataField: "city", area: "row", areaIndex: 1 }, { dataField: "country", area: "row", areaIndex: 0 // Countries nest cities }, // ... ] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: "city", area: "row", areaIndex: 1 }, { dataField: "country", area: "row", areaIndex: 0 // Countries nest cities }, // ... ] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: 'city', area: 'row', areaIndex: 1 }, { dataField: 'country', area: 'row', areaIndex: 0 // Countries nest cities }, // ... ] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: 'city', area: 'row', areaIndex: 1 }, { dataField: 'country', area: 'row', areaIndex: 0 // Countries nest cities }, // ... ] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
calculateCustomSummary
Specifies a custom aggregate function. Applies only if the summaryType is "custom" and the remoteOperations is false. Cannot be used with an XmlaStore.
A summary value calculation is conducted in three stages: start - in which the totalValue is initialized, calculate - in which it is modified, and finalize - in which the totalValue is adjusted. To identify the current stage, check the value of the summaryProcess field that belongs to the function's parameter.
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ // ... summaryType: "custom", calculateCustomSummary: function(options) { switch(options.summaryProcess) { case "start": // Initializing "totalValue" here break; case "calculate": // Modifying "totalValue" here break; case "finalize": // Assigning the final value to "totalValue" here break; } } }] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... summaryType: "custom", calculateCustomSummary: (options) => { switch(options.summaryProcess) { case "start": // Initializing "totalValue" here break; case "calculate": // Modifying "totalValue" here break; case "finalize": // Assigning the final value to "totalValue" here break; } } }] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... summaryType: 'custom', calculateCustomSummary: (options) => { switch(options.summaryProcess) { case 'start': // Initializing "totalValue" here break; case 'calculate': // Modifying "totalValue" here break; case 'finalize': // Assigning the final value to "totalValue" here break; } } }] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... summaryType: 'custom', calculateCustomSummary: (options) => { switch(options.summaryProcess) { case 'start': // Initializing "totalValue" here break; case 'calculate': // Modifying "totalValue" here break; case 'finalize': // Assigning the final value to "totalValue" here break; } } }] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
ASP.NET MVC Controls
@(Html.DevExtreme().PivotGrid() .DataSource(ds => ds // ... .Fields(fields => { fields.Add() // ... .SummaryType(SummaryType.Custom) .CalculateCustomSummary("calculateCustomSummary"); }) ) ) <script type="text/javascript"> function calculateCustomSummary(options) { switch(options.summaryProcess) { case "start": // Initializing "totalValue" here break; case "calculate": // Modifying "totalValue" here break; case "finalize": // Assigning the final value to "totalValue" here break; } } </script>
this
keyword refers to the field's configuration.calculateSummaryValue
This and other post-processing functions allow you to perform additional calculations on each summary value and take into account neighboring cells' summary values.
On the following image, pivot grid cells display indexes that show the order in which the calculateSummaryValue function is called. This order stays the same even if the layout is changed, for example, if rows or columns are sorted differently or the total columns are relocated.
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ // ... calculateSummaryValue: function (summaryCell) { // Your code goes here } }] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... calculateSummaryValue: function (summaryCell) { // Your code goes here } }] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... calculateSummaryValue: function (summaryCell) { // Your code goes here } }] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... calculateSummaryValue: function (summaryCell) { // Your code goes here } }] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
ASP.NET MVC Controls
@(Html.DevExtreme().PivotGrid() .DataSource(ds => ds // ... .Fields(fields => { fields.Add() // ... .CalculateSummaryValue("calculateSummaryValue"); }) ) ) <script type="text/javascript"> function calculateSummaryValue (summaryCell) { // Your code goes here } </script>
this
keyword refers to the field's configuration.caption
Specifies the field's caption to be displayed in the field chooser and on the field panel.
customizeText
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ // ... customizeText: function (cellInfo) { // Your code goes here } }] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... customizeText: function (cellInfo) { // Your code goes here } }] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... customizeText: function (cellInfo) { // Your code goes here } }] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... customizeText: function (cellInfo) { // Your code goes here } }] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
ASP.NET MVC Controls
@(Html.DevExtreme().PivotGrid() .DataSource(ds => ds // ... .Fields(fields => { fields.Add() // ... .CustomizeText("customizeText"); }) ) ) <script type="text/javascript"> function customizeText (cellInfo) { // Your code goes here } </script>
this
keyword refers to the field's configuration.dataType
If your data source stores, for example, numbers or dates as strings, specify the proper type using this property. Date-time values stored as strings should have one of the following formats: "yyyy/MM/dd"
, "yyyy/MM/dd HH:mm:ss"
, "yyyy-MM-ddTHH:mm:ssx"
, or "yyyy-MM-ddTHH:mm:ss"
.
filterType
Specifies whether a user changes the current filter by including (selecting) or excluding (clearing the selection of) values.
This property accepts the following values:
include
Values in the header filter are unselected initially. Users change the filter by selecting the values.exclude
All values in the header filter are selected initially. Users changes the filter by clearing the selection of certain values.
See Also
- fields[].filterValues
- fields[].allowFiltering
filterValues
Array<any>
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ // ... filterType: "exclude", filterValues: [[2015], [2016]] }] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... filterType: "exclude", filterValues: [[2015], [2016]] }] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... filterType: 'exclude', filterValues: [[2015], [2016]] }] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... filterType: 'exclude', filterValues: [[2015], [2016]] }] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
See Also
- fields[].filterType
- fields[].allowFiltering
format
See the format section for information on accepted values.
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ // ... format: { type: "largeNumber", precision: 2 }, // ===== or a shorter version ===== format: "largeNumber" }] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... format: { type: "largeNumber", precision: 2 }, // ===== or a shorter version ===== format: "largeNumber" }] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... format: { type: 'largeNumber', precision: 2 }, // ===== or a shorter version ===== format: 'largeNumber' }] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... format: { type: 'largeNumber', precision: 2 }, // ===== or a shorter version ===== format: 'largeNumber' }] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
ASP.NET MVC Controls
@(Html.DevExtreme().PivotGrid() .DataSource(ds => ds // ... .Fields(fields => { fields.Add() // ... .Format(f => f .Type(Format.LargeNumber) .Precision(2) ) // ===== or a shorter version ===== .Format(Format.LargeNumber) }) ) )
Date formats require additional configuration. The PivotGridDataSource groups date field values by date component: Year-Quarter-Month. For this, the values are converted from dates to numbers, but you cannot use date formats to format numbers. To apply a date format in this case, override this grouping.
The following code declares a custom group for the ShippingDate
data field. This configuration overrides this field's default grouping. Field values are grouped by year and then by month. Months are formatted as MMM
(Jan
, Feb
, Mar
, etc.). This format is applied to the Date values the selector function returns.
jQuery
$(function() { const pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ dataField: "ShippingDate", dataType: "date", area: "column", groupName: "dateGroup" }, { groupName: "dateGroup", groupIndex: 0, groupInterval: "year" }, { groupName: "dateGroup", groupIndex: 1, format: "MMM", selector: function(data) { const year = new Date(data.date).getFullYear(); const month = new Date(data.date).getMonth(); return new Date(year, month); } }, // ... ] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import { Component } from '@angular/core'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; @Component({ selector: 'app-root', templateUrl: './app.component.html', styleUrls: ['./app.component.css'] }) export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: "ShippingDate", dataType: "date", area: "column", groupName: "dateGroup" }, { groupName: "dateGroup", groupIndex: 0, groupInterval: "year" }, { groupName: "dateGroup", groupIndex: 1, format: "MMM", selector: function(data) { const year = new Date(data.date).getFullYear(); const month = new Date(data.date).getMonth(); return new Date(year, month); } }, // ... ] }); } }
<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
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: "ShippingDate", dataType: "date", area: "column", groupName: "dateGroup" }, { groupName: "dateGroup", groupIndex: 0, groupInterval: "year" }, { groupName: "dateGroup", groupIndex: 1, format: "MMM", selector: function(data) { const year = new Date(data.date).getFullYear(); const month = new Date(data.date).getMonth(); return new Date(year, month); } }, // ... ] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
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'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: "ShippingDate", dataType: "date", area: "column", groupName: "dateGroup" }, { groupName: "dateGroup", groupIndex: 0, groupInterval: "year" }, { groupName: "dateGroup", groupIndex: 1, format: "MMM", selector: function(data) { const year = new Date(data.date).getFullYear(); const month = new Date(data.date).getMonth(); return new Date(year, month); } }, // ... ] }); export default function App() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); }
When a format is exported to Excel, it is converted to an Excel format as follows:
Numeric
Converted to Number format, except for "largeNumber", which is exported without formatting.Currency
Converted to the Currency format.Date-Time
Converted to the Date or Time format, depending on the cell value.
Exceptions: "quarter" and "quarterAndYear" are exported as Short Date; "minute" and "millisecond" are exported as Long Time.Function
Cannot be converted automatically. Assign this function to the customizeText property instead of the format property.Custom format string
Cannot be converted, the cell value is exported without formatting. To export this format, return the formatted string from the customizeText callback as follows:jQuery
index.js$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ // ... customizeText: (e) => { return e.valueText; }, dataType: "number", format: "0.##" }, { // ... }] }); $("#pivotGrid").dxPivotGrid({ // ... export: { enabled: true }, dataSource: pivotGridDataSource }); });
Angular
app.component.htmlapp.component.tsapp.module.ts<dx-pivot-grid ... [dataSource]="pivotGridDataSource"> <dxo-export [enabled]="true"></dxo-export> </dx-pivot-grid>
import { Component } from '@angular/core'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; @Component({ selector: 'app-root', templateUrl: './app.component.html', styleUrls: ['./app.component.css'] }) export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ fields: [{ // ... customizeText: (e) => { return e.valueText; }, dataType: "number", format: "0.##" }, { // ... }] }); } }
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"> <DxExport :enabled="true" /> </DxPivotGrid> </template> <script> import 'devextreme/dist/css/dx.common.css'; import 'devextreme/dist/css/dx.light.css'; import DxPivotGrid, { DxExport } from 'devextreme-vue/pivot-grid'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... customizeText: (e) => { return e.valueText; }, dataType: "number", format: "0.##" }, { // ... }] }); export default { components: { DxPivotGrid, DxExport }, data() { return { pivotGridDataSource } } } </script>
React
App.jsimport React from 'react'; import 'devextreme/dist/css/dx.common.css'; import 'devextreme/dist/css/dx.light.css'; import PivotGrid, { Export } from 'devextreme-react/pivot-grid'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; const pivotGridDataSource = new PivotGridDataSource({ fields: [{ // ... customizeText: (e) => { return e.valueText; }, dataType: "number", format: "0.##" }, { // ... }] }); class App extends React.Component { render() { return ( <PivotGrid ... dataSource={pivotGridDataSource}> <Export enabled={true} /> </PivotGrid> ); } } export default App;
See Also
- fields[].customizeText
groupInterval
Specifies how the field's values are combined into groups for the headers. Cannot be used with an XmlaStore.
If the field's values are dates, specify this property with one of the accepted string values.
If the field's values are numbers, specify this property with a number that designates a step with which groups should be generated.
See Also
- Data Grouping
- fields[].selector
headerFilter
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ // ... allowFiltering: true, headerFilter: { allowSearch: true } }] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... allowFiltering: true, headerFilter: { allowSearch: true } }] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
See Also
- fields[].allowFiltering
- PivotGrid.headerFilter
isMeasure
Specifies whether the field should be treated as a measure (a field providing data for calculation).
There are no restrictions for rearranging fields in the areas using the field chooser. However, we recommend restricting measures to the data area by setting their isMeasure property to true. You can also set this property to false for a non-measure field to prohibit placing it in the data area.
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ dataField: "sales", area: "data", isMeasure: true }, { dataField: "date", dataType: "date", area: "column", isMeasure: false }, // ... ] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: "sales", area: "data", isMeasure: true }, { dataField: "date", dataType: "date", area: "column", isMeasure: false }, // ... ] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: 'sales', area: 'data', isMeasure: true }, { dataField: 'date', dataType: 'date', area: 'column', isMeasure: false }, // ... ] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ dataField: 'sales', area: 'data', isMeasure: true }, { dataField: 'date', dataType: 'date', area: 'column', isMeasure: false }, // ... ] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
See Also
name
Set this property to refer to the field in code, for example, when changing field options.
runningTotal
For example, a pivot grid row contains the following summary values:
[ 3, 3, 3, 3, 3 ]
If the runningTotal property is set to "row", each value in this row is added to the previous value:
[ 3, 6, 9, 12, 15 ]
This also applies to columns if the runningTotal property is set to "column".
See Also
- fields[].allowCrossGroupCalculation
selector
Specifies a function that combines the field's values into groups for the headers. Cannot be used with an XmlaStore or remote operations.
This function is an alternative to the groupInterval property and should return a group name that corresponds to a data object. The function's parameter represents this data object. For example, the selector function in the following code groups birth dates by decades:
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ caption: "Birth Decade", area: "column", selector: function (data) { var birthDate = new Date(data.birthDate); return Math.floor(birthDate.getFullYear() / 10) * 10; } }] }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: "Birth Decade", area: "column", selector: function (data) { let birthDate = new Date(data.birthDate); return Math.floor(birthDate.getFullYear() / 10) * 10; } }] }); } }
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: 'Birth Decade', area: 'column', selector: function (data) { let birthDate = new Date(data.birthDate); return Math.floor(birthDate.getFullYear() / 10) * 10; } }] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: 'Birth Decade', area: 'column', selector: function (data) { let birthDate = new Date(data.birthDate); return Math.floor(birthDate.getFullYear() / 10) * 10; } }] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
ASP.NET MVC Controls
@(Html.DevExtreme().PivotGrid() .DataSource(ds => ds // ... .Fields(fields => { fields.Add() .Caption("Birth Decade") .Area(PivotGridArea.Column) .Selector("groupByDecade") }) ) ) <script type="text/javascript"> function groupByDecade (data) { var birthDate = new Date(data.birthDate); return Math.floor(birthDate.getFullYear() / 10) * 10; } </script>
Another example: a selector that places values below and over 1000 into different groups:
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ caption: "Amount", area: "column", selector: function (data) { return data.amount > 1000 ? "> 1000" : "< 1000"; } }] }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: "Amount", area: "column", selector: function (data) { return data.amount > 1000 ? "> 1000" : "< 1000"; } }] }); } }
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: 'Amount', area: 'column', selector: function (data) { return data.amount > 1000 ? '> 1000' : '< 1000'; } }] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: 'Amount', area: 'column', selector: function (data) { return data.amount > 1000 ? '> 1000' : '< 1000'; } }] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
ASP.NET MVC Controls
@(Html.DevExtreme().PivotGrid() .DataSource(ds => ds // ... .Fields(fields => { fields.Add() .Caption("Amount") .Area(PivotGridArea.Column) .Selector("classifyValues") }) ) ) <script type="text/javascript"> function classifyValues (data) { return data.amount > 1000 ? "> 1000" : "< 1000"; } </script>
this
keyword refers to the field's configuration.See Also
showGrandTotals
Specifies whether to display the field's grand totals. Applies only if the field is in the data area.
showTotals
Specifies whether to display the field's totals.
See Also
- PivotGrid.showColumnTotals
- PivotGrid.showRowTotals
showValues
Specifies whether to display the field's summary values. Applies only if the field is in the data area. Inherits the showTotals' value by default.
sortBy
Specifies how the field's values in the headers should be sorted.
Dimension attributes have a value and a display text. For example, a "Month" attribute's display text can be "January", "February", ..., "December"; its values can be 1, 2, ..., 12. "January" is first when sorting by values, and "April" is first when sorting by display text.
No sorting is applied when sortBy is set to "none".
See Also
- fields[].sortOrder
sortBySummaryField
Sorts the field's values in the headers by the specified measure's summary values. Accepts the measure's name, caption, dataField, or index in the fields array.
This and the sortBySummaryPath properties can be used together to sort the field's values by a specific column's or row's summary values. If sortBySummaryPath is unspecified, the field's values are sorted by the specified measure's grand totals, as it is with the Region
field's values in the following code:
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ caption: "Sales", dataField: "sales", area: "data", isMeasure: true, summaryType: "sum" }, { caption: "Region", dataField: "region", area: "row", sortBySummaryField: "Sales" }, // ... ] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: "Sales", dataField: "sales", area: "data", isMeasure: true, summaryType: "sum" }, { caption: "Region", dataField: "region", area: "row", sortBySummaryField: "Sales" }, // ... ] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: 'Sales', dataField: 'sales', area: 'data', isMeasure: true, summaryType: 'sum' }, { caption: 'Region', dataField: 'region', area: 'row', sortBySummaryField: 'Sales' }, // ... ] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: 'Sales', dataField: 'sales', area: 'data', isMeasure: true, summaryType: 'sum' }, { caption: 'Region', dataField: 'region', area: 'row', sortBySummaryField: 'Sales' }, // ... ] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
See Also
- Sorting by Summary Values
- fields[].allowSortingBySummary
- fields[].sortOrder
sortBySummaryPath
Specifies a path to the column or row whose summary values should be used to sort the field's values in the headers.
This and the sortBySummaryField properties are applied together. If this property is not specified, the field's values are sorted by the grand totals of the measure that sortBySummaryField specifies.
In the following example, geographic regions displayed in the row header are sorted by sales in the second quarter of 2015:
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ caption: "Sales", dataField: "sales", area: "data", isMeasure: true, summaryType: "sum" }, { caption: "Date", dataField: "date", dataType: "date", area: "column" }, { caption: "Region", dataField: "region", area: "row", sortBySummaryField: "Sales", sortBySummaryPath: [2015, 2] }] }); $("#pivotGridContainer").dxPivotGrid({ dataSource: pivotGridDataSource }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; import { DxPivotGridModule } from "devextreme-angular"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: "Sales", dataField: "sales", area: "data", isMeasure: true, summaryType: "sum" }, { caption: "Date", dataField: "date", dataType: "date", area: "column" }, { caption: "Region", dataField: "region", area: "row", sortBySummaryField: "Sales", sortBySummaryPath: [2015, 2] }] }); } } @NgModule({ imports: [ // ... DxPivotGridModule ], // ... })
<dx-pivot-grid [dataSource]="pivotGridDataSource"> </dx-pivot-grid>
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: 'Sales', dataField: 'sales', area: 'data', isMeasure: true, summaryType: 'sum' }, { caption: 'Date', dataField: 'date', dataType: 'date', area: 'column' }, { caption: 'Region', dataField: 'region', area: 'row', sortBySummaryField: 'Sales', sortBySummaryPath: [2015, 2] }] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ caption: 'Sales', dataField: 'sales', area: 'data', isMeasure: true, summaryType: 'sum' }, { caption: 'Date', dataField: 'date', dataType: 'date', area: 'column' }, { caption: 'Region', dataField: 'region', area: 'row', sortBySummaryField: 'Sales', sortBySummaryPath: [2015, 2] }] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
See Also
sortingMethod
Specifies a custom comparison function that sorts the field's values in the headers.
Name | Type | Description |
---|---|---|
children | Array<any> |
The field value's children; available only when the corresponding header item is expanded. |
value | | |
The field value. |
This function accepts two field values and should return a number indicating their sort order:
- Less than zero
a goes before b. - Zero
a and b remain unchanged relative to each other. - Greater than zero
a goes after b.
Inside this function, you can access the field's configuration using the this keyword:
jQuery
$(function() { var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({ // ... fields: [{ // ... sortingMethod: function (a, b) { var fieldSortOrder = this.sortOrder; // ... } }, // ... ] }); });
Angular
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source"; // ... export class AppComponent { pivotGridDataSource: PivotGridDataSource; constructor() { this.pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... sortingMethod: function (a, b) { let field = this as any; let fieldSortOrder = field.sortOrder; // ... } }, // ... ] }); } }
Vue
<template> <DxPivotGrid :data-source="pivotGridDataSource" /> </template> <script> import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-vue/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... sortingMethod: function (a, b) { let field = this as any; let fieldSortOrder = field.sortOrder; // ... } }, // ... ] }); export default { components: { DxPivotGrid }, data() { return { pivotGridDataSource } } } </script>
React
import React from 'react'; import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source'; import DxPivotGrid from 'devextreme-react/pivot-grid'; const pivotGridDataSource = new PivotGridDataSource({ // ... fields: [{ // ... sortingMethod: function (a, b) { let field = this as any; let fieldSortOrder = field.sortOrder; // ... } }, // ... ] }); class App extends React.Component { render() { return ( <PivotGrid dataSource={pivotGridDataSource} /> ); } } export default App;
ASP.NET MVC Controls
@(Html.DevExtreme().PivotGrid() .DataSource(ds => ds // ... .Fields(fields => { fields.Add() // ... .SortingMethod("sortingMethod"); }) ) ) <script type="text/javascript"> function sortingMethod (a, b) { var fieldSortOrder = this.sortOrder; // ... } </script>
this
keyword refers to the field's configuration.See Also
- fields[].sortOrder
summaryDisplayMode
Specifies a predefined post-processing function. Does not apply when the calculateSummaryValue property is set.
Post-processing functions allow you to perform additional calculations on each summary value and take into account neighboring cells' summary values.
The following list describes the available post-processing functions. These functions apply to one summary value at a time. This value is referred to as the "current value" in the descriptions.
"absoluteVariation"
Calculates the absolute difference between the current and previous value in a row. Starts from the second value in the row because the first does not have a previous value."percentVariation"
Same as the absolute variation, but the difference is calculated as a percentage."percentOfColumnTotal"
Calculates the current value as a percentage in one of the column's intermediate totals or the column's grand total when there are no expanded rows."percentOfRowTotal"
Calculates the current value as a percentage in one of the row's intermediate totals or the row's grand total when there are no expanded columns."percentOfColumnGrandTotal"
Calculates the current value as a percentage in the column's grand total."percentOfRowGrandTotal"
Calculates the current value as a percentage in the row's grand total."percentOfGrandTotal"
Calculates the current value as a percentage in the grand total of the entire pivot grid.
You can implement the capability to select the post-processing function at runtime using instructions from the Runtime Summary Type Selection article.
To specify a custom post-processing function, use the calculateSummaryValue property.
summaryType
Specifies how to aggregate the field's data. Cannot be used with an XmlaStore.
The summary type defines how to calculate the summary values that are displayed in the pivot grid's cells. The calculation is performed on a subset of facts that correspond to each field value in three areas (column, row, and filter).
The following summary types are supported:
"sum"
"min"
"max"
"avg"
"count"
"custom"
Applies a custom client-side aggregate function (calculateCustomSummary). Refer to Client-Side Data Aggregation for more information.Any other type
Applies a custom server-side aggregate function. Refer to Server-Side Data Aggregation for more information.
You can implement the capability to select the summary type at runtime using instructions from the Runtime Summary Type Selection article.
visible
Specifies whether the field is visible in the pivot grid and field chooser.
wordWrapEnabled
Specifies whether text that does not fit into a header item should be wrapped.
If you have technical questions, please create a support ticket in the DevExpress Support Center.