Kepion enables Advanced Contributors to perform transactional drill-through in Forms. In short, transactional drill-through pulls all or a specified set of values contributing to a displayed value.
Tip: To learn how to add Advanced Contributors, refer to this tutorial video.
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.
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. Click 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.
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.
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.
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.
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.
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: