Date Filters in Excel's Pivot Table

When I browse OLAP cubes using Excel 2007 and put date dimensions in the column or row area, some of them have a Date filter, while others only have a normal Label filter.

I was curious about what makes Excel recognise the dimension as a date dimension and thus makes the Date filter available. So I went about googling and found this post in the MSDN forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2174795&SiteID=1. Essentially, setting the ValueColumn of the date dimension's key field to a date type column will help Excel recognise the dimension as a date dimension.

I decided to experiment a bit more with a few different but common combinations of Key, Name and Value columns, to see their effects in Excel. Here are the results:

KeyColumn	NameColumn	ValueColumn	Date Filter?
Date	Date	(none)	Yes
Date	Char	(none)	No (*)
Date	Char	Date	Yes
Int	Int	(none)	No
Int	Char	(none)	No
Int	Char	Date	Yes

(*) Note: in this combination, if you also set the attribute's AttributeHierarchyVisible to false, then Excel makes date filter available for the dimension. However, it seem the filtering doesn't actually work correctly. For example, even if you apply a filter of "after 31/12/9999", every member is still coming through unfiltered.

Silverlight with SharePoint

Starting from today I am attending a four day MOSS 2007 Boot Camp. The instructor is Mick Badran of Breeze Training. He was pretty excited about Silverlight and encouraged us, the students, to start learning it and utilising it together with SharePoint.

He said deploying Silverlight on SharePoint will bring great benefits to the user experience aspect of a site. Instead of transferring a web page of several hundred kilo bytes each time, Silverlight can make the interaction much smoother by transferring a smaller chunk of data. And even though AJAX can also achieve similar reductions in network traffic, for a .NET developer, coding in Silverlight will be much easier than JavaScript.

I was already quite interested in learning Silverlight. After hearing him extolling its benefit, I am even more excited to deep dive into this technology.