Working with formulas

Accountants Enterprise in Australia only

Formulas are used to either calculate a value (using constants, cell values, named ranges, Chart Map or Database Map references and operators) or to compare values to produce an outcome of either TRUE or FALSE.

You can use formulas to:

• Reference account information using the Chart Map within a Format.

• Reference non-transactional data in the Database Map within a Format.

• Set conditions for row inclusion when printing or previewing a Format.

• Set conditions for including an element in a report.

• Set conditions for a page break.

• Calculate values, using references to cells and constants.

• Create notes and cross–references.

There is a special kind of formula called a function. A function is a pre-defined formula that is used for a specific purpose. Reporter comes with a list of available functions to make it easy for you to work with the more common ones. See Working with functions for more information.

Formulas can range from very simple references to complex expressions. For example, the following are formulas (numbers 1 and 2 will give the same result, as will numbers 3 and 4):

1. =DB.OtherReports.DirectorsReport.SignedDay 
   

2. {DB.OtherReports.DirectorsReport.SignedDay} 
   

3. {DB.OtherReports.Director.Title} {DB.OtherReports.Director.FirstName} {DB.OtherReports.Director.MiddleName} {DB.OtherReports.Director.LastName} {IF(DB.OtherReports.Director.DateRetired<DB.#EntityDetails.ClientDetails.YearEndDate, "retired {DB.OtherReports.Director.DateRetired}",IF(DB.OtherReports.Director.AppointedAfterFinYear="Yes", "appointed {DB.OtherReports.Director.DateAppointed}",""))} 
   

4. =DB.OtherReports.Director.Title&" "&DB.OtherReports.Director.FirstName&" "&DB.OtherReports.Director.MiddleName&" "&DB.OtherReports.Director.LastName&IF(DB.OtherReports.Director.DateRetired<DB.#EntityDetails.ClientDetails.YearEndDate," retired "&DB.OtherReports.Director.DateRetired,IF(DB.OtherReports.Director.AppointedAfterFinYear="Yes"," appointed "&DB.OtherReports.Director.DateAppointed,"")) 

In the examples given above, you will notice that the formulas are not expressed in the same way. In the first formula, the expression is surrounded by braces {}. In the second formula, the expression begins with an equal sign = and double quotes ". Ampersands (&) are used to identify the start and end of a field name.

Typically, the method using braces is used to provide a referenced value in a text string.

For example:

Dated this {OtherReports.DirectorsReport.SignedDay} day of {DB.OtherReports.DirectorsReport.SignedMonth},{DB.OtherReports. DirectorsReport.SignedYear} 

or

The {DB.Text.Director} {DB.Text.IsAre} solely responsible for the information contained in the general purpose financial report.

However, more complex formulas can be expressed using braces. For example,

{IF(DB.#EntityDetails.ClientDetails.YearEndDate= DB.#EntityDetails.ClientDetails.PeriodEndDate,"year","period")} 

Remember that spaces are static text.

Any text within the braces must be enclosed in quotes to avoid being confused with the names of Chart Map items or Database Map table and field names. For example: