Formula Functions

Formula sums are used for summing and KPIs. For this, a function or input is defined in the sum. If a sum refers to items or sums, these must be located before the sum in the formula.

Sum and Item Functions

Item

Gets the value of an item.

Parameters:

Number	Description
1	Name of the item

Example:

Item("P001")

ItemRange

Gets the value of an item range.

Parameters:

Number	Description
1	Name of the start item
2	Name of the end item

Example:

ItemRange("P001","P005")

Sum

Gets the value of a sum.

Parameters:

Number	Description
1	Name of the sum

Example:

Sum("A Product")

SumRange

Gets the value of a sum range.

Parameters:

Number	Description
1	Name of the start sum
2	Name of the end sum

Example:

SumRange("A Product","E Product")

Rubric

Gets the value of a rubric. Rubrics can be set for items and sums.

Parameters:

Number	Description
1	Name of the rubric

Example:

Rubric("A Prod.")

RubricRange

Gets the value of a rubric range. Rubrics can be set for items and sums.

Parameters:

Number	Description
1	Name of the start rubric
2	Name of the end rubric

Example:

RubricRange("A Prod.","E Prod.")

TreeSum

Gets the value of a tree sum. It is not necessary for the items of the tree sum to be included in the formula.

Parameters:

Number	Description
1	Name of the tree
2 .. n	Name of the tree sum

Example:

TreeSum("Product Tree","A Products")
TreeSum("Product Tree","A Products","B Products")

Field

Returns the name of the database field currently being calculated.

Example:

Field() -> Amount

FieldCaption

Returns the caption of the field currently being calculated. The caption in the query can differ from the database field name.

Example:

FieldCaption() -> Sales Amount

Default Functions

If

Performs a logical test and returns one of the specified values depending on the test result.

Parameters:

Number	Description
1	Logical test
2	True value
3	False value

Example:

If(Field()="Amount", "Field is Amount", "Field is not Amount")

NullDiv

Divides two values. If division by zero occurs, an error value is used.

Parameters:

Number	Description
1	Value 1
2	Value 2
3	Error value (optional)

Example:

NullDiv(10,2) -> 5
NullDiv(10,0) -> 0
NullDiv(10,2,9999) -> 5
NullDiv(10,0,9999) -> 9999
NullDiv(Item("P001"),Item("P005"))

Percent

Calculates the percentage of two values.

Parameters:

Number	Description
1	Value 1
2	Value 2

Example:

Percent(15,5) -> 300

PercentDeviation

Calculates the percentage deviation of two values.

Parameters:

Number	Description
1	Value 1
2	Value 2

Example:

PercentDeviation(20,30) -> 50
PercentDeviation(10,30) -> 200