Pivot Table Show Data As

You can show the PivotTable data using different types of calculation values with the showDataAs method. For example, instead of showing the actual currency value, you could display the data as a percentage of the parent or grand total. Select the different options below to see some of the available calculations.

SpreadJS pivot table provides show values as to quickly present values in different ways. There are 14 kinds of show data options are available. Show Data As name Enum Value Show Data As Info Required Properties No Calculation normal showDataAs % of Grand Total percentOfTotal showDataAs % of Column Total percentOfRow showDataAs % of Row Total percentOfCol showDataAs % Of percent showDataAs, baseFieldName, baseFieldItemType, baseFieldItem? % of Parent Row Total percentOfParentRow showDataAs % of Parent Column Total percentOfParentCol showDataAs % of Parent Total percentOfParent showDataAs, baseFieldName Difference From difference showDataAs, baseFieldName, baseFieldItemType, baseFieldItem? % Difference From percentDiff showDataAs, baseFieldName, baseFieldItemType, baseFieldItem? Running Total in runTotal showDataAs, baseFieldName % Running Total in percentOfRunningTotal showDataAs, baseFieldName Rank Smallest to Largest rankAscending showDataAs, baseFieldName Rank Largest to Smallest rankDescending showDataAs, baseFieldName Index index showDataAs Sample:
window.onload = function () { let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); let sheet1 = spread.getSheet(0); let sheet2 = spread.getSheet(1); let tableName = getDataSource(sheet2, pivotSales); let pivotTable = initPivotTable(sheet1, tableName); bindEvent(pivotTable, spread); spread.resumePaint(); } function getDataSource(sheet, tableSource) { 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('table', 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, tableSource); return table.name(); } function initPivotTable(sheet, source) { sheet.name("ShowDataAs"); sheet.setRowCount(1000); let option = { showRowHeader:true, showColumnHeader:true, bandRows:true, bandColumns:true }; let pivotTable = sheet.pivotTables.add("pivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium8,option); pivotTable.suspendLayout(); pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] }; pivotTable.group(groupInfo); pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); pivotTable.resumeLayout(); pivotTable.autoFitColumn(); return pivotTable; } function applyShowDataAsStyle(pivotTable, fieldName, showValueAsType) { let style, valueFieldArea = { dataOnly: true, references: [{ fieldName: "Values", items: [fieldName] }] }; style = pivotTable.getStyle(valueFieldArea); if (!style) { style = new GC.Spread.Sheets.Style(); } let needApplyStyle = [GC.Pivot.PivotShowDataAs.percentOfTotal, GC.Pivot.PivotShowDataAs.percentOfRow, GC.Pivot.PivotShowDataAs.percentOfCol, GC.Pivot.PivotShowDataAs.percent, GC.Pivot.PivotShowDataAs.percentOfParentRow, GC.Pivot.PivotShowDataAs.percentOfParentCol, GC.Pivot.PivotShowDataAs.percentOfParent, GC.Pivot.PivotShowDataAs.percentDiff, GC.Pivot.PivotShowDataAs.percentOfRunningTotal].indexOf(showValueAsType) > -1; if (needApplyStyle) { style.formatter = "0.00%"; } else { delete style.formatter; } pivotTable.setStyle(valueFieldArea, style); } function bindEvent(pivotTable, spread) { document.getElementById("showValueAs").addEventListener("change", function (event) { let selectIndex = event.target.value; let showValueAsBaseFieldPanel = document.getElementById("showValueAsBaseFieldPanel"), showValueAsDialogLabel = document.getElementById("showValueAsDialogLabel"), showValueAsBaseItemPanel = document.getElementById("showValueAsBaseItemPanel"); showValueAsBaseFieldPanel.style.display = "none"; showValueAsBaseItemPanel.style.display = "none"; showValueAsDialogLabel.innerText = ""; let showValueAsBaseItem = document.getElementById("showValueAsBaseItem"); showValueAsBaseItem.innerHTML = ""; let showValueAsBaseField = document.getElementById("showValueAsBaseField"); showValueAsBaseField.innerHTML = ""; if (["4", "7", "8", "9", "10", "11", "12", "13"].indexOf(selectIndex) > -1) { showValueAsBaseFieldPanel.style.display = null; let baseField = [...pivotTable.getFieldsByArea(1), ...pivotTable.getFieldsByArea(2)]; showValueAsDialogLabel.innerText = "Calculation: " + event.target.selectedOptions[0].text; baseField.forEach((item, index) => { let option = document.createElement('option'); option.value = index; option.innerText = item.fieldName; showValueAsBaseField.appendChild(option); }); if (["4", "8", "9"].indexOf(selectIndex) > -1) { showValueAsBaseItemPanel.style.display = null; let text = showValueAsBaseField.selectedOptions[0].text; let baseItems = pivotTable.getItemsByField(text); baseItems.unshift("next"); baseItems.unshift("previous"); baseItems.forEach((item, index) => { let option = document.createElement('option'); option.value = index; option.innerText = item; showValueAsBaseItem.appendChild(option); }); } } }); document.getElementById("showValueAsBaseField").addEventListener("change", function (event) { let text = event.target.selectedOptions[0].text; let showValueAsBaseItem = document.getElementById("showValueAsBaseItem"); showValueAsBaseItem.innerHTML = ""; let baseItems = pivotTable.getItemsByField(text); baseItems.unshift("next"); baseItems.unshift("previous"); baseItems.forEach((item, index) => { let option = document.createElement('option'); option.value = index; option.innerText = item; showValueAsBaseItem.appendChild(option); }); }); document.getElementById("applySetting").addEventListener('click', () => { let showValueAsIndex = parseInt(document.getElementById("showValueAs").value, 10); let valueFieldName = pivotTable.getFieldsByArea(3)[0].fieldName; if ([4, 8, 9].indexOf(showValueAsIndex) > -1) { let fileName = document.getElementById("showValueAsBaseField").selectedOptions[0].text; let showValueAsBaseItem = document.getElementById("showValueAsBaseItem"); let baseIndex = showValueAsBaseItem.value; let baseFieldItem = showValueAsBaseItem.selectedOptions[0].text; if (baseIndex === "0") { pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName, baseFieldItemType: 2 }); } else if (baseIndex === "1") { pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName, baseFieldItemType: 1 }); } else { pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName, baseFieldItemType: 0, baseFieldItem: baseFieldItem }); } } else if ([7, 10, 11, 12, 13].indexOf(showValueAsIndex) > -1) { let fileName = document.getElementById("showValueAsBaseField").selectedOptions[0].text; pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName }); } else { pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex }); } applyShowDataAsStyle(pivotTable, valueFieldName, showValueAsIndex); pivotTable.refresh(); }); }
<!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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/pivot-data.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>ShowValueAs:</label> <select id="showValueAs"> <option value='0' selected>No Calculation</option> <option value='1'>% of Grand Total</option> <option value='2'>% of Column Total</option> <option value='3'>% of Row Total</option> <option value='4'>% Of ...</option> <option value='5'>% of Parent Row Total</option> <option value='6'>% of Parent Column Total</option> <option value='7'>% of Parent Total ...</option> <option value='8'>Difference From ...</option> <option value='9'>% Of Difference From ...</option> <option value='10'>Running Total In ...</option> <option value='11'>% Running Total In ...</option> <option value='12'>Rank Smallest to Largest ...</option> <option value='13'>Rank Largest to Smallest ...</option> <option value='14'>Index</option> </select> </div> <label style="padding: 5px; margin-top: 10px;" id="showValueAsDialogLabel"></label> <div class="option-row" id="showValueAsBaseFieldPanel" style="display: none;"> <label id="showValueAsBaseFieldLabel">Base Field:</label> <select id="showValueAsBaseField"> </select> </div> <div class="option-row" id="showValueAsBaseItemPanel" style="display: none;"> <label id="showValueAsBaseItemLabel">Base Item:</label> <select id="showValueAsBaseItem"> </select> </div> <div class="option-row"> <input type="button" value="Apply Setting" id="applySetting" /> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } p{ padding:2px 10px; background-color:#F4F8EB; } input, select { width: 100%; padding: 4px 6px; box-sizing: border-box; } label { display:block; margin-bottom: 6px; } input[type="checkbox"], input[type="radio"] { display: inline-block; width: auto; } input[type="checkbox"]+label, input[type="radio"]+label { display: inline-block; } input[type="button"] { display: block; margin: 0 0 6px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }