Formula Parameters
A formula may include parameters that reference the data you want use in calculating the values in the report. There are two types of parameters: predefined parameters; and links to specific rows, columns, or individual report cells.
Predefined Parameters
The predefined parameters are application-specific and refer to the report parameters that you specify on other ARM forms, such as the Report Definitions (CS206000) form or the Row Sets (CS206010) form. For example, the @StartPeriod parameter refers to the value specified in the Start Period box on the Report Definitions form, and the @BaseRowCode parameter refers to the value specified in the Base Row box on the Row Sets form. All the available predefined parameters are listed in the Data Source Editor dialog box.
The table below provides the list of the predefined parameters with their descriptions.
| Parameter | Description |
|---|---|
| @AccountCode |
The code of the current account if the Account option is selected in the Expand box in the Data Source Editor dialog box. This is the code of the current subaccount if the Sub option is selected in the Expand box. |
| @AccountDescr |
The description of the current account if the Account option is selected in the Expand box in the Data Source Editor dialog box. This is the description of the current subaccount if the Sub option is selected in the Expand box. |
| @BaseRowCode | The row code specified for the selected row in the Base Row column on the Row Sets form. This row code is referred to by this parameter, which you use in the formula in the Value box on the Column Sets (CS206020) form to retrieve the value specified in this row for computing another value in the report. |
| @BookCode | The code of the ledger whose data is used in the report. This parameter refers to the value in the Ledger box in the Default Data Source Settings section. |
| @BranchName | The company branch identifier in the current tenant. For more information on company branches, see Branches (CS102000) form. |
| @ColumnCode | The code of the current column in the report. |
| @ColumnIndex | The index of the current column in the report. |
| @ColumnSetCode | The code of the current column set in the report. |
| @ColumnText | The description of the current column in the report. |
| @EndAccount | The last account in the range of accounts used in the report. This parameter refers to the value in the End Account box in the Default Data Source Settings section of the Report Definitions form. |
| @EndAccountGroup | The last account group in the range of account groups used in the report. This
parameter refers to the value in the End Acc.
Group box in the Default Data Source
Settings section of the Report Definitions form. Note: This parameter is valid
only in reports of the PM type. |
| @EndBranch | The last branch in the range of branches used in the report. This parameter refers to the value in the End Branch box in the Default Data Source Settings section of the Report Definitions form. |
| @EndPeriod | The end period of the report. This parameter refers to the value in the End Period box in the Default Data Source Settings section of the Report Definitions form. |
| @EndProject | The last project in the range of projects used in the report. This parameter refers to
the value in the End Project box in the
Default Data Source Settings section of
the Report Definitions form. Note: This parameter is
valid only in reports of the PM type. |
| @EndProjectTask | The last project task in the range of the project tasks used in the report. This
parameter refers to the value in the End Task
box in the Default Data Source Settings
section of the Report Definitions form. Note: This
parameter is valid only in reports of the PM
type. |
| @EndSub | The last subaccount in the range of subaccounts used in the report. This parameter refers to the value in the End Sub box in the Default Data Source Settings section of the Report Definitions form. |
| @StartAccount | The first account in the range of accounts used in the report. This parameter refers to the value in the Start Account box in the Default Data Source Settings section of the Report Definitions form. |
| @StartAccountGroup | The first account group in the range of account groups used in the report. Refers to
the value in the Start Acc. Group box in the
Default Data Source Settings section of
the Report Definitions form. Note: This parameter is
valid only in reports of the PM type. |
| @StartBranch | The first branch in the range of branches used in the report. This parameter refers to the value in the Start Branch box in the Default Data Source Settings section of the Report Definitions form. |
| @StartPeriod | The start period of the report. This parameter refers to the value in the Start Period box in the Default Data Source Settings section of the Report Definitions form. |
| @StartProject | The first project in the range of projects used in the report. This parameter refers
to the value in the Start Project box in the
Default Data Source Settings section of
the Report Definitions form. Note: This parameter is
valid only in reports of the PM type. |
| @StartProjectTask | The first project task in the range of project tasks used in the report. This
parameter refers to the value in the Start
Task box in the Default Data Source
Settings section of the Report Definitions form. Note: This parameter is valid
only in reports of the PM type. |
| @StartSub | The first subaccount in the range of subaccounts used in the report. This parameter refers to the value in the Start Sub box in the Default Data Source Settings section of the Report Definitions form. |
| @ReportDescr | The report description that the system prints in the column set header. |
| @RowCode | The code of the current row in the report. |
| @RowIndex | The index of the current row in the report. |
| @RowSetCode | The code of the current row set in the report. |
| @RowText | The description of the current row in the report. |
| @UnitCode | The code of the current unit in the report. |
| @UnitSetCode | The code of the current unit set in the report. |
| @UnitText | The description of the current unit in the report. |
Links to Rows
You can use the links to the rows in the formulas to define the parameters. The links to the rows are used when a formula is defined for the row to calculate its values, and the referenced rows are the parameters used in these calculations. For example, when a row is a summarized value of the other two rows, the formula includes the references to the rows to be summarized.
You can use the links to the rows in the formulas designed for the calculation of row and column values. However, we do not recommend that you reference the rows in the formulas defined for columns because of the self-references in these formulas. Self-references can be the source of circular reference errors in the formulas.
The links to the rows have the following format: row_code, where row_code is the code of the referenced row (for example, 00112).
The following are examples of formulas using links to the rows.
=@30+C30+E30
=C110-@110+H110In the examples above, @30, C30, E30, C110, H110, and @110 are the cells in rows 30 and 110. The row codes are entered manually by the report designer.
Links to Columns
You can use the links to the columns in the formulas to define the parameters. You use the links to the columns when a formula is defined for the column to calculate its values, and the referenced columns are the parameters used in these calculations. For example, when a column is a summarized value of the other two columns, the formula includes the references to the columns to be summarized. You can use the links to the columns in the formulas designed for calculation of column and row values. Again, avoid referencing the columns in the formulas defined for rows because self-references in these formulas can cause circular reference errors.
To reference a column, replace its code in the formula as a parameter.
See the following examples of formulas with links to the columns.
=A+B+D
=C-DA, B, C, and D are the column codes. The codes of the columns are assigned by the system and cannot be changed.
Links to Cells
You can use the links to the report cells in the formulas to define the parameters. You use these links when a formula is defined for the column or row to calculate its values, and the referenced cells are the parameters used in these calculations.
To reference a cell, substitute its code into the formula as a parameter.
See the following examples of formulas using links to the cells.
=A60+B30+D20
=C30-D20In the examples, A, B, C, and D are the column codes, while 20, 30, and 60 are the row codes. The cells located in the specified rows of the columns indicated by the first letters are used as the parameters in these formulas.
Self-Links in Formula Parameters
The self-links in the formulas can be used in very few cases; they typically are not used to avoid the situations when a self-link results in circular reference error. An example of a useful self-link is the case when some operations must be performed with the values selected directly from the data source, such as calculations of charges for a certain period. To use a self-link in a formula, insert a link to the row or column in the formula defined for this row or column.
The following example of self-reference in a row and column can be used to calculate charges of 5%.
=@20*0.05In the example, 20 is the code of the row where the formula is defined. In this example, all values in the 20th row will be multiplied by 0.05.
=A*0.05