The Formula detail row applies to calculation columns (those with column type CALC).
You can add, subtract, multiply, or divide the amounts in columns. You can also perform any type of complex calculation by including IF/THEN/ELSE statements within the formula.
Column calculations can refer to any other column, including subsequent columns. Microsoft Office PerformancePoint 2007 Management Reporter resolves the dependent columns first. If you refer a column to another column that, in turn, refers back to the first column, a circular reference error results.
To express the calculation result as a percentage, use a special format mask.
NOTE The results of calculation formulas do not include the values in non-printing columns.
A calculation formula can include any or all of the following:
Add, Subtract
To add or subtract amounts in the columns, specify the column letter and the plus sign (+) or minus sign (-), for example:
A+B or A-B+C
You can also use the colon (:) separator to add a range of consecutive columns, for example:
A:D
TOP OF PAGE
Multiply, Divide
To multiply and divide columns, type the column letters in the order of computation. Use the appropriate operator to separate each column letter (* for multiplication, / for division). For example, if you type B*D, you are instructing Management Reporter to multiply column B by column D.
You can also refer to a specific report cell by typing a column letter and a row code. For example, B.100 refers to column B, row code 100.
You can divide an entire column by a specific report cell amount that is in the same column. For example, if you type B/B.100, you are instructing Management Reporter to divide the entire column B by the value in column B, row code 100.
NOTE This calculation will be incorrect if you change the report's calculation priority. You can set the calculation priority on the Settings tab of the report definition.
TOP OF PAGE
Complex calculations
SucceededA complex calculation can contain any combination of cell references, operators, values, and levels of nested parentheses. For example, to compute the average of columns A and B, type:
((A+B)/2)
TOP OF PAGE
Multiply or Divide by Base Row
You can create a column that displays all of the values in a specified column as a percentage of a base number. This feature provides a method to show relationships between rows, such as a percentage of a sales row or a percentage of a total expenses row.
To multiply or divide each row in a specific column by a base row, type the column to be used in the calculation, and then type *CBR or /CBR (for example, type C*CBR or C/CBR).
NOTE When you use a base row calculation in a column definition, make sure that each row definition that is used with this column definition contains at least one base row for calculations. For more information, see Setting the Base Row for a Column Calculation.
TOP OF PAGE
IF/THEN/ELSE Statements
An IF/THEN/ELSE statement enables any calculation to be conditional upon the results of any other column. You can refer to other columns, but not to a report cell in the IF statement; thus, any calculation must be applied to the entire column.
For example, the statement IF B>100 THEN B ELSE C*1.25 means the following: If the amount in column B is greater than 100, then place the value from column B in the CALC column. If the amount is not greater than 100, multiply the value in column C by 1.25, and place the result in the CALC column.
Always follow the IF clause with a logic statement which evaluates to TRUE or FALSE. The formulas that you use for both the THEN clause and the ELSE clause can contain references to any number of columns, and may be as complex as you want.
NOTE You cannot place the results of a calculation in any other column; the results must be in the column that contains the formula.
TOP OF PAGE
Divide by the Number of Periods
You can divide the amount in a column by a specified number of periods. For example, the calculation B/Periodsdivides the value in column B by the number of periods in column B. If the calculation spans multiple columns, specify the number of periods to use in the calculation.
For example, the formula (B+C)/Periods means to add the amounts in columns B and C, and then divide the result by the value of the Period for this column.
