Using a formula as a filter to hide or display columns and rows

This example uses a formula to compare a value for sales volume with an entered value. Rows containing sales volumes that are smaller than the entered value are hidden. This form of filtering is necessary because the required filter depends on more than one cube.

This example uses the Sales and Finance cubes.

  1. Create a report and create a standard slice on the Finance cube. Add Unit as the Rows hierarchy.
  2. Click Edit list in the 1st Segment column. In the list designer, click the Functions icon and select Elements underneath > Company. Click the Apply Changes icon.
  3. Add Account as the Columns hierarchy.
  4. Click Edit list in the 1st Segment column. In the list designer, select IFRS > P&L > Net Income/Loss > Earnings before tax (EBT). Click the Apply Changes icon and then click OK.
  5. Drag the slice to cell B8, and then extend the rows hyperblock to cell F8.
  6. Click cell D8 and then click the Edit OLAP formula icon on the Objects toolbar.
  7. Select Cell values as the formula type and Sales as the cube.
  8. Click Select Hierarchy and select Profit. In the Selection column, select the Sales Volume element.
  9. Click Select Hierarchy and select Version. In the Selection column, select the Actual element.
  10. Click Select Hierarchy and select Formula. Specify =B8 in the Selection column.
  11. In cell F8, specify =IF(D8>=C5,TRUE,FALSE)
  12. Right-click the header of row 8 and select Rows. Select the Formula check box and specify =F8.
  13. In Cell B5, specify Sales volume bigger than:
  14. Remove cell protection from cell C5.
  15. In View mode, specify a value in cell C5 that is greater than at least one of the sales volume values. Rows containing lesser values are hidden.