Skip to content
February 25, 2013 / Erik Svensen

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.

Advertisements

5 Comments

Leave a Comment
  1. Alesandra Blakeston / Feb 28 2013 3:37 pm

    Love slicers. Wouldn’t get much done without them. I’ve even taken the time to learn how to code them in vba. Excellent post!

  2. who / Nov 22 2013 10:07 am

    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 ?

    • Erik Svensen / Nov 22 2013 10:13 am

      Hi

      I have not experienced that – can you tell me how to reproduce the error ?

      /Erik

  3. Luiz / Dec 19 2013 7:51 pm

    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)

    • Erik Svensen / Jan 15 2014 4:02 pm

      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

      /Erik

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: