As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013. Much of the content will be a result of my daily interactions with business users and other BI devs. In order to not forget what I learn or discover, I write it down … here. I hope you too will discover something new you can use. Enjoy!
So, you have connected Excel to your SSAS cube. You really wish you could cross drill easily in the product. If you have used PerformancePoint Services you know the process. Right click on the bar or cell and then choose the dimension to drill to using the information you clicked on as a starting point. You can now do this in Excel 2013 using Quick Explore. Here’s how to do it.
1. Click on a cell and hover over it.
2. Click the Quick Explore button a.k.a. magnifying glass and lightning bolt. That will pop up the following dialog box.
In this box, you can see that the cell has focus on “Boston” based on context and is drilling into the Geography hierarchy of the Client dimension based on the selected filters and slice of data we are focused on.
3. Click the Drill To target. Excel will create a new view of the Pivot Table with the Drill to on the row as shown here with the filter still in place.
4. Going back… One of those frustrating things with this is how do I go back to my original view. Ironically, you use Undo. This will reset the view back to the previous. So, if you choose to drill into this data again and again, you have to Undo that many operations to get back to the starting point. Of course, can click the arrow by the arrow to see your “bread crumb” and pick where you want to go back to as well.
Until the next tip…