Custom subtotals let you control which subtotal calculations are displayed for a PivotTable dimension field. Instead of using only the default subtotal behavior, you can provide one or more subtotal types such as sum, average, count, min, or max.
Custom subtotal settings can be applied to row or column fields, including leaf fields. This makes it possible to show different summary calculations at different levels of a PivotTable hierarchy.
Users can get or set custom subtotal information for a source field with the following API:
For example:
The custom subtotal information uses the following type:
Use useDefault: true to restore the default subtotal behavior. Use useDefault: false with types to show only the specified subtotal calculations.
Custom subtotal settings are serialized as part of PivotTable serialization:
Pivot area references can also target custom subtotal types. This is useful when applying styles or resolving PivotTable ranges for specific custom subtotal cells:
For example, a style can be applied only to average subtotal cells for a field:
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
var pivotSheet = spread.getSheet(0);
var sourceSheet = spread.getSheet(1);
var tableName = initDataSource(sourceSheet);
initPivotTable(pivotSheet, tableName);
spread.resumePaint();
}
function initDataSource(sheet) {
sheet.name("DataSource");
sheet.setRowCount(117);
sheet.setColumnCount(8);
sheet.getCell(-1, 0).formatter("YYYY-mm-DD");
sheet.getRange(-1, 4, 0, 2).formatter("$#,##0");
sheet.setColumnWidth(0, 100);
sheet.setColumnWidth(1, 120);
sheet.setColumnWidth(2, 80);
sheet.setColumnWidth(3, 90);
sheet.setColumnWidth(4, 80);
sheet.setColumnWidth(5, 100);
var table = sheet.tables.add("tableSales", 0, 0, 117, 6);
table.style(GC.Spread.Sheets.Tables.TableThemes.medium2);
sheet.setArray(0, 0, pivotSales);
for (var row = 2; row <= 117; row++) {
sheet.setFormula(row - 1, 5, "=D" + row + "*E" + row);
}
return table.name();
}
function initPivotTable(sheet, tableName) {
sheet.name("Custom Subtotals");
sheet.setRowCount(120);
sheet.setColumnCount(20);
sheet.setColumnWidth(0, 18);
var pivotTable = sheet.pivotTables.add(
"carPerformance",
tableName,
2,
1,
GC.Spread.Pivot.PivotTableLayoutType.tabular,
GC.Spread.Pivot.PivotTableThemes.medium8
);
pivotTable.suspendLayout();
pivotTable.options.showRowHeader = true;
pivotTable.options.showColumnHeader = true;
pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
var groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
pivotTable.group(groupInfo);
pivotTable.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("total", "Sales", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.dimensionSubtotals("salesperson", {
useDefault: false,
types: [
GC.Pivot.SubtotalType.sum,
GC.Pivot.SubtotalType.average
]
});
pivotTable.dimensionSubtotals("car", {
useDefault: false,
types: [
GC.Pivot.SubtotalType.count,
GC.Pivot.SubtotalType.max
]
});
var averageSubtotalStyle = new GC.Spread.Sheets.Style();
averageSubtotalStyle.formatter = "0.00";
pivotTable.setStyle({
dataOnly: true,
references: [{
fieldName: "Salesperson",
subtotalTypes: [GC.Pivot.SubtotalType.average]
}]
}, averageSubtotalStyle);
var panel = new GC.Spread.Pivot.PivotPanel("customSubtotalPanel", pivotTable, document.getElementById("panel"));
panel.sectionVisibility(GC.Spread.Pivot.PivotPanelSection.fields + GC.Spread.Pivot.PivotPanelSection.area);
pivotTable.resumeLayout();
pivotTable.autoFitColumn();
sheet.setValue(0, 1, "Car Sales Performance by Salesperson and Quarter");
sheet.setValue(1, 1, "Salesperson custom subtotals show total sales and average deal size. Cars custom subtotals show order count and largest deal.");
sheet.getCell(0, 1).font("bold 14px Calibri");
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="panel-tip">
Open a row or column field's settings in the PivotPanel, set Subtotals to Custom, and select the subtotal types to display.
</div>
<div id="panel"></div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
}
.sample-spreadsheets {
width: calc(100% - 300px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 300px;
height: 100%;
box-sizing: border-box;
background: #e6e6e6;
overflow: hidden;
display: flex;
flex-direction: column;
}
.panel-tip {
padding: 10px;
font-size: 12px;
line-height: 1.4;
color: #333;
background: #f7f7f7;
border-bottom: 1px solid #d0d0d0;
box-sizing: border-box;
}
#panel {
width: 300px;
flex: 1 1 auto;
min-height: 0;
}
.gc-panel {
padding: 10px;
background-color: #e6e6e6;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
overflow: hidden;
}
