Hierarchy Display Using excel services in Sharepoint 2013

Question

Hi,

 

We use SSAS 2012 to display cubes using excel on sharepoint. These cubes have hierarchy's and different dimensions below that. Example: Hierarchy - Country and having dimensions as Country and state.

- Country

Country

State

 

These cubes are displayed as a pivot table and when we select a dimension corresponding values are displayed in the data area.

 

When I select country dimension in this hierarchy, i get country values with plus sign - collapsed form (which is desired) and when I click on it we display the state value under it (other dimensions in the category)

 

The problem is even when we select state dimension it still shows country value with collapsed form and users have to do that manually. Is there any way by which when i select the state dimension it is expanded. i.e hierarchy's above are expanded and the ones below are collapsed.

 

ideally we would have liked if we were able to show only value of second dimension and not the hierarchy above. i.e. only State values and not country in this case. But apparently excel representation layer doesn't allow that.

 

Any help is appreciated. thanks for your help.