### Introducing wijmo.olap Server

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:

1. Add wijmo and wijmo.olap module references to the page.
2. Add a PivotPanel control.
3. 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.
4. 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.
5. (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.

1. Add fields to the view by dragging or using checkboxes.
2. Remove fields from the view by dragging them back to the top area, or by using their context menu.
3. 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.

Collapse rows/columns to level:
HTML JS X
`<div class="mdl-grid"> <div class="mdl-cell mdl-cell--4-col"> <div id="pivotPanel"></div> </div> <div class="mdl-cell mdl-cell--8-col"> <div id="pivotGrid"></div> </div> </div>`
`// create PivotPanel and PivotGrid var app = {}; app.panel = new wijmo.olap.PivotPanel('#pivotPanel'); app.pivotGrid = new wijmo.olap.PivotGrid('#pivotGrid', { itemsSource: app.panel }); // configure the PivotPanel's initial view var ng = app.panel.engine; ng.itemsSource = app.dataSets[0].value; ng.rowFields.push('Product', 'Country'); ng.valueFields.push('Sales', 'Downloads');`

You can use the viewDefinition property to save and restore view definitions, as shown in the following example:

Using viewDefinition property, you can also build a list of pre-defined views for the user to pick from, as shown in the following example:

HTML JS X
`<button class="mdl-button mdl-js-button mdl-button--raised mdl-button--colored mdl-js-ripple-effect" onclick="app.saveView()"> Save View </button> <button class="mdl-button mdl-js-button mdl-button--raised mdl-button--colored mdl-js-ripple-effect" onclick="app.loadView()"> Load View </button> <ul id="views">`
`// save/restore view definitions app.saveView = function () { var ng = app.panel.engine; if (ng.isViewDefined) { localStorage.viewDefinition = ng.viewDefinition; } } app.loadView = function () { if (localStorage.viewDefinition) { var ng = app.panel.engine; ng.viewDefinition = localStorage.viewDefinition; } } // populate list of pre-defined views var viewList = document.getElementById('views'); for (var i = 0; i < app.viewDefs.length; i++) { var li = wijmo.createElement('<li><a href="" index="' + i + '">' + app.viewDefs[i].name + '</a></li>'); viewList.appendChild(li); } // apply pre-defined views viewList.addEventListener('click', function (e) { if (e.target.tagName == 'A') { app.setProperty('data', app.dataSets[3].value); var index = parseInt(e.target.getAttribute('index')); ng.viewDefinition = app.viewDefs[index].def; e.preventDefault(); } });`

#### Configure the PivotPanel properties

The PivotPanel control has properties that allow you to customize the view. Use the controls below to modify the value of some of these properties and see their effect:

Dataset
Row totals
Column totals
Show Zeros
Totals Before Data

HTML JS X
`<dl class="dl-horizontal"> <dt>Dataset<dt> <dd><div id="cmbDataSets"></div></dd> <dt>Row totals<dt> <dd><div id="cmbRowTotals"></div></dd> <dt>Column totals<dt> <dd><div id="cmbColTotals"></div></dd> <dt>Show Zeros</dt> <dd><input id="chkShowZeros" type="checkbox" /></dd> <dt>Totals Before Data</dt> <dd><input id="chkTotalsBeforeData" type="checkbox" /></dd> </dl>`
`app.cmbDataSets = new wijmo.input.ComboBox('#cmbDataSets', { itemsSource: app.dataSets, displayMemberPath: 'name', selectedValuePath: 'value', selectedIndexChanged: function (s, e) { app.setProperty('data', s.selectedValue); } }); app.cmbRowTotals = new wijmo.input.ComboBox('#cmbRowTotals', { itemsSource: app.showTotals, displayMemberPath: 'name', selectedValuePath: 'value', selectedIndexChanged: function (s, e) { app.setProperty('showRowTotals', s.selectedValue); } }); app.cmbColTotals = new wijmo.input.ComboBox('#cmbColTotals', { itemsSource: app.showTotals, displayMemberPath: 'name', selectedValuePath: 'value', selectedIndexChanged: function (s, e) { app.setProperty('showColTotals', s.selectedValue); } }); app.chkShowZeros = document.getElementById('chkShowZeros'); app.chkShowZeros.addEventListener('click', function (e) { app.setProperty('showZeros', app.chkShowZeros.checked); }); app.chkTotalsBeforeData = document.getElementById('chkTotalsBeforeData'); app.chkTotalsBeforeData.addEventListener('click', function (e) { app.setProperty('totalsBeforeData', app.chkTotalsBeforeData.checked); });`

#### Show the results in a PivotChart

The PivotChart control provides a graphical visualization of the results. It is similar to Microsoft Excel's pivot charts, and includes support for multiple chart types and hierarchical axes.

To use the PivotChart control, connect it to a PivotPanel using the itemsSource property.

Chart Type
Show Legend
Show Title

Please create a view in order to see the chart.

HTML JS X
`<dl class="dl-horizontal"> <dt>Chart Type</dt> <dd><div id="cmbChartType"></div></dd> <dt>Show Legend</dt> <dd><div id="cmbShowLegend"></div></dd> <dt>Show Title</dt> <dd><input id="chkShowTitle" type="checkbox" checked="checked" /></dd> </dl> <div id="pivotChart"></div>`
`app.pivotChart = new wijmo.olap.PivotChart('#pivotChart', { itemsSource: app.panel, showLegend: 'Auto' }); app.cmbChartType = new wijmo.input.ComboBox('#cmbChartType', { itemsSource: app.chartTypes, displayMemberPath: 'name', selectedValuePath: 'value', selectedIndexChanged: function (s, e) { app.pivotChart.chartType = s.selectedValue; } }); app.cmbShowLegend = new wijmo.input.ComboBox('#cmbShowLegend', { itemsSource: app.legendVisibility, displayMemberPath: 'name', selectedValuePath: 'value', selectedValue: app.pivotChart.showLegend, selectedIndexChanged: function (s, e) { app.pivotChart.showLegend = s.selectedValue; } }); app.chkShowTitle = document.getElementById('chkShowTitle'); app.chkShowTitle.addEventListener('click', function (e) { app.pivotChart.showTitle = e.target.checked; });`

#### View and edit the source data

Raw data is not available because the PivotEngine is connected to a server.

The following pivot view is live. If you edit any of the n items, the pivot view will be updated automatically. (This grid is read-only because it is bound to a read-only data source.)

HTML JS X
`<div id="rawGrid" style="border:none"></div>`
`app.rawGrid = new wijmo.grid.FlexGrid('#rawGrid'); app.rawGridFilter = new wijmo.grid.filter.FlexGridFilter(app.rawGrid);`

#### Export the results to Excel

The PivotGrid control extends the FlexGrid control, so you can export it to any of the formats supported by the extension modules provided with the FlexGrid. The list of the supported formats includes XLSLX, CSV, and PDF.

For example, the button below creates an Excel file with up to three sheets: the current view, a transposed version of the current view, and the raw data:

HTML JS X
`<button class="mdl-button mdl-js-button mdl-button--raised mdl-button--colored mdl-js-ripple-effect" onclick="app.export()"> Export to XLSX </button>`
`app.export = function () { // create book with current view var book = wijmo.grid.xlsx.FlexGridXlsxConverter.save(app.pivotGrid, { includeColumnHeaders: true, includeRowHeaders: true }); book.sheets[0].name = 'Main View'; // add sheet with raw data if (app.rawGrid.rows.length < 20000) { var raw = wijmo.grid.xlsx.FlexGridXlsxConverter.save(app.rawGrid, { includeColumnHeaders: true, includeRowHeaders: false }); raw.sheets[0].name = 'Raw Data'; book.sheets.push(raw.sheets[0]); } // save book book.save('wijmo.olap.xlsx'); }`

#### Customize the PivotGrid cells

The PivotGrid control extends the FlexGrid control, so you can customize the display of the grid cells using the formatItem event and modify the content of each cell with complete flexibility.

For example, the PivotGrid below uses colors and icons, similar to the ones in Microsoft Excel's icon sets, to show how sales changed from quarter to quarter:

HTML JS X
`<div id="formattedGrid" style="border:none"> </div>`
`// create a PivotEngine with a custom view var ngFmt = new wijmo.olap.PivotEngine({ autoGenerateFields: false, itemsSource: getSimpleDataSet(10000), showColumnTotals: wijmo.olap.ShowTotals.GrandTotals, showRowTotals: wijmo.olap.ShowTotals.None, fields: [ { binding: 'product', header: 'Product' }, { binding: 'date', header: 'Date', format: 'yyyy \"Q\"q' }, { binding: 'sales', header: 'Sales', format: 'n0' }, { binding: 'sales', header: 'Diff', format: 'p0', showAs: wijmo.olap.ShowAs.DiffRowPct } ] }); ngFmt.rowFields.push('Date'); ngFmt.columnFields.push('Product'); ngFmt.valueFields.push('Sales', 'Diff'); // create a PivotGrid to show the custom view app.formattedGrid = new wijmo.olap.PivotGrid('#formattedGrid', { itemsSource: ngFmt, formatItem: function (s, e) { if (e.panel == s.cells && e.col % 2 == 1) { var value = s.getCellData(e.row, e.col), color = '#d8b400', glyph = 'circle'; if (value != null) { if (value < 0) { // negative variation color = '#9f0000'; glyph = 'down'; } else if (value > 0.05) { // positive variation color = '#4c8f00'; glyph = 'down'; } e.cell.style.color = color; e.cell.innerHTML += ' <span style="font-size:120%" class="wj-glyph-' + glyph + '"></span>'; } } } });`

Note that in this example, we only have one pre-defined view, and do not need a PivotPanel. Instead, the code creates a PivotEngine directly and uses it as the grid's itemsSource.