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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<div id="PivotClient1"></div>
<script>
$("#PivotClient1").ejPivotClient({
beforeServiceInvoke: function(args) { }
});
</script>
Triggering event before exporting
The beforeExport
event is triggered before exporting the control.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
<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.
$("#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.
$("#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.
$("#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.
$("#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.
<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.
<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.
<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.
$("#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.
$("#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.
$("#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.
$("#PivotClient1").ejPivotClient({
maxNodeLimitInMemberEditor: 1500
});
Setting custom theme
You can render the pivot client with one of the built-in themes using the cssClass
property.
<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.
<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>
$("#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.
<div id="PivotClient1"> </div>
$("#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.
<div id="PivotClient1"> </div>
$("#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.
<div id="PivotClient1"> </div>
$("#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
istrue
.
Enabling/Disabling multilevel labels
You can enable or disable the enableMultiLevelLabels
property of the pivot chart through the renderSuccess
event of the pivot client.
<div id="PivotClient1"> </div>
$("#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.
<div id="PivotClient1"> </div>
$("#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.
<div id="PivotClient1"> </div>
$("#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.
<div id="PivotClient1"> </div>
$("#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. |