Using a slicer to accurately show parent/child hierarchy

[borntorun75]

Hi there,

I'm new to using slicers and really like the functionality they bring to a pivot.

In the example below, I'm showing the source data (A1 to E17) and a couple of pivots. You'll see that for each department (Sales and Admin), there's a Country and Area - with Wages information for each. In respect of the Area (column C), in terms of relationships its 'parent' is the Country (column B)

So, Country USA has 4 areas (North, East, South and West). Country Ireland has one area called (all)

---

The first pivot (G1 to H16) is a traditional pivot (no slicers).

The second pivot (J17 to M22) is a pivot with the 3 slicers you see positioned above it.

As you can see from the screen shot, on the slicers I've currently selected Sales and Ireland. The 3rd slicer correctly shows that the only Area available to select is (all). That's highlighted in the darker blue, at the top of the list.

This third slicer also shows the Areas of the other available Countries below it (North, East, South and West). As you can see from the data, they don't apply to the selected country of Ireland in this case.

My question is can I control the contents of the 3rd slicer box to only show the Area(s) of the parent Country (in the 2nd slicer box).

Can I show that relationship and only populate the contents of child slicers based on the children its parent has ... ?

In this example, since I've selected Ireland, could the 3rd slicer box be manipulated so that it only showed "(all)"? Similarly if I selected USA as the Country, could the 3rd slicer box be manipulated so that it only showed the 4 Areas (North, East, South and West) ?

I can get by with how it is now, but I'm working on something that I want to distribute and i think it'd be a nicer solution to not show children items that don't belong to the parent.

Many thanks. I'm using Excel 2010 on Windows 7.

My usual preference is to try and achieve results by avoiding VBA, so that users don't have to 'manage' turning on macros, etc. If there's a non-VBA solution, that'd be great. However, I appreciate that sometimes VBA is the only way to resolve a problem easily.

Best regards,
Mike.

 

[Tianbas]

Not sure if this is really a PowerPivot question but its an issue in PowerPivot as well as in standard Excel. Iam not aware of any solution to show only the slicers that have valid data. All you can do is make your slicer box a bit smaller so that (most) of the valid slicer are visible and the light blue non valid slicer at the bottom are only visble if you scroll down in the box. There is an additional ugly bug/feature with slicer that you dont want to see in a report. If you remove all data with area North from your table, most likely the slicer will still show North.


But even with some of this weaknesses Slicer are still one of the best things that came with Excel 2010

 

[borntorun75]

Thanks Tianbas,

That's a shame if that's the case.

They are really useful, and I'm surprised that I've never really picked up on how useful they are until now.

The reason why I'm wanting to do as I described is that I want to start using this functionality in some of my spreadsheet, and typically they'll have a more complex structure than the above example. It'd be really nice to show only that correct parent/child relationship in the slicers, as (with respect) some of my audience don't necessarily have the level of awareness that others do. So, it'd be nice to stop them from being able to click and selecting children that aren't really part of the parent structure.

Thanks.

 

[GDRIII]

I have an idea that I can't take the time to finish testing out but, in essence, I was thinking about putting your Children on a separate Pivot in a separate sheet that is controlled by the Slicer of the Parent using Pivot Table Connections. Children on Rows would be it.

Then, create a table to the right of that Children Pivot, that would show what is available based on the slicer. To try and force this in and I used =if(a2>0,a2,"")

That table would then get linked into the PP window, and then linked back to the table that holds the original children and drop that "dynamically changing table based on slicer selection of Parent" into your slicer... It works and eliminates everything BUT, once it is liked I get an error BUT, before linking it my slicer changes based on the selection and doesn't show all of the Children if I hit update or reselect It would update on the refresh BUT, I don't have time to fully test it out and get rid of the bugs I got about a mismatched data type.

Maybe this will help. Sooo close. It is certainly something I want to pursue when I am not in such a crunch. Thanks for the idea.

 

[Tianbas]

GDRIII please test and report but I have some doubts because normally the data flow from your PP model to the Pivot and the Slicer only changes the view on that Pivot without refreshing the link to the data model. With your solution you would need a kind of refresh of the data model and the pivot after somebody hit the slicer.

 

[GDRIII]

Tianbas,

You are correct, you need to refresh and then refresh the slicer by reselecting the parent to get the Children to disappear... but, maybe someone with fancy VBA skills can make this work.

Also interesting that after linking the "Child" table, PP will not allow it to be used in any Field at all...(Exception from HRESULT: 0x80070057(E_INVALIDARG)) Even after I deleted the relationship.

Oh well.

 

[borntorun75]

Hi,

I like your train of though GDRIII and can see where you're coming from in your thinking. Tantalisingly close.

Maybe this might need some VBA ultimately (if it's crackable). I always do like to stretch things without going the VBA route, and I've often been surprised how sophisticated you can be with the 'core' Excel functionality, rather than using VBA.

Please keep this nut in mind if you do ever find a quiet moment and want to try cracking it again. Any input is always very much appreciated. I know how busy people can be, so thanks for taking time-out to reply.

Mike.

 

[XLBob]

I don't know why you want to do it. Interesting to know how to solve this problem as I can not think an easy to do it, not even VBA

 

[borntorun75]

Hi,

The reason why I want to do it is that the example I gave is a significantly oversimplified example of the real problem. In actual fact, the real data I have has approx 20 countries (the 'parent' in this case), and approx 250+ Areas (the 'child/children' in this case).

Every time my users choose one of the Countries in the parent slicer, I do not really want to present them with the full 250+ Areas that exist as children to the countries.

e.g.
Ireland really has 15+ Areas.
USA really has 40+ Areas
Canada really has 30+ Areas
England really has 25+ Areas.
etc. etc.

So, if my user selects Canada as the Country, I don't want to shown the full list of 250+ Areas in the Area slicer. I just want the user to be presented with the 30+ Areas that are relevant to Canada. By showing another 220+ Areas that aren't for Canada, I feel that will confuse my users.

Cheers, Mike.

 

[Tianbas]

Found someting on another site. It seems with Excel 2013 the checkbox in the slicer settings is working and you can remove the items with no data. I think this also works in above case. Cant test it as Iam still with 2010 and here it does not work

Exploring Excel 2013 for BI Tip #5: Cleaning Up Slicers | Data on Wheels - Steve Hughes