Calculated columns

If you want to calculate column's data, you have to set its attribute Formula to expression to calculate.
In this expression you can use any JavaScript syntax, operators and even functions.
To reference other cells in actual row, you can use column name as variable.
Calculated columns cannot be edited by user.

<Grid>
  <Cfg id='Calc1'/>
  <Cols>
    <C Name='Data' Type='Int'/>
    <C Name='Result' Type='Int' 
       Formula='Data*Data-1'/>
  </Cols>
  <Body>
    <B>
      <I Data='10'/>
      <I Data='-5'/>
      <I Data='0'/>
      <I Data='134'/>
    </B>
  </Body>
</Grid>

Show example

Calculated (result) rows

Show data source

Calculated row must have attribute Calculated='1'. And for every cell you have to set its Formula.
In calculated rows are never used formulas from columns. If you want to use the same formula as in column, you need to repeat it to the calculated row's cell.
Calculated rows are usually fixed rows or rows with children.
In calculated rows you can use aggregate functions to iterate all variable rows or row's children.
Cells with formula cannot be edited by user.

<Grid>
  <Cfg id='Calc2'/>
  <Cols>
    <C Name='Data' Type='Int'/>
    <C Name='Result' Type='Int' 
       Formula='Data*Data-1'/>
  </Cols>
  <Body>
    <B>
      <I Data='10'/>
      <I Data='-5'/>
      <I Data='0'/>
      <I Data='134'/>
    </B>
  </Body>
  <Foot>
    <I Calculated='1' 
       DataFormula='count()' 
       ResultFormula='sum()'/>
    <I Calculated='1' 
       DataFormula='sum()' 
       ResultFormula='Data*Data-1'/>
  </Foot>
</Grid>

Show example

Calculation order

Show data source

If you use in formulas results of other formulas, you need to set calculation order in that will be these formulas calculated.
By default there is used alphabetical order, acording to column names (not by column position !).
You have to set in every row its attribute CalcOrder to comma separated list of all calculated cells.
Any cell not listed in CalcOrder will not be calculated, even it has set Formula.
To set the same CalcOrder to more rows you can set it to their default row (<D>). Every row without Def attribute has default row named "R".

<Grid>
  <Cfg id='Calc3'/>
  <Cols>
    <C Name='Data1' Type='Int'/>
    <C Name='Data2' Type='Int'/>
    <C Name='Tmp1' Type='Int' 
       Formula='Data1*Data1+Data2*Data2'/>
    <C Name='Tmp2' Type='Int' 
       Formula='Data1*Data1-Data2*Data2'/>
    <C Name='Result' Type='Float' 
       Formula='Tmp1/Tmp2' Format='0.000'/>
  </Cols>
  <Def> 
    <D Name='R' 
       CalcOrder='Tmp1,Tmp2,Result'/>
  </Def>
  <Body>
    <B>
      <I Data1='10' Data2='6'/>
      <I Data1='-5' Data2='3'/>
      <I Data1='0' Data2='1'/>
      <I Data1='134' Data2='123'/>
    </B>
  </Body>
  <Foot>
    <I Calculated='1' Tmp1Formula='sum()' 
       Tmp2Formula='sum()' 
       Data1Formula='Tmp1*Tmp1+Tmp2*Tmp2' 
       Data2Formula='Tmp1*Tmp1-Tmp2*Tmp2' 
       ResultFormula='Data1/Data2' 
       CalcOrder='Tmp1,Tmp2,Data1,Data2,Result'
       />
  </Foot>
</Grid>

Show example

Calculated tree

Show data source

In tree you can use results in parent rows for its children. This can be used in the same way as calculated fixed rows, see example 2.
The aggregate functions iterate all immediate children of the parent.
The children are always calculated before their parent.
There is often the same setting for many rows, you can use defaults (one default row for all) to shorter data description.
If you refer to other cells, you can set row's or cell's attribute Recalc to specify what to recalculate if user changes the input value.

<Grid>
  <Cfg id='Calc4' MainCol='Data'/>
  <Cols>
    <C Name='Data' Type='Int' Width='120'/>
    <C Name='Result' Type='Int' 
	    Formula='Data*Data-1'/>
  </Cols>
  <Def>
    <D Name='Tree' Calculated='1' 
       DataFormula='count()' 
       ResultFormula='sum()' 
       DataFormat='Count=######'/>
  </Def>
  <Body>
    <B>
      <I Def='Tree'>
        <I Data='10'/>
        <I Data='-5'/>
      </I>
      <I Def='Tree'>
        <I Def='Tree'>
          <I Data='0'/>
          <I Data='8'/>
        </I>
        <I Data='5'/>
        <I Data='-1'/>
      </I>
    </B>
  </Body>
  <Foot>
    <I Def='Tree'/>
  </Foot>
</Grid>

Show example

Calculation order in tree

Show data source

By default are row's children calculated before row's cells. You can change this direction by adding '*' to CalcOrder to position, where you want to calculate children.
For more complex calculation order you can divide CalcOrder attributes to more parts (CalcOrder,CalcOrder1,CalcOrder2,...) and reference these orders from parent row's CalcOrder by *,*1,*2,...
In this example is used this calculation order:
1) First are calculated columns Total and Tmp upstairs (child rows before parent). The tmp column is used only as temporary and should be hidden by setting Visible='0' and CanHide='0'.
2) Next are calculated columns PerParent and PerTotal downstairs (parent before child rows). Because formulas in these columns use calculated result from fixed Root row.
Because of complex calculation order, the Recalc attribute of all rows is set to 256 to recalculate the whole grid after any change.
If you have more fixed rows and you want to calculate their cells by random, you can use <Cfg> attribute CalcOrder instead of CalcOrder of fixed rows.

<Grid>
  <Cfg id='Calc4' MainCol='Node'/>
  <Cols>
    <C Name='Node' Type='Text'/>
    <C Name='Active' Type='Bool'/>
    <C Name='Cost' Type='Float'/>
    <C Name='Total' Type='Float'/>
    <C Name='Quantity' Type='Int'/>
    <C Name='Tmp' Type='Float'/>
    <C Name='PerParent' Type='Int' 
       Format='0%'/>
    <C Name='PerTotal' Type='Int' 
       Format='0%'/>
  </Cols>
  <Header Wrap='1' Cost='Local cost' 
     Total='Total cost' 
     Quantity='Total quantity' 
     Tmp='Tmp cost (hidden)' 
     PerParent='% of parent cost' 
     PerTotal='% of total cost'/>
  <Def>
    <D Name='R' Calculated='1' Recalc='256'
       TotalFormula='Cost+sum("Tmp")' 
       TmpFormula='Active*Total*Quantity' 
       CalcOrder='*,Total,Tmp' 
       CalcOrder1='PerParent,PerTotal,*1'
       PerParentFormula='Tmp/Parent.Total'
       PerTotalFormula='PerParent*Parent.PerTotal'
       />
    <D Name='FirstLevel'
      PerParentFormula='Tmp/Root.Total' 
      PerTotalFormula='PerParent'
      />
  </Def>
  <Body>
    <B>
      <I Def='FirstLevel' Node='Level 1' 
        Active='1' Cost='125' Quantity='4'>
        <I Node='Level 1-1' Active='1' 
          Cost='25' Quantity='3'/>
        <I Node='Level 1-2' Active='1' 
          Cost='4' Quantity='4'>
          <I Node='Level 1-2-1' Active='0' 
            Cost='20' Quantity='8'/>
          <I Node='Level 1-2-2' Active='1' 
            Cost='5' Quantity='2'/>
        </I>
        <I Node='Level 1-3' Active='0' 
          Cost='122' Quantity='12'/>
      </I>
      <I Def='FirstLevel'  Node='Level 2' 
        Active='1' Cost='21' Quantity='3'>
        <I Node='Level 2-1' Active='1' 
          Cost='28' Quantity='4'/>
      </I>
    </B>
  </Body>
  <Foot>
    <I id='Root' CalcOrder='*,Total,Tmp,*1' 
      Node='Root' CanEdit='0' 
      ActiveType='Text' CostType='Text' 
      QuantityType='Text' TmpFormula=' ' 
      TmpType='Text' 
      TotalFormula='sum("Tmp")' 
      PerParentType='Text' PerTotal='1'/>
  </Foot>
</Grid>