Configure Transactional Drill-through
By Kepion Solution - Sep 25, 2014
Kepion allows users to perform transactional drill-through from forms. Any user belonging to the Advanced Contributor role would be able to perform a transactional drill-through from planning and reporting forms. However, before anyone can perform this drill-through, the Modeler would need to first define its definition.
How does transactional drill-through work?
In general, the transactional drill-through architecture is as follows:
Configure transactional drill-through
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 I would like to know more information about a particular product that I am planning for, I can perform a drill-through and get the particular product’s information.
Let’s see how this can be done. Go to Modeler and select Application -> Drill-through (Transactional). Click Add to create a transactional drill-through.
On the DESIGN tab, configure the type of transactional drill-through and the Depends on dimension(s). This helps define the system parameter @FilterScope when the drill-through is triggered.
There are two types of transactional drill-through. Drill-through by DATA is selected by default.
- 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.
Click on QUERY tab to define what information will be returned from the drill-through.
- Enter your SQL statement on 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.
- You can also take a look on the EXAMPLE tab and see how some of that syntax would look like.
On the FORMAT tab, you can add custom formatting for the results by column. For example, you can give a different font color to the Brand column.
Next, on the SCOPE tab, select the form(s) that will support this drill-through.
Once you click OK, the system will let you know if there are any syntax errors and if not, will save the definition to the server.
Use transactional drill-through on a form
Once configured, you can use the transactional drill-through on a form. Simply right click on a cell, and select Drill-through (Transactional).
It will bring up the Drill-through window. Select the drill-through definition PRODUCT INFO BY DATA that was previously defined and click the Drill-through button.
You can see the results below:
Transactional drill-through by DATA vs. CONTEXT
The diagram below illustrates a segment of the Sales Profit & Loss report.
- There are four products brands (Adair, Aviva, Harper and Zofia) planned for United States, May FY17, 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!
Build Enterprise BI & CPM solutions within minutes with Kepion Modeler.