PivotTable Subtotal

You can set the position of subtotals and grandtotals. The subtotalsPosition can be set to none, top or bottom values and the grandTotalPosition can be set to all values such as none, both, col or row. You can also choose what calc. function to apply to the totals.

Description
app.js
index.html
styles.css
Copy to CodeMine

In PivotTable, there are 11 kinds of subtotal types. Each type defines the way that values are calculated in the data area.

Enumeration Value Description
average (Average) The average of the values.
count (Count) The number of data values. The Count consolidation function works the same as the COUNTA worksheet function.
countNums (CountNums) The number of data values that are numbers. The Count Nums consolidation function works the same as the COUNT worksheet function.
max (Maximum) The largest value.
min (Minimum) The smallest value.
product (Product) The product of the values.
stdDev (StdDev) An estimate of the standard deviation of a population, where the sample is a subset of the entire population.
stdDevp (StdDevP) The standard deviation of a population, where the population is all of the data to be summarized.
sum (Sum) The sum of the values.
var (Variance) An estimate of the variance of a population, where the sample is a subset of the entire population.
varp (VarP) The variance of a population, where the population is all of the data to be summarized.

In multiple value fields, the ∑Value field will be added to column area automatically.
The ∑Value field worked as the row / column field to define the data distribution in PivotTable, row / column header.

Users can get or set SubTotalType of the PivotTable field with the below API:

///* function subTotalType (fieldName: string, type?: GC.Pivot.SubtotalType): void
/**
 * get or set SubTotalType for a field
 * @param {string} fieldName the target field name of pivot table
 * @param {GC.Pivot.SubtotalType} type the SubTotalType
 * @example
 * pivotTable.subTotalType("fieldName", GC.Pivot.SubtotalType);
 */
subTotalType (fieldName: string, type?: SubtotalType): SubtotalType | void;

Before setting the subTotalType, a user must have added the field to the current PivotTable. For example:

pivotTable.add("TotalPrice", "Sum of price", GC.Spread.Pivot.PivotTableFieldType.valueField);
pivotTable.subTotalType("Sum of price", GC.Pivot.SubtotalType.sum);

All of the supported subtotal types are in the below enumeration:

// GC.Pivot.SubtotalType
enum SubtotalType {
    /** average
     * @type {number}
     */
    average = 0,
    /** count
     * @type {number}
     */
    count = 1,
    /** countNums
     * @type {number}
     */
    countNums = 2,
    /**max
     * @type {number}
     */
    max = 3,
    /**min
     * @type {number}
     */
    min = 4,
    /** product
     * @type {number}
     */
    product = 5,
    /** stdDev
     * @type {number}
     */
    stdDev = 6,
    /** stdDevp
     * @type {number}
     */
    stdDevp = 7,
    /**sum
     * @type {number}
     */
    sum = 8,
    /**var
     * @type {number}
     */
    varr = 9,
    /** varp
     * @type {number}
     */
    varp = 10
} 

The user can get or set the position of ∑Value:

///* function dataPosition(positionType?: GC.Pivot.DataPosition, positionIndex?: number): GC.Pivot.IDataPosition | void
/**
 * @description Get or set the values position.
 * @param {GC.Pivot.DataPosition} positionType Indicates the position which is in row or column area.
 * @param {number} positionIndex Indicates the order in field row or column list.
 */
dataPosition (positionType?: DataPosition, positionIndex?: number): IDataPosition | void;

The user can get or set the subtotal position (top/bottom) for a single field:

///* function subtotalPosition(fieldName: string, position: GC.Spread.Pivot.SubtotalsPosition): GC.Spread.Pivot.SubtotalsPosition | void
/**
    * @description set or get field show subtotal position information.
    * @param {string} fieldName Indicates the field name.
    * @param {GC.Spread.Pivot.SubtotalsPosition} position The indicates set whether subtotal position, only top and bottom is supported.
    * @returns {GC.Spread.Pivot.SubtotalsPosition}
 */
subtotalPosition (fieldName: string, position?: SubtotalsPosition): SubtotalsPosition | void;

The can get or set whether subtotals for individual fields are displayed:

///* function function subtotalVisible(fieldName: string, isVisible: boolean): boolean
/**
    * @description set or get whether the field displays subtotal information.
    * @param {string} fieldName Indicates the field name.
    * @param {boolean} position Indicates whether to display subtotal information for the field.
    * @returns {boolean}
 */
subtotalVisible (fieldName: string, isVisible?: boolean): boolean;
In PivotTable, there are 11 kinds of subtotal types. Each type defines the way that values are calculated in the data area. Enumeration Value Description average (Average) The average of the values. count (Count) The number of data values. The Count consolidation function works the same as the COUNTA worksheet function. countNums (CountNums) The number of data values that are numbers. The Count Nums consolidation function works the same as the COUNT worksheet function. max (Maximum) The largest value. min (Minimum) The smallest value. product (Product) The product of the values. stdDev (StdDev) An estimate of the standard deviation of a population, where the sample is a subset of the entire population. stdDevp (StdDevP) The standard deviation of a population, where the population is all of the data to be summarized. sum (Sum) The sum of the values. var (Variance) An estimate of the variance of a population, where the sample is a subset of the entire population. varp (VarP) The variance of a population, where the population is all of the data to be summarized. In multiple value fields, the ∑Value field will be added to column area automatically. The ∑Value field worked as the row / column field to define the data distribution in PivotTable, row / column header. Users can get or set SubTotalType of the PivotTable field with the below API: Before setting the subTotalType, a user must have added the field to the current PivotTable. For example: All of the supported subtotal types are in the below enumeration: The user can get or set the position of ∑Value: The user can get or set the subtotal position (top/bottom) for a single field: The can get or set whether subtotals for individual fields are displayed:
const SubtotalType = ["Average of total", "Count of total", "Count numbers of total", "Max of total", "Min of total", "Product of total", "StdDev of total", "StdDevp of total", "Sum of total", "Varr of total", "Varp of total"]; var currentFieldName = "Sum of total"; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 2 }); initSpread(spread); var pivotLayoutSheet = spread.getSheet(0); var pt = initPivotTable(pivotLayoutSheet); bindEvent(spread, pt); }; function initSpread(spread) { spread.suspendPaint(); let sheet = spread.getSheet(1); sheet.name("DataSource"); sheet.setRowCount(117); sheet.setColumnWidth(0, 120); sheet.getCell(-1, 0).formatter("YYYY-mm-DD"); sheet.getRange(-1,4,0,2).formatter("$ #,##0"); let table = sheet.tables.add('tableSales', 0, 0, 117, 6); for(let i=2;i<=117;i++) { sheet.setFormula(i-1,5,'=D'+i+'*E'+i) } table.style(GC.Spread.Sheets.Tables.TableThemes["none"]); sheet.setArray(0, 0, pivotSales); var sheet0 = spread.getSheet(0); sheet0.name("PivotLayout"); sheet0.setColumnCount(50); spread.resumePaint(); } function initPivotTable(sheet) { let myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8); myPivotTable.suspendLayout(); myPivotTable.options.showRowHeader = true; myPivotTable.options.showColumnHeader = true; myPivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField); myPivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField); myPivotTable.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.rowField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] }; myPivotTable.group(groupInfo); myPivotTable.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.columnField); myPivotTable.add("total", this.currentFieldName, GC.Spread.Pivot.PivotTableFieldType.valueField); myPivotTable.subTotalType(this.currentFieldName, GC.Pivot.SubtotalType.sum); myPivotTable.add("total", "Average of total", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.average); myPivotTable.resumeLayout(); myPivotTable.autoFitColumn(); return myPivotTable; } function getUniqueName(pt, name, index = 0) { let realName = index === 0 ? name : name + (index + ""); if (pt.getField(realName)) { return getUniqueName(pt, name, index + 1); } else { return realName; } } function bindEvent (spread, pt) { _getElementById("subtotalType").addEventListener("change", () => { if (pt) { var type = parseInt(_getElementById("subtotalType").value, 10); var newFieldName = SubtotalType[type]; pt.subTotalType(currentFieldName, type); newFieldName = getUniqueName(pt, newFieldName); pt.updateFieldName(currentFieldName, newFieldName); pt.autoFitColumn(); currentFieldName = newFieldName; } }); _getElementById("subtotalType2").addEventListener("change", () => { if (pt) { var type = parseInt(_getElementById("subtotalType2").value, 10); pt.subTotalType("Average of total", type); pt.autoFitColumn(); pt.refresh(); } }); _getElementById("positionType").addEventListener("change", () => { updateDataPosition(pt); }); _getElementById("positionIndex").addEventListener("change", () => { updateDataPosition(pt); }); _getElementById("pt_subtotalsPosition").addEventListener("change", () => { if (pt) { var subtotalPosition = parseInt(_getElementById("pt_subtotalsPosition").value, 10); pt.options.subtotalsPosition = subtotalPosition; pt.autoFitColumn(); } }); _getElementById("field_subtotalsPosition").addEventListener("change", () => { if (pt) { var subtotalPosition = parseInt(_getElementById("field_subtotalsPosition").value, 10); pt.subtotalPosition("Cars", subtotalPosition); pt.autoFitColumn(); } }); _getElementById("field_subtotalsVisible").addEventListener("change", () => { if (pt) { var subtotalPosition = parseInt(_getElementById("field_subtotalsVisible").value, 10); pt.subtotalVisible("Cars", !!subtotalPosition); pt.autoFitColumn(); } }); } function updateDataPosition (pt) { if (pt) { var positionType = parseInt(_getElementById("positionType").value, 10); var positionIndex = parseInt(_getElementById("positionIndex").value, 10); pt.dataPosition(positionType, positionIndex); } } function _getElementById(id) { return document.getElementById(id); }
<!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="option-row"> <label><b>Settings</b> </label> </div> <hr /> <div class="option-row"> Change the subtotal type for the <b>Sum of Total</b> fields (col D-G): <select id="subtotalType"> <option value="0">average (Average)</option> <option value="1">count (Count)</option> <option value="2">countNums (CountNums)</option> <option value="3">max (Maximum)</option> <option value="4">min (Minimum)</option> <option value="5">product (Product)</option> <option value="6">stdDev (StdDev)</option> <option value="7">stdDevp (StdDevP)</option> <option value="8" selected>sum (Sum)</option> <option value="9">var (Variance)</option> <option value="10">varp (VarP)</option> </select> </div> <div class="option-row"> Changes the subtotal type for the <b>Average of total</b> fields (col H-M): <select id="subtotalType2"> <option value="0" selected>average (Average)</option> <option value="1">count (Count)</option> <option value="2">countNums (CountNums)</option> <option value="3">max (Maximum)</option> <option value="4">min (Minimum)</option> <option value="5">product (Product)</option> <option value="6">stdDev (StdDev)</option> <option value="7">stdDevp (StdDevP)</option> <option value="8">sum (Sum)</option> <option value="9">var (Variance)</option> <option value="10">varp (VarP)</option> </select> </div> <div class="option-row"> Changes the data position. <div class="option-item"> Position Type: <select id="positionType"> <option value="0">row</option> <option value="1" selected>col</option> </select> </div> <div class="option-item"> Position Index: <input type="number" value="0" id="positionIndex" min="0" max="2" /> </div> </div> <div class="option-row"> Change pivotTable subtotals position: <div class="option-item"> Subtotals Position: <select name="" id="pt_subtotalsPosition" class="select-option-select"> <option value="0">none</option> <option value="1">top</option> <option value="2" selected>bottom</option> </select> </div> </div> <div class="option-row"> Change subtotals position for the <b>Cars</b> fields: <div class="option-item"> Subtotals Position: <select name="" id="field_subtotalsPosition" class="select-option-select"> <option value="1">top</option> <option value="2" selected>bottom</option> </select> </div> </div> <div class="option-row"> Sets whether the <b>Cars</b> field displays subtotals: <div class="option-item"> Subtotal Visible: <select name="" id="field_subtotalsVisible" class="select-option-select"> <option value="0">none</option> <option value="1" selected>show</option> </select> </div> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; } .sample-spreadsheets { width: calc(100% - 220px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 220px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; scrollbar-width: none; -ms-overflow-style: none; } .options-container::-webkit-scrollbar { display: none; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } .option-item { margin-top: 5px; } .option-item #positionType { float: right; width: 80px; } .option-item #positionIndex { float: right; width: 72px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } #app { height: 100%; }