When the data source is hierarchical, a formula can be configured for data fields in the schema, and the formula only be invoked when the record has children.
This means that the value for the parent record is summarized, and the value from the child record
are in the same field when retrieving.
The hierarchy summary field options:
The following is a sample that shows how to configure the hierarchy summary fields:
The Built-in Formulas
The built-in formulas can be used in the hierarchy summary fields or in the calculation column when it’s in the hierarchy:
CHILDREN
This formula is for retrieving the value that is specified by the [fieldName] of the children that are [levelOffset] steps away from the parent.
If the level offset is bigger than the interval between the current record and the lowest children, the values are from the children that are not parents.
ONLYCHILDREN
This formula is for retrieving the value that is specified by [fieldName] of the children that are not parents.
PARENT
This formula is for retrieving the value that is specified by [fieldName] of the parent that are [levelOffset] steps away from the current record.
If the level offset is bigger than the interval between the current record and the highest parent, the value is from the highest parent. However if the current record is the highest parent, nothing will be retrieved.
LEVEL
This formula is for retrieving the level of the current record.
LEVELROWNUMBER
This formula is for retrieving the row number of the current record under the parent scope.
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 0 });
initSpread(spread);
};
function initSpread(spread) {
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
spread.clearSheets();
var dataManager = spread.dataManager();
initHierarchyFormulas(spread, dataManager);
}
function initHierarchyFormulas(spread, dataManager) {
var table = dataManager.addTable("Table", {
remote: {
read: {
url: getBaseApiUrl() + "/Hierarchy_Formula"
}
},
schema: {
hierarchy: {
type: 'Parent',
column: 'parent',
summaryFields: {
'budget':'=SUM(CHILDREN(1,"budget"))'
}
},
columns: {
id: {
isPrimaryKey: true,
},
},
}
});
var sheet = spread.addSheetTab(0, "HierarchyFormula", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false;
table.fetch().then(function () {
var myView = table.addView("myView", [
{ value: '=CONCAT([@department]," (L",LEVEL(),"-",LEVELROWNUMBER(),")")', caption: 'Department', width: 265, outlineColumn: true },
{ value: "budget", width: 100, caption: 'Budget' },
{ value: '=IF(LEVEL()=0,"",[@budget]/PARENT(1,"budget"))', width: 120, caption: 'Percentage', style: { formatter: '0.00%' } },
{ value: "location", width: 100, caption: 'Location' },
{ value: "phone", width: 150, caption: 'Phone' },
{ value: "country", width: 100, caption: 'Country' },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function _getElementById(id) {
return document.getElementById(id);
}
function getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
}
<!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$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.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></body>
</html>
.colorLabel {
background-color: #F4F8EB;
}
.rightAlignLabel {
width: 120px;
text-align: right;
margin-right: 8px;
}
input[type="text"] {
width: 190px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
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;
}
label {
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
width: 190px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
