Multiselecting Dates in Excel 2013 on top of SSAS/BISM Multidimensional

It is not uncommon to compare BI clients on a very detailed feature level in discussions with customers but this blog post is about a question in the Analysis services forum that I posted an answer to. I have written a longer blog post on the SolidQ blog about the most important new features in Excel 2013 for multidimensional analysis and this post add a feature that was not on my mind while I wrote this:

http://blogs.solidq.com/Business-Intelligence-SQL-Server-pa-svenska/Post.aspx?ID=15&title=Excel+2013+and+the+new+Multidimensional+Improvements

You can work with sets of dates in Excel 2013 if you use the GUI approach. First you can use the old Pivot Table report filter approach like below where you have a filter above the Pivot Table.

Add Dates as Report Filter

If you would like to use a date interval you activate “Select Multiple Items” in the left corner and then you are left to click-click and click for all the relevamt dates.

Report Filter Select Multiple Items

This might involv a lot of clicking especially if you would like intervals of 20-30 dates.

With the Excel slicers you have a much more efficient way of marking ranges of dates. I assume that you know how to add slicers for a natural hierarchy in Excel. Below I have selected the first 5 days in July 2005 by clicking the first date in the interval and then using shift ´click on the last date in the interval.

Use shift in slicers to mark date intervals

In Excel 2013 there is a third approach with Time Lines. That option is placed next to Insert Slicer in the Ribbon under PivotTable Tools.

Insert TimeLine Ribbon

You are presented to the option to select the date hierarchy.

DateHierarchyChoicesTimeLine

I have swedish settings and År means Year. You can select levels in the upper right corner to select years and then the months of interest for quick navigation. I am on my way to the first five days in July in the picture below.

TimeLineSelection2005

When I have navigated to the date level of July 2005 I simply drag the cursor over the dates that I am interested in and the Internet Order Quantity sums up in the same way as with the slicer approach.

DragTheCursorOnTheTimelIne

It is always important to know about these less apparatent features in clients that will make the analytic daily work easier for our customers.

About these ads

4 thoughts on “Multiselecting Dates in Excel 2013 on top of SSAS/BISM Multidimensional

  1. KenA August 21, 2013 / 17:06

    Hi Thomas – Sorry this topic is a little old but I’m trying to use the 2013 timeline slicer in Excel 2013. My workbook is connected to a cube(SSAS 2008 R2) and my date dimension is relatively rich. When trying to add the timeline I get “We can’t create a Timeline for this report because it doesn’t have a field formatted as Date.” This has to be an issue with my dimension set up in SSAS. I do have an attribute named Date that is just that – a date such as 1/1/2013; 1/2/2013; et al. I’ve got to be missing something though. In your screenshots above I see you have a calendar hierarchy. Is there something about the setting in your lowest date node or possibly in the actual Date Key that triggers Excel 2013 to allow you to include a Timeline. Again I apologize for these comments being months after your post but I’m just now hitting the snag. Any help you can provide would be appreciated. -Ken

    • thomasivarssonmalmo August 21, 2013 / 17:16

      What you can do is to check that you have a value column activated for your different date attributes. You can also check if you have the type property of date fields like year,month, date applied to the attribute. Check attribute properties in the dimension editor.

      • KenA August 22, 2013 / 21:52

        Let me try that. Is it necessary that each attribute involved in the cube have a value in order for the timeline slicer to work? I can verify this but thought you might know. Thanks for the reply.

  2. KenA August 27, 2013 / 17:14

    Hi Thomas. I went back and read a reply for using the timeline in Excel and found that the key(DateKey in my case) has to have a value that’s a date. Even if it’s not visible to the user the value of the key must be set. As soon as I set the value to Date the timeline started working in Excel. Here’s the thread I’m referencing:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2fed1bf6-6c41-403a-92b1-625be81dd89c/using-excel-2013-timeline-filter-with-ssas-2008-r2-cube

    Thanks for your help!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s