Setting Up Drill-Down in Oracle BI from General Ledger to Subledger

You can set up Drill-Down in Oracle BI from General Ledger to Subledger.

To set up drill down in Oracle BI Answers from General Ledger to subledger:

  1. Create your subledger request from 'Financials - AP Transactions' or 'Financials - AR Transactions' catalog as applicable.
  2. In your request, add a filter on the column 'GL Journal ID' under the 'Document Details' subfolder for the 'AP Line Details' or 'AR Line Details' folder,=65 and then set the operator of the filter to 'Is Prompted'.
  3. Build your GL Journal request from the 'Financials - GL Detail Transactions' catalog.
  4. To your request, add the column 'GL Journal ID' under the 'Document Details' folder.
  5. Navigate to the Column Properties of this column, and set the Value Primary Interaction property in the Column Format Interaction tab to 'NavigateAction Links'.
  6. Add a navigation target and set the target location to the sub ledger request you created earlier.

You might add multiple navigation targets if your GL report shows transactions from multiple subledgers and you want to drill from GL to the appropriate Subledger report. For example, if your GL report shows transactions from AP, AR and Revenue, and you have three subledger analyses for each of these, you can add three navigation targets (by selecting the option 'Add Navigation TargetsAction Link') and set the locations to each of these analyses. Subsequently, when you run the GL report and click on the 'GL Journal ID' column value, a popup appears, where you need to click on the appropriate target based on the journal you clicked on. This will not happen automatically. For example, if you click on a journal transaction originating from AP, you need to pick the appropriate subledger report (that is, the AP report in this case) to drill into the AP report and see the details. You can add the Group Account Number attribute from GL Account Dimension to your GL report to easily identify the subledger that the GL transaction belongs to.

Setting Up Drill-Down from GL to COGS

For COGS, the 'GL Journal ID' column is not exposed in any presentation catalogs. It is available in the business model layer of the RPD metadata under the logical tables 'Dim - GL COGS Details'. As a workaround, you can create presentation catalogs to report on detail level transactions for COGS and expose this column under the 'Document Details' folder in the presentation catalog. You use similar steps as described above to setup a drill-down from GL to COGS.

  1. In Oracle BI EE Administration Tool, edit the BI metadata repository (for example, OracleBIAnalyticsApps.rpd).

    The RPD file is located in the \bifoundation\OracleBIServerComponent\coreapplication_obisn\repository folder.

  2. Create an empty presentation catalog (for example, Financials – GL Cost of Goods Sold).

    Set properties by following other presentation catalogs.

  3. Drag 'Dim – GL COGS Details' and 'Fact - Fins - GL Cost of Goods Sold Posted' to the presentation catalog.
  4. Drag other dimensions.
  5. Rename the presentation table 'Dim – GL COGS Details' to Document Details.
  6. Rename the presentation table 'Fact - Fins - GL Cost of Goods Sold Posted' to 'Facts - GL Cost of Goods Sold'. Rename other dimensions if necessary.

You might also follow this same process to create a Presentation Table for Revenue to be able to drill from GL to Revenue level detail transactions.