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?
Todas las respuestas
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.
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.
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?
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..
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