Multiselecting Dates in Excel 2013 on top of SSAS/BISM Multidimensional
Posted by thomasivarssonmalmo on March 3, 2013
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:
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.
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.
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.
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.
You are presented to the option to select the date hierarchy.
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.
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.
It is always important to know about these less apparatent features in clients that will make the analytic daily work easier for our customers.