Using ERP Integrator Drill Through from Financial Reporting
Purpose
In this tutorial, you learn how to access the drill-through functionality of Oracle® Hyperion Financial Data Quality Management ERP Integration Adapter for Oracle Applications, Fusion Edition (ERP Integrator) from Oracle® Hyperion Financial Reporting, Fusion Edition to access underlying Enterprise Resource Planning (ERP) transaction data. With the ability to drill through from Enterprise Performance Management (EPM) applications such as Oracle® Hyperion Financial Management, Fusion Edition and Oracle® Hyperion Planning, Fusion Edition to the related transaction systems, you gain greater visibility into business processes and greater understanding of the consolidated data.
Time to Complete
Approximately 45 minutes
Overview
For most organizations, the basis for global financial consolidation, reporting and analysis, planning, budgeting, and forecasting is derived from a company's existing operational information. This information can be stored in many different sources, often ERP systems, such as Oracle E-Business Suite and Oracle PeopleSoft Enterprise Financial Management.
When you load data from ERP Integrator or Oracle® Hyperion Financial Data Quality Management, Fusion Edition (FDM) to Financial Management or Planning target applications, a drill region is created to enable drill through to ERP source systems. The drill region includes dimensions for Financial Management or Planning, a uniform resource locator (URL) to get back to either ERP Integrator or FDM, and a region name. The drill region includes the Account, Scenario, Year, and Period dimensions for Financial Management, and the Entity, Account, Scenario, Year, and Period dimensions for Planning. The drill region URL enables you to drill through from Financial Management, Planning, Financial Reporting, and Oracle® Hyperion Smart View for Office, Fusion Edition (Smart View) to the proper landing page (ERP Integrator or FDM). From the landing page, you can drill through to the general ledger (GL) source data in the ERP system.
In Oracle Enterprise Performance Management Workspace, Fusion Edition (Workspace), you can drill through to GL source data from Financial Management, Planning, and Financial Reporting. For Financial Reporting, you drill through defined related-content links in the report to the ERP Integrator or FDM landing page, and then you drill through to the GL source data.
Note: This release supports integration with Oracle E-Business Suite 11 and 12, and Oracle PeopleSoft Enterprise Financial Management 9.0 and 9.1. In this tutorial, we refer to the general ledgers for these products and versions as GLs.
The following table lists some common terminology:
| Term | Definition |
| Drill down | Navigation through the query result set in FDM using the dimensional hierarchy; drilling down moves the user perspective from aggregated data to detail data |
| Drill through | Navigation from a value in an application to the corresponding data in a source system |
| Drill region | Named region of data intersections in either Planning or Financial Managment which contains a name, a URL for the drill through, and intersection information |
| Landing page | Page that displays the data and metadata from the source system |
| Source system | Transaction system in which the source data resides |
| Target application | Application to which you can load data with FDM, and load data and metadata with ERP Integrator |
Scenario
You view data in Planning that was loaded with ERP Integrator, and you drill through from the Planning data form to the ERP Integrator landing page. You want users to view the same data in a Financial Reporting report, and then drill through from the report to the GL source data. You create a report in Reporting Studio, and you enable it for drill through. In Workspace, you view the report and verify the drill-through functionality by drilling through to the ERP Integrator landing page, and then to the GL source data in Oracle E-Business Suite.
You also view data in Financial Management that was loaded with FDM, and you drill through from the Financial Management data grid to the FDM landing page. You create a report in Reporting Studio, and you enable it for drill through. In Workspace, you view the report and verify that users can drill through to the FDM landing page, and then drill down, through ERP Integrator, to the GL source data in PeopleSoft Enterprise Financial Management.
Prerequisites
Before starting this tutorial, you should have:
- Access to an EPM application, such as Planning or Financial Management for release 11.1.2.1
- Access to a transactional data source, such as Oracle E-Business Suite or PeopleSoft Enterprise Financial Management
- Access to Reporting Studio 11.1.2.1
- Experience in creating basic reports in Reporting Studio
- Experience in viewing reports in Workspace
- A user account in your authentication directory
Note: In order to use ERP Integrator drill-through functionality, you must register and deploy your EPM applications with the mapped metadata. You must also configure the applications with data rules for extraction from the GL source system to ERP Integrator or FDM.
Drilling Through with ERP Integrator
In this example, you view data in Planning that was loaded with ERP Integrator, and you drill through from Planning to the ERP Integrator landing page. You create a Financial Reporting report to display some of the same data, and you enable the report for drill through. In the last step, you view the report in Workspace, and drill through to the ERP Integrator landing page, and then to the source data in Oracle E-Business Suite.
Viewing Data Source Details from Planning
A drill region is a named region of data intersections in either Planning or Financial Management applications that can be audited with the drill-through functionality. The data in the drill region was loaded to the Planning or Financial Management application with ERP Integrator or FDM. In Workspace, you can view the drill region in Planning data forms or Financial Management data grids.
In this example, you view data that was loaded from ERP Integrator to a Planning application.
To view the drill regions in a Planning data form, perform the following steps:
. |
In Workspace, open a data form in a Planning application. The data form should include data that was loaded from ERP Integrator. In the data form, blue indicators in the cells of the drill region indicate the drillable data intersections. In this example, the drill region contains Actual data that was loaded by ERP Integrator.
|
|---|---|
. |
Position the cursor over a data cell that contains the blue indicator to display the message "This cell is drillable." In this example, the Actual data (15,523) for the 7410:Utilities account is drillable.
|
. |
Click in the drillable cell, and then click one more time to display the Drill Through icon. In this example, the Drill Through icon is displayed for the data (15,523.2) in the 7410:Utilities account.
|
. |
Click the Drill Through icon to display the ERP Integrator landing page. In this example, since data was loaded from ERP Integrator to the Planning application, the drill through from Planning navigates you to the ERP Integrator landing page, which is displayed as a Drill-Through tab in Workspace. The amount, 15,523.20, is displayed on the page. In the Period Activity column, you can drill through from the data value link to the GL source system.
Note: Later in the tutorial, you drill through from the landing page to the ERP source system.
|
. |
Close the Drill-Through tab, the data form, and the Planning application.
|
Enabling Drill Through in Reports
In Reporting Studio, you create a report that connects to a Planning or Financial Management application that contains data that was loaded with ERP Integrator or FDM. The report must include the data intersections for drill regions in Planning or Financial Management. You enable drill through to transaction data from the report. When you view the report in Workspace, the drillable data cells are displayed as related content links that drill through to the GL source data.
To create a report and enable it for drill through, perform the following steps:
. |
In Reporting Studio, create a report, and insert a grid. Note: The report must connect to a database connection for an application that has ERP Integrator or FDM drill through enabled. In this example, you select the VisPlan1:Planning database connection, add the Department dimension to the page area, the Account dimension to the rows, and the Scenario dimension to the columns.
|
|---|---|
. |
Select members for the rows, columns, and page areas of the grid. The grid must include data intersections for drill regions in a Planning or Financial Management application. In this example, you select the 100 department and its descendants for the page area. For the Scenario dimension in the columns, you select Actual and Plan, and you define a variance formula. For the Account dimension in the rows, you select several expense accounts.
|
. |
Preview the report. In this example, the data value, 15,523, for the 7410 Utilities account for the Actual scenario is displayed in the report.
|
. |
Close the report preview, select the grid, and on the Grid Properties General sheet, select Drill Through.
Note: You can enable the drill through functionality only when the dimensions in the grid contain valid data intersections for drill through.
|
. |
Save and close the report. In this example, the report is saved in the Demos folder with the name Act vs Plan Variances.
|
Viewing Data Source Details from Financial Reporting Reports
When you view a Financial Reporting report with data from EPM applications, you may want to understand the source of a data value. In this case, you can drill through from the data value in the report to the ERP Integrator or FDM landing page and to journal entries in the GL.
To drill through to source data from a Financial Reporting report, perform the following steps:
. |
Log on to Workspace, select Explore, and select the folder that contains the Financial Reporting report that you enabled for drill through. In this example, you select the Demos folder that includes the Act vs Plan Variances report.
|
|---|---|
. |
Open the Financial Reporting report. In this example, you open the Act vs Plan Variances report. The data in the Actual column contains related content links to drill through to the source data. Since the data for this example was loaded to Planning with ERP Integrator, the links drill through to the ERP Integrator landing page.
|
. |
Click a link to drill through to the ERP Integrator landing page. In this example, you click on the 15,523 value for the 7410 Utilities account and the Actual scenario. The ERP Integrator landing page is displayed, and the data value is displayed in the Amount and the Period Activity columns.
|
. |
On the ERP Integrator landing page, click the link in the Period Activity column of the Drill-Through Details area to view the GL transactional details in Oracle E-Business Suite or PeopleSoft. You may be prompted to enter the authorized user name and password for the source system. In this example, you click the 15,523.2 link in the Period Activity column to launch Oracle E-Business Suite. The Account Balances page is displayed with the 15,523.20 value in the PTD column.
|
. |
In the GL, click a value link to view the journal details of debits and credits. In this example, you click the 15,523.20 value in the PTD column to display the following four journal lines.
|
. |
Close all windows, and exit Workspace.
|
Drilling Through with FDM
In this example, you view data in Financial Management that was loaded with FDM, and you drill through from Financial Management to the FDM landing page. You create a Financial Reporting report to display some of the same data, and you enable the report for drill through. In the last step, you view the report in Workspace, and drill through to the FDM landing page, and then to the source data in Oracle PeopleSoft Enterprise Financial Management.
Viewing Data Source Details from Financial Management
A drill region is a named region of data intersections in either Planning or Financial Management applications that can be audited with the drill-through functionality. The data in the drill region was loaded to the Planning or Financial Management applications with ERP Integrator or FDM. In Workspace, you can view the drill region in Planning data forms or Financial Management data grids.
In this example, you view data that was loaded from FDM to a Financial Management application.
To view the drill regions in a Financial Management data grid, perform the following steps:
. |
In Workspace, open a data grid in a Financial Management application. The data grid should include data that was loaded from FDM. In the data grid, blue indicators in the cells of the drill region indicate the drillable data intersections. In this example, the drill region contains Actual data that was loaded with FDM.
|
|---|---|
. |
Right-click a drillable cell, and select Drill Through to FDM to drill through to the landing page. In this example, you drill through from the Actual data value (30,389) for the 681400 - Dep Expense - Mach and Equip account to FDM.
Note: If data was loaded with ERP Integrator to the Financial Management application, you select the Drill Through to ERP Integrator command.
|
. |
The landing page is displayed. In this example, since data was loaded from FDM to the Financial Management application, you drill through from Financial Management to the FDM landing page, which is displayed in a new browser window. The amount, 30,388.72, is displayed on the page. You can drill down from the amount link to the GL source system.
Note: Later in the tutorial, you drill through from the landing page to the ERP source system.
|
. |
Close the FDM landing page, and exit Workspace.
|
Enabling Drill Through in Reports
In Reporting Studio, you create a report that connects to a Planning or Financial Management application that contains data that was loaded with ERP Integrator or FDM. The report must include the data intersections for drill regions in Planning or Financial Management. You enable drill through to transaction data from the report. When you view the report in Workspace, the drillable data cells are displayed as related content links that drill through to the ERP source data.
To create a report and enable it for drill through, perform the following steps:
. |
In Reporting Studio, create a report, and insert a grid. Note: The report must connect to a database connection for an application that has ERP Integrator or FDM drill through enabled. In this example, you select the PSCONSOL database connection for a Financial Management application, add the Account dimension to the rows, and the Scenario dimension to the columns.
|
|---|---|
. |
Select members for the rows and columns of the grid. The grid must include data intersections for drill regions in a Planning or Financial Management application. In this example, you select several accounts for the rows. For the Scenario dimension in the columns, you select Actual and Plan, and you define a variance formula.
|
. |
Preview the report. In this example, the data value, 30,389, for the 681400 account and the Actual scenario is displayed in the report.
|
. |
Close the report preview, select the grid, and on the Grid Properties General sheet, select Drill Through.
Note: You can enable the drill through functionality only when the dimensions in the grid contain valid data intersections for drill through.
|
. |
Save and close the report. In this exaple, the report is saved in the Demos folder with the name Expense Analysis.
|
Viewing Data Source Details from Reports
When you view a Financial Reporting report with data from EPM applications, you may want to understand the source of a data value. In this case, you can drill through from the data value in the report to the ERP Integrator or FDM landing page and to journal entries in the GL.
To drill through to source data from a Financial Reporting report, perform the following steps:
. |
Log on to Workspace, select Explore, and select the folder that contains the Financial Reporting report that you enabled for drill through. In this example, you select the Demos folder that includes the Expense Analysis report.
|
|---|---|
. |
Open the Financial Reporting report. In this example, you open the Expense Analysis report. The data in the Actual column contains related content links to drill through to the source data. Since the data for this example was loaded to Financial Management with FDM, the links drill through to the FDM landing page..
|
. |
Click a data value link to drill through to source data. In this example, you click on the 30,389 value for the 681400 account and the Actual scenario. When additional related content is associated with the data cell in the grid (for example, a link to another Financial Reporting report), the Related Content dialog box is displayed, and you are prompted to select from the available related content.
Note: If data was loaded with ERP Integrator to the Financial Management application, the Related Content dialog box displays Drill Through to ERP Integrator.
|
. |
In the Related Content dialog box, click Drill Through to FDM. In this example, you drill through to the FDM landing page which is displayed in a new browser window. The data value (30,388.72) is displayed in the Amount column.
|
. |
On the Drill Through from Target System page, click the data value link in the Amount column (30,388.72) to display the data details. The FDM Drill Down page displays the detail.
|
. |
On the Drill Down page, click a data value link in the Amount column to display the shortcut menu.
|
. |
Select Open Source System to view the GL transaction data in Oracle E-Business Suite or Oracle PeopleSoft Enterprise Financial Management. You may be prompted to enter the authorized user name and password for the source system. In this example, you view the transaction data in Oracle PeopleSoft Enterprise Financial Management. The Period Balance column displays the source data.
|
. |
Close all windows, and exit Workspace.
|
Summary
ERP Integrator and FDM enable you to map and load GL data directly into your EPM applications. With the drill-through functionality, you can audit your data by drilling through to the journal entry details and have confidence in the consolidation and integration of your data.
In this tutorial, you should have learned how to:
- View data source details from Planning and Financial Management
- Create a Financial Reporting report with drill-through functionality enabled
- Drill through from Financial Reporting reports to view ERP transaction data in source systems
Resources
- To learn more about ERP Integrator, refer to the ERP Integration Adapter for Oracle Applications Administrator's Guide.
- To learn more about enabling the drill-through functionality in Financial Reporting, refer to the Oracle® Hyperion Financial Reporting Studio, Fusion Edition Release 11.1.2.1 User’s Guide
- To learn more about Financial Reporting, refer to additional OBEs in the OLL Web site
 |
About
Oracle | Oracle and Sun |  | Subscribe | Careers | Contact
Us | Site Maps | Legal
Notices | Terms of Use | Your Privacy Rights |