Calculated measures

To create a calculated measure we need to set the aggregate type as "Calculation" or as "Calculation (detail)" in the measure editor.

The two types of calculated measure are:

• Calculation based on other measure values ("Calculation")

Measures which are calculated after other measures have been built using the standard measure processing. The calculation function has access to other measure values, dimension values and previous measure values. The "Calc all cells" checkbox turns on calculation for all cells, including "empty" cells (cells which do not have corresponding rows in the source data).

• Calculation based on source data ("Calculation (detail)")

Measures which are calculated on source data and built together with standard measures. The calculation function has access to the current source data row and to dimension values. The "Calc all cells" checkbox is disabled for this type of calculation.

The use of either type of calculated measure requires either a new calculation function to be written or an existing function to be chosen in the "Function" field - double-clicking on the "Function" field opens the measure editor. If the "Function" field is empty then a new function is automatically created.

The "Order" field sets the calculation order.

The measure editor window has three areas:

1. toolbar
2. text editor with syntax highlighting
3. treeview with available variables, functions and classes

The toolbar contains the actions for checking syntax, saving changes, cancelling changes and choice of programming language.

The text editor is used to compose and edit the function code (a 'script'). The function code is a program written in the chosen language that is interpreted by FastScript at calculation time. The function code must return a value through the "Result" variable.

The script has access to the following grid elements:

• dimension list - Dimensions : TDimensions;
• measure list - Measures : TMeasures;
• field list - SliceFields : TfcxSliceFields;
• service variable - CustomObject: TfcxCustomObject.

The service variable enables the reading/writing of user data for a particular measure cell. For example, a pointer for future use can be assigned to any object created in the calculation function. Each cell has its own service variable. The CustomObject can only be used in measures built on source data. If the CustomObject variable contains a pointer to an allocated object or memory area then it must be deallocated after use. For this purpose a 'Final: boolean' argument is passed to the function body. If 'Final' = 'True' then the finalization procedure is activated, which includes memory deallocation.

The following classes are available for use in calculated measure functions:

// Measure list

TMeasures = class
published
  property Items[AIndex: Integer]: TMeasure;
  property ItemByCaption[AIndex: String]: TMeasure;
  property ItemByName[AIndex: String]: TMeasure;
  property Count: Integer;
  property RecordCount: integer;
  property DetailValue[ARecordIndex: Integer; AFieldName: String]: Variant;
  property XLevel: integer;
  property YLevel: integer;
end;

// Dimension list

TDimensions = class
published
  property Items[AIndex: Integer]: TDimension;
  property ItemByCaption[AIndex: String]: TDimension;
  property ItemByName[AIndex: String]: TDimension;
  property XAxisItems[AIndex: Integer]: TDimension;
  property YAxisItems[AIndex: Integer]: TDimension;
  property IsTotalByCol: boolean
  property IsTotalByRow: boolean
  property XLevel: integer;
  property YLevel: integer;
  property XAxisLevelsCount: integer;
  property YAxisLevelsCount: integer;
end;

// Field list

TfcxSliceFields = class
published
    property Items[Index: Integer]: TfcxSliceField;
    property ItemByCaption[AIndex: String]: TfcxSliceField;
    property ItemByName[AIndex: String]: TfcxSliceField;
    property Count;
end;

// Measure

TMeasure = class
published
  property Caption: String;
  property CurrentValue: Variant;
  property CurrentCaption: String;
  property FieldName: String;
  property ColOffsetValue[Offset: integer]: Variant;
  property RowOffsetValue[Offset: integer]: Variant;
  property ColOffsetValueWithDimValue[ADimValue: Variant]: Variant;
  property RowOffsetValueWithDimValue[ADimValue: Variant]: Variant;
  property TotalValueForDims[ADimNames: String]: Variant;
  property ColRowOffsetValue[ColOffset, RowOffset: integer]: Variant;
  property ColRowOffsetValueWLevel[ColOffset, RowOffset, ColLevelOffset, RowLevelOffset: integer]: Variant
end;

// Dimension

TDimension = class
published
  property FieldName: String;
  property Caption: String;
  property CurrentValue: Variant;
  property CurrentCaption: String;
  property SubGroup: TDimension;
end;

// Field

TfcxSliceField = class(TPersistent)
published
  property FieldName: String;
  property Caption: String;
  property CurrentValue: Variant;
  property CurrentCaption: String;
  property FilterCount: Integer;
  property IsFiltered: Boolean;
end;

// Service object

TfcxCustomObject = class(TPersistent)
published
  property Value: Pointer;
  property ValueIsNil: Boolean;
end;

Here are detailed descriptions of the methods and properties of the above listed classes:

TMeasure

• Caption - measure caption
• FieldName - measure field name
• CurrentValue - current value
• CurrentCaption - current value caption
• ColOffsetValue[Offset: integer] - measure value from cell with column offset from the current value
• RowOffsetValue[Offset: integer] - measure value from cell with row offset from the current value
• ColOffsetValueWithDimValue[ADimValue: Variant] - measure value corresponding to current dimension ADimValue value of column dimensions
• RowOffsetValueWithDimValue[ADimValue: Variant] - measure value corresponding to current dimension ADimValue value of row dimensions
• TotalValueForDims[ADimNames: String] - value of total over requested dimensions (DimNames separated by ',')
• ColRowOffsetValue[ColOffset, RowOffset: integer] - measure value from cell with column and row offset from the current value
• ColRowOffsetValueWLevel[ColOffset, RowOffset, ColLevelOffset, RowLevelOffset: integer] - measure value from cell with column and row offset from the value on requested level

TDimension

• Caption - dimension caption
• FieldName - dimension field name
• CurrentValue - current value
• CurrentCaption - current value caption
• SubGroup - dimension subgroup

TfcxSliceField

• Caption - field caption
• FieldName - field name
• CurrentValue - current value (available only for filter script)
• CurrentCaption - current caption (available only for filter script)
• FilterCount - number of filtered values
• IsFiltered - true when field values are filtered, otherwise false

TMeasures

• Items[AIndex: Integer] - measure array with access by index
• ItemByCaption[AIndex: String] - measure array with access by caption
• ItemByName[AIndex: String] - measure array with access by name (default property)
• Count - number of measures
• RecordCount - number of detail rows
• DetailValue[ARecordIndex: Integer; AFieldName: String] - field value in the detail row
• XLevel - Level of X axis to which the current cell belongs
• YLevel - Level of Y axis to which the current cell belongs

TDimensions

• Items[AIndex: Integer] - dimension array with access by index
• ItemByCaption[AIndex: String] - dimension array with access by caption
• ItemByName[AIndex: String] - dimension array with access by name (default property)
• Count - number of dimensions
• XAxisItems[AIndex: Integer] - dimension in X axis
• YAxisItems[AIndex: Integer] - dimension in Y axis
• IsTotalByCol - true when cell is a total cell by X axis, otherwise false
• IsTotalByRow - true when cell is a total cell by Y axis, otherwise false
• XAxisLevelsCount - number of dimensions in X axis
• YAxisLevelsCount - number of dimensions in Y axis
• XLevel - Level of X axis to which the current cell belongs
• YLevel - Level of Y axis to which the current cell belongs

TfcxSliceFields

• Items[Index: Integer] - field array with access by index
• ItemByCaption[AIndex: String] - field array with access by caption
• ItemByName[AIndex: String] - field array with access by name (default property)
• Count - number of fields

TfcxCustomObject

• Value - pointer to a user object or memory area
• ValueIsNil - checks whether value is empty : used for checking before initial object creation or memory allocation

Here is an example of a calculated measure value on other measures:

```Code Example Sum = Price * Amount + WorkPrice

||procedure func\_3(var Result: Variant);|procedure header|
| ----- | ----- | ----- |
||var  wp: Variant;|declare variable wp|
||begin||
||`   wp := Measures['Work price'].CurrentValue;`|assign current measure "Work price" value to wp variable|
||`   if VarType(wp) <= 1 then wp := 0;`|if wp does not exists (Null or UnAssigned), then treat wp value as 0   (we use this test to prevent addition using Null and UnAssigned values)|
||`   Result :=``      Measures['Price'].CurrentValue*``      Measures['Amount'].CurrentValue+``      wp;`|define Result as current measure "Price" value   multiplied by current value of measure "Amount"   plus wp value.|
||end;||

![](../../imgs/fastscript_3.png)

A function which calculates the measure value on other measures is called once for each cell (dimension intersection). If "Calc all cells" is not set then the function is not called for empty cells.

A function which calculates the measure value on source data is called for each cell as many times as rows exist in the source data. The function has two arguments: Result and Final:

Result - current measure value

Final - true when this is a final call, otherwise false

The final call occurs for each non-empty cell when the source data traverse has finished. In this case the function Final argument has the value "True". The final call is needed for performance supplementary calculations and for memory deallocation.

In the following example there are two functions:

AmountScriptOnGetValue function - calculates sum of "Amount" field : note that summation occurs only when Final = False

AveragePriceOnGetValue function - calculates average price : requires the summation of values of "Amount" and "Cost" measures and calculation of the average in the final call : note that the CustomObject variable is used to store the sum of the "Amount" measure : memory deallocation is not needed as memory has not previously been allocated

![](../../imgs/fastscript_4.png)