fields[]

An array of pivot grid fields.

Type: Array
Default Value: undefined

If the store type you use is not XmlaStore, you need to describe data from the data source for dxPivotGrid by assigning a list of fields to this option. Each pivot grid field must be associated with the field in the store using the dataField option.

Fields can be instantly placed into the pivot grid by assigning a value to the area option. If this option is undefined, the field will be displayed in the Field Chooser only.

NOTE
If this array is not specified and the retrieveFields option is set to true, the fields will be generated automatically and placed to the Field Chooser.

allowExpandAll

Allows an end-user to expand/collapse all header items within a header level.

Type: Boolean
Default Value: false

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

allowFiltering

Allows an end-user to change filtering options.

Type: Boolean
Default Value: false

With this option enabled, an end-user can filter pivot grid data using the filter icons in the Field Chooser.

allowSorting

Allows an end-user to change sorting options.

Type: Boolean
Default Value: false

With this option enabled, an end-user can sort pivot grid data by clicking the arrow icons in the Field Chooser.

allowSortingBySummary

Allows an end-user to sort columns by summary values.

Type: Boolean
Default Value: false

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

area

Type of the area where the field is located.

Type: String
Default Value: undefined
Accepted Values: undefined | 'row' | 'column' | 'data'

If this option is undefined, the field is displayed in the Field Chooser only. An end-user can place this field to any area. Otherwise, the field is displayed in the corresponding area of the pivot grid and its area can be changed by an end-user using the Field Chooser.

NOTE
You can allow users to place the field to the Data Fields area only by setting the isMeasure option to true. To deny using the integrated Field Chooser, set the dxPivotGrid | fieldChooser | enabled option to false.

areaIndex

Index among the other fields displayed within the same area.

Type: Number
Default Value: undefined

You can change the field order within an area by using this option. By default, the field order in the pivot grid is similar to the fields order in the fields array.

NOTE
The order of fields affects the hierarchy structure in the header of the pivot grid. This option should not be confused with the sorting concept.

calculateCustomSummary

Allows you to use a custom aggregate function to calculate the summary values. Cannot be used for the XmlaStore store type.

Type: function(options)
Function parameters:
options: Object
Summary information.
Object structure:
summaryProcess: String
Indicates the stage of the summary value calculation; possible values are 'start', 'calculate' or 'finalize'.
value: any
The data value for aggregation.
totalValue: any
The resulting summary value.

If predefined aggregate functions do not meet your requirements, implement a custom one and assign it to the calculateCustomSummary option. If the summaryType property is set to 'custom', this function will be called for each value from the data field.

The calculation of a summary value is conducted in several phases. Usually, you need to initialize totalValue on start. Then, you modify totalValue in the calculation phase. In the final phase, you adjust totalValue. To identify the current phase, use the summaryProcess field of the object passed as a parameter.

The following code demonstrates a general structure of the calculateCustomSummary function.

JavaScript
$("#pivotGridContainer").dxPivotGrid({
    dataSource: {
        //...
        fields: [{
            //...
            summaryType: 'custom',
            calculateCustomSummary: function (options) {
                if (options.summaryProcess == 'start') {
                    // Initializing "totalValue" here
                }
                if (options.summaryProcess == 'calculate') {
                    // Modifying "totalValue" here
                }
                if (options.summaryProcess == 'finalize') {
                    // Assigning the final value to "totalValue" here
                }
            }
        },
        //...
        ]
    }
});

caption

A caption that will be displayed in the pivot grid's field chooser to identify the field.

Type: String
Default Value: undefined

customizeText

Specifies a callback function that returns the text to be displayed in the cells of a field.

Type: function(cellInfo)
Function parameters:
cellInfo: Object
The value of a cell.
Object structure:
A cell value as it is specified in the data source.
valueText: String
A cell value with applied format and precision.
Return Value: String
The text to be displayed in a cell.

When implementing a callback function for this option, you can access the value of a cell using the object passed as the function's parameter. Alternatively, you can use the this object within the function. This object has the same structure as the object passed as the parameter.

dataField

Name of the data source field containing data for the pivot grid field.

Type: String
Default Value: undefined

dataType

Specifies a type of field values.

Type: String
Default Value: undefined
Accepted Values: 'string' | 'number' | 'date'

displayFolder

The name of the folder in which the field is located.

Type: String
Default Value: undefined

You can use folders to organize fields in the "All Fields" section of the Field Chooser.

expanded

Indicates whether all header items of the field's header level are expanded.

Type: Boolean
Default Value: false

filterType

The filter type for the current field.

Type: String
Default Value: 'include'
Accepted Values: 'include' | 'exclude'

You can exclude values from data of the data source or include values to keep only them.

filterValues

The filter values for the current field.

Type: Array
Default Value: undefined

You can exclude these values from the data returned by the data source or keep them only by including these values.

format

Specifies a display format for field values.

Type: String
Default Value: ''
Accepted Values: 'currency' | 'fixedPoint' | 'percent' | 'decimal' | 'exponential' | 'largeNumber' | 'thousands' | 'millions' | 'billions' | 'trillions' | 'longDate' | 'longTime' | 'monthAndDay' | 'monthAndYear' | 'quarterAndYear' | 'shortDate' | 'shortTime' | 'millisecond' | 'day' | 'month' | 'quarter' | 'year'

groupIndex

The index of the field within a group.

Type: Number
Default Value: undefined

groupInterval

Specifies how the values of the current field are combined into groups. Cannot be used for the XmlaStore store type.

Type: String|Number
Default Value: undefined
Accepted Values: 'year' | 'quarter' | 'month' | 'month' | 'day' | 'dayOfWeek'

groupName

The name of the group to which the field belongs.

Type: String
Default Value: undefined

isMeasure

Specifies whether the field should be treated as a Data Field.

Type: Boolean
Default Value: undefined

You can allow users to place the field to the Data Fields area only by setting this option to true. If this option is set to false, the field is treated as a dimension attribute and could be placed into the Row Fields, Column Fields or Filter Fields only.

precision

Specifies a precision for formatted field values.

Type: Number
Default Value: undefined

Assign an integer value to this option for specifying the number of digits displayed in the pivot grid. This option applies only when the values in this column are of a numeric format.

selector

Specifies the function that determines how to split data from the data source into ranges for header items. Cannot be used for the XmlaStore store type.

Type: function(data)
Default Value: undefined

When implementing a callback function for this option, you can access the value of a field using the object passed as the function's parameter. This function must return the range name according to the data value passed into it.

The following code implements a selector function that groups the birth dates by decades.

JavaScript
function(data){
    return Math.floor(data.birthdate.getFullYear() / 10) * 10 
}

sortBy

Specifies how field data should be sorted. Can be used for the XmlaStore store type only.

Type: String
Default Value: undefined
Accepted Values: 'caption' | 'value'

In XMLA, each data object of a dimension attribute has a caption and a value. For example, month attribute captions are 'January', 'February', ..., 'December'; and values are 1, 2, ..., 12. Sorting by value will make the first month be 'January', whereas, sorting by caption will perform alphabet sorting and make the first month be 'April'.

sortBySummaryField

Specifies the data field against which the header items of this field should be sorted.

Type: String
Default Value: undefined

If the sortBySummaryPath is undefined, the header items will be sorted against the grand total of the data field specified here.

sortBySummaryPath

The array of field names that specify a path to column/row whose summary field is used for sorting of this field's header items.

Type: Array
Default Value: undefined

sortingMethod

Specifies how to sort the header items.

Type: function(a, b)
Function parameters:
A header item.
Object structure:
value: String|Number
A header item value.
children: Array
An array of header item's children.
A header item.
Object structure:
value: String|Number
A header item value.
children: Array
An array of header item's children.
Return Value: Number
Indicates whether a comes before b.
Default Value: undefined

You can order the header items per your requirements by specifying a comparison function. This function accepts two parameters that represent two header items and should return the value on which the order will be based. The value of these header items can be accessed using the value field. You can base your sorting method on the header item's children that are available in the children field.

Assume that a and b are the parameters. If the function returns a value of less than 0, a comes before b in the resulting order. If the function returns a value greater than 0, b comes before a in the resulting order. If the function returns 0, a and b remain unchanged relative to each other.

sortOrder

Specifies the initial sort order of field values.

Type: String
Default Value: 'asc'
Accepted Values: 'asc' | 'desc'

summaryType

Specifies how to aggregate field data. Cannot be used for th XmlaStore store type.

Type: String
Default Value: 'count'
Accepted Values: 'sum' | 'min' | 'max' | 'avg' | 'count' | 'custom'

The following list gives an overview of available summary types.

  • "sum"
    Sums up all facts within an area.
  • "min"
    Calculates the minimum fact's value.
  • "max"
    Calculates the maximum fact's value.
  • "avg"
    Calculates the average of all facts within an area.
  • "count"
    Calculates the number of facts within an area.
  • "custom"
    Allows you to specify a custom aggregate function using the calculateCustomSummary option.

visible

A boolean value specifying whether or not the field is visible in the pivot grid and the Field Chooser.

Type: Boolean
Default Value: true

width

Specifies the absolute width of the field in the pivot grid.

Type: Number
Default Value: undefined