THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Setting default values for slicers in PowerPivot

I've been doing some work with PowerPivot and SharePoint/Excel Services this week. I wanted the user interface to have slicers for:

  • Year
  • Month
  • Day

But I wanted the slicer to be preselected for the current month. There is no property on the slicers to set a default value. I read a number of websites and the suggestion was to use VBA code to set the value. This works but if you want to have the VBA code run at workbook open, you have to create a macro-enabled workbook, and these are not supported by Excel Services.

So I seemed to be fresh out of luck. However, one of my Spanish SolidQ colleagues José Quinto Zamora came to the rescue. All you need to do is to select the slicer filter value that you want as the default, before you save the workbook, and every time you open the workbook, the slicer value will be already selected. That's as good as a default value for me. Thanks José!

Hope this helps someone else.

Published Thursday, August 11, 2011 2:56 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Challen Fu said:

How to select the current year and month automatically when you open the workbook?

February 28, 2012 12:57 AM

Bhavik Merchant said:

I have the same issue Greg. However, that solution is not the best.. its still a workaround. Lets say we are in June and i publish with June selected. in July i would have liked the slicer to automatically roll over to July as the default value without any manual intervention.

Have you come across any solutions?

June 5, 2012 10:54 PM

Greg Low said:


what I do is to update the date dimension daily, have columns that indicate things like "yesterday", "mtd", etc. then have slicers based on those columns and preselect the slicer value. That way I can have a chart that always shows yesterday's data (as an example), even though that date keeps moving.



June 6, 2012 12:45 AM

Frederik said:

Hi Greg, nice and simple solution with the daily update of the date dimension for setting the slicer to yesterday.

September 16, 2014 4:39 AM

Rupesh said:

Yes, Even I am trying to get the current date to be get selected with respective data after opening an Excel.

November 21, 2014 10:02 AM

Graham Whiteman said:

Thanks Greg,

Exactly what I needed to see. I was thinking about this method over lunch and hadn't put all the pieces together.. I am about to try it.. Fingers crossed.

March 18, 2015 12:07 AM

JohnY said:

Hi, this solution sounds good if you want a STATIC default, but what if you want the date to be dynamic; i.e roll over to the current month, but then the user can change the selection?

May 31, 2015 8:24 PM

Greg Low said:

Sorry, not quite following. If I reload my date dimension every night, I can have indicators like MTD, Yesterday, etc. based on the day's date values, then I can set the default slicer value to that, and it "dynamically" changes to the appropriate value each time. The user can then change to whatever they want.

Otherwise, might have to get you to provide an example of what you mean.



June 2, 2015 6:11 AM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement