PivotGrid with Sparklines

You can use the formatItem event to add custom content such as sparklines and sparkbars to grid cells. This example adds two extra fields to a PivotEngine and uses the formatItem event to add sparklines and sparkbars to the extra fields. The engine's getDetail method is also used retrieve the detail records for each cell and uses that data to build svg elements displayed in each cell.

import 'bootstrap.css'; import '@grapecity/wijmo.styles/wijmo.css'; import './styles.css'; import '@grapecity/wijmo.touch'; // support drag/drop on touch devices import * as wijmo from '@grapecity/wijmo'; import * as olap from '@grapecity/wijmo.olap'; import { getData } from './data'; document.readyState === 'complete' ? init() : window.onload = init; function init() { // create a PivotEngine with a custom view let ng = new olap.PivotEngine({ autoGenerateFields: false, itemsSource: getData(1000), showColumnTotals: 'Subtotals', showRowTotals: 'Subtotals', fields: [ { binding: 'product', header: 'Product', width: 100 }, { binding: 'date', header: 'Date', format: 'yyyy \"Q\"q', width: 100 }, { binding: 'sales', header: 'Sales', format: 'n0', width: 100 }, { binding: 'sales', header: 'Sparklines', width: 100 }, { binding: 'sales', header: 'Sparkbars', width: 100 } ], rowFields: ['Date'], columnFields: ['Product'], valueFields: ['Sales', 'Sparklines', 'Sparkbars'] }); // show panel let pivotPanel = new olap.PivotPanel('#pivotPanel', { itemsSource: ng }); // show summary let pivotGrid = new olap.PivotGrid('#pivotGrid', { isReadOnly: true, itemsSource: ng, formatItem: formatItem // customize the grid cells }); // use formatItem to add sparklines and/or sparkbars let maxSparkLength = 25; function formatItem(s, e) { let ng = s.engine; // we want value fields in the cells panel if (e.panel == s.cells && ng.valueFields.length) { // we want the 'Sparklines' and 'Sparkbars' value fields let field = ng.valueFields[e.col % ng.valueFields.length], item = s.rows[e.row].dataItem, binding = s.columns[e.col].binding, spark = field.header.match(/Sparklines|Sparkbars/) != null; // add/remove spark class wijmo.toggleClass(e.cell, 'spark', spark); // add sparklines if (spark) { // if we have the data, show it if (item.sparkData) { let data = item.sparkData, delta = data[data.length - 1] - data[0]; e.cell.innerHTML = field.header == 'Sparklines' ? getSparklines(item.sparkData) : getSparkbars(item.sparkData); wijmo.toggleClass(e.cell, 'spark-up', delta > 0); wijmo.toggleClass(e.cell, 'spark-down', delta < 0); } // we don't have the data yet, so go get it if (!item.sparkData) { e.cell.innerHTML = ''; setTimeout(function () { let detail = ng.getDetail(item, binding), len = detail.length; if (len > maxSparkLength) { detail = detail.slice(len - maxSparkLength); } item.sparkData = detail.map(dataItem => dataItem.sales); s.invalidate(); // invalidate to show the sparlines }); } } } } // generate sparklines as SVG function getSparklines(data) { if (!data.length) { return ''; } let svg = '<svg width="100%" height="100%">', min = Math.min.apply(Math, data), max = Math.max.apply(Math, data), x1 = 0, y1 = scaleY(data[0], min, max); for (let i = 1; i < data.length; i++) { let x2 = Math.round((i) / (data.length - 1) * 100), y2 = scaleY(data[i], min, max); svg += `<line x1="${x1}%" y1="${y1}%" x2="${x2}%" y2="${y2}%" />`; x1 = x2; y1 = y2; } svg += '</svg>'; return svg; } function getSparkbars(data) { if (!data.length) { return ''; } let svg = '<svg width="100%" height="100%">', min = Math.min.apply(Math, data), max = Math.max.apply(Math, data), base = Math.min(max, Math.max(min, 0)), basey = scaleY(base, min, max), w = Math.round(100 / data.length) - 2; for (let i = 0; i < data.length; i++) { let x = i * Math.round(100 / data.length) + 1, y = scaleY(data[i], min, max); svg += `<rect x="${x}%" width="${w}%" y="${Math.min(y, basey)}%" height="${Math.abs(y - basey)}%" />`; } svg += `<rect x="0%" width="100%" height="1" y="${basey}%" opacity="0.5" />`; svg += '</svg>'; return svg; } // min <= value <= max function scaleY(value, min, max) { return min === max ? 0 : 100 - Math.round((value - min) / (max - min) * 100); } }
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>Grapecity Wijmo OLAP Pivot Grid Sparkline</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <!-- SystemJS --> <script src="node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app'); </script> </head> <body> <div class="container-fluid"> <div class="row"> <div class="col-xs-5"> <div id="pivotPanel"></div> </div> <div class="col-xs-7"> <div id="pivotGrid"></div> </div> </div> </div> </body> </html>
// export function getData(cnt) { let year = new Date().getFullYear(), data = []; // for (let i = 0; i < cnt; i++) { data.push({ product: randomInt(0, 1) ? 'Wijmo' : 'Aoba', country: randomInt(0, 1) ? 'USA' : 'Japan', active: i % 2 == 0, date: new Date(year - randomInt(0, 2), randomInt(0, 11), randomInt(0, 27) + 1), sales: randomInt(10, 20), downloads: randomInt(10, 200) }); } // return data; } // function randomInt(min, max) { return Math.floor(Math.random() * (max - min + 1) + min); }
.wj-pivotgrid { max-height: 400px; box-shadow: 0 10px 20px rgba(0,0,0,0.19), 0 6px 6px rgba(0,0,0,0.23); } .wj-pivotgrid .wj-cell.spark { padding: 8px; overflow: visible; } .wj-pivotgrid .wj-cell.spark svg { overflow: visible; stroke: currentColor; fill: currentColor; } .wj-pivotgrid .wj-cell.spark.spark-up svg { color: #009000; /* green for up */ } .wj-pivotgrid .wj-cell.spark.spark-down svg { color: #d00000; /* red for down */ } body { margin-bottom: 48pt; }
(function (global) { System.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true }, meta: { '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { 'jszip': 'npm:jszip/dist/jszip.js', '@grapecity/wijmo': 'npm:@grapecity/wijmo/index.js', '@grapecity/wijmo.input': 'npm:@grapecity/wijmo.input/index.js', '@grapecity/wijmo.styles': 'npm:@grapecity/wijmo.styles', '@grapecity/wijmo.cultures': 'npm:@grapecity/wijmo.cultures', '@grapecity/wijmo.chart': 'npm:@grapecity/wijmo.chart/index.js', '@grapecity/wijmo.chart.analytics': 'npm:@grapecity/wijmo.chart.analytics/index.js', '@grapecity/wijmo.chart.animation': 'npm:@grapecity/wijmo.chart.animation/index.js', '@grapecity/wijmo.chart.annotation': 'npm:@grapecity/wijmo.chart.annotation/index.js', '@grapecity/wijmo.chart.finance': 'npm:@grapecity/wijmo.chart.finance/index.js', '@grapecity/wijmo.chart.finance.analytics': 'npm:@grapecity/wijmo.chart.finance.analytics/index.js', '@grapecity/wijmo.chart.hierarchical': 'npm:@grapecity/wijmo.chart.hierarchical/index.js', '@grapecity/wijmo.chart.interaction': 'npm:@grapecity/wijmo.chart.interaction/index.js', '@grapecity/wijmo.chart.radar': 'npm:@grapecity/wijmo.chart.radar/index.js', '@grapecity/wijmo.chart.render': 'npm:@grapecity/wijmo.chart.render/index.js', '@grapecity/wijmo.chart.webgl': 'npm:@grapecity/wijmo.chart.webgl/index.js', '@grapecity/wijmo.gauge': 'npm:@grapecity/wijmo.gauge/index.js', '@grapecity/wijmo.grid': 'npm:@grapecity/wijmo.grid/index.js', '@grapecity/wijmo.grid.detail': 'npm:@grapecity/wijmo.grid.detail/index.js', '@grapecity/wijmo.grid.filter': 'npm:@grapecity/wijmo.grid.filter/index.js', '@grapecity/wijmo.grid.search': 'npm:@grapecity/wijmo.grid.search/index.js', '@grapecity/wijmo.grid.grouppanel': 'npm:@grapecity/wijmo.grid.grouppanel/index.js', '@grapecity/wijmo.grid.multirow': 'npm:@grapecity/wijmo.grid.multirow/index.js', '@grapecity/wijmo.grid.transposed': 'npm:@grapecity/wijmo.grid.transposed/index.js', '@grapecity/wijmo.grid.transposedmultirow': 'npm:@grapecity/wijmo.grid.transposedmultirow/index.js', '@grapecity/wijmo.grid.pdf': 'npm:@grapecity/wijmo.grid.pdf/index.js', '@grapecity/wijmo.grid.sheet': 'npm:@grapecity/wijmo.grid.sheet/index.js', '@grapecity/wijmo.grid.xlsx': 'npm:@grapecity/wijmo.grid.xlsx/index.js', '@grapecity/wijmo.grid.selector': 'npm:@grapecity/wijmo.grid.selector/index.js', '@grapecity/wijmo.grid.cellmaker': 'npm:@grapecity/wijmo.grid.cellmaker/index.js', '@grapecity/wijmo.nav': 'npm:@grapecity/wijmo.nav/index.js', '@grapecity/wijmo.odata': 'npm:@grapecity/wijmo.odata/index.js', '@grapecity/wijmo.olap': 'npm:@grapecity/wijmo.olap/index.js', '@grapecity/wijmo.pdf': 'npm:@grapecity/wijmo.pdf/index.js', '@grapecity/wijmo.pdf.security': 'npm:@grapecity/wijmo.pdf.security/index.js', '@grapecity/wijmo.viewer': 'npm:@grapecity/wijmo.viewer/index.js', '@grapecity/wijmo.xlsx': 'npm:@grapecity/wijmo.xlsx/index.js', '@grapecity/wijmo.undo': 'npm:@grapecity/wijmo.undo/index.js', '@grapecity/wijmo.interop.grid': 'npm:@grapecity/wijmo.interop.grid/index.js', '@grapecity/wijmo.touch': 'npm:@grapecity/wijmo.touch/index.js', '@grapecity/wijmo.cloud': 'npm:@grapecity/wijmo.cloud/index.js', '@grapecity/wijmo.barcode': 'npm:@grapecity/wijmo.barcode/index.js', '@grapecity/wijmo.barcode.common': 'npm:@grapecity/wijmo.barcode.common/index.js', '@grapecity/wijmo.barcode.composite': 'npm:@grapecity/wijmo.barcode.composite/index.js', '@grapecity/wijmo.barcode.specialized': 'npm:@grapecity/wijmo.barcode.specialized/index.js', 'jszip': 'npm:jszip/dist/jszip.js', 'bootstrap.css': 'npm:bootstrap/dist/css/bootstrap.min.css', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);