Excel 2007 and multiple row hierarchies


  • Hello,

    When you drag on  multiple hierarchies to a row on a pivot report in Excel 2007 it always shows the top level of each hierarchy.

    I'd like it to show only the top level of the 1st hierarchy and then show the next hierarchy if the user double clicks (drills through) on a particular member.

    Is ther an option I can change to get this behaviour?

    It does this automatically with multi-level hierarchies defined on the server but I want the same display behaviour when the user is dragging on more than 1 hierarchy for a report.

    Does that make sense?
    sábado, 21 de marzo de 2009 11:01

Todas las respuestas

  • Use Classic Pivot table layout (Pivot table properties -> layout -> Check the option to show classic pivot table layout there). This will show expandable hierarchy in rows. Is this what you were looking for?
    sábado, 21 de marzo de 2009 12:29
  • No, that doesn't seem to make a difference to how multiple row hierarchies are displayed.

    Ideally I want Excel to display them the same way Office Web Components does.

    So if I have a Region hierarchy and then I drag on a Manager hierarchy as the second hierarchy on the row then I want all the Regions showing but no Managers showing.  Then when I expand a region I'll see the relevant Managers and the breakdown of data.

    At the moment if I drag on the Manager hierarchy all the regions are expanded and show all the managers below each region.  Their is no way to collapse them. 

    There doesn't seem to be any way to have only 1 region expanded and the rest collapsed unless you design the hierarchy in the cube on the server and then you can expand individual members.

    sábado, 21 de marzo de 2009 21:58
  •  If you use attribute hierarchies only, like Region & Manager as attributes from different dimensions, then it should work (Right click Region -> Expand/Collapse -> Collapse All). This works even if you use more than 2 attributes in the report row. But for when you use hierarchy (user defined hierarchy in the cube), it doesn’t work for some reason. If you have hierarchy levels as separate attributes, that should work.
    lunes, 23 de marzo de 2009 10:04
  • Hi there, thanks for the extra info and sorry it's taken so long to get back to you.

    I tried what you suggested and couldn't get it to work.  Regardless of whether I tried attributes (from same or different dimensions) or user hierarchies (fram same or different dimensions) all I get is both levels expanded and i can't collapse either.

    Any other thoughts.  Is there an Excel setting I'm missing?
    jueves, 30 de abril de 2009 11:48
  • Anyone figure this out because it's killing me and I can't find anything on it other than this topic.
    miércoles, 21 de octubre de 2009 14:39
  • Hi
    Sorry, I realized that I hadn't understood the problem fully when I posted earlier. Whether it's attribut hierarchy or user hierarchy - when two hierarchies are added, atleast one level from each will be visible as far as i know... If you try hiding the levels (show-hide fields), it throws an error when the last one is unchecked -and the message says it's required to have atleast one level from the hierarchy to be visible.. I'm not aware of any work-around either.
    Btw, if you want to hide all the levels from a hierarchy, why do you even add it in the row? One can as well place it in filter or just let whoever wants to see it drag and drop from fields list (?). Afterall, that's why we have pivot table - have all fields exposed and users build ad-hoc reports on need basis rather than adding all fields in report..
    martes, 10 de noviembre de 2009 18:23
  • Hi, 

    I've got exactly the same problem and that's killing me too ...

    I've posted an image so maybe people could understand better...

    You see, there i try to put 2 hierarchies in a row... But i don't want to show the second one UNTIL we deploy the First one...

    Can someone get us help? 

    Belgium - Liège

    miércoles, 22 de febrero de 2012 13:53