Adding further data slicers

There are seven dimensions in the sample ANALYSIS cube. All the dimensions contribute to the view of the data that the report displays but, as you can in the Edit OLAP Formula dialog box, we have made only three available to users.

Here we add combo boxes for the Channel, POS, Region and Valtype dimensions.

  1. In Tutorial2, create combo boxes in cells E6, G6, I6 and K6.
  2. Click Database Structure and expand the ANALYSIS cube.
  3. Drag these dimensions onto the specified cells:
    Dimension Cell
    Channel E6
    Point of Sales G6
    Region I6
    Value Type K6
  4. Open the Object Properties dialog box of the combo boxes and change their names to Channel, POS, Region, and Valtype respectively.
  5. Click cell C11 and click Edit OLAP Formula. Update the dialog so that the Selection column contains these entries:
    • =C10
    • =B11
    • ReportObjects.Measure.Text
    • ReportObjects.Channel.Text
    • ReportObjects.POS.Text
    • ReportObjects.Region.Text
    • ReportObjects.Valtype.Text

    Click Select Hierarchy in the Hierarchy column and choose the required hierarchy (for example, Region). The unique name of the default element of the hierarchy (for example,[Region].[All Regions]) is displayed in the Selection column. In the Selection column, click the down arrow (not the browse button) on the unique name of the element and select the name of the combo box from the list (for example =Objects.Region.Text). The Hierarchy column now displays Formula.

  6. In View mode, make different selections from the combo boxes and check that the data changes accordingly.
    Note: If you change the name of a list object, the references in the OLAP formula automatically update to the new name.

    As you make different selections from the different combo boxes, you may notice that, sometimes, the report shows only zero values. It is a common feature of multidimensional databases that, for many combinations of elements, there is no data.

  7. Select these elements in the combo boxes: Gross Margin, All Channels, All POS, All Regions, and Variance.
  8. Save the report and then create a copy called Tutorial3.