none
How to hide blank levels in User defined hierarchies

    Question

  • Hello All,

    My Greetings for the day!!!

    We are trying to implement a dynamic hierarchy affiliates wise with maximum of 8 levels.

    So in case of France there is 4 levels and in Spain there are 6 levels.

    Can you suggest, how to hide 4 blank levels in case of France or 2 blank levels in case of Spain.

    I have used HideMemberIf property = NoName, but while browsing the cube in Excel I still see all 8 levels in Hierarchy for France & Spain

    Screen shot for reference

    Regards,


    Hiren Parikh

    Thursday, June 14, 2018 10:33 AM

Answers

  • Hi Hiren,

    Thanks for your question.

    If I understand you correctly, you want to hide the unknown members in your user hierarchy, right?

    If so, you can try to set unknown member from visible to hide,see below screenshot:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 18, 2018 1:04 AM
    Moderator
  • Hi Hiren Parikh,

    As far as I know you cannot hide the levels in a user defined hierarchy.

    The hierarchy becomes static and all the levels will be seen in the front end reporting client.

    You can only hide dimension members in the levels of the hierarchy with the HideIfMember property as suggested in earlier posts by Willson and myself.

    If you want the levels to be dynamic then you need to go for a parent child hierarchy.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Monday, June 18, 2018 12:46 PM

All replies

  • Hi Hiren,

    Ideally it should work. 

    Can you please post some sample data so that we can have a look at it? Are there levels where there are no values coming as NULL or blanks in the database?

    Alternatively what you could do is for all the lower levels, if it is NULL then please fill in the NULL values with the same parent name and then for the child members in the hierarchy you can set the property hide member if parent name.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Thursday, June 14, 2018 7:28 PM
  • Hi Hiren,

    Thanks for your question.

    If I understand you correctly, you want to hide the unknown members in your user hierarchy, right?

    If so, you can try to set unknown member from visible to hide,see below screenshot:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 18, 2018 1:04 AM
    Moderator
  • Hello Ramakrishnan,

    Thanks alot for giving your valued time for this question.

    As per your suggestion I have applied the following :

    1 - I have 2 levels i.e. Level 7 & 8 null in the table.

    2 -  Level 7 & 8 filled with parent name value

    3 - I have applied the changes in the cube and still be able to see the Level 7 & 8 while browsing the cube in EXCEL. 

    Please revert with your valued suggestion as I want to hide the level 7 & 8 as I don't have data for level 7 & 8.

    Regards,


    Hiren Parikh

    Monday, June 18, 2018 11:05 AM
  • Hi,

    does Level 7 and level 8 have data for other dimension members apart from Spain and France? Or are level 7 and level 8 completely empty?

    Regards,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Monday, June 18, 2018 11:17 AM
  • Hello Ramakrishnan,

    Yes, The levels 7 & 8 completely empty.

    Regards,


    Hiren Parikh

    Monday, June 18, 2018 11:39 AM
  • >>Yes, The levels 7 & 8 completely empty.

    If levels 7 and level 8 are completely empty then you can just remove the levels from the hierarchy. Can you please let me know why you want to show levels in a hierarchy which don't have any data?

    Just to be aware that you cannot completely hide the level of a hierarchy.  But you can hide the members in the level of the hierarchy with the HideIFMember property.

    Also when you set the property, although you will see level 7 and level 8 in the pivot fields, when you drill down you can go until level 6,

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps




    Monday, June 18, 2018 11:49 AM
  • Hello Ramakrishnan,

    The purpose is to make dynamic hierarchy, which I plan to deploy for multiple affiliates.

    So, we cannot remove the hierarchy levels as you see there are only 4 levels for Spain and 6 for France.

    In future affiliates wise levels can change that's the reason I want to make dynamic hierarchy.

    For example : 

    For France affiliate we have 6 levels of data but in excel its shows 8 levels. This can confuse the users specific to France region.

    Regards,


    Hiren Parikh

    Monday, June 18, 2018 12:35 PM
  • Hello Willson,

    Thanks for your valued suggestion.

    I have applied the same in my cube and while I browse the cube in Excel. It is not producing the expected results.

    In excel I am getting 8 levels and can we hide the last 2 levels in excel as it does not hold any data.

    Regards,


    Hiren Parikh

    Monday, June 18, 2018 12:40 PM
  • Hi Hiren Parikh,

    As far as I know you cannot hide the levels in a user defined hierarchy.

    The hierarchy becomes static and all the levels will be seen in the front end reporting client.

    You can only hide dimension members in the levels of the hierarchy with the HideIfMember property as suggested in earlier posts by Willson and myself.

    If you want the levels to be dynamic then you need to go for a parent child hierarchy.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Monday, June 18, 2018 12:46 PM