Report Designer: Working with formulas

Show expandable text

We moved!

Help articles have been migrated to the new Help and Support. You can find help for your products and accounts, discover FAQs, explore training, and contact us!

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

Creating formulas and functions

  1. In the design grid, select the cell, row, or column where the formula is to be inserted.
  2. In the Contents field of Row Properties (Row mode), Cell Properties (Cell mode), or Column Properties (Column mode) in the Design Tools frame, click the Formula Insert formula button button, or choose Insert > Formula to open the Formula dialog.
  3. Formulas can be created using variables, functions, static amounts, row/column/cell references or ranges, or any combination of those items with the desired operator button.
    1. Select the Variables option to specify variables from the tree view.

    2. Select the Functions option to specify dates, range of date counts, row counts, running totals, and to define variables and/or other detail for the selected function, as required. Show me.

      formula dialog
      Function Detail
      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
      Row Count. 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 (e.g., the RowCount function in level 1 typically counts a row in the level 2). The function resets when the repeating level group starts again.
      		 Region; Row number (1-nn)
      Total Row Count
      Total Row Count. 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.
      		 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.

    3. Use the Amount, Region, Row, Column, and/or Cell fields to define static amounts and/or row/column/cell references or ranges. Note that the Region field can be specified for cross-region cell references.
  4. Click OK to save and insert the formula into the selected report, and close the Formula dialog.

Applying grouping content to a selected row or cell

For accounting reports, you can select a grouping type in the Grouping type field and apply a selected grouping to the Chart of Accounts Activity variables within a row or cell.

Account groupings enable you to group similar accounts together to compare balances and optimize reporting capabilities. For details, see Setup > Firm Information > Firm Account Groupings.

With a Row or Cell selected, click the Formula button in the Grouping content field to create a formula based on the selected grouping type and apply it to the contents of the selected row or cell.

Notes

  • Cell references will only work if the formula is entered in a cell below and to the right of all referenced cells. For details, see Report Designer: Formulas not working properly because of cell referencing.
  • 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 click 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, it must be enclosed in square brackets and the correct syntax must be used to create a valid formula. For example, [B3 + B9]. When the syntax is incorrect —for example, 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. Examples

    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).

    repeating row CA

    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.)

    formula not allowed

    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).

    one time row

    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).

    all CA

Was this article helpful?

Thank you for the feedback!

Internal only

Validate example.

The Validate function enables you to check data for specific formatting by using custom regular expressions for the selected variable. Example.

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)