PivotGrid Customizations
Custom Cells
The PivotGrid 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.
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:
var pivotGrid = new wjOlap.PivotGrid('#pivotGrid', {
isReadOnly: true,
itemsSource: ngFmt,
formatItem: formatItem // customize the grid cells
});
function formatItem(s, e) {
// we are interested in the cells panel
if (e.panel == s.cells) {
// remove custom color by default
var color = '';
// format diff columns if custom formatting is on
if (e.col % 2 == 1 && customCells.checked) {
var value = s.getCellData(e.row, e.col),
glyph = 'circle',
span = ' <span style="font-size:120%" class="wj-glyph-{glyph}"></span>';
color = '#d8b400';
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.innerHTML += span.replace('{glyph}', glyph);
}
}
// apply cell color
e.cell.style.color = color;
}
}Sparklines
You can use the formatItem event to add custom content such as sparklines and sparkbars to grid cells also.
This example adds two extra fields to a PivotEngine and uses the formatItem event to add sparklines and sparkbars to the extra fields.
To do this, the sample uses the engine's getDetail method to retrieve the detail records for each cell and uses that data to build svg elements displayed in each cell. The cell details are stored in the data item so they can be re-used when the grid scrolls.
Refer to the Pivot Grid Sparklines demo for an example.
var pivotGrid = new wjOlap.PivotGrid('#pivotGrid', {
isReadOnly: true,
itemsSource: ng,
formatItem: formatItem // customize the grid cells
});
// use formatItem to add sparklines and/or sparkbars
var maxSparkLength = 25;
function formatItem(s, e) {
// we want the cells panel
if (e.panel == s.cells) {
// we want the 'Sparklines' and 'Sparkbars' value fields
var ng = s.engine,
field = ng.valueFields[e.col % ng.valueFields.length],
item = s.rows[e.row].dataItem,
binding = s.columns[e.col].binding,
spark = field.header == 'Sparklines' || field.header == 'Sparkbars';
// add/remove spark class
wijmo.toggleClass(e.cell, 'spark', spark);
// add sparklines
if (spark) {
// if we have the data, show it
if (item.sparkData) {
var 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 dont have the data yet, so go get it
if (!item.sparkData) {
e.cell.innerHTML = '';
setTimeout(function() {
var detail = s.engine.getDetail(item, binding),
len = detail.length;
if (len > maxSparkLength) {
detail = detail.slice(len - maxSparkLength);
}
item.sparkData = detail.map(function(dataItem) {
return dataItem.sales;
});
s.invalidate(); // invalidate to show the sparlines
});
}
}
}
}
// generate sparklines as SVG
function getSparklines(data) {
var 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 (var i = 1; i < data.length; i++) {
var 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) {
var 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 (var i = 0; i < data.length; i++) {
var 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=.5 />';
svg += '</svg>';
return svg;
}
function scaleY(value, min, max) {
return 100 - Math.round((value - min) / (max - min) * 100);
}HTML Content
Use the PivotField's isContentHtml property to render fields that contain HTML instead of plain text.
For example, in the array below the "Buyer" and "Type" fields are bound to properties that contain HTML text:
[
{
date: new Date(yr, 0, 1),
buyer: '<span class="initial">M</span>om',
type: '<span class="initial">F</span>uel',
amount: 74
},
{
date: new Date(yr, 0, 15),
buyer: '<span class="initial">M</span>om',
type: '<span class="initial">F</span>ood',
amount: 235
},
{
date: new Date(yr, 0, 17),
buyer: '<span class="initial">D</span>ad',
type: '<span class="initial">S</span>ports',
amount: 20
},
{
date: new Date(yr, 0, 21),
buyer: '<span class="initial">K</span>elly',
type: '<span class="initial">B</span>ooks',
amount: 125
},
{
date: new Date(yr, 1, 2),
buyer: '<span class="initial">M</span>om',
type: '<span class="initial">F</span>ood',
amount: 235
},
{
date: new Date(yr, 1, 20),
buyer: '<span class="initial">K</span>elly',
type: '<span class="initial">M</span>usic',
amount: 20
},
{
date: new Date(yr, 1, 25),
buyer: '<span class="initial">K</span>elly',
type: '<span class="initial">T</span>ickets',
amount: 125
},
];To enable the content to render HTML, set isContentHTML to true.
var ng = new wjOlap.PivotEngine({
itemsSource: getData(), // raw data
valueFields: ['Amount'], // summarize amounts
rowFields: ['Buyer', 'Type'] // summarize amounts
});
ng.fields.getField('Buyer').isContentHtml = true;
ng.fields.getField('Type').isContentHtml = true;Result
