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
    .
  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).
      • Total Row Count
        : Region; Row number (1-nn).
      • 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.
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.
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.

This article applies to:

  • Product: Workpapers CS
  • Subject: Reports

Related content