Author: Kepion Product Team
Kepion allows users in the Advanced Contributor role to perform transactional drill-through in planning and reporting Forms. However, before anyone can perform this drill-through, the definition needs to be defined in the Modeler.
In general, the transactional drill-through architecture works as follows:
Here we will define a transactional drill-through for retrieving members from the Product Dimension based on the context of the currently selected cell on a Form. For instance, if I am a planning user and need to know more about a particular product to properly perform my role, I can perform a drill-through to get the required information.
1. Select the target Application in the Modeler.
2. Go to Application > Drill-through (Transactional).
3. Click Add to create a transactional drill-through.
4. In the Design tab, enter a name for the drill-through.
5. Configure the Drill-through By and the Depends on Dimension(s). These fields helps 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.
Here we select Drill-through By DATA, and Product from the Depends on list to indicate that this particular drill-through will have its results filtered by the Product Dimension.
6. Go to Query 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 and see how some of that syntax would look like.
7. Go to Column to create custom formatting for each column.
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 are embedded in the result set. Embedding URLs gives allows users to reference other hosted documents or applications in the results of a drill-through.
8. Go to the Scope tab and select the Form(s) that will support this drill-through.
9. Once you click Save, the system will let you know if there are any syntax errors; otherwise the system will save the definition to the server.
Use on Form
1. Open an App and go to a Form configured for drill-through.
2. Right-click a cell and select Drill-through (Transactional).
3. Select the drill-through definition that was previously defined (e.g., PRODUCT INFO BY DATA).
4. Click Run.
You can see the results below:
DATA vs CONTEXT
The diagram below illustrates a segment of the Sales P&L report:
- There are four brands (Adair, Aviva, Harper and Zofia) planned for the United States, Jul FY19, and Forecast scenario.
- All of the brands have values on the Product Revenue account. However, there is no Post Invoice planned for Zofia yet.
- When we conduct a transactional drill-through on the All product brand, @FilterScope will be populated with different Product members depending on the type of transactional drill-through.
Transactional drill-through is a powerful feature that is enabled for Advanced Contributors. You can define one or more drill-through definitions and you can also return many different results straight from the relational database. With transactional drill-through, accessing source information becomes easy!