Using ERP Integration Drill Through for Financial Reporting

Purpose

In this tutorial, you learn how to use the ERP Integrator drill through functionality in Oracle Hyperion Financial Reporting, Fusion Edition (Financial Reporting) to access underlying Enterprise Resource Planning (ERP) transaction data. With the ability to drill down from Enterprise Performance Management (EPM) applications such as Oracle Hyperion Financial Management, Fusion Edition (Financial Management) and Oracle Hyperion Planning, Fusion Edition (Planning) 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.

Topics

This tutorial covers the following topics:

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, PeopleSoft Enterprise applications, and JD Edwards Enterprise One.

In Oracle Enterprise Performance Management Workspace, Fusion Edition (Workspace), you can drill down to ERP transaction data from Financial Reporting through defined URL hyperlinks in a report.
The URLs are defined in Enterprise Performance Management (EPM) applications such as Financial Management and Planning. For Financial Management, URLs are defined by a set of data slices known as drill regions, using the following dimensions: Scenario, Year, Period, Entity, and Account. For Planning, using an Enterprise Performance Management Architect (EPMA) managed Essbase cube, the data slices included in the URL definition are defined as Essbase filters using the following dimensions: Scenario, Version, Year, Period, Entity, and Account.
 

This ERP integration with EPM applications enables you to load metadata and data from select ERP systems to the EPM applications. You also have the benefit of integration with Oracle Hyperion Financial Data Quality Management (FDM) drill back support, support for Oracle general ledgers, and drill back from Planning, Financial Management, Financial Reporting, and Oracle Hyperion Smart View for Office, Fusion Edition (Smart View) through ERP Integration Suite or FDM to your source General Ledgers.

Note: This release supports integration with Oracle E-Business Suite General Ledger 11.5.10, 12.06, and 12.11, and PeopleSoft General Ledger 9.0. Throughout this session, we refer to the general ledgers for these products and versions as General Ledgers.

Terminology:

Scenario

In this tutorial, you access Workspace to view intersection data in Planning and Financial Management Web forms. In Oracle Hyperion Financial Reporting Studio, Fusion Edition (Reporting Studio), you create reports that display Financial Management and Planning application data. You then enable and validate drill through links to transactional data in the source General Ledger.
Next, you open a Financial Reporting report in Workspace and use the drill through link to view the source data on the landing page. Then, you use the drill back feature to navigate from the landing page to the source system. Finally, you open a Financial Reporting report, and use the drill through link to FDM source data.

Software Requirements

The following is a list of software requirements:

• Web browsers, Internet Explorer 7, or Firefox 3 or 3.1
• Windows XP, Vista, or 2003 Server
• EPMA 11.1.1.3
   

Prerequisites

Before starting this tutorial, you should have:

• Access to an EPM application, such as Planning or Financial Management
• Access to a transactional data source, such as Oracle E-Business Suite or PeopleSoft
• Access to Reporting Studio 11.1.1.3
• Experience in creating basic reports in Reporting Studio
• A user account in your authentication directory
• View access to Financial Reporting reports in Workspace

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 General Ledger source application to ERP Integrator or FDM.

Viewing Data Intersection Details

The drillable region is a named region of intersections in either Planning or Financial Management that can be audited through the drill-back function. It contains a name, a URL to drill back to, and intersection information. In Workspace, you can view the Planning or Financial Management Web forms that show the data intersections as they appear in the report. To view intersection details, follow these steps:

1. In Workspace, open a Planning Web form:

a. Click Navigate > Applications > Planning > Application Name.

b. Expand Forms.

c. Select a Web form.

In the web form, indicators show that this intersection data is drillable (a drill region).

Your Planning Web form may look like this:



2. Click on the blue indicator in the intersection data cell.

The drill through icon is displayed in the form.



3. Click the drill through icon.
4. The ERP Integrator or FDM landing page is displayed, depending on the mapped and configured source system.

The FDM landing page is displayed in a new browser window.



5. Click Close.
6. Alternatively, the ERP Integrator landing page is displayed as a new Workspace tab.



Click File > Close Current.

Note: You can have multiple drill regions available from the intersection data cell.

7. In Workspace, open a Financial Management Web form:

a. Click Navigate > Applications > Consolidation > Application Name.

b. Expand Documents.

c. Select Data Grids.

d. Select a data grid.

The Financial Management intersection data is displayed in the data grid.

Your Financial Management data grid may look like this:



The drill region is displayed in the data intersections. An indicator in the intersection data cell informs you that the drill through functionality is available.

8. Right-click the cell, select Drill Through to ERP Integrator to view additional details.



9. The ERP Integrator landing page is displayed.



10. Click File > Close Current.
11. Close Workspace.

Select File > Exit.

Creating the Financial Reporting Report

In Reporting Studio, you have the option to enable drill through to transaction data when you create or modify a report. In order to create a report with drill through functionality, you need to ensure that the dimensions have valid intersections where drill through is enabled. See Viewing Data Intersection Details for more information. To create or modify a report with the ERP Integrator drill through functionality, perform the following steps:

Adding a Grid

1. Log on to Financial Reporting Studio.

a. Select Start > Programs > Oracle EPM System > Reporting and Analysis > Financial Reporting Studio.

b. Enter your User ID, Password, and Server details and click OK.



2. In Reporting Studio, create or open a Financial Reporting report.

Select Insert > Grid.



3. In the report workspace, drag the crosshairs to draw the grid size.

a. From the Database Connection drop-down list, select a database connection, or click New Database Connection.

Note: If you select an existing database connection, ensure that the application has ERP Integrator/ FDM drill through enabled. Alternatively, if you clicked New Database Connection, create a new connection, again pointing to an application that has ERP Integrator/FDM drill through enabled



b. In the User ID and Password text boxes, enter your database login credentials.

c. Click OK.

4. In the Dimension Layout dialog box, select the dimensions for the rows and columns of the report.

a. Drag one or more dimensions to the Rows frame.

b. Drag one or more dimensions to the Columns frame.
 

c. Click OK.



Note: The dimensions must contain data intersections that support ERP Integrator drill through functionality.

5. The report workspace is displayed, and the dimensions are visible in the grid.

Your grid should look like the following image:



6. In the grid, double-click the row heading or column heading to open the Select Members dialog box.

The Select Members dialog box is displayed.
 



7. Modify the member selection.

a. In the Selected area, select the existing member and click Remove .

b. In the Available area, expand the available members list.

c. Select members and click Add .

d. Repeat steps a through c for each dimension in the grid.

Enabling ERP Integrator Drill Through from a Grid

1. In Reporting Studio, select the grid that you created in the previous section.

On the Grid Properties General sheet, select Drill Through.

Your report may look like this:



Note: You can enable Drill Through using ERP Integrator only when the dimensions contain valid data intersections for drill through.

2. Save the report.

a. Select File > Save As.

The Save Report dialog box is displayed.



b. In the Save Report dialog box, enter a name for your report.

c. Optional: Enter a description for your report.

d. Click Save.

3. Select File > Close to close the report.

Using the ERP Integrator Drill Through Functionality

When you are working on data in EPM applications, you may want to understand the source of a data value. In this case, you can use the ERP Integrator drill through functionality from a data value in the Financial Reporting report to the source data and to journal entries in the General Ledger.

To view source data, perform the following steps:

Validating the ERP Integrator Drill Through from Reporting Studio

1. In Reporting Studio, open the report that you created in the previous section Adding a Grid.

Select File > Web Preview.

The report opens in HTML mode in Workspace.

Your report may look like this:



Note: In this example, only four data values are loaded and are available for drill through.

Click on a drill through link, to validate the link to the ERP Integrator landing page.

2. The data and metadata are loaded from the General Ledger to an ERP Integrator landing page in Workspace.

The landing page may look like this:



3. Click a link to drill back to the ERP source system and view the transactional detail.

A browser window opens to launch your Oracle E-Business Suite or PeopleSoft General Ledger.

Note: If you are not using Oracle Single Sign-On, you may be prompted to enter the authorized user name and password.

The General Ledger Journal entry may look like this:



4. Optional: Click the PTD value to view details of debits and credits.

Note: Debit and credit details will be provided as the final display only when you drill into the General Ledger.

5. Click Logout.
6. In Workspace, select File > Close Current to close the report.

Viewing ERP Integrator Drill Through Transaction Data from Workspace

1. Log on to Workspace:

a. In a Web browser, enter the following address: http://<server name>:19000/workspace/index.jsp.

b. In the User Name and Password text boxes, enter your login credentials.

c. Click Log On to authenticate your user credentials.



2. On the toolbar, click Explore.

The Root folder is displayed.



3. Open a Financial Reporting report that contains mapped ERP Integrator drill through links.

The report opens in a new Workspace tab:



4. Click a link to drill through to the ERP Integrator landing page.



Note: When related content is associated with a cell (for example, a link to another Financial Reporting report), an additional dialog box is displayed.

In the Related Content dialog box, click Drill Through to ERP Integrator.



 
5. On the ERP Integrator landing page, click a link in a grid cell to view the General Ledger transactional details.



6. A new browser opens to launch your ERP system (Oracle E-Business Suite or PeopleSoft General Ledger).

Note: If you are not using Oracle Single Sign-On you may be prompted to enter the authorized user name and password.

7. The journal entry may look like this:



8. In the General Ledger, click a value link to view the journal details of debits and credits.



9. Select File > Exit to close Workspace.

Using ERP Integrator Drill Through with FDM

Data that ERP Integrator extracts from ERP transaction systems is available through FDM for source management and easy loading into target EPM applications.To create a report that enables you to view transaction data in FDM, perform the following steps:

Creating a Report with Drill Through Links to FDM

1. In Reporting Studio, open or create a report that displays data loaded from FDM to your target application.

Select the grid.

In the Grid Properties General sheet, select drill through.

Your report may look like this:



Note: You can enable Drill Through using ERP Integrator and FDM only when the dimensions contain valid data intersections for drill through.

2. Save the report.

a. Select File > Save As.

b. In the Save Report dialog box, enter a name for your report.

c. Optional: Enter a description for your report.

d. Click Save.

3. Select File > Close.

Viewing Drill Through Data from FDM

1. In Workspace, open the report that you created in the previous section Creating a Report with Drill Through links to FDM.



2. Click a drill through link.

Note: When related content is associated with a cell (for example, another Financial Reporting report or multiple drill through links), you will be prompted to select from the related content that is available.
 

In the Related Content dialog box, click Drill Through to FDM.



Note: It is possible to have drill through links to both ERP Integrator and FDM. If the data was loaded by ERP Integrator, the link navigates back to the target application. If the data was loaded by FDM, you drill back to FDM.

3. The ERP Integrator landing page for FDM is displayed in a new browser window.

Click a drill through link to view the data that was loaded into FDM.



4. The FDM Drill Down page is displayed.

Your page in FDM may look like this:



5. Click the hyperlinked amount to view more details about the data.

A shortcut menu is displayed.

Select Open Source System.



6. The FDM Drill-Through tab opens in a new browser window.

Your tab may look like this:



On the FDM Drill-Through tab, click a link in a grid cell to view the General Ledger transactional details.

7. The General Ledger entry is displayed in Oracle E-Business Suite or PeopleSoft.

Your entry may look like this:



Note: If you are not using Oracle Single Sign-On you may be prompted to enter the authorized user name and password.

8. Optional: Click the PTD value to view details of debits and credits.

Note: Debit and credit details will be provided as the final display only when you drill into the General Ledger.

Click Logout.

9. The Drill Down page in FDM is displayed.

Click Close.

10. The FDM landing page is displayed.

Click Close.

11. Close the report.

Select File > Close Current.

Summary

ERP Integration Suite enables you to map and load General Ledger data directly into your EPM applications. With the drill through functionality you can audit your data by drilling through to thejournal entry details and have confidence in the consolidation and integration of your data.

In this tutorial, you should have learned how to:

• View data intersections details
• Create a Financial Reporting report and enable drill through functionality
• Use ERP Integrator drill through links to view ERP transaction data in source General Legders