THE SQL Server Blog Spot on the Web

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

The Bit Bucket (Greg Low): 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

Comments

 

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:

Yes,

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.

HTH,

Greg

June 6, 2012 12:45 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement