Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Report Designer

Work with formulas

Use the following procedures to insert formulas and functions directly into the design grid of the Report Designer workspace and apply grouping content to a selected row or cell in the design grid.

Create formulas and functions

  1. In the design grid, select the cell, row, or column where you want to insert the formula.
  2. Select
    Insert
     then
    Formula
    .
    note
    Alternatively, select Formula Formula button with flask iconnext to the
    Contents
     field of either Row, Cell, or Column Properties.
  3. Create your formula with variables, functions, static amounts, row/column/cell references or ranges, or any combination of these items with an operator.
    • Select
      Variables
       if you want to specify variables from the tree view.
    • Select
      Functions
       if you want to specify dates, range of date counts, row counts, running totals, and to define variables and/or other detail for the selected function.
      • Date:
         Variable 1; Date format; Period.
      • Day:
         Variable 1.
      • Month:
        Variable 1.
      • Year:
        Variable 1.
      • End of Month
        : Month (1-12)
      • Day Count:
         Variable 1-2.
      • Week Count
        : Variable 1-2.
      • Month Count
        : Variable 1-2.
      • Row Count:
        Region; Row number (1-nn).
        note
        Displays the number of times a specified row is output in the report. The function reads a row that is only a single repeating level away from the level in which it is placed. Ffor example, the RowCount function in
        level 1
         typically counts a row in the
        level 2
        . The function resets when the repeating level group starts again.
      • Total Row Count
        : Region; Row number (1-nn).
        note
        Displays the total number of times a specified row is output in the report. The function reads from a row for an unlimited number of nested repeating levels and continues to count even when the functions repeating level starts over.
      • Running Total
        : Cell reference.
      • Validate:
        Variable 1; Regex format.
      • The
        Validate
         function enables you to check data for specific formatting by using custom regular expressions for the selected variable.
    • Use
      Amount
      ,
      Region
      ,
      Row
      ,
      Column
      , and/or
      Cell
       fields to define static amounts and/or row/column/cell references or ranges. You can specify the
      Region
       for cross-region cell references.
  4. Select
    OK
    .

Apply grouping content to a selected row or cell

For accounting reports, you can select a
Grouping type
 and apply a selected grouping to the
Chart of Accounts Activity
 variables in a row or cell.
note
Account groupings enable you to group similar accounts together to compare balances and optimize reporting capabilities.
Select a Row or Cell then select
Formula
 Formula button with flask icon next to
Grouping content
 to create a formula based on the selected grouping type and apply it to the contents of the selected row or cell.
note
  • Cell references will only work if the formula is entered in a cell below and to the right of all referenced cells.
  • Rather than applying properties to an entire cell via Cell Properties, you can apply attributes to specific variables using the Formula dialog. For example, you can use the
    Grouping type
    ,
    Grouping content
    ,
    Period
    ,
    Amount type
    , and
    Basis
     fields in the
    Formula
     dialog to apply attributes to Chart of Accounts Activity variables, such as
    ChartofAccountsActivity.Amount
     or
    ChartofAccountsActivity.Core.Amount
    .
  • The formula is displayed in the
    Formula
     pane of the dialog as you select items and operator buttons to create the formula. You may edit the formula directly in the
    Formula
     pane, but you must use the correct syntax to create a valid formula.
  • If you enter a formula directly into a cell in a report, you must enclose it in square brackets and use the correct syntax to create a valid formula. For example,
    [B3 + B9]
    . When the syntax is incorrect (such as when a letter is missing from the variable to create a misspelling) a red arrow appears in the corner of the cell that contains the error. If a bracket is missing from the formula, the
    #FORM!
     error is displayed in the cell, and when and invalid cell reference is part of the formula, the
    #REF!
     error is displayed in the cell.
  • When there is an amount row that repeats multiple times (most commonly a row that uses an account description variable, such as
    [ChartofAccountsActivity.Core.Amount]
    ), a formula such as
    [(CA) - (CA:CASH)]
     cannot be applied to that row in the
    Grouping content
     field of the
    Design Tools
     frame. To display the correct results, formulas that display only
    total amounts
     or those that are assigned to a single account group should be entered in the
    Grouping Content
     field.
Example 1
The following example provides a method to display a row that is set to repeat for each account that has the classification code CA (no subcode).
The Insert Formula dialog. On the left, the Balance Sheet - Current Year tab is shown. The Formula field contains [ChartofAccountsActivity.Core.GLAccountDescription]. Underneath, the Header, Alternate Header and Main regions are shown. The Main section shows a list of Assets. Under the heading Current Assets, in row 4, the variable from the Formula field is shown. One the right the Design Tools section is open and shows Row Properties fields. The Contents field displays the varialble, the Grouping type displays 'Account Classification' and Grouping content displays 'CA'. All checkboxes are cleared.
Example 2 (incorrect)
The following example displays the same row as in Example 1, but includes an incorrect formula
[(CA) - (CA:CASH)]
 in the Grouping content field. (This method incorrectly assumes the formula will repeat the row for all CA accounts, except the accounts with a subcode of CASH.)
The Insert Formula dialog. Shows the same information as Example 1, but the Grouping content field shows '[(CA) - (CA:Cash)]'. All checkboxes are cleared.
Example 3
The following example provides a method to display a row that displays only once as a
total
 of all the accounts for a single grouping (Non-cash accounts).
The Insert Formula dialog. On the left, the Balance Sheet - Current Year tab is shown. The Formula field contains [ChartofAccountsActivity.Core.Amount]. Underneath, the Header, Alternate Header and Main regions are shown. The Main region shows a list of Assets. Under the heading Current Assets, in row 4, the variable from the Formula field is shown in cell B4, for the Non-cash accounts item. On the right, the Design Tools is open and shows Cell Properties. The Contents field shows the variable from the selected cell: B4. The Grouping type is Account Classification, the Grouping content is [(CA) - (CA:CASH)], the Period is CY and the Amount type is BA. The Print total amount checkbox is marked.
Example 4
The following example provides a method to print all Current Asset (CA) accounts, except those accounts with a subcode of CASH. Each row repeats separately with a different subcode for the CA classification code (for example, MS, AR, INV, OCA, No subcode).
The Insert Formula dialog. On the left, the Balance Sheet - Current Year tab is shown. The Formula field contains [ChartofAccountsActivity.Core.GLAccountDescription]. Underneath, the Header, Alternate Header and Main regions are shown. The Main region shows a list of Assets. Under the heading Current Assets, there are 6 rows that all contain the variable in column A and B. Row 4 is highlighted. On the right the Design Tools section is open and shows Row Properties fields. The Contents field displays the variable, the Grouping type displays 'Account Classification' and Grouping content displays 'CA : MS'. All checkboxes are cleared.
Internal use only
The
Validate
 function enables you to check data for specific formatting by using custom regular expressions for the selected variable.
note
As in the following examples, the
Validate
 function requires a variable and a regular expression to test.
Yes
 or
No
 is displayed in the report or financial statement based on the values that flow from the source data and validated against the regular expression.
Validate function with a regular expression
Formatting to validate
[Validate(Client.CustomFields.CustomID.Value,“^(?!0)(\d{6})$”)]
Cannot start with 0 and must contain 6 digits.
[Validate(Client.Business.Number,“^(?!9)(\d{3})-(\d{3})-(\d{4})$”)]
Cannot start with 9 and must contain the format of xxx-xxx-xxxx (3 digits, hyphen, 3 digits, hyphen, 4 digits)
[Validate(Employee.EINSSN,"^(?!078-05-1120|219-09-9999)(?!000|666|9\d{2})(\d{3})-(?!00)(\d{2})-(?!0000)(\d{4})$")]
Cannot be 078-05-1120 or 219-09-9999; cannot contain 000, 666, or start with 9 in the first group of 3 digits; cannot contain 00 in the second group of 2 digits; cannot contain 0000 in the third group of 4 digits, and must contain the format of xxx-xx-xxxx (3 digits, hyphen, 2 digits, hyphen, 4 digits)

This article applies to:

  • Product: Accounting CS
  • Subject: Reports

Related content

error-icon

Triva isn't available right now.

Check out the support page for our phone number and hours

error-close