How To in JQuery PivotClient widget

13 Feb 201924 minutes to read

Public methods

Refresh the pivot client with specified page number

The refreshPagedPivotClient method is used to re-render the pivot client component with the given axis and page number.

html

<div id="PivotClient1"></div>

<script>
    $("PivotClient1").ejPivotClient();
    var clientObj = $("#PivotClient1").data("ejPivotClient");
    clientObj.refreshPagedPivotClient("series", 24);//Loads the 24th page in series axis
</script>

Refresh the pivot client with modified report

The refreshControl method is used to re-render the pivot client control with the report at that instant.

html

<div id="PivotClient1"></div>

<script>
    $("PivotClient1").ejPivotClient();
    var clientObj = $("#PivotClient1").data("ejPivotClient");
    clientObj.refreshControl();
</script>

Generating JSON records to render the control

The generateJSON method is used to render the pivot chart and pivot grid controls in the pivot client with the provided JSON data.

html

<div id="PivotClient1"></div>

<script>
    $("PivotClient1").ejPivotClient();
    var clientObj = $("#PivotClient1").data("ejPivotClient");
    clientObj.generateJSON(clientObj.getJSONRecords());//jsonData object holds the data in JSON format required to render the control
</script>

Explicit asynchronous invoke

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

html

<div id="PivotClient1"></div>

<script>
    $("PivotClient1").ejPivotClient();
    var clientObj = $("#PivotClient1").data("ejPivotClient");
    clientObj.doAjaxPost("POST", "/OlapService/Initialize", { "key", "Hello World" }, "renderControlSuccess", null);
</script>

Destroying the object of pivot client

The destroy method is used to destroy the pivot client widget associated events bound using “this._on” and bring the control to pre-init state.

html

<div id="PivotClient1"></div>

<script>
    $("PivotClient1").ejPivotClient();
    var clientObj = $("#PivotClient1").data("ejPivotClient");
    clientObj.destroy();
</script>

Getting JSON records from control object

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

html

<div id="PivotClient1"></div>

<script>
    $("PivotClient1").ejPivotClient();
    var clientObj = $("#PivotClient1").data("ejPivotClient");
    var jsonRecords = clientObj.getJSONRecords();
</script>

Setting JSON records to control object

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

html

<div id="PivotClient1"></div>

<script>
    $("PivotClient1").ejPivotClient();
    var clientObj = $("#PivotClient1").data("ejPivotClient");
    clientObj.setJSONRecords(clientObj.getJSONRecords());
</script>

Refresh the pivot client with paging

The refreshPagedPivotClientSuccess method is used to update the pivot client component with the JSON data that is fetched from the service while navigating between pages.

html

<div id="PivotClient1"></div>

<script>
    $("PivotClient1").ejPivotClient();
    var clientObj = $("#PivotClient1").data("ejPivotClient");
    clientObj.refreshPagedPivotClientSuccess(clientObj.getJSONRecords()); //jsonData object holds the JSON data required to render a specific page of the control.
</script>

Getting active tab from pivot client

The getActiveTab method is used to return the control tab string that displays currently in the pivot client.

html

<div id="PivotClient1"></div>

<script>
    $("PivotClient1").ejPivotClient();
    var clientObj = $("#PivotClient1").data("ejPivotClient");
    var report = clientObj.getActiveTab();
</script>

Getting the current OLAP report

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

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient();
    var pivotClientObj = $("#PivotClient1").data("ejPivotClient");
    var report = pivotClientObj.getOlapReport();
</script>

Setting the OLAP report

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

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient();
    var pivotClientObj = $("#PivotClient1").data("ejPivotClient");
    var report = pivotClientObj.setOlapReport(olapReportObj);
</script>

Explicit asynchronous post

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

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient();
    var pivotClientObj = $("#PivotClient1").data("ejPivotClient");
    pivotClientObj.doPostBack("/PivotService/Initialize", { "key", "Hello World" });
</script>

Events

Triggering event before saving the reports

The saveReport event is triggered to save the current collection of reports.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        saveReport: function(args) { }
    });
</script>

Triggering event before fetching the report from database

The fetchReport event is triggered before fetching the report collection from the storage.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        fetchReport: function(args) { }
    });
</script>

Triggering event on value cell click

The valueCellHyperlinkClick event is triggered when you click any value cell in the pivot grid.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        valueCellHyperlinkClick: function (args) { }
    });
</script>

Triggering event on row header click

The rowHeaderHyperlinkClick event is triggered when you click any row header cell in the pivot grid.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        rowHeaderHyperlinkClick: function (args) { }
    });
</script>

Triggering event on column header click

The columnHeaderHyperlinkClick event is triggered when you click any column header cell in the pivot grid.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        columnHeaderHyperlinkClick: function (args) { }
    });
</script>

Triggering event on summary cell click

The summaryCellHyperlinkClick event is triggered when you click any summary cell in the pivot grid.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        summaryCellHyperlinkClick: function (args) { }
    });
</script>

Triggering event on cell right-click in pivot grid

The cellContext event is triggered when you right-click any cell in the pivot grid.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        cellContext: function (args) { }
    });
</script>

Triggering event before selecting the cells in pivot grid

The cellSelection event is triggered when you select/click any cell in the pivot grid.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        cellSelection: function (args) { }
    });
</script>

Triggering event before editing the cell in pivot grid

The cellEdit event is triggered when the value cell is edited in the pivot grid.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        cellEdit: function (args) { }
    });
</script>

Triggering event on cell double-click in pivot grid

The cellDoubleClick event is triggered when you double-click any cell in the pivot grid. It can be enabled through the enableCellDoubleClick property.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({

        enableCellDoubleClick: true,

        cellDoubleClick: function (args) { }
    });
</script>

Triggering event on point region click in PivotChart

The pointRegionClick event is triggered when you click any chart series points in the pivot chart.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        pointRegionClick: function (args) { }
    });
</script>

Triggering event before rendering of axes labels in pivot chart

The axesLabelRendering event is triggered before rendering the chart label in the pivot chart.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        axesLabelRendering: function (args) { }
    });
</script>

Invoking event in client-side after service invoke

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

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        afterServiceInvoke: function(args) { }
    });
</script>

Invoking event in client-side before service invoke

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

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        beforeServiceInvoke: function(args) { }
    });
</script>

Triggering event before exporting

The beforeExport event is triggered before exporting the control.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        beforeExport: function(args) { }
    });
</script>

Triggering event after performing drill operation in pivot grid

The gridDrillSuccess event is triggered when performing the drill up/down operation on row/columns headers.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        gridDrillSuccess: function (args) { }
    });
</script>

Triggering event after performing drill operation in pivot chart

The chartDrillSuccess event is triggered when performing the drill operation on the chart series.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        chartDrillSuccess: function (args) { }
    });
</script>

Triggering event after performing drill operation in pivot tree map

The treeMapDrillSuccess event is triggered when performing the drill operation on the tree map.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        treeMapDrillSuccess: function (args) { }
    });
</script>

Triggering event before the pivot schema designer loaded

The schemaLoad event is triggered before rendering the pivot schema designer.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        schemaLoad: function (args) { }
    });
</script>

Triggering event before the pivot tree map loaded

The treeMapLoad event is triggered before rendering the pivot tree map.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        treeMapLoad: function (args) { }
    });
</script>

Triggering event before the pivot chart loaded

The chartLoad event is triggered before rendering the pivot chart.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        chartLoad: function (args) { }
    });
</script>

Triggering event before the pivot client loaded

The load event is triggered while you initiate loading of the widget.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({
        load: function (args) { }
    });
</script>

Triggering event on successful completion of AJAX request

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

javascript

$("#PivotClient1").ejPivotClient ({

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

        },

            //...
    });

Triggering event after the control rendered

The renderComplete event is triggered after the pivot client is rendered completely.

javascript

$("#PivotClient1").ejPivotClient({

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

        },

            //...
    });

Triggering event while loading the saved report

The loadReport event is triggered while loading the saved reports collection from the database.

javascript

$("#PivotClient1").ejPivotClient({

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

        },

            //...
    });

Triggering event on failure of AJAX request

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

javascript

$("#PivotClient1").ejPivotClient({

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

        },

            //...
    });

Members

Grid layout in pivot client

The gridLayout is used to set the summary layout for the pivot grid OLAP server-end. The following are the ways in which the summary can be positioned: normal summary (bottom), top summary, no summary, and Microsoft Excel summary.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({ gridLayout: ej.PivotGrid.Layout.NoSummaries });
</script>

Showing unique name on pivot button

The showUniqueNameOnPivotButton property allows you to show a unique name on the pivot button for the OLAP datasource.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({ showUniqueNameOnPivotButton: true });
</script>

Showing report collection in toolbar

The showReportCollection property allows you to load the saved report collection from the database and show it in the toolbar.

html

<div id="PivotClient1"></div>

<script>
    $("#PivotClient1").ejPivotClient({ showReportCollection: true });
</script>

Getting JSON format by triggering cell click event

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

javascript

$("#PivotClient1").ejPivotClient({

        enableCellClick: true,

        cellClick: function (args) {

        }

    });

Enabling XMLHttpRequest object for CORS

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

javascript

$("#PivotClient1").ejPivotClient({

        enableXHRCredentials: true

    });

Enabling complete data export on pivot client

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

javascript

$("#PivotClient1").ejPivotClient({

          enableCompleteDataExport: true

 });

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.

javascript

$("#PivotClient1").ejPivotClient({

         maxNodeLimitInMemberEditor: 1500

    });

Setting custom theme

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

html

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

        });

    </script>

Customizing Pivot Grid properties in Pivot Client

Applying conditional formatting in Pivot Client

The conditional formatting in the pivot client allows you to highlight the particular cells in the pivot grid with certain color, font-style, font-family, etc., based on the applied condition. Also, the condition can be applied for certain measure.
The conditional formatting is enabled by setting the enableConditionalFormatting property to true in the renderSuccess event and the formatting dialog is launched when the openConditionalFormattingDialog method is invoked.

html

<div id="sampleProperties">
   <div class="prop-grid">
      <div class="row">
        <button class="customBtn" id="Btn1">
           Apply
         </button>
         <button class="customBtn" id="Btn2">
           Reset
        </button>
       </div>
    </div>
</div>

js

$("#PivotClient").ejPivotClient({
//Datasource bound to PivotClient control.
renderSuccess: "successEvent"
});
function successEvent(args) {
//…
   this._pivotGrid.model.enableConditionalFormatting = true;
   pivotGridObj = this._pivotGrid;
   $("#Btn1").ejButton({
   roundedCorner: true,
   size: "small",
   type: ej.ButtonType.Button,
   click: "ApplyChanges"
 });
   $("#Btn2").ejButton({
   roundedCorner: true,
   size: "small",
   type: ej.ButtonType.Button,
   click: "Reset"
 });
}
function ApplyChanges() {
  if (pivotGridObj.model.enableConditionalFormatting) {
  pivotGridObj.openConditionalFormattingDialog();
}
}
function Reset() {
  pivotGridObj._removeCellFormatting(true, false);
  pivotGridObj._list = ["Add New"];
}

Enabling/Disabling Tooltip

You can enable or disable the Tooltip in the pivot grid using the renderSuccess event of pivot client.

html

<div id="PivotClient1"> </div>

javascript

$("#PivotClient").ejPivotClient({
  //Datasource bound to PivotClient control.
    renderSuccess: "successEvent"
});
function successEvent(args) {
    this._pivotGrid.model.enableToolTip = false;
}

NOTE

The default value of the enableToolTip is true.

Enabling/Disabling frozen headers

You can enable or disable the frozenHeaderSettings of the pivot grid through the renderSuccess event of pivot client.

html

<div id="PivotClient1"> </div>

javascript

$("#PivotClient").ejPivotClient({
  //Datasource bound to PivotClient control.
    renderSuccess: "successEvent"
});
function successEvent(args) {
    this._pivotGrid.model.frozenHeaderSettings = {
            enableFrozenHeaders : true
    }

    //this._pivotGrid.model.frozenHeaderSettings: {
    //        enableFrozenRowHeaders: true      //To Freeze the Row headers only
    //}

    //this._pivotGrid.model.frozenHeaderSettings: {
    //        enableFrozenColumnHeaders: true  //To Freeze the Column headers only
    //}

    //this._pivotGrid.model.frozenHeaderSettings: {
    //        scrollerSize: 18  // To set the scroller size
    //}
}

Customizing Pivot Chart properties in Pivot Client

Enabling/Disabling legend

You can enable or disable the legend of the pivot chart through the renderSuccess event of pivot client.

html

<div id="PivotClient1"> </div>

javascript

$("#PivotClient").ejPivotClient({
  //Datasource bound to PivotClient control.
    renderSuccess: "successEvent"
});
function successEvent(args) {
    this._pivotChart.model.legend.visible = false;
}

NOTE

Default value of the legend.visible is true.

Enabling/Disabling multilevel labels

You can enable or disable the enableMultiLevelLabels property of the pivot chart through the renderSuccess event of the pivot client.

html

<div id="PivotClient1"> </div>

javascript

$("#PivotClient").ejPivotClient({
  //Datasource bound to PivotClient control.
    renderSuccess: "successEvent"
});
function successEvent(args) {
    this._pivotChart.model.enableMultiLevelLabels = true;
}

For more details, click here

Enabling/Disabling 3D Charts

You can enable or disable the enable3D property of the pivot chart through the renderSuccess event of pivot client.

html

<div id="PivotClient1"> </div>

javascript

$("#PivotClient").ejPivotClient({
  //Datasource bound to PivotClient control.
    renderSuccess: "successEvent"
});
function successEvent(args) {
    this._pivotChart.model.enable3D = true;
}

For more details, click here.

Series customization

You can customize the series of the pivot chart by triggering the seriesRendering event in renderSuccess of pivot client.

html

<div id="PivotClient1"> </div>

javascript

$("#PivotClient").ejPivotClient({
  //Datasource bound to PivotClient control.
    renderSuccess: "successEvent"
});
function successEvent(args) {
    this._pivotChart.model.seriesRendering = "seriesRendering";
}

function seriesRendering(args) {
    this.model.series[0].points[0].fill = "aqua";
    this.model.series[0].points[0].border = {
    color: "black",
    width: 2
    };
}

Changing chart type through code-behind

You can change the chart type of the pivot chart in the renderSuccess event of pivot client.

html

<div id="PivotClient1"> </div>

javascript

$("#PivotClient").ejPivotClient({
  //Datasource bound to PivotClient control.
    renderSuccess: "successEvent"
});
function successEvent(args) {
    this._pivotChart.model.commonSeriesOptions.type = "line";
}

Setting custom name to service methods

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

Common service methods to OLAP and relational data source

Service Methods	Description
initialize	It fetches the data required to initialize the control.
nodeDropped	It fetches the data required to render the control when performing the node drop action.
toolbarServices	It fetches the data required to render the control when performing toolbar operations.
saveReport	It saves the current report to database with the specified name.
exportPivotClient	It exports the pivot grid or pivot chart or both to the selected format.
fetchReportList	It fetches the list of names of reports stored in the database.
loadReport	It loads the report with specified name from the database to the control.
removeDBReport	It is responsible to remove a report collection from the database.
renameDBReport	It is responsible for renaming the report collection in the database.
filterElement	It fetches the filtered data that is required to render the control after performing filtering.
valueSorting	It is responsible for performing value sorting operation in the pivot client.

OLAP

Service methods	Description
removeSplitButton	It fetches the drilled data required to render the pivot chart when removing an item from the report.
fetchMemberTreeNodes	It fetches the details of members to render the member editor dialog.
cubeChanged	It fetches the data that is required to render the control when changing the cube.
measureGroupChanged	It fetches the data that is required to render the control when changing the measure group.
memberExpand	It fetches the children nodes when expanding a node in the member editor.
updateReport	It updates the report in the server-side.
exportPivotClient	It exports the pivot grid or pivot chart or both with OLAP data to the selected format.
mdxQuery	It retrieves the MDX query that is formed to fetch the data at that instant.
toggleAxis	It fetches the data after interchanging the elements in row and column axes.
paging	It fetches the data when navigating between pages in the pivot client with the OLAP data.
drillThroughHierarchies	It is responsible for performing the drill through operation.
drillThroughDataTable	It is responsible for performing the drill through operation in the data table.
calculatedMember	It is used for updating the report with the calculated member.