In this article we will examine how to design a Drillthrough Action using SQL Server Analysis Services 2012.

What is a Drillthrough Action?

A Drillthrough Action provides a way to return detailed transactions based on a aggregated value, and allows for you to restrict the total number of rows returned, which helps improve query performance. For example, let's say you run a summary report for total revenues by territory. With Drillthrough enabled, you could click on a revenue data value under the desired territory column to get details of how the aggregated value is derived. The details could include: Sales Person, State, City, Sales Amount, Tax, etc.

1) Download and open the attached Analysis Services solution provided at the end of this turorial.

2) Under the Cubes folder, double click on the Analysis Services Tutorial to open the Cube Designer.

3) Click on the Actions tab.

4) Right click from within the Action Organizer pane and select New Drillthrough Action.

5) Change the Name field to "Reseller Sales - Southwest" (without quotes).

6) Under the Action Target section, select "Reseller Sales" for the Measure Group Members value.

Note: The Action Target defines the scope of the Action. In this case, we're defining any measure(s) under the Reseller Sales measure group are included in the scope. That means, you can right click any of these data values in a Data Viewer (e.g., SSAS Broswer or Excel) to render the details view.

6) Scroll down the PivotTable Fields and expand Sales Territory >> More Fields... and check Sales Territory Region to add it in the Rows area.

7) Under the Reseller Sales-Sales Amount column, right click the value for the Northwest row and select Additional Actions. You'll notice (No Action Defined). This is because of our previously set Condition of Southwest.

From the Cube Designer, click the Browser tab. Once the Browser view renders, there should be a toolbar that lists the Excel button (icon), along with other useful buttons. Click the Excel button and it will execute Excel.

In "Step 2 - Testing your Drillthrough Action", I've included a more descriptive illustration. I hope this helps!

Google Plus One

Search

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.