The wijmo.olap module contains controls that allow you to add
Excel-like pivot table and pivot chart features to your JavaScript
applications.
Follow these steps to add wijmo.olap to your applications:
Add a PivotPanel control.
Add one or more PivotGrid and PivotChart controls to the page,
and connect them to the PivotPanel by setting their itemsSource
property to the PivotPanel control.
Set the PivotPanel's itemsSource property to an array containing the
raw data that you want to analyze, or to a string containing the URL of a
data service based on our
WebAPI.
Using data services is the recommended way to work with large data sets
because it offloads all analysis work to the server, eliminating the need
to download massive amounts of data.
See this sample's readme file
for details.
(Optional) Add the code to print, export, save and load views, and to view the definitions.
On doing this, a list of available fields will show in the PivotPanel, and
users will be able to drag fields between summary areas to generate data summaries
(also known as "views").
Users can also configure the fields by setting their headers, summary functions,
filters, and formats.
Users can analyze the data and print the results, export them to XLSX or PDF,
and save the view definitions to be re-used later.
Here is a simple example:
Add a PivotPanel and a PivotGrid to the page
The PivotPanel control is similar to Microsoft Excel's "field list" window
that is associated with pivot tables and charts.
Add fields to the view by dragging or using checkboxes.
Remove fields from the view by dragging them back to the top area, or by using their
context menu.
Configure the fields by using their context menu. You can modify their header, summary function,
and format. Note that the format is used while grouping the data. You can, therefore, group the data
by day, month, quarter or year by changing the format of the date fields for instance.
The PivotGrid control extends the FlexGrid control to support pivoting features. These
include custom cell merging, hierarchical row and column groups, and a custom
context menu, which allows users to configure the pivot fields and to drill-down
into the data items that were used in the computation of specific summary cells.
The PivotPanel control has properties that allow you to customize
the view. Use the controls below to modify the value of some properties
and see their effect:
The PivotChart control provides a graphical visualization of the results.
It is similar to Excel's pivot charts, including support for multiple chart
types and hierarchical axes.
To use the PivotChart control, connect it to a PivotPanel using
the itemsSource property:
The PivotGrid control extends the FlexGrid, so you can export it to any of
the formats supported by the extension modules provided with the FlexGrid. The
list of supported formats includes XLSLX, CSV, and PDF.
For example, the button below creates an Excel file with three sheets: the current view,
a transposed version of the current view, and the raw data:
// export pivot table and raw data to Excel
export() {
var ng = this.thePanel.engine;
// create book with current view
var book = wjcGridXlsx.FlexGridXlsxConverter.save(this.pivotGrid, {
includeColumnHeaders: true,
includeRowHeaders: true
});
book.sheets[0].name = 'Main View';
this.addTitleCell(book.sheets[0], this.getViewTitle(ng));
// add sheet with transposed view
this.transposeView(ng);
var transposed = wjcGridXlsx.FlexGridXlsxConverter.save(this.pivotGrid, {
includeColumnHeaders: true,
includeRowHeaders: true
});
transposed.sheets[0].name = 'Transposed View';
this.addTitleCell(transposed.sheets[0], this.getViewTitle(ng));
book.sheets.push(transposed.sheets[0]);
this.transposeView(ng);
// add sheet with raw data (unless there's too much data)
if (this.rawGrid.rows.length < 20000) {
var raw = wjcGridXlsx.FlexGridXlsxConverter.save(this.rawGrid, {
includeColumnHeaders: true,
includeRowHeaders: false
});
raw.sheets[0].name = 'Raw Data';
book.sheets.push(raw.sheets[0]);
}
// save the book
book.save('wijmo.olap.xlsx');
}
The PivotGrid control extends the FlexGrid, so you can customize the display
of the grid cells using the formatItem event and modifying the content of each
cell with complete flexibility.
For example, the PivotGrid below uses colors and icons similar to the ones in Excel's
icon sets to show how sales changed from quarter to quarter:
Notice that in this example we only have one pre-defined view, and don't need a PivotPanel
at all. Instead, the code creates a PivotEngine directly and uses that as the grid's
itemsSource.