In this procedure, we will create a formula to calculate the deviation
between Actual and Budgeted figures.
Open the Tutorial7 report.
Drag the right-hand edge of the Product hyperblock to cell F11.
Specify
Deviation as a label in F9.
Use Translateuniquestring to ensure that the label is translated
if the report language is changed. See
Translating labels for
further details.
In cell F11, specify this formula:
=(C11-D11)/C11.
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.
In View Mode, collapse the Period hierarchy so that only the All
Years element is displayed.
Cell F11 now shows the deviation between the Actual and Budget
figures. However, where the Actual figure is zero, a #DIV0! error is displayed
because you cannot divide by zero. To avoid this error, enclose the formula in
an IF statement:
=IF((C11<>0),(C11-D11)/C11,0) In View Mode,
the #DIV0! error is no longer displayed. However, a #Value error is displayed
when you expand the Period hierarchy.
In Design Mode, select the Period hyperblock.
In Structure Selection, select the All Years element and click
Apply changes to the
list.
In View Mode, only the figures for All Years are displayed.
Save the report and then create a copy called Tutorial8.