Group By Formula or Related Field

TableSheet supports not only the fields but also formulas and related fields as the group or slice column.

You can set a formula or a related field as the grouping option: The slice column can support formulas and related fields in the same way: Using DATEPART formula can help to group the field by date type, the syntax as follows: Argument Description date_value (Required) The date value. format_string (Required) The format string of date. [week_num_type] (Optional) The same as the second argument of WEEKNUM. The format string for DATEPART formula and the samples: Format Result Description yyyyQ 20214 Numeric: 1 digit(Quarter number/name.) yyyyQQ 202104 Numeric: 2 digits + zero pad yyyyQQQ 2021Q4 Abbreviated yyyy QQQQ 2021 4th quarter Wide YYYY w 2021 8 Numeric: minimum digits(Week of Year (numeric). When used in a pattern with year, use ‘Y’ for the year field .) YYYY ww 2021 08 Numeric: 2 digits, zero pad if needed MM-yyyy 09-2021 Provide partial date formatter in cell formatting Using CALCULATE and REMOVEFILTERS formulas can help to expand the group context, the syntax as follows: Argument Description formula_string (Required) The formula will evalute by the context from the expand_context. expand_context (Required) The expand_context is from REMOVEFILTERS. Argument Description [group_field_string] (Optional) The group field indicates the scope that is expanded to. The CALCULATE formula should only be used in the summaryFields section, and the REMOVEFILTERS should only be used for the combination of REMOVEFILTERS and CALCULATE.
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ var baseApiUrl = getBaseApiUrl(); function getApiUrl(tableName) { return baseApiUrl + "/" + tableName; } window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 }); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); spread.clearSheets(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; //init a data manager var dataManager = spread.dataManager(); var ordersTable = dataManager.addTable("ordersTable", { remote: { read: { url: getApiUrl("Order") } }, schema: { columns: { orderDate: { dataType: "date" }, requiredDate: { dataType: "date" }, shippedDate: { dataType: "date" } } } }); var customersTable = dataManager.addTable("customersTable", { remote: { read: { url: getApiUrl("Customer") } } }); dataManager.addRelationship(ordersTable, "CustomerId", "Customers", customersTable, "Id", "Orders"); var view = ordersTable.addView("orderView", [ { value: "Id", width: 65 }, { value: "OrderDate", width: 100 }, { value: "RequiredDate", width: 120 }, { value: "ShippedDate", width: 110 }, { value: "ShipVia", width: 110 }, { value: "Freight", width: 80 }, { value: "ShipName", width: 200 }, ]); //init a table sheet var sheet = spread.addSheetTab(0, "MyTableSheet", GC.Spread.Sheets.SheetType.tableSheet); view.fetch().then(function () { sheet.setDataView(view); sheet.groupOutlinePosition(GC.Spread.Sheets.TableSheet.GroupOutlinePosition.none); sheet.groupBy([ { caption: "Company Name", field: "Customers.CompanyName", width: 160, }, { caption: "Year Quarter", field: '=DATEPART([@OrderDate],"yyyy-QQQ")', width: 160, }, { caption: "Ship Via", field: 'ShipVia', style: { formatter: '=SWITCH(@,1,"Speedy Express", 2,"United Package", 3, "Federal Shipping","")' }, width: 135, summaryFields: [ { caption: "Freight", width: 100, formula: "=SUM([Freight])", }, { caption: "Company Ratio", width: 130, style: { formatter: "0.00%" }, formula: '=SUM([Freight]) / CALCULATE( SUM([Freight]), REMOVEFILTERS("ShipVia","=DATEPART([@OrderDate],""yyyy-QQQ"")"))', // ratio of sum of freight under freight level to sum of freight under year quarter }, { caption: "% Grand Total", width: 120, style: { formatter: "0.00%" }, formula: '=SUM([Freight]) / CALCULATE( SUM([Freight]), REMOVEFILTERS())', // ratio of sum of freight under freight level to sum of freight under all records } ] }, { caption: "Freight Level", width: 135, field: '=IFS([@Freight]<30,0,AND([@Freight]>=30,[@Freight]<60),1,[@Freight]>60,2)', style: { formatter: '=SWITCH(@,0,"Low",1,"Medium",2,"High","no match")' }, } ]); }); spread.resumePaint(); } 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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill --> <script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script> <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-tablesheet/dist/gc.spread.sheets.tablesheet.min.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> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; }