Related formulas/rows/units
The Related Formulas/Rows/Units cell has multiple purposes. Depending on the type of row, a Related Formulas/Rows/Units cell can do one of the following functions:
Define the rows to include in a calculation when you use a TOT format code or a CAL format code. For more information, see Specify a row code in a row definition.
Link a formatting row to an amount row to print the formatting only when the related amount is printed. For more information, see Relate a format row to an amount row.
Restrict a row to a specific reporting unit. For more information, see the “Restrict a row to a reporting unit” section in Advanced formatting options.
Define the base row for calculations when you use the BASEROW format code. For more information, see Select the base row for a column calculation.
Define the rows to sort when you use any of the sorting format codes. For more information, see Select a sorting code for a row definition.
This topic contains the following sections:
Use a row total in a row definition
Components of a row total formula
Relate a format row to an amount row
Example of suppressing printing of rows
Select the base row for a column calculation
Example of base row calculation
Select a sorting code for a row definition
Use a row total in a row definition
Use a row total formula to add or subtract amounts in other rows. A formula for creating a row total can include the + and - operators to combine individual row codes and ranges, indicated by a colon (:). The formula can be up to 1024 characters.
The following is an example of a standard totaling formula:
400+420+430+450+460LIABILITIES+EQUITY520:546520:546-LIABILITIES
Components of a row total formula
When you create a row total formula, you must use row codes to specify which rows to add or subtract in the current row definition, and you must use operators to specify how the rows are combined. Total rows and amount rows can be used in any combination.
Note
All total rows that are in a range are excluded. To create a grand total, you can specify the range of rows. If the first row of a range is a total, that row is included in the new total.
The following table describes how operators are used in row total formulas.
Operator |
Example formula |
Description |
|---|---|---|
+ |
100+330 |
Adds the amount in row 100 to the amount in row 330. |
: |
100:330 |
Sums the totals of all rows between row 100 and row 330. |
- |
100-330 |
Subtracts the amount in row 100 from the amount in row 330. |
Create a row total
In Report Designer, click Row Definitions, and then open the row definition to modify.
Double-click the Format Code cell in the row definition, and select TOT.
In the Related Formulas/Rows/Units cell, type the total formula.
Relate a format row to an amount row
In the Format Code column in a row definition, the DES, LFT, RGT, CEN, ---, and === format codes apply formatting to non-amount rows. To avoid printing this formatting when the related amount rows are suppressed, because the amount rows contain zero values or no period activity, you must relate the format rows to the corresponding amount rows. This is helpful when you want to suppress the printing of headers or formatting related to subtotals when there is no detail to print for the period.
Note
You can also suppress the detailed amount rows from printing by clearing the option to display rows without amounts, which is on the Settings tab of the report definition.
By default, transaction detail accounts that have a zero balance or no period activity are suppressed in reports. To show these transaction detail accounts, select the Display rows without an amounts check box on the Settings tab of the report definition.
Relate a format row to an amount row
In Report Designer, click Row Definitions, and then select a row definition to modify.
In the Related Formulas/Rows/Units cell in the formatting row, type the row code of the amount row to be suppressed.
Note
To suppress an amount row, the balance of the row must be zero. An amount row with a balance is not suppressed.
On the File menu, click Save.
Example of suppressing printing of rows
In the following example, Phyllis wants to suppress printing of the heading and underscores in the Total Cash row in her report because there was no activity in either of the cash accounts. She types 190, the amount row code to be suppressed, in the Related Formulas/Rows/Units cell in row 160, which is a formatting row because the Format Code is ---.
.gif "Suppress printing example")
Select the base row for a column calculation
In relational reporting, one or more base rows are assigned in the row definition by using the CBR (Change Base Row) format code. A base row is then referenced by a calculation in the column definition. Common examples of CBR calculations include the following:
Percentage of total revenue as it relates to individual revenue items.
Percentage of total expense as it relates to individual expense items.
Percentage of gross margin as it relates to division or department details.
One or more base rows are defined in the row definition, and then the column definition determines the relationship that the base row is reported on. The code used in the column formula is BASEROW. The following basic mathematical operators are used with BASEROW: divide, multiply, add, or subtract, although the most common is divide by BASEROW, where the result is displayed as a percentage.
Column calculations that use BASEROW in the formula use the row definition for the related base row code(s). CBR rows have the following characteristics:
CBR rows are not printed on the completed report.
The CBR format code and its related row code are positioned above the row or section that displays related calculations.
In a column definition, the CALC column type indicates a column that specifies a formula in the Formula row. This formula operates on the data for this column of the report and uses the Baserow keyword to base calculations off of the CBR format codes in the row.
In the row definition, the CBR format code defines the base row for columns that calculate a percentage of or multiply by the base row for each row in the report. You can have multiple CBRs in a row format, such as one with net sales, one with gross sales, and one with total expenses. Usually, the CBR is used to create a percentage for accounts that are compared to a total line. A base row is used for all calculations until another base row is defined. You must define a starting CBR and an ending CBR.
For example, to determine expenses as a percentage of net sales, you could divide the value in each expense row by the value in the net sales row. In this case, the net sales row is the base row.
You can define a column definition that reports current and year-to-date results, together with a base percentage of each as shown in the following example. Start with a detailed income statement.
Select the base row in a row definition for a column calculation
In Report Designer, click Column Definitions, and then open the column definition for an income statement.
Add a new column to the column definition with a column type of CALC.
In the Formula cell of the new column, enter the formula of X/BASEROW, where X is the FD column type that you want to see a percentage of.
Double-click the Format/Currency Override cell to open the Format Override dialog box. Select Percentage in the Format Category list, and then click OK.
On the File menu, click Save As to save the column definition with a new name. Append the current file name with CBR, such as CUR_YTD_CBR. This is your base row column definition.
In Report Designer, click Row Definitions, and then open the row definition to modify with the base row calculation. Insert a new row above where the base row calculation should start.
Double-click the Format Code cell of the row definition, and then select CBR.
In the Related Formulas/Rows/Units cell, type the Row Code number for the base row.
Example of base row calculation
In the following row definition example, row 100 shows that the base row for calculations is row 340.
.gif "Screenshot of column definition to set baserow")
In the following column definition example, the calculations use the CBR code. The calculation in column C has the effect of dividing the value in column B of the report by the value in row 340 of column B. The format override in column B prints the result of the calculation as a percentage. Similarly, each amount in column E is the amount in column D expressed as a percentage of net sales.
.gif "Column def screen shot for Set Base Row Column")
As a result of the previous calculations, the following sample report could be generated.
.gif "Sample report for Set Base Row for a Column Calc")
Select a sorting code for a row definition
Sorting codes sort accounts, values, sequence an actual or budget variance report by the largest variance, or sort the row descriptions alphabetically. The following sorting codes are available:
SORT – Sorts the report in ascending order, based on the values in the specified column.
ASORT – Sorts the report by the absolute value of the values in the specified column in ascending order. In other words, the sign of each value is ignored in the sort. This format code sequences the values by the magnitude of the variance, whether positive or negative.
SORTDESC – Sorts the report in descending order, based on the values in the specified column.
ASORTDESC – Sorts the report in descending order by the absolute value of the values in the specified column.
Select a sorting code
In Report Designer, click Row Definitions, and then open the row definition to modify.
Double-click the Format Code cell, and then select a sorting code.
In the Related Formulas/Rows/Units cell, type the range of row codes to sort. To specify a range, enter the first and last row codes separated with a colon. For example, 160:490 specifies the range 160 through 490.
In the Column Restriction cell, type the letter of the report column that you want to use for the sort.
Note
Include only amount rows in a sort calculation.