DevExtreme jQuery - PivotGridDataSource Methods

|

The field's name, dataField, caption, or index in the fields array.

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

pivotGridDataSource.collapseAll("Region");

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });

        this.pivotGridDataSource.collapseAll("Region");
    }
}

collapseHeaderItem(area, path)

Collapses a specific header item.

Parameters:

area:

The area that contains the header item to collapse.

path:

Array<String | Number | Date>

A path to the header item.

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

// Collapses the second quarter of 2015 in the column area
pivotGridDataSource.collapseHeaderItem("column", [2015, 2]);

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });

        // Collapses the second quarter of 2015 in the column area
        this.pivotGridDataSource.collapseHeaderItem("column", [2015, 2]);
    }
}

createDrillDownDataSource(options)

Provides access to the facts that were used to calculate a specific summary value.

Parameters:

options:

Configuration object.

Object structure:

Name	Type	Description
columnPath	Array<String \| Number \| Date>	A path to the cell with the summary value by columns.
rowPath	Array<String \| Number \| Date>	A path to the cell with the summary value by rows.
dataIndex	Number	The index of the measure for which the summary value is calculated.
maxRowCount	Number	Maximum number of facts to get.
customColumns	Array<String>	The field names to be included for each fact.

Return Value:

DataSource

A drill-down data source.

NOTE

Data in the drill-down data source is paginated by default. Only the items on the first page are loaded when you call the load() method. To get all the items, turn the pagination off:

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

var drillDownDataSource = pivotGridDataSource.createDrillDownDataSource({
    // Options are passed here
});

drillDownDataSource.paginate(false);

drillDownDataSource.load()
    .done(function (data) {
        // Process "data" here
    })
    .fail(function (error) {
        // Handle the "error" here
    });

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import DataSource from "devextreme/data/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    drillDownDataSource: DataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });

        this.drillDownDataSource = this.pivotGridDataSource.createDrillDownDataSource({
            // Options are passed here
        });

        this.drillDownDataSource.paginate(false);

        this.drillDownDataSource.load()
            .then(
                (data) => { /* Process "data" here */ },
                (error) => { /* Handle the "error" here */ }
            )
    }
}

View Demo

dispose()

Disposes of all the resources allocated to the PivotGridDataSource instance.

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

pivotGridDataSource.dispose();

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });

        this.pivotGridDataSource.dispose();
    }
}

NOTE

Do not call this method if the widget created the PivotGridDataSource instance.

expandAll(id)

Expands all the header items of a field with the specified identifier.

Parameters:

id:

|

The field's name, caption, dataField, or index in the fields array.

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

pivotGridDataSource.expandAll("Region");

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });

        this.pivotGridDataSource.expandAll("Region");
    }
}

expandHeaderItem(area, path)

Expands a specific header item.

Parameters:

area:

The area that contains the header item to expand.

path:

Array<Object>

A path to the header item.

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

// Expands the second quarter of 2015 in the column area
pivotGridDataSource.expandHeaderItem("column", [2015, 2]);

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });

        // Expands the second quarter of 2015 in the column area
        this.pivotGridDataSource.expandHeaderItem("column", [2015, 2]);
    }
}

field(id)

Gets all the options of a field with the specified identifier.

Parameters:

id:

|

The field's name, caption, dataField, or index in the fields array.

Return Value:

The field's options.

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // ...
    fields: [{
        caption: "Sales",
        dataField: "amount",
        summaryType: "sum",
        area: "data"
    }]
});

// The following commands return the same object
pivotGridDataSource.field("Sales");
pivotGridDataSource.field("amount");
pivotGridDataSource.field(0);

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                caption: "Sales",
                dataField: "amount",
                summaryType: "sum",
                area: "data"
            }]
        });

        // The following commands return the same object
        this.pivotGridDataSource.field("Sales");
        this.pivotGridDataSource.field("amount");
        this.pivotGridDataSource.field(0);
    }
}

field(id, options)

Updates field options' values.

Parameters:

id:

|

The field's name, caption, dataField, or index in the fields array.

options:

The options with their new values.

Call the load() method to update the PivotGrid:

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // ...
    fields: [{
        caption: "Sales",
        dataField: "amount",
        summaryType: "sum",
        area: "data"
    }]
});

// Changes the "Sales" field's summaryType
pivotGridDataSource.field("Sales", { summaryType: "avg" });
pivotGridDataSource.load();

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                caption: "Sales",
                dataField: "amount",
                summaryType: "sum",
                area: "data"
            }]
        });

        // Changes the "Sales" field's summaryType
        this.pivotGridDataSource.field("Sales", { summaryType: "avg" });
        this.pivotGridDataSource.load();
    }
}

fields()

Gets all the fields including those generated automatically.

Return Value:

Array<PivotGrid Field>

All options of all the fields.

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

var pivotGridFields = pivotGridDataSource.fields();

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });

        let pivotGridFields = this.pivotGridDataSource.fields();
    }
}

fields(fields)

Specifies a new fields collection.

Parameters:

fields:

Array<PivotGrid Field>

New fields.

Call the load() method to update the PivotGrid:

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

pivotGridDataSource.fields([{
    dataField: "region",
    area: "row"
}, {
    dataField: "date",
    dataType: "date",
    area: "column"
},  {
    dataField: "sales",
    summaryType: "sum",
    area: "data"
}]);

pivotGridDataSource.load();

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });

        this.pivotGridDataSource.fields([{
            dataField: "region",
            area: "row"
        }, {
            dataField: "date",
            dataType: "date",
            area: "column"
        },  {
            dataField: "sales",
            summaryType: "sum",
            area: "data"
        }]);

        this.pivotGridDataSource.load();
    }
}

filter()

Gets the filter option's value. Does not affect an XmlaStore.

Return Value:

A filter expression.

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // ...
    filter: ["age", ">", 18]
});

var filterExpr = pivotGridDataSource.filter(); // returns ["age", ">", 18]

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            filter: ["age", ">", 18]
        });

        let filterExpr = this.pivotGridDataSource.filter(); // returns ["age", ">", 18]
    }
}

filter(filterExpr)

Sets the filter option's value. Does not affect an XmlaStore.

Parameters:

filterExpr:

A filter expression. Pass null to clear filtering settings.

Call the load() method to update the PivotGrid:

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

pivotGridDataSource.filter(["age", ">", 18]);
// or
// pivotGridDataSource.filter("age", ">", 18);

pivotGridDataSource.load();

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });

        this.pivotGridDataSource.filter(["age", ">", 18]);
        // or
        // this.pivotGridDataSource.filter("age", ">", 18);

        this.pivotGridDataSource.load();
    }
}

getAreaFields(area, collectGroups)

Gets all the fields within an area.

Parameters:

area:

The area that contains the fields to get.

collectGroups:

Boolean

Pass true to return grouped fields in a single array entry or false to return them as separate entries.
The default value is false.

Return Value:

Array<PivotGrid Field>

All the options of all the fields within the area.

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

var columnAreaFields = pivotGridDataSource.getAreaFields("column", true);

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });

        let columnAreaFields = this.pivotGridDataSource.getAreaFields("column", true);
    }
}

getData()

Gets the loaded data. Another data portion is loaded every time a header item is expanded.

Return Value:

The loaded data.

This method returns an object with the following structure:

JavaScript

{
    rows: [{
        index: /* Row 1 index */,
        text:  /* Row 1 caption */,
        value: /* Row 1 value */
    }, {
        index: /* Row 2 index */,
        text:  /* Row 2 caption */,
        value: /* Row 2 value */,
        children: [{
            index: /* Row 2.1 index */,
            text:  /* Row 2.1 caption */,
            value: /* Row 2.1 value */,
            children: [ /* Level 3 and deeper */ ]
        },
        // ...
        ]
    },
    // ...
    ],
    columns: [{
        index: /* Column 1 index */,
        text:  /* Column 1 caption */,
        value: /* Column 1 value */
    }, {
        index: /* Column 2 index */,
        text:  /* Column 2 caption */,
        value: /* Column 2 value */,
        children: [{
            index: /* Column 2.1 index */,
            text:  /* Column 2.1 caption */,
            value: /* Column 2.1 value */,
            children: [ /* Level 3 and deeper */ ]
        },
        // ...
        ]
    },
    // ...
    ],
    values: [
        [
            [
                /* Measure 1 summary value 1 */,
                /* Measure 2 summary value 1 */,
                // ...
            ],
            [
                /* Measure 1 summary value 2 */,
                /* Measure 2 summary value 2 */,
                // ...
            ],
            // ...
        ],
        // ...
    ],
    grandTotalRowIndex: 0,
    grandTotalColumnIndex: 0

}

NOTE

"Measure" refers to a pivot grid field in the data area.

The object mentioned above contains three arrays: rows, columns, and values. Objects in the rows and columns arrays describe header items in the row and column areas and contain the following fields:

index - the index of the row/column that contains the header item; used to find a summary value in the values array;
value - the field value from the data source that corresponds to the header item;
text - the header item's caption;
children - an optional array that contains lower-level header items.

The values array contains summary values. Each of them has three indexes. To get a summary value, use the following pattern:

JavaScript

var value = values[/* row index */][/* column index */][/* measure index */];

You can pass grandTotalRowIndex and grandTotalColumnIndex as row index and column index to get grand total values.

NOTE

If you use the XmlaStore and your data area is empty, this method returns the default measure values. They are hidden from the UI until you place the default measure in the data area using the field(id, options) method. The default measure is specified on the OLAP server.

JavaScript

pivotGridDataSource.field(/* measure name */, { area: "data" });

isLoading()

Checks whether the PivotGridDataSource is loading data.

Return Value:

Boolean

true if data is being loaded; otherwise false.

load()

Starts loading data.

Return Value:

Promise<any> (jQuery or native)

A Promise that is resolved after data is loaded. It is a native Promise or a jQuery.Promise when you use jQuery.

jQuery

JavaScript

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    // PivotGridDataSource is configured here
});

pivotGridDataSource.load()
    .done(function (data) {
        // Process "data" here
    })
    .fail(function (error) {
        // Handle the "error" here
    });

Angular

TypeScript

import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // PivotGridDataSource is configured here
        });
        this.pivotGridDataSource.load()
            .then(
                (data) => { /* Process "data" here */ },
                (error) => { /* Handle the "error" here */ }
            )
    }
}

off(eventName)

Detaches all event handlers from a single event.

Parameters:

eventName:

The event's name.

Return Value:

The object for which this method is called.

off(eventName, eventHandler)

Detaches a particular event handler from a single event.

Parameters:

eventName:

The event's name.

eventHandler:

Function

The event's handler.

Return Value:

The object for which this method is called.

on(eventName, eventHandler)

Subscribes to an event.

Parameters:

eventName:

The event's name.

eventHandler:

Function

The event's handler.

Return Value:

The object for which this method is called.

Use this method to subscribe to one of the events listed in the Events section.

on(events)

Subscribes to events.

Parameters:

events:

Events with their handlers: { "eventName1": handler1, "eventName2": handler2, ...}

Return Value:

The object for which this method is called.

Use this method to subscribe to several events with one method call. Available events are listed in the Events section.

reload()

Clears the loaded PivotGridDataSource data and calls the load() method.

Return Value:

Promise<any> (jQuery or native)

A Promise that is resolved after data is loaded. It is a native Promise or a jQuery.Promise when you use jQuery.

state()

Gets the current PivotGridDataSource state.

Return Value:

The PivotGridDataSource state - fields configuration, filters, expanded headers, etc.

Use the returned object as an argument of the state(state) method to restore the pivot grid state.

NOTE

This method is a part of the state storing feature. Refer to the PivotGrid configuration options for details.

state(state)

Sets the PivotGridDataSource state.

Parameters:

state: