RP004

By default, when you sort the elements in a hierarchy in ascending or descending order, they are sorted within the hierarchy levels to which they belong. The hierarchy levels are then sorted. Like Report RP003, the purpose of Report RP004 is to show how to sort the elements non-hierarchically. That is, to sort the hierarchy overall, rather than at its separate levels. But, unlike Report RP003, Report RP004 uses pairs of overlapping hyperblocks. In this case, sorting the elements, both hierarchically and non-hierarchically, requires an additional step.

Report RP004 contains four pairs of overlapping hyperblocks. Each pair is created from the Product and Period hierarchies of the Analysis cube. In each pair, the Product hyperblock displays only the Winter Tires level of the hierarchy, and the Period hyperblock displays only 2016, its quarters and their months. The formula in the value cell of each pair of hyperblocks is the same.

In the first pair of hyperblocks, the values are not sorted.

In the second pair of hyperblocks, an ascending sort has been specified on the Period hierarchy. Expand the levels of the hierarchy and notice that the values are not sorted correctly - either within their levels or in the hierarchy as a whole.

In the third pair of hyperblocks, the Period hierarchy is also sorted in ascending order. Additionally, the Break Hierarchy option has been selected. In Report RP003 this was all that was required to sort the values non-hierarchically. But, in this case, the values are still not sorted in ascending order.

In the fourth pair of hyperblocks, the Period hierarchy is again sorted in ascending order. The values are correctly sorted in ascending order, from lowest value to highest. This is because the Data Area has been amended so that it references the Product hierarchy.

To make the second pair of hyperblocks display the same results as the fourth, take these steps to break the hierarchy and define the Data Area:

  1. In Design mode, click the border of the Period hyperblock in the second pair of hyperblocks.
  2. In the List Designer, Value of data area ascending is displayed in the Sorting pane.
    If the List Designer is not displayed, press F12. Click Sorting.
  3. In the tool bar of the Sorting pane, click Break Hierarchy.
  4. Click Apply changes to the list in the tool bar of the List Designer.
  5. View the report in View mode.
    The second pair of hyperblocks now displays the same results as the third pair.
  6. Repeat Steps 1 and 2.
  7. In the Sorting pane, click Value of data area ascending.
    The Sorting Criteria settings and Data Area are displayed. For each hierarchy in the Data Area, Current element is selected.
  8. In the Data Area, click the Product hierarchy.
  9. Select Set from the list on the Product hierarchy.
    A warning icon is displayed in the Selection column of the Data Area.
  10. Double-click the warning icon.
    The Select Elements dialog is displayed.
  11. Expand the Hyperblocks node and double-click Hyperblock5, which is the Product hyperblock in this pair of hyperblocks.
    =ReportObjects.Hyperblock5.Text is displayed in the right-hand pane.
  12. Click OK.
  13. Click Apply changes to the list in the tool bar of the List Designer.
  14. View the report in View mode.
    The results displayed by the second pair of hyperblocks are the same as those displayed by the fourth pair of hyperblocks.