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 BASEROWin the formula use the row definition for the related base row code(s). CBRrows have the following characteristics:
-
CBRrows are not printed print on the completed report.
-
The CBRformat code and its related row code are positioned above the row or section that displays related calculations.
In a column definition, the CALCcolumn type indicates a column that specifies a formula in the Formularow. This formula operates on the data for this column of the report and uses the Baserow keyword to base calculations off of the CBRformat code(s) in the row.
In the row definition, the CBRformat 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 CBRis 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 CBRand 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.
Select the base row in a row
definition for a column calculation
You can define a column definition that reports current and year-to-date results, together with a base percentage of each. Start with a detailed income statement.
-
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 Formulacell 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 Overridecell to open the Format Overridedialogue box. Select Percentagein the Format Categorylist, and then click OK.
-
On the Filemenu, click Save Asto save the column definition with a new name. Append the current file name with CBR, such as CUR_YTD_CBR. This is your baserow column definition.
-
In Report Designer, click Row Definitions, and then open the row definition to modify with the baserow calculation. Insert a new row above where the baserow calculation should start.
-
Double-click the Format Codecell of the row definition, and then select CBR.
-
In the Related Formulas/Rows/Unitscell, type the Row Codenumber for the base row.
Example
In the following row definition example, row 100 shows that the base row for calculations is row 340.
In the following column definition example, the calculations use the CBRcode. 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.
As a result of the previous calculations, the following sample report could be generated.
See Also
Build date: 2012-12-03 [15:51]