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 of the Best Practices sample database. In each pair, the Product hyperblock displays only the Winter Tires level of the hierarchy, and the Period hyperblock displays only 2002, its quarters and their months. The ROC 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: