Introduction
Transactional drill-through pulls all or a specified set of values contributing to a displayed value on a form. This article explains how to configure and use transactional drill-throughs.
Before we Start
To explain this feature, we'll use a Net Income report for FY16 to FY20:
As the form is set up on the year level, users cannot see the costs incurred for all the months of a given FY. Transactional drill-through lets you get the information you need without the extended search.
Configuration
Let's define a transactional drill-through to retrieve the contributing values for the various operating expense items.
1. Select the target application in the Modeler.
2. Go to Application > Drill-through (Transactional).
3. Select Add.
The Drill-through configuration window should open automatically.
4. Enter a descriptive and distinct name for the drill-through.
Tip: If you have created many transactional drill-throughs but given them vague names, you will have difficulties selecting the appropriate one for your purposes.
Design tab
In the Design tab, you select the drill-through type (DATA or CONTEXT) and the Dimensions involved. These fields help define the system parameter @FilterScope when the drill-through is triggered.
There are two types of transactional drill-through:
- Drill-through By DATA: Only the Dimension Members with valid data contributing to the selected cell will be included in the @FilterScope.
- Drill-through By CONTEXT: All the valid Dimension Members that apply to the context of the selected cell will be included in the @FilterScope.
For this example, we will go with DATA. We've selected Account, Scenario, and Time because our data exists in the intersection between those Dimensions.
Query tab
The Query tab allows you to define what information will be returned from the drill-through:
- Enter your SQL statement in the Definition tab. Use @FilterScope to scope down the results.
- Set the maximum number of rows that could be returned from the drill-through in Max Results.
- Refer to the Example tab for sample queries.
Column tab
The Column tab enables you to create custom formatting for each column of your drill-through results table.
For example, you can change the font color for the Brand column. Additionally, use the section below the formatting preview window to configure URLs, which will be embedded in the result set. Embedded URLs allow users to reference other hosted documents or Applications in the results of a drill-through.
Scope tab
The Scope tab allows you to select the Forms that support this drill-through. We will be drilling down into the operating expense items in the Net Income report, so we'll select that Form.
Click Save when you have fully configured your drill-through. If you need to edit any of your drill-throughs, return to the Drill-through (Transactional) node and select the corresponding of your target drill-through.
Use Transactional Drill-Through
Let's test the transactional drill-through we just configured.
1. Go to the App from the Apps module and locate your Form.
2. Right-click the target cell. We have selected Travel Expense for FY20.
3. Select your transactional drill-through and click Run.
After a few seconds, you should see the results of your drill-through: