layout	post
title	How to section with PivotGrid widget for Syncfusion Essential JS
description	How to
platform	js
control	PivotGrid
documentation	ug
api	/api/js/ejpivotgrid

Public Methods

Refresh the PivotGrid with paging

The refreshPagedPivotGrid method is used to re-render the PivotGrid component with given axis and page number.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.refreshPagedPivotGrid("series", 4); </script>

{% endhighlight %}

Refresh the PivotGrid without paging

The refreshPivotGrid method is used to re-render the PivotGrid control with modified data input in client-mode.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.refreshPivotGrid(); </script>

{% endhighlight %}

Refresh the PivotGrid with modified report

The refreshControl method is used to refresh the PivotGrid component with the report available at that instant.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.model.dataSource = newDataSource; pivotGridObj.refreshControl(); </script>

{% endhighlight %}

Returning height and width of all cells of PivotGrid

The calculateCellWidths method is used to return the height of all rows and width of all columns of cells in PivotGrid component.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); var cellDimensions = pivotGridObj.calculateCellWidths(); </script>

{% endhighlight %}

Show/Open the Conditional formatting dialog

The openConditionalFormattingDialog method is used to create conditional formatting dialog to apply conditional formatting for the PivotGrid control.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.openConditionalFormattingDialog(); </script>

{% endhighlight %}

Saving the Report collection

The saveReport method is used to save the current report to the database/local storage.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); var storageOption = "local"; //it takes the string value "local" for local storage and "database" for storing to database. var url = "";//it takes the service url for storing to database. For local it is not required. pivotGridObj.saveReport("reportName", storageOption, url); </script>

{% endhighlight %}

Render the PivotGrid in Excel Like format

The excelLikeLayout method is used to reconstruct the JSON data that is formed for rendering the PivotGrid in the excel-like layout format.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.excelLikeLayout(pivotGridObj.getJSONRecords()); </script>

{% endhighlight %}

Refresh the field caption dynamically

The refreshFieldCaption method is used to change the caption of the pivot item (name displayed in UI) on-demand for the relational datasource in client-mode.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.refreshFieldCaption( "name", "caption", "axisClassName"); </script>

{% endhighlight %}

Explicit asynchronous invoke

The doAjaxPost method is used to Perform an asynchronous HTTP (AJAX) request.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.doAjaxPost("POST", "/PivotService/Initialize", { "key", "Hello World" }, successEvent, null); </script>

{% endhighlight %}

Destroying the object of PivotGrid

The destroy method is used to destroy the PivotGrid widget associated events that are bound using “this._on” and brings the control to pre-init state.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.destroy(); </script>

{% endhighlight %}

Getting JSON records from control object

The getJSONRecords method is used to return the JSON records that are formed to render the control.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); var jsonRecords = pivotGridObj.getJSONRecords(); </script>

{% endhighlight %}

Setting JSON records to control object

The setJSONRecords method is used to set the JSON records that are formed to render the control.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.setJSONRecords(jsonRecords); </script>

{% endhighlight %}

Rendering the widget with pre-defined JSON records

The renderControlFromJSON method is used to render the PivotGrid widget with the pre-defined JSON records available at that instant.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.renderControlFromJSON({ pivotGridObj.getJSONRecords() }); </script>

{% endhighlight %}

Getting the current OLAP report

You can get the current OLAP report along with axis information by getOlapReport method.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); var report = pivotGridObj.getOlapReport(); </script>

{% endhighlight %}

Setting the OLAP report

You can set the OLAP report along with axis information by the setOlapReport method.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); var report = pivotGridObj.setOlapReport(olapReportObj); </script>

{% endhighlight %}

Loading the report

You can load the specified report from the database/local storage by loadReport method.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); var storageOption = "local"; //it takes the string value "local" for loading a report from local storage and "database" for loading from database. var url = "";//it takes the service method url for loading report from database. For local it is not required. pivotGridObj.loadReport("reportName", storageOption, url); </script>

{% endhighlight %}

Explicit asynchronous post

The doPostBack method is used to perform an asynchronous HTTP (AJAX) post operation.

{% highlight html %}

<script> $("#PivotGrid1").ejPivotGrid(); var pivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); pivotGridObj.doPostBack("/PivotService/Initialize", { "key", "Hello World" }); </script>

{% endhighlight %}

Events

Invoking event before Pivot Engine population

The beforePivotEnginePopulate event is triggered before the pivot engine starts to populate.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //before pivot engine populate event
        beforePivotEnginePopulate: function(args) {

        },

        //...
    });

{% endhighlight %}

Invoking event before saving the report in database

The saveReport event is triggered before saving the current report to the database.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //save report event
        saveReport: function(args) {

        },

        //...
    });

{% endhighlight %}

Triggering event before editing the cell in PivotGrid

The cellEdit event is triggered before editing the cells.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //cell edit event
        cellEdit: function(args) {

        },

        //...
    });

{% endhighlight %}

Invoking event in client-side after service invoke

The afterServiceInvoke event is triggered when it is reached client-side after the AJAX request.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //after service invoke event
        afterServiceInvoke: function(args) {

        },

        //...
    });

{% endhighlight %}

Invoking event in client-side before service invoke

The beforeServiceInvoke event is triggered before any AJAX request is passed from the PivotGrid to service methods.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //before service invoke event
        beforeServiceInvoke: function(args) {

        },

        //...
    });

{% endhighlight %}

Triggering event after performing drill operation

The drillSuccess event is triggered after performing drill operation in the PivotGrid.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //drill success event
        drillSuccess: function(args) {

        },

        //...
    });

{% endhighlight %}

Triggering event before exporting

The beforeExport event is triggered before performing exporting in the pivot grid.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //before export event
        beforeExport: function(args) {

        },

        //...
    });

{% endhighlight %}

Triggering event before the PivotGrid loaded

The load event is triggered when the PivotGrid loading is initiated.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //load event
        load: function(args) {

        },

        //...
    });

{% endhighlight %}

Triggering event on successful completion of AJAX request

The renderSuccess event is triggered when the AJAX request returns successfully at the client-side.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //render success event
        renderSuccess: function(args) {

    },

        //...
});

{% endhighlight %}

Triggering event after completing the controls rendering

The renderComplete event is triggered after PivotClient gets completely rendered.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //render complete event
        renderComplete: function(args) {

        },

        //...
    });

{% endhighlight %}

Triggering event on failure of AJAX request

The renderFailure event is triggered when any error occurred during the AJAX request.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //render complete event
        renderFailure: function(args) {

    },

        //...
});

{% endhighlight %}

Triggering event while loading report

The loadReport event is triggered while loading report from database.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        //load report event
        loadReport: function(args) {

    },

        //...
});

{% endhighlight %}

Members

Getting Raw items by triggering cell double click event

Cell double click on pivot grid allows you to get the raw items of cell which is clicked. To enable the cell double click event, you can use the enableCellDoubleClick property. You can get the raw items of the cell through the cellDoubleClick event.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        enableCellDoubleClick: true,

        //cell double click event
        cellDoubleClick: function(args) {

        },

        //...
    });

{% endhighlight %}

Setting custom theme

You can render the PivotClient with any one of the built-in themes by using the cssClassproperty.

{% highlight html %}

<script type="text/javascript">
    $(function() {
        $("#PivotGrid1").ejPivotGrid({
          cssClass: "gradient-lime"
        });

    });

</script>

{% endhighlight %}

Getting JSON format by triggering cell click event

Cell click on pivot grid allows you to get JSON format of the cell which is clicked. To enable cell click event, you can use the enableCellClick property. You can get the JSON format of the value cell through the cellClick event.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        enableCellClick: true,

        cellClick: function (args) {

        }

        //...
    });

{% endhighlight %}

Enabling XMLHttpRequest object for CORS

Allows you to enable the “withCredentials” property in the XMLHttpRequest object for CORS(Cross-Origin Resource Sharing) request. This feature can be enabled by the enableXHRCredentials property.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

      enableXHRCredentials: true

});

{% endhighlight %}

Enabling complete data export on pivot grid

This feature allows you to export the entire data instead of current page data, while paging option is enabled. It can be enabled by using the enableCompleteDataExport property.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

      enableCompleteDataExport: true

});

{% endhighlight %}

Maximum Node limit in Member Editor

This feature allows you to set the maximum number of nodes and child nodes to be displayed in the member editor. It can be enabled by using the maxNodeLimitInMemberEditor property.

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

     maxNodeLimitInMemberEditor: 1500

});

{% endhighlight %}

Rendering PivotGrid through JSON data

You have an option to render PivotGrid through JSON data by setting the enableJSONRendering property and also you should provide jsonRecords.

N> URL is not necessary for JSON rendering

{% highlight javascript %}

$("#PivotGrid1").ejPivotGrid({

        enableJSONRendering: true,

        jsonRecords: data     // JSON records

        //...
    });

{% endhighlight %}

Connect the Field List with PivotGrid using its ID

You can connect PivotSchemaDesigner with specified ID to the PivotGrid control. This connection can be enabled by the pivotTableFieldListID property. The enablePivotFieldList property also helps you to show/hide PivotSchemaDesigner.

{% highlight html %}

//...

<div id="PivotGrid1" style="width: 55%; height: 670px; overflow: scroll; float: left;"></div>

<div id="PivotSchemaDesigner1" style="height:650px;width:40%;">
</div>

<script type="text/javascript">
    $(function() {
        $("#PivotGrid1").ejPivotGrid({

          pivotTableFieldListID: "PivotSchemaDesigner1"

        });

        $("#PivotSchemaDesigner1").ejPivotSchemaDesigner({ });
    });


</script>

{% endhighlight %}

Enabling Context Menu in PivotGrid

To improve user action, the Context Menu option in PivotGrid allows you to enable/disable the features with UI operations. This feature can be enabled by the enableContextMenu property. The features in Context Menu is displayed with respective to the datasource (OLAP/Relational) and operational modes (client-side/server-side).

{% highlight html %}

<script type="text/javascript">
    $(function() {
        $("#PivotGrid1").ejPivotGrid({

          enableContextMenu: true

        });

    });


</script>

{% endhighlight %}

The following are the available features in Context Menu.

• Cell Selection - Enables/Disables the cell selection for a specific range of value cells.
• Column Resize - Enables/Disables the width of columns dynamically within given widget size.
• Tool Tip - Enables/Disables the Tooltip.
• Collapse By Default - Enables/Disables to Collapse the pivot items along rows and columns by default.
• Advanced Filtering - Enables/Disables the filtering options.
• Grouping Bar - Enables/Disables the display of GroupingBar allowing you to filter, sort, and remove fields obtained from the datasource.
• Cell Editing - Enables/Disables to to edit value cells.
• Drill Through - Enables/Disables to retrieve raw items by value cell click.
• RTL - Enables/Disables the RTL.

The following are the available customizations in Context Menu.

• Summary Customization - Allows you to customize the row/column totals through dialog.

• Conditional Formatting - Allows you to format a specific set of cells based on the condition by the conditional dialog.
• Calculated Field - Supports to insert a new calculated field based on the existing pivot fields through the calculated field dialog.
• Number Formatting - Allows you to specify the required number format that should be used in values of the PivotGrid by setting the format through Formatting dialog.

• Summary Types - Allow you to specify the required layout that should be used in summary cells of the PivotGrid through Summary Types dialog.

The following are the available features through sub context menu.

• Layouts - Allows you to specify the required layout through sub menus.

• Hyper Link - Allows you to enable/disable hyperlink for row header, column header, value, and summary cells.

• Exporting - Allows you to export PivotGrid in a desired format. You have to mention the export settings through beforeExport event.

• Frozen Headers - Allows you to freeze the PivotGrid in a desired dimension row/column.

Setting Custom Name to Service Methods

The serviceMethodSettings allows you to set the custom name for the methods in WebAPI/WCF, communicated during AJAX post.

Common Service Methods to OLAP and Relational datasource

Service Methods	Description
initialize	It fetches the data required to render the PivotGrid initially.
fetchMembers	It fetches the members of the selected field to render the member editor tree.
filtering	It fetches the data required to render the PivotGrid control on performing filtering action.
nodeDropped	It fetches the relational data required to render the PivotGrid control on node drop action.
sorting	It fetches the sorted data to render the PivotGrid control on performing sorting.
exportPivotGrid	It is used to export the PivotGrid data to specified format.
saveReport	It saves the current report to database with the specified name.
loadReport	It loads a report from the database and refreshes the control with it.
deferUpdate	It fetches the data with respect to the report available at that instant (i.e) updates the control with current report.

Relational

Service Methods	Description
calculatedField	It forms a calculated field in values area and fetches the data along with it to render the PivotGrid control.
cellEditing	It rewrites the content of database on editing a cell.
valueSorting	It allows to perform sorting by clicking of column header.
nodeStateModified	It fetches the relational data required to render the PivotGrid control on selecting/unselecting fields in Field list.

OLAP

Service Methods	Description
drillDown	It fetches the OLAP data required to render the PivotGrid control after drilling it.
Paging	It fetches the OLAP data required to render the specific page of PivotGrid with paging enabled.
removeButton	It fetches the data required to render the control after removing a button.
memberExpand	It fetches the data to render children nodes of a member in Member Editor Tree.
drillThroughHierarchies	It returns dimensions that are associated with the measure of clicked value cell.
drillThroughDataTable	It returns a grid with data that are associated with measure values of the clicked value cell.
writeBack	It allows you to edit the values in the PivotGrid and update a write enabled cube in the back-end (SSAS) dynamically at runtime.

Pivot Pager

Public Methods

Initialize pivot pager with page properties

The initPagerProperties method is used to initialize the page counts and page numbers of the pivot pager.

{% highlight js %}

 var pagerObj = $("#PivotPager1").data("ejPivotPager");
 pagerObj.initPagerProperties(150, { CategorialPageSize: 10, SeriesPageSize: 10, CategorialCurrentPage: 1, SeriesCurrentPage: 1});

{% endhighlight %}

Members

Current page number in categorical axis

The categoricalCurrentPage property is used to specify the current page number in the categorical axis.

{% highlight js %}

<script type="text/javascript"> $("#PivotPager1").ejPivotPager({ categoricalCurrentPage: 1 }); </script>

{% endhighlight %}

Page count in categorical axis

The categoricalPageCount property is used to specify the total page count in the categorical axis.

{% highlight js %}

<script type="text/javascript"> $("#PivotPager1").ejPivotPager({ categPageCount: 1 }); </script>

{% endhighlight %}

Current page number in series axis

The seriesCurrentPage property is used to specify the current page number in the series axis.

{% highlight js %}

<script type="text/javascript"> $("#PivotPager1").ejPivotPager({ seriesCurrentPage: 1 }); </script>

{% endhighlight %}

Page count in series axis

The seriesPageCount property is used to specify the total page count in the series axis.

{% highlight js %}

<script type="text/javascript"> $("#PivotPager1").ejPivotPager({ seriesPageCount: 1 }); </script>

{% endhighlight %}

Target control ID in pivot pager

The targetControlID property is used to specify the ID of the target element for which paging needs to be done.

{% highlight js %}

<script type="text/javascript"> $("#PivotPager1").ejPivotPager({ targetControlID: "PivotGrid1" }); </script>

{% endhighlight %}

Localization in pivot pager

The locale property is used to set the localized language for the pivot pager widget.

{% highlight js %}

<script type="text/javascript"> $("#PivotPager1").ejPivotPager({ locale: "en-US" }); </script>

{% endhighlight %}

Modes in pivot pager

The mode property is used to set the pager mode for the pivot pager. The following table will explain the available modes in the pivot pager widget.

Mode	Description
Both	To set both categorical and series pager for paging.
Categorical	To set only categorical pager for paging.
Series	To set only series pager for paging.

{% highlight js %}

<script type="text/javascript"> $("#PivotPager1").ejPivotPager({ mode: ej.PivotPager.Mode.Series }); </script>

{% endhighlight %}

Pivot Schema Designer

Public Methods

Refresh the pivot schema designer with the report

The refreshControl method is used to refresh the pivot schema designer component with the report available at that instant.

{% highlight html %}

<script> $("#PivotSchemaDesigner1").ejPivotSchemaDesigner(); var schemaObj = $("#PivotSchemaDesigner1").data("ejPivotSchemaDesigner"); schemaObj.refreshControl(); </script>

{% endhighlight %}

Events

Invoking event in client-side after service invoke

The afterServiceInvoke event is triggered when it is reached client-side after the AJAX request.

{% highlight javascript %}

$("#PivotSchemaDesigner1").ejPivotSchemaDesigner({

        //after service invoke event
        afterServiceInvoke: function(args) {

        },

        //...
    });

{% endhighlight %}

Invoking event in client-side before service invoke

The beforeServiceInvoke event is triggered before any AJAX request is passed from the pivot schema designer to service methods.

{% highlight javascript %}

$("#PivotSchemaDesigner1").ejPivotSchemaDesigner({

        //before service invoke event
        beforeServiceInvoke: function(args) {

        },

        //...
    });

{% endhighlight %}

Triggering event while dragging the field

The dragMove event is triggered when a field in the pivot schema designer is started to drag.

{% highlight javascript %}

$("#PivotSchemaDesigner1").$("#PivotSchemaDesigner1") ({ // drag move event dragMove: function (args) {} });

{% endhighlight %}

Members

Localization in pivot schema designer

The locale property is used to set the localized language for the pivot schema designer widget.

{% highlight js %}

<script type="text/javascript"> $("#PivotSchemaDesigner1").ejPivotSchemaDesigner({ locale: "en-US" }); </script>

{% endhighlight %}

Setting custom theme

You can render the pivot schema designer with one of the built-in themes using the cssClass property.

{% highlight html %}

<script type="text/javascript">
    $(function() {
        $("#PivotSchemaDesigner1").ejPivotSchemaDesigner ({
          cssClass: "gradient-lime"
        });

    });
</script>

{% endhighlight %}