Sorting Question with Stacked Hierarchies
Is it possible to apply sorting to an inner hierarchy stacked with an outer hierarchy so that the inner hierarchy is sorted while respecting the current member of the outer hierarchy?
For example, (working with the standard Adventure Works cube from Microsoft), assume I have two hierarchies as follows:
- Product Categories that contains three levels: Category, Subcategory, and Product
- Sales Territory that contains three levels: Group, Country, and Region
I want to create a report that contains Subcategory with Country on the rows, showing the Reseller Sales Amount measure (doesn't really matter what the measure is for this example, I just chose one that is related to both hierarchies). I can create that report such that it looks like this:
Now, I want to sort the Subcategory level in descending order (to get the ones with the highest Reseller Sales Amount first), so I add this filter:
Note that I need to include "Break Hierarchies" because I'm working with the second level of the first hierarchy involved and I want the Subcategory members to sort across the Category parent members. So the report now looks like this:
So, the first sort is correct as Road Bikes does have the highest Reseller Sales Amount, followed by Mountain Bikes, Road Frames, and so on.
Now, however, I want to sort the Country members (which is the second level of the second hierarchy involved) so that they are listed in ascending order based on the Reseller Sales Amount -- but that sort should be done *within* the context of the current Subcategory member. So, just looking visually at the report above, for Road Bikes, the Country members should be in the order of United Kingdom, France, Canada, United States, but for Mountain Bikes, they should be in the order of France, United Kingdom, Canada, and United States.
I can't include the Sales Territory hierarchy in the first sort because that one is in descending order, so I tried adding a second sort as follows:
Again, I chose "Break Hierarchies" here because I want the Country members sorted regardless of their Group parent members (since I'm working with the second level in the Sales Territory hierarchy). This second sort does apply a sort to the report, but the sorting of the Country members appears to be independent of the Subcategory level included in the report (as you can see, the United Kingdom is listed first in every Subcategory since it has the least amount of Reseller Sales Amount overall, regardless of the Subcategory involved -- and thus for the Mountain Bikes member, the Country members "appear" to be sorted wrong):
If I go back and include the Product Categories hierarchy in the second sort, it doesn't appear to work as I get this result:
This result seems to indicate that the second sort, since it now includes Product Categories, is overriding the first sort -- which I can understand. But the question remains: how do I sort the second hierarchy involved such that the sort of the elements in that hierarchy occurs within the context of the current member of the first hierarchy involved in the report??
Is this still cooking? I've been crockpotting the same issue in BI Office.
The sort is still working as described in this question where each hierarchy is sorted independently.
Here is an example,
To achieve the requested functionality, you can use the N-of-N option.
Here is how,
I'm not sure that it'll become a built-in option as part of the sorting options, but if not, you can add a post request in our Ideas category, so it can be voted by others and be considered for a future release.
Hope this helps.