Cell
formulas description
TreeGrid
v3.5
Updates
3.5
Added function countrows
3.3
Updated aggregate functions
Added DebugCalc
Many information were updated and added
There can be columns
marked as calculated with one
expression for whole column. Expression is string in parameter Formula (if set, column is calculated).
These columns cannot be edited.
There can be rows
marked as calculated with different
expressions in every cell. Calculated row must have set attribute Calculated. Every calculated cell in
row has own Formula parameter. If
cell has not set Formula, it is not calculated even if this column is
calculated.
Calculated cells cannot be edited.
Remember, if you use
TreeGrid.dll/so as server
application, not all expressions are possible, see
Server calculations.
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.
You can use cell values from all fixed rows. These values can be
accessed by function Get (id, “column”). id is keyword (row’s
id), but column is 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 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.
You cannot use any other cell values from variable rows, except those
were mentioned above.
You cannot use relative reference to other row or column.
If expression results to
If any input value is null and you use some arithmetic operation it will
result to
Thus, set explicitly
all cell values used in formulas, you can do it
also in defaults. If value is not set, it is usually displayed as 0 according
to its formatting, but cannot be used in formula!
If you have any problems with calculations, you can set <Cfg DebugCalc=’1’> to see all errors in
formulas.
Cells in row are calculated in order given by CalcOrder attribute – it is comma separated array string of column
names. 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. In tree, first are calculated children and after them parent row.
Thus, 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 !
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.
Row’s children are computed always before the row.
Deleted rows are not iterated in aggregate functions.
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.
Predefined keywords for TreeGrid are column names, row ids, aggregate
function names, any custom function names, Get,
Row, Col, Grid, Children and val. 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.
Data type can be number or string.
Number is used for TreeGrid types Bool, Int, Float, Enum, Date.
String is used for text columns. Html and Pass types cannot be used
formulas.
Enum type is index to Enum array.
Date is number of milliseconds from GMT
Type conversions are the same as in JavaScript.
Example: number + string = string, number – string = number.
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
Constants also can be calculated.
White space characters <=32 are ignored except in string constants.
Function calls use standard C++/JavaScript syntax: funcname (argument1,
argument2, ...).
Function names are case sensitive. All TreeGrid function names are
written in lower case except Get macro.
You can use global object Math with all standard JavaScript functions
and constants (for example Math.abs(col1))
Constants
E e = 2.718281828459045
LN10 ln (10) = 2.302585092994046
LN2 ln (2) =
0.6931471805599453
LOG10E log 10 (e) = 0.4342944819032518
LOG2E log 2 (e) = 1.4426950408889633
PI π = 3.141592653589793
SQRT1_2 1 / √2 = 0.7071067811865476
SQRT2 √2 = 1.4142135623730951
Functions
random ( ) random number in range 0.0 – 1.0
abs (x) absolute value
round (x) rounds to integer, for example
2.5 => 3, -1.4 => -1, -2.5 =>
-2
ceil (x) nearest bigger number, for
example 1.3 => 2, -1.3 = > -1
floor (x) nearest smaller number, for example 1.3 =>
1, -1.3 = > -2
exp (x) e power x
log (x) ln x, decimal logarithm is
LOG10E * log(x), binary is LOG2E*log(x).
pow (x, y) x power y.
sqrt (x) √x
sin (x) sin x, in radians
cos (x) cos x, in radians
tan (x) tan x, in radians
acos (x) arccos x, x must be in range
–1.0 to 1.0
asin (x) arcsin x, x must be in range
–1.0 to 1.0
atan (x) arctan x
atan2 (y,x) arctan x/y
All aggregate functions iterate all children of the row, where is
function used. If row has not children returns 0.
In fixed rows aggregate functions iterate all variable root rows (in
pure grid all variable rows).
First parameter of any function is column name in quotes or “” for
actual column, if function has only one parameter, the column name can be
omitted to use actual column.
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. For sumif and
countif the name is “col + function_name + ecol”. For countrows is “countrows +
type”.
sum (string col) Sums all values in column
sumsq (string col) Sums all squares of values in column
sumif (string col, string condition, string
ecol) Sums all values in column
ecol, where the value in column col satisfies condition.
If ecol is missing,
sums values in col, if function has only one parameter (condition), sums actual
column
condition is code
to evaluate, must return boolean value.
condition is running in
context of TCalc and actual value of column col is in property val. In very simple condition can be
keyword val missing.
Examples:
sumif(“A”,”<5”,”B”) Sums values
in column B in rows that have in column A value less then 5.
sumif
(“A”, “val>=5 && val<10”) Sums
values in column A in rows that have in column A value in range <5,10).
sumif(“val<5
&& B<10 || val>=5 && Get(F,’B’)<10”) Sums values in actual column in rows that
satisfied the condition.
count ( ) Returns count of rows
counta (string col) Counts all non blank values in the column
countblank (string col) Counts all blank values in the column
countif (string col, string condition) Counts
all values in column that satisfies the condition, see sumif.
product (string col) Multiplies all values in column
max (string col) Returns maximum value from the column
min (string col) Returns minimum value from the column
countrows (int type) Counts specific count of rows according to type. type is bit array.
type&1 include filtered rows, type&2 include deleted rows, type&4 include all children, not only
immediate,
type&8 selected rows only (the count is
not updated after selection changes).
Next functions 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.
You can use any JavaScript statement or function. Evaluation has context
of objects TCalc. So you can use its
parameters and functions and you can add member functions or properties to
TCalc and use them directly. For example, you can reference values from some
user configuration or values set in another html input.
All custom functions you must define in script file not in formula
itself.
TCalc
TGrid Grid Pointer to actual grid where calculation is
running.
TRow Row Actual row, where the being computed cell
lies.
string
TRow [] Children All child rows of actual rows. Is filled only if row
is Calculated. For fixed row there are all root variable rows. Does not contain
deleted rows.
There are also available all aggregate functions and cell values, see
paragraph Input values.
For example, simple aggregate count function is defined: TCalc.prototype.count = function(){ return this.Children.length; }
Remember, if you are using server paging and you want use custom
functions, you must also prepare bodies on server side by these functions.