Skip to content

Calculation dependencies in CALUMO

With the use of custom formula, the formula must return a value to the cell each time the sheet is calculated.

CALUMO Calculation

In CALUMO, because there may be inter-dependencies between two CALUMO formula cells and because of the need to get data from a server to calculate certain formula, CALUMO makes several passes when a sheet calculate event fires:

PASS #1

This is the first calculation of the sheet as triggered by the user

  • As each cell comes in to the engine, it is cached (so CALUMO knows to act on it)
  • *Calc* is written to the cell
  • Once all CALUMO Formula cells have been cached, another sheet calculate is fired

Info

This is just an Excel calculation and so is very fast (no data has been requested from the server yet)

PASS #2..n

This is internal to the CALUMO engine and will happen as many times as needed until there are no CALUMO Formula cells left in *Calc* (or a maximum number of calculation iterations per cell has been reached).

  • Cached cells are grouped by formula type and flagged for processing - only if none of their arguments contain *Calc* or #VALUE!
  • For each group with cells ready to process, their processing action is performed (eg CGET formula requests data from the server or CCONCAT formula performs the concatenation)
  • Result is returned and the cached CALUMO Formula cell is updated with the value
  • The current value is set on the cell (this may be *Calc* for some cells as they had dependencies that had not yet been satisfied. ie one of their arguments still says *Calc*)

When complete, CALUMO performs a sheet calculate - one last time - to make sure all the formula (Excel and CALUMO) are updated with their final values.  Then control returns to the user.

Understanding CALUMO Formula Dependencies

The above method for calculating CALUMO Formula cells, creates a perfect calculation chain when CALUMO formula are dependent on other CALUMO formula, because each cell will report *Calc* until they are resolved.

So any downstream cell that depends on them will see one of their arguments as *Calc* and know not to calculate and so on.However, there are situations in reports where it is impossible for the engine to create a correct dependency chain. For example, if you have a CMEMBER in B1 and its UID in A1 and then a CMEMBERPARENT in B2 pointing at the UID in A1, because the UID cell is not a CALUMO Formula cell, the CMEMBERPARENT in B2 has no way of knowing that it needs to wait for the CMEMBER in B1 to finish calculating before it calculates, so, because there is no innate calculation order in the engine, there is a possibility that the CMEMBERPARENT will calculate before the CMEMBER and if the CMEMBER was driven by a QueryString parameter in a Published Report, it would not update its UID in A1 until it calculated which would mean that the CMEMBERPARENT in B2 calculated against the original UID, not the updated UID.

To solve this, you can forcefully create a calculation chain for the CMEMBERPARENT by wrapping the UID argument in an IF statement that checks to see if the CMEMBER cell is reporting *Calc* and if so, it makes the UID argument return *Calc* so the engine knows this cell is not ready to be processed yet, or it returns the UID so that the engine knows that it is ready.