Over the course of my career, I have developed and deployed to production several business intelligence solutions. These solutions have been consumed using numerous data visualisation tools. One of those data visualisation tools, is Microsoft Excel – which remains a popular data visualisations tool for many of my end users. Like many software products, newer version releases of Excel usually introduce new features. One such feature is the Timeline filter control which was first introduced in Microsoft Office 2013. The Timeline filter control enables end users to visually filter Excel PivotTables using fields defined as dates. The Timeline filter has several benefits compared to the traditional Excel dropdown filter. Figure 1 illustrates one of those benefits whereby the Timeline filter allows end-users to easily apply a filter (i.e. using a scroll bar) again a PivotTable. It also has useful annotations that describes what you have filtered on – in this case, we have chosen sales period between February and March 2011. Figure 1: Sample Timeline Filter This is in stark contrast to the traditional filter shown in line 1 of Figure 2 wherein it only indicates that multiple items have been chosen and the only way to understand on what those filter items are, is by clicking on the dropdown button. Figure 2: Traditional excel filter Although the Timeline feature is a great control, getting it to work and correctly pick out date defined fields can be a frustrating task, particularly if your PivotTable is sourcing date from any of the SQL Server OLAP solutions (i.e. Multidimensional cubes, Tabular Model, and PowerPivot excel workbooks). If you have not properly configured your OLAP solution, then you are likely to run into error message “ We can’t create a Timeline for this report because it doesn’t have a field formatted as Date”, similar to what is shown in Figure 3. Figure 3: Timeline filter error The reason for this error is because the Timeline filter is unable to detect any dimensions containing dates field in your PivotTable.

Resolving Timeline Filter error when using Multidimensional Cubes

Fixing the Timeline error involves several steps. The following is what you will have to do to fix the error when PivotTable is sourcing data from Multidimensional Cubes:

1. Open your Multidimensional solution in SQL Server Data Tools (SSDT)
2. Identify your date dimension, in our example, we are using Adventure Works’ DimDate shown in Figure 4 Figure 4: Multidimensional data source view
3. Open the date dimension and ensure that at the very least it contains two attributes, namely, an integer key attribute and a date attribute. Figure 5 shows the attributes of my date dimension, namely, Date Key as well as Full Date Alternate Key. Figure 5: Structure of my sample date dimension
4. The next step involves converting your date dimension to Time. Setting dimension to time provides the capability of time based-levels of granularity i.e. Year, Quarter, Month etc. Figure 6: Setting dimension to Time
5. The final step involves configuring NameColumn and ValueColumn properties of your key attribute which are usually set to none by default. Because we don’t have many attributes in our date dimension, both NameColumn and ValueColumn properties are set to Full Date Alternate Key. Whatever fields you may have in your dimension, the trick is to always ensure that the ValueColumn property is set to an attribute that is defined as date. Figure 7: Configuring date key attributes

Once you are done making changes into your Multidimensional mode, you can process the date dimension. When the processing of date dimension is complete, go back and refresh the excel workbook and try to add a Timeline filter again. You should get an Insert Timelines dialog box similarly to that in Figure 8, indicating that Excel is finally able to detect your Multidimensional date dimensions. Figure 8: Insert Timeline from a Multidimensional cube

Resolving Timeline Filter error when using Tabular Models

The error displayed in Figure 3 could also be as a result of a PivotTable that is based off a Tabular Model. Resolving the issue when the connection is Tabular Model is very different from what we did for a Multidimensional source. In fact, the fix for a Tabular Model date source is much simpler. All that you have to do is:

1. Open your Tabular model in SSDT
2. Identify and highlight the dimension that you want to set as date, in our example it’s DimDate shown in Figure 9 Figure 9
3. Navigate to the Table menu properties and click Date option
4. Within the Date sub-menu, click Mark as Date Table option – shown in Figure 10. The Mark as Date Table option will be disabled if you are trying to configure it for a table that doesn’t have date fields. Figure 10: Configuring table properties
5. A Mark as Date Table dialog box will come up and you should choose the date columns that you want used as unique identifier for the date dimension. In our example, we are using FullDateAlternateKey column Figure 11: Mark as Date Table dialog box
6. Reprocess the Tabular model and refresh the excel document, thereafter

Later, when you try to insert the Timeline filter again into your excel document, an Insert Timelines dialog box should come up as shown in Figure 12 Figure 12

PowerPivot Date Dimension

Resolving the Timeline filter error when your data is sourced from an Excel PowerPivot workbook is similar to what we did in a Tabular Model solution. All you have to do is:

1. identify the dimension that you want to set as date
2. Navigate to the Design menu tab
3. Within Design menu tab, navigate to Mark as Date Table option, as shown in Figure 13
4. Click the drop down button and choose Mark as Date Table
5. Save your Excel PowerPivot workbook

Figure 13: Configuring PowerPivot workbook Similarly to Tabular Model fix, Mark as Date Table option will be disabled if you are attempting to make that change on a dimension that doesn’t contain fields with date data types.

Conclusion

Although Excel can be an end-users popular data visualisation tool, depending on your OLAP solution design, Excel has the potential of being a BI developer’s worst nightmare. In this article we went through different workarounds to resolving an Excel Timeline filter error when a given PivotTable references data from OLAP solutions such as Multidimensional cubes, Tabular Models as well as Excel PowerPivot.

Downloads

• Multidimensional sample solution
• Tabular Model sample solution
• SQL Server Adventure Works 2014 (Data Warehouse) Database

References

• Create a Date type Dimension
• Create a PivotTable timeline to filter dates
• Configure Attribute Types