wijmo.olap Introduction

Introducing wijmo.olap

The wijmo.olap module contains controls that allow you to add Microsoft Excel-like pivot table and pivot chart features to your applications. To use it, follow these steps:

Add references to the wijmo and wijmo.olap modules to the page.
Add a PivotPanel control to the page.
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.
(Optional) Add code to print, export, save and load views and view definitions.

That is all. The PivotPanel will show a list of the fields available, and users will be able to drag fields between summary areas to generate data summaries (AKA "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 the results to XLSX or PDF, and save 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 the checkboxes.
Remove fields from the view by dragging them back to the top area or by using their context menu.
Configure fields using their context menu. You can modify their header, summary function, and format. Note that the format is used when grouping the data, so you can group data by day, month, quarter or year by changing the format of the date fields for example.

The PivotGrid control extends the FlexGrid to support pivoting features including custom cell merging, hierarchical row and column groups, and a custom context menu that 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.

HTML JS X


<div class="mdl-grid" id="theView">
    <div class="mdl-cell mdl-cell--4-col">
        <wj-pivot-panel
            control="thePanel"
            :items-source="rawData"
            :initialized="initPanel">
        </wj-pivot-panel>
    </div>
    <div class="mdl-cell mdl-cell--8-col">
        <wj-pivot-grid
            control="pivotGrid"
            :items-source="thePanel"
            show-selected-headers="All">
        </wj-pivot-grid>
    </div>
</div>


// app view (main component)
new Vue({
    el: '#app',
    data: {

        // define sample data sets
        dataSets: [
            { name: 'Simple (1,000 items)', value: getSimpleDataSet(1000) },
            // ...
        ],
        rawData: [],
    },
    methods: {

        // initialize the view definition
        initPanel: function (sender, e) {
            var ng = sender.engine;
            this.rawData = this.dataSets[0].value;
            ng.itemsSource = this.rawData;
            ng.rowFields.push('Product', 'Country');
            ng.valueFields.push('Sales', 'Downloads');
            ng.showRowTotals = wijmo.olap.ShowTotals.Subtotals;
            ng.showColumnTotals = wijmo.olap.ShowTotals.Subtotals;
        }
    }
});

Use the viewDefinition property to save and restore view definitions. For example:

Or build a list of pre-defined views for the user to pick from. For example:

{{ view.name }}

HTML JS X


<button
    class="mdl-button mdl-js-button mdl-button--raised mdl-button--colored mdl-js-ripple-effect"
    @click="saveView()">
    Save View
</button>
<button
    class="mdl-button mdl-js-button mdl-button--raised mdl-button--colored mdl-js-ripple-effect"
    @click="loadView()">
    Load View
</button>

<ul>
    <li v-for="view in viewDefs">
        <a href="#theView" @click="loadView(view.def)">
            {{ view.name }}
        </a>
    </li>
</ul>


// app view (main component)
new Vue({
    el: '#app',
    data: {

        // define sample data sets
        dataSets: [
            { name: 'Simple (1,000 items)', value: getSimpleDataSet(1000) },
            // ...
        ],
        rawData: [],
    },
    methods: {

        // save/restore view definitions
        saveView: function () {
            var ng = this.thePanel.engine;
            if (ng.isViewDefined) {
                localStorage.viewDefinition = ng.viewDefinition;
            }
        },
        loadView: function (def) {
            var ng = this.thePanel.engine;
            if (def) {
                // load pre-defined view (against specific dataset)
                this.rawData = this.dataSets[3].value;
                ng.itemsSource = this.rawData;
                ng.viewDefinition = def;
            } else if (localStorage.viewDefinition) {
                // load view from localStorage (whatever the user saved)
                ng.viewDefinition = localStorage.viewDefinition;
            }
        }
    }
});

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 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>
        <wj-combo-box 
            :items-source="dataSets"
            display-member-path="name"
            selected-value-path="value"
            :selected-index-changed="dataSetChanged">
        </wj-combo-box>
    </dd>
    <dt>Row totals</dt>
    <dd>
        <wj-combo-box
            :items-source="showTotals"
            text="Subtotals"
            :text-changed="showRowTotalsChanged">
        </wj-combo-box>
    </dd>
    <dt>Column totals</dt>
    <dd>
        <wj-combo-box 
            :items-source="showTotals"
            text="Subtotals"
            :text-changed="showColumnTotalsChanged">
        </wj-combo-box>
    </dd>
    <dt>Show Zeros</dt>
    <dd>
        <input v-model="thePanel.engine.showZeros" type="checkbox" />
    </dd>
    <dt>Totals Before Data</dt>
    <dd>
        <input v-model="thePanel.engine.totalsBeforeData" type="checkbox" />
    </dd>
</dl>


// no code required!

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


<div v-show="thePanel && thePanel.isViewDefined">
    <dl class="dl-horizontal">
        <dt>Chart Type</dt>
        <dd>
            <wj-combo-box 
                :items-source="chartTypes"
                text="Column"
                :text-changed="chartTypeChanged">
            </wj-combo-box>
        </dd>
    </dl>
    <dt>Show Legend</dt>
    <dd>
        <wj-combo-box 
            :items-source="legendVisibility"
            text="Auto"
            :text-changed="showLegendChanged">
        </wj-combo-box>
    </dd>
    <dt>Show Title</dt>
    <dd>
        <input v-model="theChart.showTitle" type="checkbox" />
    </dd>
    <wj-pivot-chart 
        control="theChart"
        :items-source="thePanel">
    </wj-pivot-chart>
</div>
<p v-else>
    Please create a view in order to see the chart.</p>


// app view (main component)
new Vue({
    el: '#app',
    data: {
        // ...
        chartTypes: 'Column,Bar,Scatter,Line,Area,Pie'.split(','),
        legendVisibility: 'Always,Never,Auto'.split(','),
    },
    methods: {
        // ...
        chartTypeChanged: function(s, e) {
            this.theChart.chartType = s.text;
        },
        showLegendChanged: function (s, e) {
            this.theChart.showLegend = s.text;
        },
        showTitleChanged: function (e) {
            this.theChart.showTitle = e.target.checked;
        },
    }
});

View and edit the source data

The pivot view is live. If you edit any of the {{ rawData.items.length | wj-format 'c0' }} 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


<wj-flex-grid
    style="border:none"
    control="rawGrid"
    :items-source="rawData"
    show-selected-headers="All">
    <wj-flex-grid-filter></wj-flex-grid-filter>
</wj-flex-grid>


// no code required!

Export the results to Spreadsheet

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 XLSX, CSV, and PDF.

For example, the button below creates an Excel file with two sheets: the current view and the raw data (if the data has 10,000 items or less):

HTML JS X


<button
    class="mdl-button mdl-js-button mdl-button--raised mdl-button--colored mdl-js-ripple-effect"
    @click="exportXlsx()">
    Export to XLSX
</button>


// app view (main component)
new Vue({
    el: '#app',
    data: {

        // define sample data sets
        dataSets: [
            { name: 'Simple (1,000 items)', value: getSimpleDataSet(1000) },
            // ...
        ],
        rawData: [],
    },
    methods: {

        // export pivot table and raw data to Excel
        exportXlsx: function () {
            var ng = this.thePanel.engine;

            // create book with current view
            var book = wijmo.grid.xlsx.FlexGridXlsxConverter.save(this.pivotGrid, {
                includeColumnHeaders: true,
                includeRowHeaders: true
            });
            book.sheets[0].name = 'Main View';
            addTitleCell(book.sheets[0], getViewTitle(ng));

            // add sheet with raw data (unless there's too much data)
            if (this.rawGrid.rows.length <= 10000) {
                var raw = wijmo.grid.xlsx.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');
        }
    }
});

Customize the PivotGrid cells

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 Microsoft Excel's icons set to show how sales changed from quarter to quarter:

HTML JS X


<wj-pivot-grid 
    :items-source="ngFmt"
    :format-item="formatItem"
    show-selected-headers="All"
    style="border:none">
</wj-pivot-grid>


    // app view (main component)
    new Vue({
        el: '#app',
        data: {

            // custom Olap engine
            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 }
                ],
                rowFields: ['Date'],
                columnFields: ['Product'],
                valueFields: ['Sales', 'Diff']
            })
        },
        methods: {

            // custom item formatter for PivotGrid
            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 require a PivotPanel at all. Instead, the code creates a PivotEngine directly and uses that as the grid's itemsSource.