Restricting Rules to Simple Values

In the previous exercise, you created a rule that applies to all cells containing sales numbers. This type of rule supersedes consolidations within dimensions, producing incorrect results. In the following exercise, you inspect a quarterly total through a slice worksheet, and restrict the SalesCube rule to simple values, thereby allowing consolidations to function properly.

Procedure

  1. Open the sample view Sales1qtr of the SalesCube cube.
  2. Click Slice Slice button to slice the view to an Excel worksheet.

    The slice should appear as follows.

    View of cube slice showing calculated grand totals.
  3. Save the slice worksheet with the name Test.
  4. Examine the Sales value for 1Quarter in cell B10.

    According to the SalesCube rule, the value is the product of multiplying the first quarter price by a fraction (1/1000) of first quarter units. Instead, the value should be the consolidation of sales for the first three months. However, the values derived by the SalesCube rule (as it currently exists) take precedence over the values derived through consolidation. To correct this, you need to modify the rule so that it does not calculate the values for consolidated elements.

  5. Open the SalesCube rule in the Rules Editor.
  6. On the third line, insert N: in front of ['Price'] so that the formula reads: 
    ['Sales']=N:>['Price']*['Units']\1000;

    The restrictor N: limits the rule to the cells identified only by simple elements. The rule no longer applies to consolidated elements, leaving consolidations to function properly.

  7. Click Save to save the edited rule.
  8. Press F9 to recalculate the test worksheet.

    The correct value now opens at the intersection of Sales and 1Quarter.