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 🙂
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 ?
@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.
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.