Hide Empty Hierarchy Dimension in Pivot Table

[SterreKapteijns]

Hi, 

I need to find a solution to hide 'empty' dimensions in a pivot table. The dimensions in the pivot table follow a certain hierarchy. Some ledgers have 5 levels and some have only 1 level. For the ledgers that have less levels, empty dimensions are shown. Of course, this is not desirable as some (sub) total values will be shown multiple times. 

I have tried it with a Vizlib Pivot Table, conditional show and if statements, but I could not get it to work. Does anyone have a possible solution or work-around?

Thanks in advance!

[Chanty4u]

you can try to create like this in script

IF(Not IsNull(Level1), Level1) AS Dimension1,

[Or]

I don't think there's any way to achieve what you're describing directly - pivot tables always use a balanced hierarchy and can't be made to use an unbalanced one. You could bump the Level 5 dimension member up to Level 2, but then you'd just have your nulls lower in the hierarchy anyway.

If your issue is with the same figures being displayed multiple times, you could handle this at the measure level. This won't fix the dimension but it will null the repeating values. You'd need to use Dimensionality() to figure out which level you're on and check the appropriate dimension as per that level. This isn't exactly the right code, but something like this might work - I've used pick/match to make it easier for me to read, but you can use nested if statements or maybe some sort of generic formula using GetObjectDimension(Dimensionality()). Note that I've multiplied the actual measure by either 1 or null so I don't have to write Sum(Value) a bunch of times, but again, that's a personal preference. 

Sum(Value) *

Pick(Match(Dimensionality(),'1','2','3','4','5','6','7'),

1,

If(not Isnull([Level 1]),1),

If(not Isnull([Level 2]),1),

If(not Isnull([Level 3]),1),

If(not Isnull([Level 4]),1),

If(not Isnull([Level 5]),1),

1)

[SterreKapteijns]

Thanks!