Editing the formula

The Tutorial2 report displays the variance between the actual and budgeted gross margin figures in respect of all channels, all points of sale, and all regions. Remember that the CELLGET formula automatically references the default element of any dimension that you do not explicitly reference. So, although the formula in C11 does not explicitly reference the Measure dimension, it returns the value for Gross Margin, which is the default element.

To display the variance in the figures for actual units sold, instead of for gross margin, we can change the formula. The formula currently explicitly references only the Product and Period dimensions. By default, it also references Gross Margin, which is the default element of the Valtype dimension. To display the values for units instead of gross margin, we must add an explicit reference to the Measure dimension:

  1. Open the Tutorial2 report.
  2. In Design mode, click cell C11 and click the Edit OLAP Formula button.
    The Edit OLAP Formula dialog box is displayed.
  3. In the Hierarchy column, click Select Hierarchy and select the Measure hierarchy.
  4. In the Selection column for the Measure hierarchy, click Gross Margin. Click the browse button that is enabled.
    The Edit Formula dialog box is displayed.
  5. Select the existing text in the right hand field. Double-click Units in the Elements tab.
  6. Click OK and then click OK again to exit the Edit OLAP Formula dialog box.
    Note: The figures in the report now represent units instead of the gross margin. To change the formula in this way might be suitable in a report for personal use. But if the report is to be used by other users, we must let them choose their own views of the data.