When in a summary module or a dashboard, Drill to Transaction enables you to retrieve detailed transaction data from the summary module’s source module.
The feature is useful if you have data in a complex multi-dimensional module and have created a summary module showing a limited number of dimensions. Drill to transaction enables you to analyze the values in the summary module by drilling back to the component transactions in the original module.
Drill to Transaction and Drill Down provide quite different functions.
Drill to Transaction:
- shows the component values of a SUM calculation — you can only drill back to the source module.
- supports simple SUM calculations – it does not support Select or Lookup functions.
- works best from a simple two-dimensional source module.
Drill Down, on the other hand:
- enables you to drill down through many levels, often from different modules, to show the formulae used to calculate values and the data cells referenced in those calculations.
- is an iterative process — you can continue to Drill Down on values until the lowest level of data is reached.
- does not rely on SUM calculations.
How Drill to Transaction Works
From a single cell you can drill to a transaction whose value is calculated by a formula containing one or more SUM functions. Line item subsets can be referenced.
When you select Drill to Transaction, the source module is filtered by the dimensions chosen in the summary module. Only list formatted items can be retrieved.
For a user to be able to Drill to Transaction, they must have Read access to the transaction module (which means that if they de-select the Drill to Transaction filter, they will see all the data in the transaction module).
Drill to a transaction
- Select a single cell in a summary module or dashboard.
- right-click on the cell then and click Drill to Transaction from the context menu;
- select Drill to Transaction from the Data menu; or
- press Alt+F8.
A new tab is displayed, showing a filtered view of the source module referenced by the formula.
The filter displays only those dimensions referenced in the SUM formula.
When creating formulas to support Drill to Transaction, the source line item and the SUM mappings must belong to the same module.
Valid formulas include:
- Sales transaction.Amount [SUM: Sales transaction.Org]
- Sales transaction.Amount [SUM: Sales transaction.Product, SUM: Sales transaction.Org]
- Sales transaction.Amount [SUM: Sales transaction.Org] / 1000
- Sales transaction.Amount [SUM: Sales transaction.Org, SUM: Sales transaction.Time period]
The following examples show formulas that are invalid and explain why.
These formulas both refer to more than one line item:
Sales transaction.Amount [SUM: Sales transaction.Org] + COS transaction.Amount [SUM: COS transaction.Org] =>
Sales transaction.Amount [SUM: Sales transaction.Org] + Margin =>
This formula does not refer to a SUM function:
- Sales transaction.Amount [LOOKUP: Sales transaction.Org] + Margin =>
Constraints apply when Drill to Transaction is selected:
- If the default view has a filter applied, the filter created for the Drill To Transaction operation is used.
- Any pivot, sort, or conditional formatting is preserved.
- Transactions and line items must be on rows and columns.
- If transactions or line items are on pages, or are nested, the pivot will be reset.
- Hidden transactions are displayed but hidden line items remain hidden.