Can’t Create A Timeline in Excel on Multidimensional Cube

Excel 2013 - We can’t create a Timeline for this report because it doesn’t have a field formatted as Date.

After getting excited about the Timeline object in Excel 2013, I tried to add this cool feature of Excel 2013 and got an error – “We can’t create a Timeline for this report because it doesn’t have a field formatted as Date.”

 

After some Bing searches :), I found the solution to be adding the ValueColumn property of the Date Key attribute to be a date value. In this example, my Date key attribute in the Date dimension is an integer like 20140101 not the actual date itself. The NameColumn property is a text description of the date.

 

After adding the actual date (FullDate), the first error went away.

 

The following error was new – We couldn’t create a Timeline because the date field contains values that are not supported as dates in Excel.

 

 

In the initial stages of loading data, we had source dates that were unknown and we replaced those dates with 01/01/1800 (12/31/2099 for End Dates) or the date would come in as 01/01/1000 from the source. These values do not work in the Timeline. Once I verified that the unknown dates were cleared up, I changed the Date source in the Data Source View of the cube from including these date values:

BEFORE

SELECT DateKey, FullDate, DateName, DayOfWeek, DayNameOfWeek, DayOfMonth, DayOfYear, WeekdayWeekend,
    WeekOfYear, MonthName, MonthOfYear, IsLastDayOfMonth, CalendarQuarter, CalendarYear,
    CalendarYearMonth, CalendarYearQtr, 'Q' + CONVERT(varchar(1), CalendarQuarter) AS CalendarQuarterName,
    DateRange, CONVERT(int, REPLACE(CalendarYearMonth, '-', '')) AS MonthKey,
    RTRIM(LTRIM(MonthName)) + ' ' + CONVERT(varchar(4), CalendarYear) AS MonthYear
FROM dbo.DimDate
WHERE (DateKey BETWEEN 20090101 AND 20141231)
  OR (DateKey = - 1)
  OR (DateKey = - 2)

The OR in the WHERE includes a lower (1800-01-01) and upper (2099-12-31) in the Date dimension. These are no longer needed because the data in scrubbed first to exclude or correct the date problems.

AFTER

SELECT DateKey, FullDate, DateName, DayOfWeek, DayNameOfWeek, DayOfMonth, DayOfYear, WeekdayWeekend,
    WeekOfYear, MonthName, MonthOfYear, IsLastDayOfMonth, CalendarQuarter, CalendarYear,
    CalendarYearMonth, CalendarYearQtr, 'Q' + CONVERT(varchar(1), CalendarQuarter) AS CalendarQuarterName,
    DateRange, CONVERT(int, REPLACE(CalendarYearMonth, '-', '')) AS MonthKey,
    RTRIM(LTRIM(MonthName)) + ' ' + CONVERT(varchar(4), CalendarYear) AS MonthYear
FROM dbo.DimDate
WHERE (DateKey BETWEEN 20090101 AND 20141231)

Now, no more error:

 

Nice!!!