Hi there reader!
There are a couple of methods on how to populate so called ‘ragged hierarchies’ in SSAS, a few tricks:
Naturalizing a Parent-Child Hierarchy:
TopDown and hide middle levels:
BottomUp approach by Chris Webb (Ragged Hierarchies, HideMemberIf and MDX Compatibility):
I really liked the BottomUp approach that Chris Webb proposed. This is how a TopDown-hierarchy looks like:
And here’s the BottomUp-hierarchy we’re going to be using for this demonstration:
First things first, here’s the cube’s structure:
Here’s where the magic happens! Set the HideMemberIf-property to ‘OnlyChildWithParentName‘, so basically hide the child when it has the same name as its parent.
And here’s the result of that hard work:
This method works for Excel without setting the “MDX Compatibility = 2” requirement, but in SSRS and your MDX query it will only return those records which have no hidden levels. Strange issue, I suggest you make another seperate hierarchy for reporting that has the HideMemberIf property set to ‘Never’ for all levels.