Excel is a powerful tool that is used by almost everyone is the business world. Because of its numerous capabilities, Excel has many functions that can often be confusing to users. In this new blog series Our resident expert, Steve Hughes, will cover a wide variety of Excel topics ranging from easy to complex. Steve will provide you tips and tricks he has discovered in his journey through Excel.
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...
Have questions about Excel? Submit your questions in the section below and Steve will be more than happy to answer them!
