PivotGrid

The PivotGrid is a widget that allows you to display and analyze multi-dimensional data from a local storage or an OLAP cube.

Included in: dx.web.js, dx.viz-web.js, dx.all.js
Module: ui/pivot_grid
Export: default
PivotGrid interactive configuration
Copy Code
Copy to Codepen
$("#pivotgrid").dxPivotGrid({
});

                    

You can create the PivotGrid widget using one of the following approaches.

jQuery
JavaScript
HTML
$(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
HTML
<dx-pivot-grid>
    <dxo-data-source>
        <dxo-store
            type="odata"
            url="http://url/to/the/service"
            key="OrderID"
            keyType="Int32">
        </dxo-store>
    </dxo-data-source>
    <dxi-field
        area="column"
        dataField="OrderDate"
        dataType="date">
    </dxi-field>
    <dxi-field
        area="row"
        dataField="ShipCountry">
    </dxi-field>
    <dxi-field
        area="row"
        dataField="ShipCity">
    </dxi-field>
    <dxi-field
        area="row"
        dataField="ShipName">
    </dxi-field>
    <dxi-field
        area="data"
        summaryType="count">
    </dxi-field>
</dx-pivot-grid>
AngularJS
HTML
<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
HTML
<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
Razor C#
Razor VB
@(Html.DevExtreme().PivotGrid()
    .ID("pivotGrid")
    .DataSource(ds => ds
        .Store(store => store.OData()
            .Url("http://url/to/the/service")
            .Key(new string[] { "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(New String() { "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)
)

Note that DevExtreme widgets require you to link the jQuery library to your application. If you use the Knockout or AngularJS approach, the Knockout or AngularJS library is also required. For detailed information on linking these libraries to your project, refer to the topics in the Installation section.

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.

View Demo Watch Video

Configuration

This section describes the configuration options of the PivotGrid widget.

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 export.

fieldChooser

The Field Chooser configuration options.

fieldPanel

Configures the field panel.

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

This section describes the methods that can be used to manipulate the PivotGrid widget.

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.

element()

Gets the root element of the widget.

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.

instance()

Returns this widget's instance. Use it to access other methods of the widget.

off(eventName)

Detaches all event handlers from the specified event.

off(eventName, eventHandler)

Detaches a particular event handler from the specified event.

on(eventName, eventHandler)

Subscribes to a specified event.

on(events)

Subscribes to the specified events.

option()

Gets the widget's options.

option(optionName)

Gets a specific option value.

option(optionName, optionValue)

Assigns a new value to a specific option.

option(options)

Sets one or more 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

This section describes events fired by this widget.

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 an option of the widget is changed.

See Also

Summary Cell

An object exposing methods that manipulate a summary cell and provide access to its neighboring cells.

Type:

Object

Pivot Grid Cell

An object containing information about the pivot grid cell.

Type:

Object