BizTech Blog

For customers installing Oracle Business Intelligence (Financial) Applications a very useful customization can be done in a few steps that will add a nice bit of familiarity for reviewers of the financial statements.

The standard OBIA Financial reports display the typical industry standard reporting line tiems of Revenue, COGS, Liabilities etc. Customizing financial statements such as the P&L and Balance Sheet with the exact reporting lines currently used by the company can be accomplished with modifications to the ETL process, RPD, and BI Reports. In this entry, I’ll outline the steps taken to accomplish this common customization request from customers installing OBIA Financials. For this blog example, I’ll be adding a few custom ‘Revenue’ Line Items to the ‘Profit & Loss Quarterly’ BI Report. Right now, the seeded report states one line item of 'Revenue'. We want it to read 'Product Revenue' and 'Services Revenue'.

As a first step for this customization, part of the ETL process is to configure the Group Account and Financial Statement (.csv) data files with the corresponding information currently utilized by the organization. The Group Account record items placed in the data files are used for creating the required RPD objects. The following configuration files need to be updated with information and placed on the server prior to an ETL execution.

Group Account Data Files:

• file_group_acct_codes_ora.csv: List GL Balance segment data mapped to Group Account Numbers and Custom Attributes. This is the most detailed level of information. In our example, it would list the specific natural accounts that were associated with PRODUCT REVENUE and SERVICE REVENUE.
• file_group_acct_names.csv: List all custom Group Account Numbers and Group Account Names. For example, this would list a group account num of PROD_REV and a group account name of PRODUCT REVENUE, and SERV_REV with a group account name of SERVICE REVENUE
• file_grpact_fstmt.csv: List Group Account Numbers mapped to Financial Statement Item codes and GL Account Code categories. For example, this would list group account names like PRODUCT REVENUE and SERVICE REVENUE into a financial statement item code of REVENUE
• file_glacct_segment_config_ora.csv: List GL Segment hierarchy (typically completed as part of the standard configuration, and having no bearing on this customization.)

In my example, for the 2 revenue items needed - records identifying the accounts were added to the first 3-Group Account files listed.

Sample setup of the file_group_acct_names.csv file:

For any additional financial statement Group-Account items such as COGS, Liabilities, and Assets, the same process can be followed.

STEP TWO

The second step in the customization is to update the RPD with custom Measures to capture data for each of the Group Account buckets. For this example, we need to show the Revenue section of the P&L Quarterly report as 2 different Lines.

1. In the Business Model layer, locate the Fact – Fins – GL Balance.
2. Find the standard ‘Revenue’ measure and ‘duplicate’ this object. (right click, properties menu selection)
3. Rename the new measure and modify the logical column source statement’s ‘Where clause’ – changing ‘REVENUE’ to one of the custom Revenue Group Account Names. (Example: ‘PROD REV’)

1. Repeat this same process for the other custom Revenue measures. (Examples: ‘DISC PROD’; ‘SRVC REV’; ‘DISC SRVC’)
2. As a final modification step within the RPD, move the newly created measures to the Presentation layer under the applicable Subject Area for the Report. In my example for modifying the P&L statement, that would be: Financials – Profitability – Company à Facts – Profit and Loss
3. Check-In objects and Save the RPD.

STEP 3

The final step in this customization is modifying the required financial reports with any custom measures created.

In OBIA, analyze the report ‘Profit & Loss - Quarterly’. For the next action of adding the report fields, I recommend performing this step while under the ‘results’ tab opposed to being under the ‘criteria’ tab. Performing the field selection this way, allows for easier placement of the new statement line fields exactly where they need to display on the report. Completing this by dragging and dropping each of the custom Revenue fields for the statement from the left-side selection panel to the right-side report layout. Once the new objects are in place any additional modifications can be completed pertaining to field names and other display attributes.

Completing these 3-configuration steps provides a great foundation for extending the Financial Statements of an OBIA Financials installation. By following the steps outlined, any additional custom items needed for your Financial Statements can be created in this same manner. Another key-item of this customization not covered in this entry is the handling of the ‘Totals’ columns after creating custom measures. As it stands, the original ‘Revenue’ totals measure used on the modified P&L Quarterly Statement, does not include values from the custom ‘Revenue’ measures.

In next’s month Blog entry, I’ll cover creating custom ‘Total’ measures by incorporating the standard and custom Revenue items and a few extra tips pertaining to custom measure creation as it relates to calculating Totals and Margins on the P&L and other financial statements.

Blog author: Lamonte Bradley

Lamonte is a contributing blog author on the Business Intelligence Practice team at BizTech.