How to insert Slicers from PowerPivot hierarchies the right way

I think the Excel 2010 introduction of Slicers to filter your pivot tables makes it so much easier to filter the information in the pivot table and the ability to let slicers “Show items with no data last” was very helpful.

But when working on demo based on Adventure Works I was very surprised that it didn’t work when I added the Geography hierarchy as slicers to my Pivot table.

In this example I choose Canada as the Country Region and the state province slicer shows me the Canadian provinces but not as the first items … that’s still the Australian provinces 😦

So naturally I checked the setting of the State Province and it was apparently correct.

After trying a couple of times more using the “Insert Slicer” from the “Pivottable” tools table…

And thinking about MDX and how you must break hierarchies in MDX function DESCENDANTS to sort by using the BASC or BDESC – I thought it would be an idea to add the individual levels in the hierarchy as filters instead.

And voila – now the slicers “works”

So in order to make slicers from hierarchies as you would hope for – remember to select the level individually instead of from the hierarchy.

5 thoughts on “How to insert Slicers from PowerPivot hierarchies the right way

  1. the hierarchy slicer breaks and does not work after a while, ı dont understand why that happens? when you make a selection reverse the hieararchy it breaks???? any help ?

  2. hi, about slicers, how to not allow a gray option is clicked, it does not have to that hierarchy information associated with it. Is there a VBA code that locked this selection, since it is grayed out?

    //Luiz (BR)

    1. Hi Luiz,

      Sorry for the late answer – I believe that you might be able to catch the event via the Pivottable_update event and then notify the user about it – let me know if you need an example


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s