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 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.
How to select the current year and month automatically when you open the workbook?
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?
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
Hi Greg, nice and simple solution with the daily update of the date dimension for setting the slicer to yesterday.
Yes, Even I am trying to get the current date to be get selected with respective data after opening an Excel.
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.
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?
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.
Regards,
Greg