fields[]

If the store type you use is not XmlaStore, you need to describe data from the data source for PivotGrid 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.

In this example, summary values are accumulated across each row as the runningTotal option is set to "row". These values can be accumulated within each expanded group or across all groups. This characteristic is controlled by the value of the allowCrossGroupCalculation option. You can change it at runtime using the switch below the pivot grid.

In this example, summary values are accumulated across each row as the runningTotal option is set to "row". These values can be accumulated within each expanded group or across all groups. This characteristic is controlled by the value of the allowCrossGroupCalculation option. You can change it at runtime using the switch below the pivot grid.

In this example, summary values are accumulated across each row as the runningTotal option is set to "row". These values can be accumulated within each expanded group or across all groups. This characteristic is controlled by the value of the allowCrossGroupCalculation option. You can change it at runtime using the switch below the pivot grid.

allowExpandAll

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

In this example, you can change the allowExpandAll option of the "Year" column field at runtime. Turn the switch below the pivot grid to true. Then, right-click any "Year" column field. The invoked context menu will propose to expand/collapse all column fields on the same level.

In this example, you can change the allowExpandAll option of the "Year" column field at runtime. Turn the switch below the pivot grid to true. Then, right-click any "Year" column field. The invoked context menu will propose to expand/collapse all column fields on the same level.

In this example, you can change the allowExpandAll option of the "Year" column field at runtime. Turn the switch below the pivot grid to true. Then, right-click any "Year" column field. The invoked context menu will propose to expand/collapse all column fields on the same level.

In this example, you can change the allowFiltering option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the filtering icon that accompanies the "Region" field. This icon can be used to apply a filter to this field.

In this example, you can change the allowFiltering option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the filtering icon that accompanies the "Region" field. This icon can be used to apply a filter to this field.

In this example, you can change the allowFiltering option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the filtering icon that accompanies the "Region" field. This icon can be used to apply a filter to this field.

In this example, you can change the allowSorting option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the sorting icon that accompanies the "Region" field. Click this icon to sort this field in ascending/descending order.

In this example, you can change the allowSorting option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the sorting icon that accompanies the "Region" field. Click this icon to sort this field in ascending/descending order.

In this example, you can change the allowSorting option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the sorting icon that accompanies the "Region" field. Click this icon to sort this field in ascending/descending order.

In this example, you can change the allowSortingBySummary option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, right-click any column header to invoke a context menu. It will offer to sort the "Region" field by this column.

In this example, you can change the allowSortingBySummary option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, right-click any column header to invoke a context menu. It will offer to sort the "Region" field by this column.

In this example, you can change the allowSortingBySummary option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, right-click any column header to invoke a context menu. It will offer to sort the "Region" field by this column.

area

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 PivotGrid | fieldChooser | enabled option to false.

When configuring the widget using ASP.NET MVC Wrappers, specify this option using the PivotGridArea enum. This enum accepts the following values: Row, Column, Data and Filter.

In this example, the "Year" and "Quarter" fields belong to the column area. Originally, "Year" goes before "Quarter" in the hierarchy. To swap them, click the button located below the pivot grid. Internally, this action will swap the values of the areaIndex option of these fields.

In this example, the "Year" and "Quarter" fields belong to the column area. Originally, "Year" goes before "Quarter" in the hierarchy. To swap them, click the button located below the pivot grid. Internally, this action will swap the values of the areaIndex option of these fields.

In this example, the "Year" and "Quarter" fields belong to the column area. Originally, "Year" goes before "Quarter" in the hierarchy. To swap them, click the button located below the pivot grid. Internally, this action will swap the values of the areaIndex option of these fields.

calculateCustomSummary

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

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.

With this function, you can perform additional calculations on each summary value and take into account the values of the neighboring cells. The function takes the Summary Cell object as an argument and returns the new summary value. The summary value calculated by aggregation of facts (according to the summaryType) is replaced with the value returned by this function.

There are several predefined post-processing functions. You can enable one of them by using the summaryDisplayMode option.

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.

In this example, all fields are specified explicitly as the retrieveFields option is set to false. There are fields that define date constituents: "Year", "Quarter" and "Month". To be collected into one folder in the field chooser, these fields have an identical value of the displayFolder option. Click the icon in the upper-left grid corner to invoke the field chooser and note the folder.

In this example, all fields are specified explicitly as the retrieveFields option is set to false. There are fields that define date constituents: "Year", "Quarter" and "Month". To be collected into one folder in the field chooser, these fields have an identical value of the displayFolder option. Click the icon in the upper-left grid corner to invoke the field chooser and note the folder.

In this example, all fields are specified explicitly as the retrieveFields option is set to false. There are fields that define date constituents: "Year", "Quarter" and "Month". To be collected into one folder in the field chooser, these fields have an identical value of the displayFolder option. Click the icon in the upper-left grid corner to invoke the field chooser and note the folder.

expanded

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

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

format

DevExtreme widgets provide a wide choice of predefined formats. If you are, however, going to use custom formats, link the Globalize library to your project. Learn how to do this from topics in the Installation section.

runningTotal

After aggregating and post-processing stages, you can perform the last calculation on the resulting summary values — adding of each summary value to the previous one (starting from the second) by rows or columns. For example, if you have the following row values

[2, 6, -3, 0, 8]

and the runningTotal option is set to 'row', the pivot grid will display the row as follows.

[2, 8, 5, 5, 13]

When configuring the widget using ASP.NET MVC Wrappers, specify this option using the PivotGridRunningTotalMode enum. This enum accepts the following values: Row and Column.

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.

In this example, you can change the showGrandTotals option of the "Amount" field at runtime. By default, this option is true. Turn the switch below the pivot grid to false. This will cause the grand total values to be hidden.

In this example, you can change the showGrandTotals option of the "Amount" field at runtime. By default, this option is true. Turn the switch below the pivot grid to false. This will cause the grand total values to be hidden.

In this example, you can change the showGrandTotals option of the "Amount" field at runtime. By default, this option is true. Turn the switch below the pivot grid to false. This will cause the grand total values to be hidden.

In this example, you can change the showTotals option of the "Quarter" column field at runtime. By default, this option is true. Turn the switch below the pivot grid to false to hide the total values of the "Quarter" field. Please note that the "Year" field, despite being a parent field for "Quarter", does not hide its total values.

In this example, you can change the showTotals option of the "Quarter" column field at runtime. By default, this option is true. Turn the switch below the pivot grid to false to hide the total values of the "Quarter" field. Please note that the "Year" field, despite being a parent field for "Quarter", does not hide its total values.

In this example, you can change the showTotals option of the "Quarter" column field at runtime. By default, this option is true. Turn the switch below the pivot grid to false to hide the total values of the "Quarter" field. Please note that the "Year" field, despite being a parent field for "Quarter", does not hide its total values.

sortBy

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

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

When configuring the widget using ASP.NET MVC Wrappers, specify this option using the PivotGridSortBy enum. This enum accepts the following values: DisplayText and Value.

sortBySummaryField

Oftentimes, the sortBySummaryField and sortBySummaryPath options are used together to sort header items. If the value of the sortBySummaryPath option is an empty array or undefined, the header items are sorted by the grand total values of the data field specified here.

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

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.

The sortBySummaryPath option applies together with the sortBySummaryField option. When the value of the sortBySummaryPath option is an empty array or undefined, the header items are sorted by grand total values. The data field providing facts for these values is specified by the sortBySummaryField option.

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

You can order header items by specifying a comparison function. This function accepts two parameters that represent two header items and should return a value on which the order will be based. The value of these header items can be accessed using the value field. You can access and compare children belonging to a header item using the children field. Within the function, you can get the field instance using the this keyword.

This option allows you to set up additional calculations on summary values (calculated by aggregation of facts according to the summaryType). This allows you to take into account the neighboring summary values or total values. You can use one of the predefined post-processing functions (display modes) or implement your own one using the calculateSummaryValue option. The default summary display modes are listed below.

NOTE

All post-processing functions are designed to iterate through pivot grid summary values. The current value concept refers to a summary value at any iteration. In other words, the current value is any summary value for which the post-processing function can be applied.

"absoluteVariation"
Calculates the absolute difference between the current and the previous row value. The processing starts from the second column because the first value does not have the previous one.

"percentVariation"
Calculates the relative difference between the current and the previous row value assuming the previous value is 100%. If the current value is lower than the previous one, the percent variation will be negative. The processing starts from the second column because the first value does not have the previous one.

"percentOfColumnTotal"
Calculates the relative difference between the current value and the parental total assuming the latter is 100%. Note that the 100% value is displayed only for Grand Totals because they have no parental total row and the percentage cannot be calculated. All intermediate total rows will display the percentage values calculated with respect to their parental total rows, whereas for each inner summary row parental total values will be assumed to be 100% internally.

"percentOfRowTotal"
Calculates the relative difference between the current value and the parental total assuming the latter is 100%. Note that the 100% value is displayed only for Grand Totals because they have no parental total column and the percentage cannot be calculated. All intermediate total columns will display the percentage values calculated with respect to their parental total columns, whereas for each inner summary column parental total values will be assumed to be 100% internally.

"percentOfColumnGrandTotal"
Calculates the relative difference between the current value and the corresponding grand total row value assuming the latter is 100%.

"percentOfRowGrandTotal"
Calculates the relative difference between the current value and the corresponding grand total column value assuming the latter is 100%.

"percentOfGrandTotal"
Calculates the relative difference between the current value and the grand total value of the entire pivot grid assuming the latter is 100%.

You can allow the user to change the summary display mode at runtime. Learn how to implement this capability from the Runtime Summary Type Selection topic.

When configuring the widget using ASP.NET MVC Wrappers, specify this option using the PivotGridSummaryDisplayMode enum. This enum accepts the following values: AbsoluteVariation, PercentVariation, PercentOfColumnTotal, PercentOfRowTotal, PercentOfColumnGrandTotal, PercentOfRowGrandTotal and PercentOfGrandTotal.

In this example, the pivot grid contains two data fields with an identical dataField value. The "Sales Total" field displays the data field values and the "Var" field displays the result of post-processing those values. Right-click the "Var" field on the field panel and select an item from the appeared context menu to switch the post-processing algorithm (summary display mode).

In this example, the pivot grid contains two data fields with an identical dataField value. The "Sales Total" field displays the data field values and the "Var" field displays the result of post-processing those values. Right-click the "Var" field on the field panel and select an item from the appeared context menu to switch the post-processing algorithm (summary display mode).

In this example, the pivot grid contains two data fields with an identical dataField value. The "Sales Total" field displays the data field values and the "Var" field displays the result of post-processing those values. Right-click the "Var" field on the field panel and select an item from the appeared context menu to switch the post-processing algorithm (summary display mode).

summaryType

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

In this example, you can select the summary type of the "Sales" data field at runtime. Right-click this data field on the field panel and choose the required summary type. Note that along with the summary type, the data field's caption and the format of summary values change.

In this example, you can select the summary type of the "Sales" data field at runtime. Right-click this data field on the field panel and choose the required summary type. Note that along with the summary type, the data field's caption and the format of summary values change.

In this example, you can select the summary type of the "Sales" data field at runtime. Right-click this data field on the field panel and choose the required summary type. Note that along with the summary type, the data field's caption and the format of summary values change.

visible

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

In this example, you can change the visibility of the "City" field at runtime using the switch below the grid. Note that the field appears/disappears not only in the grid, but in the field chooser as well.

In this example, you can change the visibility of the "City" field at runtime using the switch below the grid. Note that the field appears/disappears not only in the grid, but in the field chooser as well.

In this example, you can change the visibility of the "City" field at runtime using the switch below the grid. Note that the field appears/disappears not only in the grid, but in the field chooser as well.