TreeGrid v6.0

Calculations - cell formulas

TreeGrid documentation index

 

<Cfg>                bool       Calculated          [1]         Saved to cookies                                                                                                         .

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.

 

<C>                   string    Formula                                                                                                                                                     chg 6.0 .

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.

 

<cell>                string    Formula                                                                                                                                                     chg 6.0 .

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.

 

<I> <cell>          string    xxxFormula                                                                                                                                           new 6.0 .

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.

 

<I>                     bool       Calculated          [0]                                                                                                                                              .

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.

 

<I>                     string [] CalcOrder                                                                                                                                                             .

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 divide calculation of children to more phases where you can calculate different cells.

 

<I>                     string [] CalcOrderX                                                                                                                                                          .

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.

 

<Cfg>                string [] CalcOrder                                                                                                                                                             .

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.

 

 

<I>                     int          Recalc        [3]                                                                                                                                                            .

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, 256 – Whole table

It must contain at least all bits as in cell’s Recalc parameters to ensure appropriate recalculations.

 

<cell>                int          Recalc        [3]                                                                                                                                                           .

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, 256 – Whole table

It must not contain more bits than row’s Recalc parameters to ensure appropriate recalculations.

 

<I>                     bool       AggChildren    [0]                                                                                                                                              .

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.

 

<Cfg>                bool       CalculateSelected  [0]                                                                                                                                 .

If there are formulas in grid that use selected rows.

If set, all calculated rows are recalculated after selection changes

 

<B cell> <cell> type      sum, count, ...    Server paging                                                                                                                           .

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’/>

 

<Cfg>                bool       ReCalc       [0]         Server paging. Output attribute only.                                                                                        .

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.

 

API event          bool       OnCalculate     (TGrid grid, bool show, TRow row, string col)                                                                       .

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.

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.

 

API event          type      OnCalculateCell       (TGrid grid, TRow row, string col, type val, bool show)                                          .

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.

Returns new value to set to the cell or null to use val.

 

 

API method      void       Calculate            (bool show = 0, bool calconly = 0)                                                                                          .

Calculates grid.

If show = 1 displays all changes.

If calconly = 1 calculates only Calculated rows.

 

API method      void       Recalculate      (TRow row, string col, bool show = 0)                                                                                   .

Re-calculates grid after given cell changes, according to cell’s or row’s parameter Recalc.

Always recalculates fixed calculated rows.

 

<Actions>                      CalcOn                   Attached to event OnClickButtonCalc                                                                                   .

Enables calculations in grid and recalculates it. It fails if calculations are already enabled.

 

<Actions>                      CalcOff                   Attached to event OnClickButtonCalc                                                                                   .

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 ,...

             and you can reference these CalcOrders in parent CalcOrder by *X, where X is number in CalcOrder (1,2,...).

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”

             => The grid will calculate columns A,B upstairs (children first) and after it will calculate columns C,D downstairs (parent first)

See also CalcOrder tutorial.

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 &amp; and &lt;

 

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.sin (x)                     sin x, in radians

Math.cos (x)                    cos x, in radians

Math.tan (x)                    tan 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

 

formula             float       sum              (string col = null, string condition = null, int type = 0, string server = null)             chg 6.0 .

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:

             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 except CalculateSelected is set),

type&16 ignore AggChildren attribute.

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).

 

formula             float       count          (string condition = null, int type = 0, string server = null)                           chg 6.0 .

Returns count of rows. See sum function.

 

formula             type      calc (string formula, int type = 0, string server = null)                                                     new 6.0 .

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

 

formula             float       max (string col = null, string condition = null, int type = 0, string server = null)             chg 6.0 .

Returns maximum value from the column. If there is no row to iterate, returns “”. See sum function.

 

formula             float       min                (string col = null, string condition = null, int type = 0, string server = null)             chg 6.0 .

Returns minimum value from the column. If there is no row to iterate, returns “”. See sum function.

 

formula             string    sumrange           (string col = null, string condition = null, int type = 0, string server = null)     chg 6.0 .

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.

 

formula             float       sumsq       (string col = null, string condition = null, int type = 0, string server = null)             chg 6.0 .

Sums all squares of values. See sum function.

 

formula             float       counta       (string col = null, string condition = null, int type = 0, string server = null)             chg 6.0 .

Counts all non blank values in the column. See sum function.

 

formula             float       countblank        (string col = null, string condition = null, int type = 0, string server = null)     chg 6.0 .

Counts all blank values in the column. See sum function.

 

formula             float       product    (string col = null, string condition = null, int type = 0, string server = null)             chg 6.0 .

Multiplies all values in column. See sum function.

 

 

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”