Calculating deviation

We can create a formula to calculate the deviation between Actual and Budgeted figures.

  1. Open the Tutorial7 report.
  2. Drag the right-hand edge of the Product hyperblock to cell F11.
  3. Specify Deviation as a label in F9.
  4. Use TRANSLATEUNIQUESTRING to ensure that the label is translated if the report language is changed.
  5. In cell F11, specify this formula:=IFERROR((C11-D11)/C11,0).
    Because division by zero is not possible, if we specify only =(C11-D11)/C11 then a #DIV0! is displayed for every zero value in the column. Also, a #Value error is displayed if the Period hyperblock is expanded.
  6. Right-click cell F11 and select Format Cells. On the Number Format tab, select Percent as the category and 0.00% as the number format.
  7. In View mode, collapse the Period hierarchy so that only the All Years element is displayed.
  8. In Design mode, select the Period hyperblock.
  9. In the list designer, select the All Years element and click Apply changes to the list.
    In View mode, only the figures for All Years can be displayed.
  10. Save the report and then create a copy called Tutorial8.