Hierarchy Data Formula

TableSheet supports built-in formulas that can be used with hierarchy data in the data source schema.

In order to specify the formula for fields, you can use the summaryFields option within the hierarchy option of the data source schema. The summaryFields property is inherited from the GC.Data.IHierarchySummaryFieldCollection interface.

The different types of hierarchy-based data formula are discussed in the following section.

CHILDREN

The CHILDREN formula is used to retrieve the value that is specified by the fieldName of the children that is levelOffset steps away from the parent record. The steps between the children and parent are denoted by the levelOffset value.

If the level offset is larger than the interval between current record and the lowest children, then the data cannot be retrieved.

The formula definition is as follows:

CHILDREN(levelOffset, fieldName)

ONLYCHILDREN

The ONLYCHILDREN formula is used to retrieve the value that be specified by the fieldName of the children, which is not the parent.

The formula definition is as follows:

ONLYCHILDREN(fieldName)

PARENT

The PARENT formula is used to retrieve the value that can be specified by the fieldName of the parent that is levelOffset steps away from the current record. The steps between the parent and current record are denoted by the levelOffset value.

If the level offset is larger than the interval between the current record and the highest parent, then no data can be retrieved.

The formula definition is as follows:

PARENT(levelOffset, fieldName)

LEVEL

The LEVEL formula is used to retrieve the level of the current record.

The formula definition is as follows:

LEVEL()

LEVELROWNUMBER

The LEVELROWNUMBER formula is used to retrieve the row number of the current record under the parent scope.

The formula definition is as follows:

LEVELROWNUMBER()

The following code sample shows how to set various hierarchy data formulas:

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();
    });
}

The preceding sample code lines when executed, gives the following output: