ORACLE MASTERMINDS: An Introduction to Report Manager in Oracle EBS R12

Wednesday 19 September 2018

An Introduction to Report Manager in Oracle EBS R12

This article describes Report Manager functionality in Oracle R12 which allows users to primarily publish and store Financial Statements/reports that are written using Financial Statement Generator (FSGs). Note that Report Manager is also present in later versions of Oracle 11i and some patching may be needed to avail the functionality. Oracle customers that are getting ready to upgrade from 11i to R12 may find this article useful, especially if they are currently using Application Desktop Integrator (Client ADI) to publish and present their FSG reports. It is important to note that Client ADI is no longer supported in R12. Report Manager has replaced Client ADI in R12 and does not require any client installation. Report Manager acts as an online secure and convenient location to store and access point-in-time reports published or uploaded to a database. Report Manager Set Up Report Manager comes installed with some pre-defined menus. In order to use all the functionalities, you may define a main menu that can be assigned to an appropriate GL responsibility or create a new responsibility altogether. The following Functions may be added to your menu:

1. Financial Report Template Editor

2. Financial Report Submission

3. Standard Report Submission

4. Repository Management

5. FSG Drilldown Launch Page

6. FSG Drilldown: Select Content Set Rows

7. FSG Drilldown: Effective Range Selection Page

8. FSG Drilldown: Balance Inquiry Page

9. Account Analysis and Drilldown

10. Report Set Publish

11. Report Set Publishing Options

12. Request Set Publish

13. Request Set Publishing Options

14. Security Workbench

Responsibility name:System Administrator

Navigation: Application > Menu

The following functionality is described in this document

· Financial Report Template Editor

· Financial Report Submission

· Drilldown capabilities on FSGs

A) Financial Report Template Editor This is used to create and maintain templates. A template is the report format/layout that is used by the Report Manager to publish the reports. All the templates are report specific and templates need to be defined for each and every FSG report. However, Oracle delivers a seeded template called ‘Generic FSG Template’ which can be used with any FSG, irrespective of the number of rows and columns that the report has. A template is customized by the user and then uploaded to the server using MS Excel. Thus, templates do not reside on the user’s local drive or network drive was the case with Client ADI. Examples of modifications that can be made to templates are:

· Addition of company logo

· Addition/removal of report elements

· Moving the location of report elements on the page

· Modify row and column sizes

· Use standard Excel formatting such as font modification, underline, shading etc.

You can create a new template by clicking on the Financial Report Template Editor Select an existing FSG report, language and editor (MS Excel) and click on Create The screenshot below shows the Template that has been created in MS Excel. Notice that the template displays the respective rows and columns of the original FSG Report.

To demonstrate the following modifications have been made to the template as seen in the screenshot.

When you are done making the modification, you need to upload the template to the Oracle server. The upload process can be submitted by clicking on Oracle Menu. This is either directly visible on the MS Excel menu or in later versions of MS Excel (2007 and later), the Oracle Menu is visible under the Add Ins tab from the standard toolbar. Upon clicking on the upload sub menu within MS Excel, provide the Template name ( which is generally the FSG report name for easy referencing) and indicate whether you wish to overwrite the existing template and whether you wish to utilize this template to drill down to the journal entry and sub ledger transactions.

Once we uploaded the following confirmation message appears.

The above message shows that the template has been successfully uploaded to the server. You are now ready to utilize this template in publishing your FSG report.B) Financial Report Submission This is used to submit FSGs defined in your applications. Double click on Financial Report Submission to launch the form and provide the following parameters -Report Name -Content Set (optional) -Period -Currency -Ledger (defaults in) The screenshot below shows the Submission parameters page.

You can select the override segment in the additional parameters field. Users can also schedule the FSG to run at a pre-scheduled time or instantly. The default value is ‘Now’ i.e. instantly. Click on Next. The next screenshot shows the publishing parameters you need to provide, to publish your FSG. Choose your newly created template from the list of values if you are running the report for the first time with the template. Going forward, subsequent submissions of the report will have the template automatically defaulted on this field.

Click on Next The following screen shows the storage location in the repository. The report will be stored in the location that you specify. This is a location on the actual Oracle server, and not your local machine or network drive. It is important to store your financial statements in an organized fashion for quick and easy retrieval. You have the ability to select an existing location or create a new location/sub location (using the green plus sign being sure to click this button while on the section/grouping where you wish to add the new location) In the future you can retrieve the report from this location in your repository using the “Repository Management” link from the Report Manager menu.

Click on Next The next step is the presentation page. You need to indicate here if you want to create a shortcut menu to be placed on your main navigation page. This is optional and may be ignored.

Click on Next After you have selected all the parameters you can review the parameters from the Review page and in case you want to make any changes, you can go back to the appropriate page by pressing the back button. Make the changes and come back to this page by clicking Next.

Click on Submit button and then click on monitor requests on the next page. This will take you to the Request Center from where you can see the report processing status and also get the output.

When you submit any FSG report from Report Manager, there will be two requests that will be submitted. One is the “<FSG Report Name> (Financial Statement Generator)” and the other one is “<FSG Report Name> (Publish and Presentation Program)”. The first request is for generating the XML tags that will be processed by the second request to publish the report output in a presentable format. You would need to click on the output icon for “<FSG Report Name> (Publish and Presentation Program)

Optionally, if you accidentally closed the web form for the requests (above), you can also click on View > Requests > Find on the main Oracle Navigator page to look for your “<FSG Report Name> (Publish and Presentation Program)” Request.

Click on View Output and click on link that comes up and the following screen comes up.

Validate that the correct time frame is displayed and choose the appropriate Output Type to publish your FSG report. The screenshot below shows the report output when the Output Type of Excel was selected.

Alternate method to Publish Financial Statements

You may utilize the “Program – Publish FSG Report” request to publish your FSGs. This request is available from the General Ledger Superuser responsibility. Again, Oracle provides a seeded template that can be selected during publishing. Provide the parameters for the report such as Ledger, Report name and period. The other parameters default in based on the core FSG report definition but they can be overwritten if needed.

Click OK and choose the Options button to choose from a bunch of pre-seeded templates. Note that Oracle customers can also create their own .rtf templates and associate them to the Program – Publish FSG Report using XML Publisher Administrator. When utilizing the FSG Report Set Publish option, the same template can be utilized for more than one report but it may need to be modified using Oracle BI Publisher technology to add adjust column widths, add company logo etc. Monitor your requests and click on View Output.

The report output using “FSGXML: Basic Template” is shown below.

C) Drilldown

The drilldown feature allows the user to drill into report values using the Account Analysis and Drilldown feature on the FSG report. Ensure that you are using a template that is drill enabled. Performing an FSG Drilldown:

1. Your FSG HTML and PDF report types output will show links for each amount on the report. In MS Excel, simply double click on the cell that you wish to drill into. You cannot drill down on calculated amounts.

1. From the account balance, you can further drill into the journals, and sub ledger details where this is applicable.

2. You can also take advantage of the Export functionality in Account Analysis and Drilldown to export the drilldown information to Excel where you can easily perform additional computations and analysis.

3. You can see the various amounts that make up the total amount in the report.

5. Clicking on the column value link will further take you to the detail balances page as shown below.

6. You may then click on a Debit or Credit link to see the transaction record. This is only available if the transaction originated from a sub ledger e.g. AP, AR.

7. Clicking on View Transaction button will take you to the forms. In case a forms session has not been started, it will ask you for the responsibility you want to use for drilling to the appropriate sub-ledger. Upon selecting the correct sub-ledger, this will take you to the sub-ledger transaction form, from which the balance emanated.

You will get the forms from the Applications like on the next screenshot (AP Invoice showing an invoice distribution.