Calculated Hierarchical Column

This example shows how to use the Hierarchy.Current(recursive) and Hierarchy.Level() functions.

This example shows how to use the Hierarchy.Current(recursive) and Hierarchy.Level() functions. Hierarchy.Level() returns the current level of the current hierarchical node. In this example, we get the hierarchical level and use it as a value in the level field. This is done by using =Hierarchy.Level() as the value for that field. That value is then displayed along with the department value in the Department column. Hierarchy.Current(recursive) is a filter function that returns the rows of the current hierarchical level. You can see this used to calculate the ratio in the budgetRatio field in the column definition.
<!DOCTYPE html> <html lang="en"> <head> <base href="/dataviewsjs/demos/en/sample/DataViews/TreeGrid/CalculatedHierarchicalColumn/purejs/" /> <meta charset="utf-8" /> <meta http-equiv="X-UA-Compatible" content="IE=edge" /> <meta name="keywords" content="tree, treegrid, tree grid, hirarchy, hirarchical, parent child, formula, expression" /> <meta name="description" content="This example shows how to use the **Hierarchy.Current(recursive)** and **Hierarchy.Level()** functions." /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <title>Calculated Hierarchical Column | Data Views | GrapeCity DataViewsJS JavaScript Demos</title> <link href="/dataviewsjs/demos/node_modules/normalize.css/normalize.css" rel="stylesheet" type="text/css" /> <link href="/dataviewsjs/demos/static/css/base.css" rel="stylesheet" type="text/css" /> <link href="/dataviewsjs/demos/static/dataviews/gc.dataviews.core.min.css" rel="stylesheet" type="text/css" /> <link href="/dataviewsjs/demos/static/dataviews/gc.dataviews.grid.min.css" rel="stylesheet" type="text/css" /> <link href="styles.css" rel="stylesheet" type="text/css" /> <!-- Google Tag Manager --> <script> (function (w, d, s, l, i) { w[l] = w[l] || []; w[l].push({ 'gtm.start': new Date().getTime(), event: 'gtm.js' }); var f = d.getElementsByTagName(s)[0], j = d.createElement(s), dl = l != 'dataLayer' ? '&l=' + l : ''; j.async = true; j.src = 'https://www.googletagmanager.com/gtm.js?id=' + i + dl; f.parentNode.insertBefore(j, f); })(window, document, 'script', 'dataLayer', 'GTM-WT462SJ'); </script> <!-- End Google Tag Manager --> <script src="/dataviewsjs/demos/static/js/app-polyfills.min.js" type="text/javascript"></script> <script type="text/javascript"> window.process = { env: { NODE_ENV: 'production', USE_NPM: false, USE_CDN: false, SITE_ROOT: '/dataviewsjs/demos', FRAMEWORK: 'purejs', DVJS_LICENSE_KEY: 'GrapeCity-Internal-Use-Only,GrapeCity,E195393772372914#B0KV4Ny56Vr5Wb7w6buJWeGRWb4NXUnNXRlFWWXl6SoN5VzVkYslmQ95WTENkTllVQzd4QhpEejdDNJZlWOp6M7oERQNnWspkaaB5QMhnR7dmUkRnVJR4L5cHTKNkZZdDbRBzZIxmSSR6NrpVQaVUQrBlc6ImYysSd4UlUEZGbw2kMl9UOzQVMId6ZXtUN83CO5RzaZt4VY5kM5pkUGpUUUV7UExUeGpUdTV5dFJ4bPhTc8pGeXJHWrkzNlFzdUtWTr5UQDdVckdGbSVUWr2yMPNzctZVRxEEW9lEbsZHUKJnVaRXT5p6Z0tWMqJGVrtSTIZnaopGSWZXbVNjY0hzQB5We4dnUr3ER8MkI0IyUiwiIGZUNwEkN5MjI0ICSiwSM9ETO6czNygTM0IicfJye#4Xfd5nIZRVV9IiOiMkIsISM6ByUKN7dllmVhRXYEJiOi8kI1tlOiQmcQJCLiEDMzATOwAyMwgDMwIDMyIiOiQncDJCLiI7au26YukHdpNWZwFmcn9iKsAnau26YukHdpNWZwFmcn9iKs86Yu46bj9Se4l6YlBXYydmLqwSbvNmL6VGZ9RXajVGchJ7ZuoCLt36YukHdpNWZwFmcn9iKiojIz5GRiwiI9RXaDVGchJ7RiojIh94QiwSZ5JHd0ICb6VkIsICNxkjM7MjM7czM9MTN9EjI0ICZJJye0ICRiwiI34zZN3yZGNlUnJDe5wkQPVlcBh7SppmUNBlU9dWS7J4ZMRES4MTZkRXTxM7Uq5GeQdHcyFHVup6dQd6ZDpEVrNDSh9kbNdzZrl4dwdPQ', SJS_LICENSE_KEY: '*.grapecity.com,E613631884219496#B0qRgJHWSJ7NyBlc8BjNMRHW7g7YldTZXFTQuFnW4hVOCplVSlVV09ERlhEZuVTVKlTazE4Q6VGSw2CdWZUWSVmbjVXbrxmWFVWR8ZzQro7U84WMGdlbuVHb73kS5kjUTN4NvFVdLdXWVR4Nox6Z7UUSysEcXJEMsN6bDN4TxMDVwVmWBRzKxhkTzAXTaJmdD3CRFJTd8R4R6M5RklWa6oUaLlXMwR4R8ZUdtRWVxUUaQh6VXNDdEhlZ7FHR6QXTPJTVvkWcyZnbSdHRtZHcYF6TKN4axYGcZNjTDF7TvFTTr24VqZjVHVjcLd7QkRmdNxkI0IyUiwiI5gDOEF4QGVjI0ICSiwiMzkTO9kTOyMTM0IicfJye35XX3JSSGljQiojIDJCLiITMuYHITpEIkFWZyB7UiojIOJyebpjIkJHUiwiI4MDMyEDMgkDM8ATOxAjMiojI4J7QiwiIt36YukHdpNWZwFmcn9iKiojIz5GRiwiIj9WagkHdpNUZwFmcHJiOiEmTDJCLlVnc4pjIsZXRiwiI6kDN9EjM4gDOxMjNzEjNiojIklkIs4XZzxWYmpjIyNHZisnOiwmbBJye0ICRiwiI34TQ72kNBV6YXpXdGxGWxdHcol4MyUGUHJVbQVHRx44Sw84YxRkS4QnZadDNmhWWxV5QxFlTlZEbBJ5N8gUNQlDb7J6Kl36YHVnb4NGN92UMFdlNORFU8VDSaFlQSVlS4EHTrA5Ohh', }, }; </script> <script src="/dataviewsjs/demos/static/dataviews/gc.dataviews.common.min.js" type="text/javascript"></script> <script src="/dataviewsjs/demos/static/dataviews/gc.dataviews.core.min.js" type="text/javascript"></script> <script src="/dataviewsjs/demos/static/dataviews/gc.dataviews.grid.min.js" type="text/javascript"></script> <script src="/dataviewsjs/demos/static/js/license.js" type="text/javascript"></script> </head> <body class="theme-default"> <!-- Google Tag Manager (noscript) --> <noscript ><iframe src="https://www.googletagmanager.com/ns.html?id=GTM-WT462SJ" height="0" width="0" style="display: none; visibility: hidden;" ></iframe ></noscript> <!-- End Google Tag Manager (noscript) --> <noscript>You need to enable JavaScript to run this app.</noscript> <div id="grid" class="grid"></div> <script src="data.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> </body> </html>
var cols = [ { id: 'department', caption: 'Department', dataField: 'department', presenter: '{{=it.department}} (L{{=it.level}})', width: 250, hierarchyFooter: '<span style="font-weight:bold">Count: {{=it.eval("=COUNT([id])")}}</span>', }, { id: 'level', caption: 'Level', minWidth: 120, dataField: '=Hierarchy.Level()', visible: false, }, { id: 'budgetTotal', caption: 'Total Budget', minWidth: 120, dataField: '=SUMX(Hierarchy.Children(true, true), [budget])', format: '$0,00', hierarchyFooter: '<span style="font-weight:bold;text-align:right">{{=it.eval("=SUMX(Hierarchy.Children(true, true), [budget])", "$0,0")}}</span>', }, { id: 'budget', caption: 'Own Budget', minWidth: 100, dataField: 'budget', format: '$0,00', }, { id: 'budgetRatio', caption: 'Percent', minWidth: 120, dataField: '=SUMX(Hierarchy.Children(true, true), [budget]) / Sumx(Hierarchy.Current(true, true), [budget])', format: '0.0%', }, { id: 'location', caption: 'Location', minWidth: 120, dataField: 'location', }, { id: 'phone', caption: 'Phone', minWidth: 120, dataField: 'phone', }, { id: 'country', caption: 'Country', minWidth: 120, dataField: 'country', }, { id: 'id', caption: 'Id', dataField: 'id', visible: false, }, { id: 'parent', caption: 'Parent', dataField: 'parent', visible: false, }, ]; var layout = new GC.DataViews.GridLayout({ allowSorting: true, showRowHeader: false, hierarchy: { keyField: 'id', parentField: 'parent', collapsed: true, column: 'department', footer: { visible: true, }, }, }); var dataView = new GC.DataViews.DataView(document.getElementById('grid'), data, cols, layout); // expand first level dataView.data.nodes.forEach(function (node) { node.collapsed = false; }); dataView.invalidate(); // focus data.view by default document.getElementById('grid').focus();
var data = [{ id: 1, department: 'Corporate Headquarters', budget: 13000, location: 'Monterey', phone: '(408) 555-1234', country: 'US', parent: null }, { id: 2, department: 'Sales and Marketing', budget: 6000, location: 'San Francisco', phone: '(408) 555-1234', country: 'US', parent: 1 }, { id: 3, department: 'Finance', budget: 4000, location: 'Monterey', phone: '(408) 555-1234', country: 'US', parent: 1 }, { id: 4, department: 'Engineering', budget: 11000, location: 'Monterey', phone: '(408) 555-1234', country: 'US', parent: 1 }, { id: 5, department: 'Field Office: East Coast', budget: 5000, location: 'Toronto', phone: '(416) 677-1000', country: 'US', parent: 2 }, { id: 6, department: 'Field Office: East Coast', budget: 5000, location: 'Boston', phone: '(408) 555-1234', country: 'US', parent: 2 }, { id: 7, department: 'Pacific Rim Headquarters', budget: 3000, location: 'Kuaui', phone: '(408) 555-1234', country: 'US', parent: 2 }, { id: 8, department: 'Marketing', budget: 15000, location: 'San Francisco', phone: '(408) 555-1234', country: 'US', parent: 2 }, { id: 9, department: 'Field Office: Singapore', budget: 3000, location: 'Singapore', phone: '(606) 555-5486', country: 'US', parent: 7 }, { id: 10, department: 'Field Office: Japan', budget: 5000, location: 'Tokyo', phone: '(707) 555-1526', country: 'US', parent: 7 }, { id: 11, department: 'Consumer Electronics Div', budget: 11000, location: 'Burlington, VT', phone: '(802) 555-1234', country: 'US', parent: 4 }, { id: 12, department: 'Software Products Div', budget: 12000, location: 'Monterey', phone: '(408) 555-1234', country: 'US', parent: 4 }, { id: 13, department: 'Software Development', budget: 4000, location: 'Monterey', phone: '(802) 555-1234', country: 'US', parent: 11 }, { id: 14, department: 'Quality Assurance', budget: 4800, location: 'Monterey', phone: '(408) 555-1234', country: 'US', parent: 12 }, { id: 15, department: 'Customer Support', budget: 3800, location: 'Monterey', phone: '(408) 555-1234', country: 'US', parent: 12 }, { id: 16, department: 'Research and Development', budget: 4600, location: 'Burlington, VT', phone: '(408) 555-1234', country: 'US', parent: 12 }, { id: 17, department: 'Customer Services', budget: 8500, location: 'Burlington, VT', phone: '(408) 555-1234', country: 'US', parent: 12 }, { id: 18, department: 'Corporate Headquarters', budget: 16000, location: 'ShangHai', phone: '(2108) 555-29321', country: 'China', parent: null }, { id: 19, department: 'Sales and Marketing', budget: 5000, location: 'Xian', phone: '(2108) 555-29321', country: 'China', parent: 18 }, { id: 20, department: 'Finance', budget: 21000, location: 'ShangHai', phone: '(2108) 555-29321', country: 'China', parent: 18 }, { id: 21, department: 'Engineering', budget: 10000, location: 'ShangHai', phone: '(2108) 555-29321', country: 'China', parent: 18 }, { id: 22, department: 'Field Office: East Coast', budget: 21000, location: 'Beijing', phone: '(2286) 62424-1000', country: 'China', parent: 19 }, { id: 23, department: 'Field Office: East Coast', budget: 5000, location: 'Boston', phone: '(2108) 555-29321', country: 'China', parent: 19 }, { id: 24, department: 'Pacific Rim Headquarters', budget: 3000, location: 'Kuaui', phone: '(2108) 555-29321', country: 'China', parent: 19 }, { id: 25, department: 'Marketing', budget: 15000, location: 'Xian', phone: '(2108) 555-29321', country: 'China', parent: 19 }, { id: 26, department: 'Field Office: Singapore', budget: 24000, location: 'Singapore', phone: '(606) 555-52186', country: 'China', parent: 24 }, { id: 27, department: 'Field Office: Japan', budget: 5000, location: 'Tokyo', phone: '(24024) 555-15196', country: 'China', parent: 24 }, { id: 28, department: 'Consumer Electronics Div', budget: 13000, location: 'Burlington, VT', phone: '(8019) 555-29321', country: 'China', parent: 21 }, { id: 29, department: 'Software Products Div', budget: 8000, location: 'ShangHai', phone: '(2108) 555-29321', country: 'China', parent: 21 }, { id: 30, department: 'Software Development', budget: 21000, location: 'ShangHai', phone: '(8019) 555-29321', country: 'China', parent: 28 }, { id: 31, department: 'Quality Assurance', budget: 51900, location: 'ShangHai', phone: '(2108) 555-29321', country: 'China', parent: 29 }, { id: 32, department: 'CChinatomer Support', budget: 32100, location: 'ShangHai', phone: '(2108) 555-29321', country: 'China', parent: 29 }, { id: 33, department: 'Research and Development', budget: 1800, location: 'Burlington, VT', phone: '(2108) 555-29321', country: 'China', parent: 29 }, { id: 34, department: 'CChinatomer Services', budget: 26600, location: 'Burlington, VT', phone: '(2108) 555-29321', country: 'China', parent: 29 }];
.grid .gc-cell.c2 { justify-content: flex-end; } .grid .gc-cell.c3 { justify-content: flex-end; } /*# sourceMappingURL=data:application/json;charset=utf8;base64,eyJ2ZXJzaW9uIjozLCJzb3VyY2VzIjpbIkRhdGFWaWV3cy9UcmVlR3JpZC9DYWxjdWxhdGVkSGllcmFyY2hpY2FsQ29sdW1uL3B1cmVqcy9zdHlsZXMuc2NzcyIsIkRhdGFWaWV3cy9UcmVlR3JpZC9DYWxjdWxhdGVkSGllcmFyY2hpY2FsQ29sdW1uL3B1cmVqcy9zdHlsZXMuY3NzIl0sIm5hbWVzIjpbXSwibWFwcGluZ3MiOiJBQUFBO0VBQ0UseUJBQUE7QUNDRjs7QURFQTtFQUNFLHlCQUFBO0FDQ0YiLCJmaWxlIjoiRGF0YVZpZXdzL1RyZWVHcmlkL0NhbGN1bGF0ZWRIaWVyYXJjaGljYWxDb2x1bW4vcHVyZWpzL3N0eWxlcy5jc3MiLCJzb3VyY2VzQ29udGVudCI6WyIuZ3JpZCAuZ2MtY2VsbC5jMiB7XG4gIGp1c3RpZnktY29udGVudDogZmxleC1lbmQ7XG59XG5cbi5ncmlkIC5nYy1jZWxsLmMzIHtcbiAganVzdGlmeS1jb250ZW50OiBmbGV4LWVuZDtcbn1cbiIsIi5ncmlkIC5nYy1jZWxsLmMyIHtcbiAganVzdGlmeS1jb250ZW50OiBmbGV4LWVuZDtcbn1cblxuLmdyaWQgLmdjLWNlbGwuYzMge1xuICBqdXN0aWZ5LWNvbnRlbnQ6IGZsZXgtZW5kO1xufSJdfQ== */