jQuery PivotGrid API
The PivotGrid is a widget that allows you to display and analyze multi-dimensional data from a local storage or an OLAP cube.
DevExtreme widgets are integrated with many popular libraries and frameworks. See the Installation section (for JavaScript libraries) or the Prerequisites and Installation section (for ASP.NET MVC framework) to find details on setting up DevExtreme with a particular library or framework.
The following code shows how to create the PivotGrid widget using every supported library and framework. For more details on working with widgets in these libraries and frameworks, see the Widget Basics topic for jQuery, Angular, AngularJS, Knockout or ASP.NET MVC.
jQuery
$(function () {
$("#pivotGrid").dxPivotGrid({
dataSource: {
store: {
type: 'odata',
url: 'http://url/to/the/service',
key: 'OrderID',
keyType: 'Int32'
},
fields: [
{ area: 'column', dataField: 'OrderDate', dataType: 'date' },
{ area: 'row', dataField: 'ShipCountry' },
{ area: 'row', dataField: 'ShipCity' },
{ area: 'row', dataField: 'ShipName' },
{ area: 'data', summaryType: 'count' }
]
}
});
});<div id="pivotGrid"></div>
Angular
<dx-pivot-grid
[dataSource]="pivotGridDataSource">
</dx-pivot-grid>
import { DxPivotGridModule } from 'devextreme-angular';
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
pivotGridDataSource: PivotGridDataSource;
constructor() {
this.pivotGridDataSource = new PivotGridDataSource({
store: {
type: 'odata',
url: 'http://url/to/the/service',
key: 'OrderID',
keyType: 'Int32'
},
fields: [
{ area: 'column', dataField: 'OrderDate', dataType: 'date' },
{ area: 'row', dataField: 'ShipCountry' },
{ area: 'row', dataField: 'ShipCity' },
{ area: 'row', dataField: 'ShipName' },
{ area: 'data', summaryType: 'count' }
]
});
}
}
@NgModule({
imports: [
// ...
DxPivotGridModule
],
// ...
})AngularJS
<div dx-pivot-grid="{
dataSource: {
store: {
type: 'odata',
url: 'http://url/to/the/service',
key: 'OrderID',
keyType: 'Int32'
},
fields: [
{ area: 'column', dataField: 'OrderDate', dataType: 'date'},
{ area: 'row', dataField: 'ShipCountry' },
{ area: 'row', dataField: 'ShipCity' },
{ area: 'row', dataField: 'ShipName' },
{ area: 'data', summaryType: 'count' }
]
}
}"></div>Knockout
<div data-bind="dxPivotGrid: {
dataSource: {
store: {
type: 'odata',
url: 'http://url/to/the/service',
key: 'OrderID',
keyType: 'Int32'
},
fields: [
{ area: 'column', dataField: 'OrderDate', dataType: 'date' },
{ area: 'row', dataField: 'ShipCountry' },
{ area: 'row', dataField: 'ShipCity' },
{ area: 'row', dataField: 'ShipName' },
{ area: 'data', summaryType: 'count' }
]
}
}"></div>ASP.NET MVC Controls
@(Html.DevExtreme().PivotGrid()
.ID("pivotGrid")
.DataSource(ds => ds
.Store(store => store.OData()
.Url("http://url/to/the/service")
.Key("OrderID")
.KeyType(EdmType.Int32)
)
.Fields(fields => {
fields.Add().Area(PivotGridArea.Column)
.DataField("OrderDate")
.DataType(PivotGridDataType.Date);
fields.Add().Area(PivotGridArea.Row).DataField("ShipCountry");
fields.Add().Area(PivotGridArea.Row).DataField("ShipCity");
fields.Add().Area(PivotGridArea.Row).DataField("ShipName");
fields.Add().Area(PivotGridArea.Data).SummaryType(SummaryType.Count);
})
)
)@(Html.DevExtreme().PivotGrid() _
.ID("pivotGrid") _
.DataSource(Sub(ds)
ds.Store(Function(store)
Return store.OData() _
.Url("http://url/to/the/service") _
.Key("OrderID") _
.KeyType(EdmType.Int32)
End Function) _
.Fields(Sub(fields)
fields.Add().Area(PivotGridArea.Column) _
.DataField("OrderDate") _
.DataType(PivotGridDataType.Date)
fields.Add().Area(PivotGridArea.Row).DataField("ShipCountry")
fields.Add().Area(PivotGridArea.Row).DataField("ShipCity")
fields.Add().Area(PivotGridArea.Row).DataField("ShipName")
fields.Add().Area(PivotGridArea.Data).SummaryType(SummaryType.Count)
End Sub)
End Sub)
)To provide data for the PivotGrid widget, specify a data source. PivotGrid accepts the PivotGridDataSource data source only. You can pass its configuration to the dataSource field without creating the separate PivotGridDataSource object as shown above.
Configuration
| Name | Description |
|---|---|
| allowExpandAll |
Allows an end-user to expand/collapse all header items within a header level. |
| allowFiltering |
Allows a user to filter fields by selecting or deselecting values in the popup menu. |
| allowSorting |
Allows an end-user to change sorting options. |
| allowSortingBySummary |
Allows an end-user to sort columns by summary values. |
| dataFieldArea |
Specifies the area to which data field headers must belong. |
| dataSource |
Specifies a data source for the pivot grid. |
| disabled |
Specifies whether the widget responds to user interaction. |
| elementAttr |
Specifies the attributes to be attached to the widget's root element. |
| export |
Configures client-side exporting. |
| fieldChooser |
The Field Chooser configuration options. |
| fieldPanel |
Configures the field panel. |
| headerFilter |
Configures the header filter feature. |
| height |
Specifies the widget's height. |
| hideEmptySummaryCells |
Specifies whether or not to hide rows and columns with no data. |
| hint |
Specifies text for a hint that appears when a user pauses on the widget. |
| loadPanel |
Specifies options configuring the load panel. |
| onCellClick |
A handler for the cellClick event. |
| onCellPrepared |
A handler for the cellPrepared event. |
| onContentReady |
A handler for the contentReady event. Executed when the widget's content is ready. This handler may be executed multiple times during the widget's lifetime depending on the number of times its content changes. |
| onContextMenuPreparing |
A handler for the contextMenuPreparing event. |
| onDisposing |
A handler for the disposing event. Executed when the widget is removed from the DOM using the remove(), empty(), or html() jQuery methods only. |
| onExported |
A handler for the exported event. |
| onExporting |
A handler for the exporting event. |
| onFileSaving |
A handler for the fileSaving event. |
| onInitialized |
A handler for the initialized event. Executed only once, after the widget is initialized. |
| onOptionChanged |
A handler for the optionChanged event. Executed after an option of the widget is changed. |
| rowHeaderLayout |
Specifies the layout of items in the row header. |
| rtlEnabled |
Switches the widget to a right-to-left representation. |
| scrolling |
A configuration object specifying scrolling options. |
| showBorders |
Specifies whether the outer borders of the grid are visible or not. |
| showColumnGrandTotals |
Specifies whether to display the Grand Total column. |
| showColumnTotals |
Specifies whether to display the Total columns. |
| showRowGrandTotals |
Specifies whether to display the Grand Total row. |
| showRowTotals |
Specifies whether to display the Total rows. Applies only if rowHeaderLayout is "standard". |
| showTotalsPrior |
Specifies where to show the total rows or columns. Applies only if rowHeaderLayout is "standard". |
| stateStoring |
A configuration object specifying options related to state storing. |
| tabIndex |
Specifies the number of the element when the Tab key is used for navigating. |
| texts |
Strings that can be changed or localized in the PivotGrid widget. |
| visible |
Specifies whether the widget is visible. |
| width |
Specifies the widget's width. |
| wordWrapEnabled |
Specifies whether long text in header items should be wrapped. |
Methods
| Name | Description |
|---|---|
| beginUpdate() |
Prevents the widget from refreshing until the endUpdate() method is called. |
| bindChart(chart, integrationOptions) |
Binds a Chart to the PivotGrid. |
| defaultOptions(rule) |
Specifies the device-dependent default configuration options for this component. |
| dispose() |
Disposes of all the resources allocated to the PivotGrid instance. |
| element() |
Gets the root widget element. |
| endUpdate() |
Refreshes the widget after a call of the beginUpdate() method. |
| exportToExcel() |
Exports pivot grid data to the Excel file. |
| focus() |
Sets focus on the widget. |
| getDataSource() |
Gets the PivotGridDataSource instance. |
| getFieldChooserPopup() |
Gets the Popup instance of the field chooser window. |
| getInstance(element) |
Gets the instance of a widget found using its DOM node. |
| instance() |
Gets the widget's instance. Use it to access other methods of the widget. |
| off(eventName) |
Detaches all event handlers from a single event. |
| off(eventName, eventHandler) |
Detaches a particular event handler from a single event. |
| on(eventName, eventHandler) |
Subscribes to an event. |
| on(events) |
Subscribes to events. |
| option() |
Gets all widget options. |
| option(optionName) |
Gets the value of a single option. |
| option(optionName, optionValue) |
Updates the value of a single option. |
| option(options) |
Updates the values of several options. |
| repaint() |
Repaints the widget. Call it if you made modifications that changed the widget's state to invalid. |
| updateDimensions() |
Updates the widget to the size of its content. |
Events
| Name | Description |
|---|---|
| cellClick |
Fires when a user clicks a pivot grid cell. |
| cellPrepared |
Fires after a cell has been rendered. |
| contentReady |
Raised when the widget's content is ready. |
| contextMenuPreparing |
Fires before displaying a context menu that is invoked on one of the pivot grid's header or row cells. |
| disposing |
Raised when the widget is removed from the DOM using the remove(), empty(), or html() jQuery methods only. |
| exported |
Fires after the exporting of grid data is completed. |
| exporting |
Fires before exporting grid data. |
| fileSaving |
Fires before the grid saves an Excel file with exported data on the user's local storage. |
| initialized |
Raised only once, after the widget is initialized. |
| optionChanged |
Raised after a widget option is changed. |
Summary Cell
An object exposing methods that manipulate a summary cell and provide access to its neighboring cells.