Using Excel 2010 slicers on a SSAS Molap datasource

On of the benefits of moving to Office 2010 (besides of course getting access to PowerPivot 🙂 ) is the existence of slicers. You can use slicers for all the Pivottable inside Excel 2010, not only PowerPivot but also a regular Pivottable on Excel data or based on SSAS Molap.

In this example I have connected to a Molap database and created a Pivottable to show the sales per Currency type:

Now I want this report to be dynamic per Year. We can use a slicer to slice the data per year.

Go to the ribbon, go to Pivottable tools, Options, Insert Slicer:

This will open a window that let’s you select the attribute you want to use in your slicer :

In our case we want to use CalendarYear. This will give us the following report on top of Molap:

Very cool 🙂

  • Cedric

    Thanks for this ,maybe you can assist.I have a date dimension as above ,however when I choose the month ,the slicer does not display a distinct list of months ,it displays and repeats the ( Jan ,feb,…..,Jan Feb,Mar) in that sequence repeatedly for how ever many years I have in my date dimension ,any ideas on what the reason could be ?

  • Jason

    @Cedric

    Check that you are not creating a slicer from a hierarchy. If the slicer is in a related table the slicer attribute should not be in a hierarchy if that hierarchy is not strictly hierarchal. Better to pick the value from the table itself and not from the location within the hierarchy.

  • Thanks for the info, Could you please let me know how to refresh the slicer,calender year here, to get latest values(Ex: year 2013, 2014 ) in the Slicer with out refreshing all the pivots.

    I am asking this because there are many pivots connected to a slicer in my excel and I do not want to refresh all the pivots connected to the slicer just to get the latest Date values in the Slicer.

    • Kasper de Jonge

      Hi, you can’t change it without refreshing everything else. Slicers work as where values in a query and if those values changes but not the pivottables the wrong queries will get send. You’ll be in trouble when things aren’t consistent. There might be a hack using VBA but not something I can think of out of the box.