Formulas can be composed of Excel Functions, fixed and dynamic references (Formula IDs), constants and operators. However, it is also possible to simply specify a text, which is then written to the Excel cells when the evaluation is executed.
Important
Note that in a Formula the English Excel Function names, commas instead of semicolons and dots as decimal separators must be used.
If there are problems with the content in a formula, Excel will alert you when opening the workbook.
Excel Formula / Excel Function
If the formula begins with an equal sign a calculation will be performed in Excel and the cell value is the result of an Excel Formula.
For example, a simple Excel Formula would be:
= 1 + 2 + 3
However, an Excel Formula can also be composed of References and Excel Functions such as:
= SUM(A1,B1) - 25 * MIN(C1:C6) + D1
Excel Functions can be added to a formula via the Add Excel Function action. This opens a page that displays an overview of all Excel Functions that have been set up.
For more information about formulas and functions, see:
https://support.microsoft.com/en-us/office/294d9486-b332-48ed-b489-abe7d0f9eda9
Fixed References
Examples of fixed references:
This formula
Refers to
And returns
=C2
Cell C2
the value in cell C2
=A1:F4
Cells A1 through F4
the values in all cells, but you must additionally set the Array Formula field
Instead of a fixed reference, a Formula ID can be specified, which is then replaced by the current value when the evaluation is executed.
Formula IDs can be added to a formula via the Add Formula ID action. This opens a page that displays an overview of all available Formula IDs.
The following Formula IDs are available in each evaluation:
Formula ID
Will be replaced by
[%1]
the value of the current field
[TOTALVALUE]
Will be replaced by the sum of the field values of all records in the case of a numeric (Decimal, Integer, BigInteger) field.
These Formula IDs can be used, for example, to implement the old NC Cube field setting Count/Total.
Since the functionality has now been implemented as a Formula ID, new possibilities arise.
For example, if the Formula =100*[%1]/[TOTALVALUE] is specified for the Sales (LCY) field of the customer, the percentage value of the amount will be calculated.
[TOTALVALUE] is replaced by the total Sales (LCY) of all customers within the filters.
[%1] is replaced by the Sales (LCY) of the customer.
Tip
The totals are always calculated across all records within the filters. It does not matter how many records are actually output.
If, for example, you want to calculate the total(s) of certain customer entries for a customer, you can set the Relationship to First Record in the Table Settings of the customer entries.
This means that automatic line breaks can be avoided. The totals are still calculated across all customer entries.
[COL]
the current Excel column no.
[ROW]
the current Excel row no.
[USERID]
the current User ID
[COMPANY]
the current Company Name
[PERIOD_TEXT]
the current period (e.g. 2022/03 March) of a (parent) Date Loop
[PERIOD_DATE]
the current period (e.g. 03/01/22..03/31/22) of a (parent) Date Loop
[TOTALRECORDS]
the number of all records
[RECORDNO]
a sequential number for the current record
[RECORDID]
the RecordID of the current Record
Custom Formula IDs
If a Formula ID is specified for a field, the ID can be used to reference or access the field value in formulas.
The Formula IDs in the formulas are replaced by the current cell references of the fields when the evaluation is executed.
If a Formula ID is assigned to the internal field, the value can be accessed in subsequent fields via a formula instead of the reference of the internal field.
Note
The row no. in the cell reference of a Formula ID is reset to the current row no. after running through the 1:n relation or the tree structure in which the Formula ID is defined.
To contain a value, internal fields must be specified before the formulas in which they are used.
The value of an internal field is reset after running through the 1:n relation or the tree structure in which the Formula ID is defined.
Example 1
Note: This example is for explanation only and assumes a very simplified database in which only positive Customer Ledger Entries exist.
The example can be downloaded in the Download Area.
The following Data Sheet was created:
For each Customer Ledger Entry, the percentage of Balance (LCY) of the customer is calculated first.
For this purpose, the Balance (LCY) field was assigned the Formula ID [B] and the Amount (LCY) field was assigned the Formula ID [A].
The percentage value can thus be calculated in a Formula Field using the Formula =100/[B]*[A].
In addition, all entries are marked that are above a certain percentage value.
For this, the Percent Formula Field was assigned the Formula ID [P].
In this example, the limit value is implemented via a text line before the data table.
The default value for the limit value is 50 percent. The Formula ID [LP] was assigned to the Limit field.
The following result is displayed in Excel after executing the evaluation:
Tip
To display the formulas instead of the results in the cells in Excel, click on the Formulas tab in the menu bar and select the Show Formulas action in the Formula Auditing area.
The dynamic references were converted as follows:
[B] was replaced by the cell reference of the Balance (LCY) field.
[A] was replaced by the respective cell reference of the Amount (LCY) field.
[P] was replaced by the respective cell reference of the Percent formula field.
[LP] was replaced by the cell reference of the Limit field.
Example 2
For each field (except internal fields) for which a Formula ID has been specified, 2 additional Formula IDs are available.
For the Balance (LCY) field, for example, the following Formula IDs are also available:
[B:COL] will be replaced e.g. by the Excel column no. of the Balance (LCY) field.
[B:ROW] will be replaced e.g. by the Excel row no. of the Balance (LCY) field.
Based on example 1, the number of entries for the customer should now also be displayed.
The example can be downloaded in the Download Area.
Version 1:
Here the *:COL] Formula ID is used.
The changes have been marked in the image.
In version 1, the Customer No. field is added to the entries.
Using the formula =COUNTIF([PNO:COL]:[PNO:COL],[NO]) all rows are counted where the column with the Formula ID [PNO] contains the value of the Formula ID [NO] and thus the No. of the customer.
Version 2:
It is not always possible to find a simple criterion for the query.
It could also be that the criterion is repeated in subsequent lines.
Version 2 shows an alternative solution.
The *:COL], *:ROW] and [ROW] Formula IDs are used for this.
The changes have been marked in the image.
In this version a Formula Field Customer RowID is added to the entries.
The field is filled via a Formula with the row no. of the Number of Entries field and thus with the row no. of the customer record. Thus, each entry has a unique criterion for the query.
Using the formula =COUNTIF([RID:COL]:[RID:COL],[ROW]) all rows are counted where the column with the Formula ID [RID] contains the value of the row no. of the customer record.
Formula Help
The Formula Help action displays an overview of all Excel Functions and Formula IDs used in the current formula. This overview can be used, for example, to call up the help for the Excel Functions used. In addition, you can also see what replaces the Formula IDs when the evaluation is executed.
Translate
Note that in a Formula the English Excel Function names, commas instead of semicolons and dots as decimal separators must be used. If required, the Excel Function names of the Formula can be translated into English Excel Function names with this action.
Additional Settings
Format Formula as
Specifies whether the formula or the result of the formula should be formatted in Excel as a number or as a date.
Decimal Places
Specifies the number of decimal places to display in Excel when the formula is formatted as a number.
Without 1000 Separator
Specifies whether a thousands separator should be used or not.
When the workbook is opened, Excel checks the formulas.
If a problem is detected, e.g. if a formula was specified incorrectly, the following message appears:
We found a problem with some content in '...xlsx'. Do you want us to try to recover as much as we can?
Click Yes to get more information and check the workbook.
Formulas
For more information about formulas and functions, see:
https://support.microsoft.com/en-us/office/294d9486-b332-48ed-b489-abe7d0f9eda9
For each Customer Ledger Entry, the percentage of Balance (LCY) of the customer is calculated first.
For this purpose, the Balance (LCY) field was assigned the Formula ID [B] and the Amount (LCY) field was assigned the Formula ID [A].
The percentage value can thus be calculated in a Formula Field using the Formula =100/[B]*[A].
In addition, all entries are marked that are above a certain percentage value.
For this, the Percent Formula Field was assigned the Formula ID [P].
In this example, the limit value is implemented via a text line before the data table.
The default value for the limit value is 50 percent. The Formula ID [LP] was assigned to the Limit field.
The following result is displayed in Excel after executing the evaluation:
[B] was replaced by the cell reference of the Balance (LCY) field.
[A] was replaced by the respective cell reference of the Amount (LCY) field.
[P] was replaced by the respective cell reference of the Percent formula field.
[LP] was replaced by the cell reference of the Limit field.
The changes have been marked in the image.
In version 1, the Customer No. field is added to the entries.
Using the formula =COUNTIF([PNO:COL]:[PNO:COL],[NO]) all rows are counted where the column with the Formula ID [PNO] contains the value of the Formula ID [NO] and thus the No. of the customer.
The changes have been marked in the image.
In this version a Formula Field Customer RowID is added to the entries.
The field is filled via a Formula with the row no. of the Number of Entries field and thus with the row no. of the customer record. Thus, each entry has a unique criterion for the query.
Using the formula =COUNTIF([RID:COL]:[RID:COL],[ROW]) all rows are counted where the column with the Formula ID [RID] contains the value of the row no. of the customer record.
