Custom OLAP Drill Through Actions

Overview

Drill through actions are very useful when the end users want to see the details behind the numbers in a pivot table. The default drill through action that can be used in Excel shows only measure fields and dimension key fields. In a drill through action the user can select exactly what information that should be displayed and in which order.

Following is an example from Excel. Notice that only the Surrogate Key is displayed for the Sales Details:

Following is an example from Excel with the drill through action enabled:

In the second example much more detail can be defined regarding specifically what the users will see when they drill through to the underlying data.

Create Drill Through Action

1. Right-click the cube desired and select Advanced > Add Drill Through Action. The Drill Through Action window is displayed.

   

   

2. Provide the following information:

   • Name: The name of the action.

   • Measure Group Members: If the drill through action should be active on measures from all measure groups, then select <All> - or choose the specific measure group for which you want the action to be functioning.

   • Condition: An MDX expression that returns True or False. The drill through action will only be active where the condition is True. Example: IIF(<Statement>,True,False)

   • Drill Through Columns: The columns that will be shown when the drill through action is used. The fields will appear in the same order as shown on the right hand side. You can drag items from the left side to the right side to include the information in the action. To delete information from the action, simple press the Delete key when the information is selected or use the right click menu.

     Please note that there are some limitations in drill through actions, these are: You can only select measures from one measure group and you can only select dimensions that are related to the measure group that you are showing measures for.

   • Is Default: Select True to include this drill through action as a default drill through action, otherwise, select False. If more than one drill through actions is set as default, the Microsoft SQL Server Analysis Services instance evaluates all default drill through actions and runs the first default drill through action that returns a non-empty set.

   • Maximum Rows: The maximum number of rows to be returned by the drill through action. Setting this option to zero or an empty value indicates that the drill through action returns all rows retrieved by the action to the client application.

   • Invocation: Select the setting that indicates when the action should be carried out. This option only provides a recommendation to a client application as to when to execute an action, and does not directly control the invocation of the action.

   • The following table describes the available settings.

     Setting	Description
     Batch	The action should run as part of a batch operation or an Integration Services task
     Interactive	The action runs when the user invokes the action
     On Open	The action runs when the cube is first opened

   • Application: Type the name of the application that can execute the drill through action. You can also use this option to identify which client application most commonly uses this action, as well as to display appropriate icons next to the action in a pop-up menu. This option only provides a recommendation to a client application as to what client application should execute an action, and does not directly control access to the action. Client applications should hide any actions that are associated with other client applications.

   • Description: An optional description of the action.

   • Caption: The caption to be displayed for the action in the client application if Caption Is MDX is set to False. Type the Multidimensional Expressions (MDX) expression that returns a string for the caption if Caption Is MDX is set to True.

   • Caption Is MDX: Select False to indicate that Caption contains a literal string representing a caption to be displayed for the action in the client application. Select True to indicate that Caption contains an MDX expression that returns a string representing a caption to be displayed for the action in the client application. The MDX expression must be resolved before the action is returned to the client application.