Calculations - cell data formulas

TreeGrid documentation

This document describes NOT EDITABLE formulas entered by developers into data.
To read about EDITABLE formulas entered by users directly into cells see Editable cell formulas.
If calculating in grid is enabled by user. If disabled, formula cells are not re-calculated after change.
Space rows are always (re-)calculated independently on Calculated setting.
Formula for calculated column. If set, all cells in the column are calculated.
Remember, set CalcOrder for every row if you use in some formula result of another formula on the same row.

The calculated cell is set as not editable by default.
You can set CanEdit='1' to let a user to edit this cell, but the cell content is recalculated after the value is edited, so the cell value should be set as source in formula to take the editing effect.>
Formula for calculated cell.
The cell Formula is used only if the row is Calculated or the column also has Formula.
You can set cell Formula in calculated column (column with Formula) to empty string to not calculate this cell.
You can calculate also any row or cell attribute.
For example <I CanEditFormula='...' Col1ColorFormula='...' CalcOrder='CanEdit,Col1Color'/>
The attribute xxxFormula is used only if the attribute is listed in the row CalcOrder array.
Remember, not all row / cell attributes can be correctly calculated, some state attributes like Deleted or Visible should not be changed by formula.
If row is calculated.
Set this attribute if the row cells contain Formula attribute(s) in not calculated column (column without Formula).
For Space rows is this attribute set automatically according to their Formula attributes in their cells.
Comma separated list of calculated cells to specify order in which the cells are calculated.
Specify the CalcOrder when some formulas have other formula results as their sources to specify which formula will calculated first.
If CalcOrder is not set, the calculation order is always according to alphabet order of column names.
If you set CalcOrder, you must specify all cells you want to calculate. If some cell is not listed, it is ignored even if it or its column has Formula set.

As CalcOrder item you can use asterisk (*) to calculate all row's children on this position.
Or you can use asterisk with number (*1,*2,...) to calculate row's children according to its CalcOrderX attribute.
You can define more CalcOrder attributes, X is number value, for example CalcOrder1.
This CalcOrder(s) you can reference in parent CalcOrder attribute by *X, where X is the CalcOrder number.
If you use such complex calculation order, you usually need to set Recalc attribute of all rows to 256, to recalculate whole grid after change.
Global calculation order for fixed rows if you have more fixed rows and want calculate cells in fixed rows at random.
If set it is used instead of CalcOrder attribute in fixed rows.
In this array you can point to individual cells by id$col,
For example "F1$A,F2$C,*" calculates cell [F1,A], next [F2,C] and next all variable rows.
What to recalculate, if any value in row has been changed or row moves / is deleted / added. Binary mask:
1 - Row, 2 - Calculated parents, 4 - All parents, 8 - Immediate children, 16 - All children, 32 - (since 8.0) All siblings, 256 - Whole table
(Since 8.1) If set to -1, it does not recalculate anything (neither fixed rows)
It must contain at least all bits as in cell's Recalc parameters to ensure appropriate recalculations.
What to recalculate, if cell value has been changed. Binary mask:
1 - Row, 2 - Calculated parents, 4 - All parents, 8 - Immediate children, 16 - All children, 32 - (since 8.0) All siblings, 256 - Whole table
(Since 8.1) If set to -1, it does not recalculate anything (neither fixed rows)
It must not contain more bits than row's Recalc parameters to ensure appropriate recalculations.
1 - when row's parent calculates some aggregate functions on its children, this row calculates its children instead of itself - behaves like its children was directly in its parent.
It is usually set in Group default.
If there are formulas in grid that use selected rows.
If set, all calculated rows are recalculated after selection changes.
If set, all rows are recalculated after any column manipulation except resize.
If calculates hidden space rows. Since 12.1 the hidden space rows are not calculated by default.
If set to 1, it marks as changed all changed calculated cells and rows.
(Since 9.2) If set to 2, it marks as changed also the cell calculated for first time if resulted to different value.
(Since 14.0) If set to 3, it marks as changed also the cell calculated for first time if it was null.
(Since 9.1) It can be set to comma separated list of columns to affect only these columns. Cannot be set by API to this list.
In the first cell calculation it stores the calculated value. If some next calculation returns different result the stored one, it marks the cell changed.
If the next calculation returns the same result as the first stored result, the changed flag is cleared.
It will also upload these changes to server.
Comma separated list of columns to mark their calculated cells as changed if their formula resulted to different value, even first time.
It sets the CalculatedChanges = 2 to particular columns. Set in <B> tag for Cfg.Paging = 3 or in <I> tag for Cfg.ChildPaging = 3
Contain results of aggregate function on children rows in the column.
For example <I Asum='123.5'/>
It is filled in Request XML as request for re-calculating grid after calculations have been permitted by user.
Take care, because of backward compatibility it is ReCalc, not Recalc.
Called before grid is calculated or recalculated.
Can be used for custom calculations. Return true to suppress default calculations.
Define this event if you want to calculate cells in special order that cannot be covered by CalcOrder.
Called for initial calculation or for recalculation of whole grid after reload or filter is applied. From Calculate and Recalculate methods.
If row is set, it is called to calculate only row’s children after loading of page. If col is set, it is a called after some cell changed to recalculate appropriate formulas.
(Since 11.0) If fixedonly is set, it is called to calculate only fixed rows after recalculating some cell.
In custom calculations you cannot use predefined formulas, you just have to provide your owns to calculate cells.
See row API how to iterate rows by API. You can also test row's attributes Visible and Deleted. Or any other row's attribute.
Called after grid is calculated or recalculated. Called from Calculate and Recalculate methods.
Called before cell value or attribute is calculated.
col is column name or attribute name to calculate.
val is new calculated value that will be set to the cell.
(since 9.0) Calc is calculation object for given cell, it is possible to call calculation functions like sum() as methods of this object.
Returns new value to set to the cell or null to use val.
Calculates grid.
If show = 1 displays all changes.
If calconly = 1 calculates only Calculated rows.
Re-calculates grid after given cell changes, according to cell's or row's parameter Recalc.
Always recalculates fixed calculated rows.
It recalculates also Gantt chart in that row, if any.
Recalculates given rows in given order.
Recalculates also Gantt chart in these rows. If the recalculation changes Gantt size, it refreshes whole Gantt chart.
It does not recalculate any other row (except the Gantt, see above), so if the calculation affects some other rows, they should be recalculated manually too.
Remember, this function breaks CalcOrder in tree, as stated above!
To display the changes in grid call the function with the second parameter (show) as 1.
The rows can be: a) array of TRow object, b) one TRow object, c) array of row ids (strings), d) one row id (string)
Enables calculations in grid and recalculates it. It fails if calculations are already enabled.
Disables calculations in grid. It fails if calculations are already disabled.

Formulas

Formulas in TreeGrid have JavaScript syntax. The formula code is run as JavaScript function in TCalc object context so all TCalc methods are directly accessible like global functions.
TCalc object contains all the aggregate functions like sum and properties Grid, Row, Col as the actual cell see custom functions.
If you have any problems with calculations, you can set <bdo Debug='Problem'> to see all errors in formulas in debug window.

Input values

In formula you can use cell values from the same row. These values are identified by column name. If you use calculations, choose column names and fixed rows ids carefully to not conflict with other keywords.
For example Formula='Col1+Col2*Col3' where Col1,Col2,Col3 are column names.

Special keywords usable in the formula: Grid, Parent, Row, Col, Attr, Value and function Get.

You can use cell values from all fixed and Space rows. These values can be accessed by function Get (id, "column"). id is keyword (row's id), but column is quoted string with column name.

You can use cell values from immediate parent row. These values can be accessed by function Get (Parent, "column"). Parent is keyword (exact word Parent), but column is quoted string with column name.

You can also access any other row's attributes given in input data, even any user defined attributes by Get (Row, "attribute"). Row is keyword (exact word Row), but attribute is string with attribute name. Instead of Row you can use also Parent or id as mentioned above.

There is also keyword Col as actual column name where the formula is called.

(new 7.0) For calculated cell attributes, there is keyword Attr to get the attribute name (like "Col1Class") and keyword Value to get actual cell value).

There is also keyword Grid as the actual grid.

Calculation order

Cells in row are calculated in order given by CalcOrder attribute - it is comma separated array string of column names or row attributes. If CalcOrder is not set for the row, cells are calculated in alphabetical order.
If CalcOrder is set, only these cells are calculated, even if other cells have Formula attribute.
In vertical direction, first are calculated body rows and after them fixed rows and finally Space rows. In tree, first are calculated children and after them parent row.
You can change the direction by advanced calculation order, see below.
If you use another calculated cell value as input in formula, you can reference only cells, which are prior (in calculation order) to the actual cell!

Advanced calculation order

You can control calculation order by more advanced way:
a) Fixed rows' cells you can calculate by random by setting <Cfg> CalcOrder attribute, and there you can set individual cells by id$col, for example "F1$A,F2$C"
b) In any row's CalcOrder or global CalcOrder you can set position of calculating row's children by placing '*' to CalcOrder
c) You can define more calculating phases in tree by dividing CalcOrder to more attributes: CalcOrder, CalcOrder1, CalcOrder2 ,...
For example you can have parent's (or global) CalcOrder = "*,A,B,*1" and all children's CalcOrder = "*,A,B" and CalcOrder1 = "C,D,*1"
If you use advanced calc order, you usually need to set Recalc attribute of all rows to 256 - to recalculated whole grid after change.

Aggregate functions

In the tree, all aggregate functions in row iterate row's immediate children only, without their children.
If the row is fixed, aggregate functions iterate all variable root rows without their children.
By default, row's children are computed before the row or you can use advanced calculation order by asterisks, see above.
Deleted and filtered rows are not iterated in aggregate functions.

Operators

Operators use standard C++/JavaScript syntax: +, -, *, /, ! (not), % (modulo), & (bit AND), | (bit OR), ^ (bit XOR), && (logical and), || (logical OR), <<, >> (bit shift), == (equals), != (not equals), <=, >=, <, >.
Priority of operators is the same as in JavaScript. Always you can use ( ).
It can also be used operator (condition ? true_result : false_result). This operator has the least priority so you must use () for other calculation.
Remember, the '&' and '<' characters are XML entities and must be replaced by & and <

Keywords

Predefined keywords for TreeGrid are column names, row ids, aggregate function names, any custom function names, Get, Row, Col, Grid, GetChildren. These keywords must not conflict with each other and with all JavaScript reserved keywords. Keywords are not quoted in formulas. Keywords are case sensitive.
JavaScript reserved keywords are: break, case, catch, continue, default, delete, do, else, false, finally, for, function, if, in, instanceof, new, null, return, switch, this, throw, true, try, typeof, var, void, while, with.

Strings & numbers

Data type can be number or string like in JavaScript.
All numbers in grid are always numbers and not strings independently on cell type!
Take care about summing strings and numbers. string + number = string. But string - number = number.
You can always explicitly convert value to string by (val+"") and to number by (val-0).
Empty cells are strings except for Int and Float types without CanEmpty set to 1.
Date type is number (count of milliseconds since 1/1/1970). Empty Date is string except CanEmpty is set to 0.

Constants

Number constants are written normally in English (C++/JavaScript) notation. String constants are in quotes or double quotes. If string contains quote, double quote or backslash it must be preceded by backslash.
Date constants are in seconds from 1/1/1970 00:00:00 GMT, so you can add constant to datetime or test two dates if less/greater. For example to add one day you need to add constant 24*60*60*1000.
Constants also can be calculated.

White space

White space characters <=32 are ignored except in string constants.

Function calls

Function calls use standard C++/JavaScript syntax: funcname (argument1, argument2, ...).
Function names are case sensitive. All TreeGrid calculation function names are written in lower case except Get macro.

Mathematical functions

Constants

Math.E	e = 2.718281828459045
Math.LN10	ln (10) = 2.302585092994046
Math.LN2	ln (2) = 0.6931471805599453
Math.LOG10E	log 10 (e) = 0.4342944819032518
Math.LOG2E	log 2 (e) = 1.4426950408889633
Math.PI	∏ = 3.141592653589793
Math.SQRT1_2	1 / √2 = 0.7071067811865476
Math.SQRT2	√2 = 1.4142135623730951

Functions

Math.random ( )	random number in range 0.0 - 1.0
Math.abs (x)	absolute value
Math.round (x)	rounds to integer, for example 2.5 => 3, -1.4 => -1, -2.5 => -2
Math.ceil (x)	nearest bigger number, for example 1.3 => 2, -1.3 = > -1
Math.floor (x)	nearest smaller number, for example 1.3 => 1, -1.3 = > -2
Math.exp (x)	e power x
Math.log (x)	ln x, decimal logarithm is LOG10E * log(x), binary is LOG2E*log(x)
Math.pow (x,y)	x power y
Math.sqrt (x)	√x
Math.sim (x)	sin x, in radians
Math.cos (x)	cos x, in radians
Math.tan (x)	an x, in radians
Math.acos (x)	arccos x, x must be in range -1.0 to 1.0
Math.asin (x)	arcsin x, x must be in range -1.0 to 1.0
Math.atan (x)	arctan x
Math.atan2 (y,x)	arctan x/y

Other functions

maximum (a,b,c,...)	returns maximum of given values
minimum (a,b,c,...)	returns minimum of given values

Aggregate functions

All aggregate functions iterate by default all children of the row, where is function used. If row has not children return 0.
Deleted and filtered rows are by default not iterated.
In fixed rows aggregate functions iterate all variable root rows (in pure grid all variable rows).

When used server paging (ChildPaging=3 or Paging=3), the not yet loaded body or parent row must contain result of the function. The attribute name is "col + function_name", for example Col1sum for column named Col1. Or use server parameter to name the attribute if the function supports it.
Only function count called without parameters reads Count attribute, so it does not be precalculated.

Basic aggregate functions

For sum in editable formula see sum.
Sums all values in column col, in rows where the conditions are satisfied. If col is missing or null, sums actual column.
All parameters are optional and can be missing.
condition is formula to evaluate, must return boolean value. condition is running in context of TCalc like standard formula. The Row variable contains actually iterated row.
type specifies which rows will be iterated, bit array:
server is set only in server paging to use precalculated values from attribute "sum"+server. If not set, the col + "sum" is used.

Examples:

sum()	Sums all values in column where is called. Iterates only visible, not deleted, immediate children (for fixed row all root rows).
sum('A')	Sums all values in column 'A'. Iterates only visible, not deleted, immediate children (for fixed row all root rows).
sum(7)	Sums all values in column where is called. Iterates all children (for fixed row all root rows and their children). Including deleted and hidden rows.
sum('Row.Def.Name=="N1"')	Sums all values in column where is called. Iterates only children with Def='N1'.
sum('A',4)	Sums all values in column 'A'. Iterates selected children (for fixed row all root rows and their children).
sum("A","A<5")	Sums values in column A, only values less than 5.
sum("A","B>=5 && B<10",4)	Sums values in column A, only values from rows where B is in range <5,10), iterates whole tree

sum("A<5 && B<10 || A>=5 && Get(F1,'B')<10",3)

Sums values in actual column in rows that satisfied the condition, including deleted and hidden rows.

Remember, the '&' and '<' characters are XML entities and should be replaced by &amp; and &lt; to produce valid XML (TreeGrid accepts also invalid XML, but its processing can be slower).
Returns count of rows. See sum function. For count in editable formula see count.
Runs the formula for every row.
The formula is running in context of TCalc like standard formula.
The actual temporary result is stored in variable Result, formula should read and use it. Return value of formula is stored back to Result and is available for the next row formula.
Result is 0 on beginning.
The Row variable contains actually iterated row.
The formula can contain any JavaScript expression.
See sum function.

Examples:

calc("Result+A")	Sums all values in column 'A'. Iterates only visible, not deleted, immediate children (for fixed row all root rows).
calc("Result+A-B")	Computes value in 'A' minus value in 'B' and sums the results. Iterates only visible, not deleted, immediate children (for fixed row all root rows).
calc("Result>A?Result:A")	Returns maximal value from column 'A'.

calc("if(Result>A) return Result; else return A;")

Returns maximal value from column 'A'. The same as previous, different notation.

calc("maximum(Result,A)")

Returns maximal value from column 'A'. The same as previous, different notation.

Special aggregate functions

Returns maximum value from the column. If there is no row to iterate, returns "". See sum function. For max in editable formula see max.
Returns minimum value from the column. If there is no row to iterate, returns "". See sum function. For min in editable formula see min.
Sums all dates in column with Range=1 Type="Date". It just creates new range from all intervals and updates it to not contain duplicate value cross intervals. See sum function.
Sums all squares of values. See sum function. For sumsq in editable formula see sumsq.
Counts all non blank values in the column. See sum function. For counta in editable formula see counta.
Counts all blank values in the column. See sum function. For countblank in editable formula see countblank.
Multiplies all values in column. See sum function. For product in editable formula see product.

String aggregate functions

Joins values in column. The values are separated by ValueSeparator (semicolon by default).
Joins values in column. The values are separated by ValueSeparator (semicolon by default).
The same values will be counted and placed once as "count x value". If the input values are already joined values, it splits them and recalculates the counts.
Sums values in column as standard sum function.
If any value is a not empty string, it joins the values as joinsum.
If all the values are empty strings or no values are in the column, returns empty string instead of zero.

Other aggregate functions that cannot be used for server paging (cannot be pre-calculated)

average (string col)	Calculates average of column values (sum/count).
median (string col)	Returns median of column (middle value of range).
mode (string col)	Returns modus of column (the most frequent value in range).
avedev (string col)	Calculates average deviation of column.
stdev (string col)	Calculates standard deviation of column.
stdevp (string col)	Calculates standard deviation of column. column is the selection of data.
vara (string col)	Calculates the sample variance of column.
varp (string col)	Calculates the sample variance of column. column is the selection of data.
rank (string col, object val)	Returns position of val in sorted column (column is sorted ascending). Starting from 0.

Special functions for actions

choose (value, values[ ], items[ ],custom)

Returns one item from items on position where values[pos] == value. If no value equals, returns custom.

	If value is null, uses cell value instead. If values is null, tests cell Defaults, if Defaults is also null, uses array [0,1,2,3,...].
	Call with null value and null values if you want to convert cell value from Defaults to another value or index.
	If items is null, returns one item from cell Defaults, if Defaults is also null, returns the index to values. If custom is null, returns cell Custom attribute.
	Call with null items and custom if you want to get item from Defaults according to index or another condition.
	Example: choose ("B",["A","B","C"],["X","Y","Z"],"None") - returns "Y", because value "B" is on second position in values and "Y" is second in items.
	This function can be used also for Select type cells.

split (value, separator)	Splits value string to array. Items are separated by separator. If value is null, uses cell value instead.
	If separator is null, splits by the first character in value. The first empty item is discarded.

Custom functions

In formula you can use any JavaScript global function.
You can define any your custom global JavaScript function and call it from any formula.
You can also call from formula any grid method by Grid object.

Global parameters and methods to use in formula

TGrid	Grid	Pointer to actual grid where calculation is running. You can call any its method.
TRow	Row	Actual row, where the being computed cell lies.
string	Col	Actual column, where the being computed cell lies.
TRow[ ]	GetChildren( )	Returns all immediate child rows of actual rows. For fixed row it returns all root variable rows. It does not contain deleted and filtered rows.

For example Formula = "escape(Col1)" or Formula=''Grid.GetText('Picture') + Col3"