In Excel 2013, there is a new Timeline filter (slicer) that allows you to easily select a range of dates over which you want to filter your data. The feature is very slick. Here’s an picture:
I wanted to use this slicer in an Analysis Services demo but run into a bit of an issue getting this to work with my OLAP cube. After spending a little time with the Excel team, it turns out that this slicer picks up on the MemberValue property. If you map an attribute’s MemberValue property to a field that uses a date data type, the Timeline filter is available to you.
In my scenario, that means that I’m setting up a Date dimension with a leaf-level Date attribute. That attribute’s MemberKey property is assigned to an integer surrogate key column and it’s MemberName property is assigned to a friendly formatted string (WChar) column. I have set up all my Type properties (on the dimension and this and other attributes) as you would expect (because I want to use some of the Time Intelligence features in Analysis Services). And now I map the MemberValue property on the Date attribute to an actual field using a Date data type to enable the Timeline filter.
It turns out that in Excel 2010 & 2007 this same step (of assigning the MemberValue property to a date data type field) triggers a Date Filter drop-down on row and column filters. Who knew?! 🙂
UPDATE After posting this, Greg Galloway sent me this link to an Excel 2007 document identifying OLAP design practices that will light up functionality in the Pivot Table. This includes the Date filtering mentioned above. The requirements mentioned there are that the dimension has a Type of Time and the leaf-level attribute has a MemberValue property mapped to a field with a date data type. These are the exact constraints on the Timeline filter.