Working with functions
Accountants Enterprise in Australia only
A function is a specific type of pre-designed formula that performs calculations by using values, referred to as arguments, in a particular order or structure. Arguments can be numbers, text, logical values such as TRUE or FALSE, error values such as #N/A, or cell references. The argument you specify must produce a valid value. Arguments can also be constants, formulas or other functions.
There are a number of different functions provided in the Report Designer. To use them, simply type the equals sign (=) into a cell in the Format Editor window, then enter the name of the function you want to use. For example ‘=Page’ or ‘=If()’. This method only works if it is the first entry in the cell. If the cell where you want to enter the function already has content, you will need to place the function command within braces, e.g., {NOTE(Cash)}.
Functions can be embedded in more complex formulas, where they can return a value or a TRUE/FALSE. For example:
The {IF(CM.DirectorsReport.NPAT+CM.SFPEF.ExtraordinaryItems <0,"profit","loss")} of the company for the financial {yearOrperiod} after providing for income tax {IF(CM.SFPEF.ExtraordinaryItems<>0,"and extraordinary items ","")} amounted to ${CM.DirectorsReport.NPAT+CM.SFPEF.ExtraordinaryItems}.
In the example above the section {yearOrperiod} is referring to a named range.
Functions are not case sensitive, e.g., sum(b2:b5) produces the same result as SUM(B2:B5). However using capitals aids in easily differentiating cell references and function names from any surrounding text.
Function | Description |
|---|---|
AccountValue | =ACCOUNTVALUE(<AccountCode>,<FieldName>) Returns the specified field value for the specified account code, e.g., =ACCOUNTVALUE(680,DESCRIPTION) returns the description of account 680. |
And | And(<Argument1>,<Argument2>...) All of a number of conditions must be met for the expression to be evaluated as being TRUE. The structure is AND(<condition to be met>,<condition to be met>,<condition to be met>). There is no limit on the number of conditions that can be specified. For example, =AND(DB.SFPE.DatabaseValues="No",DB.SFPE.Expensesby="Nature") For this formula to be evaluated as TRUE, the DatabaseValues field in the SFPE table must have a value of ‘No’ and the Expensesby field in the same table must have a value of Nature. |
Avg | Avg(<Cell Range>) The Avg function is used to calculate the average of the values in a cell range. Any blank cells in the range will not be included. For example, =AVG(B1:B5) =AVG(B1,B2,B3) =AVG(B1+B2+B3) |
Count | Count(<Cell Range>) Use the Count function to return the number of cells in a cell range that are displayed when the Format is previewed (i.e., the cells for which a print condition has not been set or for which the print condition was satisfied). Cells containing a zero will not be included in the count. If any of the cells in the range are in a row that is set to repeat, the function includes the number of times the cell repeats in the preview with a non zero balance. For example, =COUNT(A1:C5) |
Countrows | Countrows() Use the Countrows function to return the number of rows in a range that are displayed when the Format is previewed (i.e., the rows for which a print condition has not been set or for which the print condition was satisfied). If any of the rows in the range have been set to repeat, the function includes the number of times the row is repeated in the preview. For example, =COUNTROWS(1:5) This function will include rows that contain zero values, e.g., if you repeat for a Chart Map item and elect to ‘Show zero balances’. |
CURRENTPERIOD | =CURRENTPERIOD Returns the end date of the current period, as specified in the Ledger year selection dialog. |
CURRENTYEAR | =CURRENTYEAR Returns the end date of the current year, as specified in the Ledger year selection dialog. |
FIRSTPERIOD | =FIRSTPERIOD(<Date>) Returns the end date of the first period in the year containing the specified date. The date can be supplied directly, via a Database Map field or via another function. For example, =FIRSTPERIOD(30/11/2005) =FIRSTPERIOD(DB.OtherReports.Director.DateAppointed) =FIRSTPERIOD(NOW) You can also use this function without supplying a date, i.e., =FIRSTPERIOD. In this case, it will return the end date for the first period in the year for which the cell or column is formatted. If the function is used outside of a cell or column, it will use the current year, as specified in the Ledger year selection dialog. |
FINALPERIOD | =FINALPERIOD(<Date>) Returns the end date of the final period in the year containing the specified date. The date can be supplied directly, via a database map field or via another function. For example, =FINALPERIOD(30/11/2005) =FINALPERIOD(DB.OtherReports.Director.DateAppointed) =FINALPERIOD(NOW) You can also use this function without supplying a date i.e. =FINALPERIOD. In this case, it will return the end date for the final period in the year for which the cell or column is formatted. If the function is used outside of a cell or column, it will use the current year, as selected in the Ledger year selection dialog. |
FINALYEAR | =FINALYEAR Returns the end date of the latest year for which ledger data is available. |
FIRSTYEAR | =FIRSTYEAR Returns the end date of the earliest year for which ledger data is available. |
If | If(<Argument>,<Value1>,<Value2>) The If function enables you to set conditions for when a certain value displays, and to specify what value to display if those conditions are not met. The structure is IF(<condition to be met>,<value to display if condition is met>,<value to display if condition is not met>) For example, =IF(CM.BalanceSheet.Assets.Current.GST>0,CM.BalanceSheet.Assets.Current.GST,0) If the total value in the account or accounts mapped to the BalanceSheet.Assets.Current.GST Chart Map item is greater than zero, the value in the referenced account or accounts will be displayed. If the value is not greater than zero, a value of zero will be displayed. |
INOTE | INOTE(<Bookmark Name>,<Key>) Returns the note number assigned to the specified bookmark where the bookmark is on a repeating row and the note number needs to be incremented for each iteration of the repeat. The Key property indicates the field by which the repeated note numbers are to be matched to the repeated bookmarks, e.g., =INOTE(CashAssets,CODE). |
iRepeat | iRepeat(<RowNumber>) This function is used in conjunction with the RECORDVALUE and Repeat function. While RECORDVALUE (<FieldName>,<RecordNumber>) returns the value of the specified database field for the record of the specified number, iRepeat returns the <RecordNumber> based on the number of repeats that have occurred at the RowNumber specified in iRepeat. For example, iRepeat(5) returns the number of repeats that have occurred at the row 5 repeat function. =Recordvalue(DB.MembersInfoStatement.WithdrawalBenefit.RequiredPreserved,iRepeat(5)) if the repeat at row five has looped three times it returns the value in the RequiredPreserved field for the 3rd record of the WithdrawalBenefit table. |
IsFirstRecord | Returns TRUE when used during the first iteration of a repeat, otherwise returns FALSE. |
IsLastRecord | Returns TRUE when used during the last iteration of a repeat, otherwise returns FALSE. |
LEFT | LEFT(text,num_chars) The LEFT function returns the first character or characters in a text string, based on the number of characters you specify. For example, LEFT("Sale Price",4) equals "Sale". |
Len | Len(<Value/Reference>) The LEN function enables you to create formulas that obtain the length of the entry in a cell or database field. For example, =LEN(DB.DirectorsReport.SignificantChange)>1 For this formula to be evaluated as TRUE, the SignificantChange field in the DirectorsReport table must have a length greater than 1. |
LY | LY(<Chart Map Folder, Database Map Field or Driver Field>) This function returns the value for the specified chart map folder, database map field or driver field (AMOUNT, DESCRIPTION etc.) in the year immediately prior to the year the cell is currently formatted to display, e.g., =LY(CM.Notes.Cash) returns the Last Year value if the cell is formatted to show Current Year values but returns the Year 3 value if the cell is formatted to display Last Year values. |
Max | Max(<Cell Range>) Use this function to return the highest value in a specified range. For example, =MAX(A1:C5) |
Min | Min(<Cell Range>) Use this function to return the lowest value in a specified range. For example, =MIN(A1:C5) |
MOD | MOD(<Value>,<Number>) The MOD function returns the remainder after the Value is divided by the Number. The result has the same sign as the Value. For example, MOD(3,2) returns 1. |
NEXTRECORD | NEXTRECORD(<FieldName>) This function returns the value of the specified field for the item immediately following the current item in the repeat, e.g., =NEXTRECORD(CODE) returns the code of the next account in a repeat. |
Note | The Note function will return a sequential note number that has been assigned to the specified bookmark based on its relative position in the report (i.e., the first bookmark to appear in the report is assigned the note number 1, the second bookmark is assigned the note number 2 and so on). This function is commonly used when creating notes and cross–references. Be aware that if the Format being referenced is not included in the report, then #REF will be displayed in the report where the reference cannot be located. To avoid this, you can use the If() function. |
Now | Returns the current system date and time. |
ONCEPER | =ONCEPER(<FieldName>) This function, when used within a chart map repeat that is grouped by the specified field, returns TRUE for one record in each group. For example, if used in a repeat that is grouped by division, =ONCEPER(DIVISION) returns TRUE for one record per division. It could be used, e.g., as the print condition for a row containing a sub-total so that the sub-total appears only once per division. In a single-row repeat, the ONCEPER function returns TRUE for the first record in each group. It returns FALSE for all other records. In a multi-row repeat, the ONCEPER function returns TRUE for the last record, in each group, where any of the rows above the row containing the function are displayed in the preview. It returns FALSE for all other records. The specified field must be one that is selected in the Group Repeat By section of the Repeat Settings window. If the specified field is not selected in the Group Repeat By section of the Repeat Settings window, the function will return TRUE for all records. |
Or | Or(<Argument1>,<Argument2>...) At least one of two or more conditions must be met for an expression to be evaluated as being TRUE. The structure is OR(<condition to be met>,<condition to be met>,<condition to be met>). There is no limit on the number of conditions that can be specified. For example, =OR(CM.SFPO.Assets.Current.Cash.DR<>0,CashLastYear<>0) For this formula to be evaluated as TRUE, either the SFPO.Assets.Current.Cash.DR item must not be equal to zero, or the CashLastYear named range must not be equal to zero. |
Page | Use the Page function to provide a page number. This function can be used in conjunction with the Pagecount function to provide Page x of y, where x is the number of the current page and y is the total number of pages. This function is typically used in Formats that are used as headers or footers. |
Pagecount | Pagecount Use the Pagecount function to provide the total number of pages in a report. This function can be used in conjunction with the Page function to provide Page x of y, where x is the current page and y is the total number of pages. This function is typically used in Formats that are used as headers or footers. |
Period | Period This function returns the end date of the period to which a cell is formatted. |
PERIODEND | This function returns the end date of the period containing the specified date. The date can be supplied directly, via a database map field or via another function. For example, =PERIODEND(30/11/2005) =PERIODEND(DB.OtherReports.Director.DateAppointed) =PERIODEND(NOW) |
PERIODNUMBER | =PERIODNUMBER(<Date>) This function returns the number of the period containing the specified date. The date can be supplied directly, via a database map field or via another function. For example, =PERIODNUMBER(30/11/2005) =PERIODNUMBER(DB.OtherReports.Director.DateAppointed) =PERIODNUMBER(NOW) |
PERIODNUMBERDATE | =PERIODNUMBERDATE(<Number>) This function returns the end date of the period represented by the specified number, e.g. =PERIODNUMBERDATE(1). |
PERIODSTART | This function returns the start date of the period containing the specified date. The date can be supplied directly, via a database map field or via another function. For example, =PERIODSTART(30/11/2005) =PERIODSTART(DB.OtherReports.Director.DateAppointed) =PERIODSTART(NOW) |
PREVIOUSRECORD | PREVIOUSRECORD(<FieldName>) This function returns the value of the specified field for the item immediately preceding the current item in the repeat, e.g., =PREVIOUSRECORD(DB.OtherReports.Director.LastName) returns the last name of the previous record on the Director table. |
Recordcount | Recordcount(<Table Name>) The Recordcount function provides the number of records in a database table. For example, =RECORDCOUNT(DB.Director)>1 For this formula to be evaluated as TRUE, the Director table must contain more than one record. |
RecordValue | RecordValue(<FieldName>,<RecordName>) Returns the value of the specified database field for the record of the specified number, e.g., =RECORDVALUE(DB.OtherReports.Director.FirstName,3) returns the value in the FirstName field for the 3rd record on the Director table. |
Ref | Ref(<BookmarkName>) The Ref function will return the number of the page on which the specified bookmark appears in the previewed report. If the Format being referenced is not included in the report, then #REF will be displayed in the report where the reference cannot be located. To avoid this, you can use the If() function. The Ref function returns the number of the pages on which the specified bookmark appears in the previewed report. |
RepeatPosition | RepeatPostition This function returns the number of the current iteration of a repeating row, e.g., 1 if it is the first time the row has been repeated, 2 if it is the second and so on. |
RIGHT | RIGHT(text,num_chars) The RIGHT function returns the last character or characters in a text string, based on the number of characters you specify. For example, RIGHT("Sale Price",5) equals "Price". |
Sum | Sum(<Cell Range>) The Sum function is typically used to sum the values in a cell range. You can also sum numbers. There are two operators you can use when summing single values, the comma (,) and the plus (+). These operators are interchangeable and will produce exactly the same result. For example, =SUM(B4+D4) =SUM(B4,D4) When summing a range, use the colon (:) operator: =SUM(B4:D4) |
TEMPLATEHASFILTERS | =TEMPLATEHASFILTERS This function returns TRUE if the column template currently applied to the format is a user-defined template containing columns for which one or more filters have been specified, or is a ledger-defined template based on account details (e.g., Division, Subcode). Otherwise, it returns FALSE. |
Year | Year This function is used when you want the year end date to be displayed in a report. The year specified in the Balances tab of the Format Cells window will determine which year (relative to the underlying ledger year) you will receive the end date for. |
YEAREND | =YEAREND(<Date>) This function returns the end date of the year containing the specified date. The date can be supplied directly, via a database map field or via another function. For example, =YEAREND(30/11/2005) =YEAREND(DB.OtherReports.Director.DateAppointed) =YEAREND(NOW) |
YEARNUMBER | =YEARNUMBER(<Date>) This function returns the number of the year containing the specified date. Numbers are assigned to years in ascending order from earliest to latest, with the earliest year for which ledger data is available being assigned the number 1. The date can be supplied directly, via a database map field or via another function. For example, =YEARNUMBER(30/11/2005) =YEARNUMBER(DB.OtherReports.Director.DateAppointed) =YEARNUMBER(NOW) |
YEARNUMBERDATE | =YEARNUMBERDATE(<Number>) This function returns the end date of the year represented by the specified number, e.g., YEARNUMBERDATE(1). Numbers are assigned to years in ascending order from earliest to latest, with the earliest year for which ledger data is available being assigned the number 1. |
YEARSTART | =YEARSTART(<Date>) Returns the start date of the year containing the specified date. The date can be supplied directly, via a database map field or via another function. For example, =YEARSTART(30/11/2005) =YEARSTART(DB.OtherReports.Director.DateAppointed) =YEARSTART(NOW) |
