prithis das - oracle report manager tips tricks traps

1

Oracle Report ManagerOracle Report ManagerTips, Tricks and TrapsTips, Tricks and Traps

Prithis DasPrithis Das

2

AgendaAgenda

� Objectives

� Case Study Company Background

� Reporting background

� Report Manager features

� What Report Manager does not do

� Setup Considerations

� Quick Tutorial

� Using Report Manager – Tips

� Using Report Manager – Tricks

� Traps

� Lessons learned

3

ObjectivesObjectives

� Provide useful information for R12 implementation teams for implementation of Oracle General Ledger, Financial Statement Generator and Report Manager

� Indirectly “educate” users about new R12 Report Manager features

� Help avoid potential pitfalls using actual experience from implementing Report Manager

4

Case Study Company BackgroundCase Study Company Background

� Auto Salvage Company

� New implementation of R12

� Started April 2007 with very early releases of R12

� General Ledger, Payables, Assets, Cash Management, Advanced Global Intercompany System

� 120+ Legal Entities

� Six regional accounting centers

� Complex reporting requirements

� 400+ legacy reports using best-of-breed reporting tool

5

Reporting BackgroundReporting Background

� Complex reporting requirements

� 400+ legacy financial reports using dedicated reporting tool

� Typical Trial Balance took over 15 minutes to generate

� Legacy Reports in Excel, and included publishing features such as fonts, bold and underlining

� Lack of security – Users were able to access all reports across legal entities

6

Report Manager featuresReport Manager features

� Report Manager is an integral part of Oracle EBS

� Assumes implementation of Oracle General Ledger incorporating Financial Statement Generator (FSG)

� Provides an online repository to store and access financial reports

� Personalized look and feel on financial reports

� Reports can be submitted and accessed through Web browser. Hence jinitiator need not be installed if user accesses Report Manager only.

� Incorporates security rules to restrict access to published content

7

What Report Manager does not doWhat Report Manager does not do

� You cannot create FSG components (Row Sets, Column Sets etc) in Report Manager. These will need to be created through standard FSG

� Excel outputs from Report Manager will not incorporate Excel formulas

� Excel templates do not save all formatting options

� Works well with FSG reports. However Standard reports in Oracle will have formatting issues

8

Setup ConsiderationsSetup Considerations

� Patchset level – Metalink 820653.1 (9 Jul 2009)� Check for latest before implementation� For 11.5.10.2 apply FRM.H over and above FRM.G� For R12 Report Manager is Auto-installed. Apply FRM.A

� Report Manager uses Self Service Workflow Applications

� Report Manager requires the BNE platform used by Web ADI

� Functional Dependancies� FSGs should have been defined in GL� For standard reports, they should be assigned to Request Group

assigned to the Report Manager

� Custom menu and responsibility for Report Manager

� Browser and Excel settings

� Seeded template vs Custom templates

9

Custom menu and responsibility Custom menu and responsibility

� Pre-defined menus for Report Manager are not complete� “FRM_SECURITY_MENU” menu is pre-defined but not adequate

� Need to define/assign additional functions to custom menu:� Financial Report Template Editor

� Financial Report Submission

� Standard Report Submission

� Repository Management

� Alternatively, you may add these functions to the “GL_SUPERUSER” menu

10

Custom menu and responsibilityCustom menu and responsibility

� Create custom menu for Report Manager� System Administrator > Application > Menu

11

Custom menu and responsibilityCustom menu and responsibility

� Create custom Responsibility for Report Manager

12

Browser and Excel SettingsBrowser and Excel Settings

� Works with Internet Explorer, Firefox, Netscape� See Metalink 389422.1 (5-Aug-09) for latest browser requirements

� Internet Explorer Settings� Navigate to Tools -> Internet Options and choose the Security tab

� Select Local Intranet and choose the Custom Level button.

� Set the following option to Prompt.� Initialize and script Active X controls not marked as safe

� Excel Settings� Open Excel, Go to Tools -> Macro -> Security -> Trusted Sources

� Check the "Trust access to Visual Basic Project“

� Incorrect settings will lead to error “Microsoft VBScript runtime error: 429 - ActiveX component can't create object”or similar error.

13

Quick TutorialQuick Tutorial

� Create a Template

� Submit a single FSG report

� Monitor Request and View Output

� Submit a Request Set

14

Quick TutorialQuick Tutorial

� Create a Template� Select Financial Report Template Editor

� Select a Report, Select Excel Template Editor, Click on Create

15

Quick TutorialQuick Tutorial

� Create a Template� Confirm ActiveX control interaction by clicking on Yes, if prompted

Excel template gets created

with FSG components

16

Quick TutorialQuick Tutorial

� Create a Template� Personalize template with fonts, graphics etc, then upload to Oracle

Use Oracle menu to upload

modified template to

Oracle

17

Quick TutorialQuick Tutorial

� Create a Template� Name the template, select Drill option, Upload.

18

Quick TutorialQuick Tutorial

� Create a Template� Name the template, select Drill option, Upload.

19

Quick TutorialQuick Tutorial

� Run a Report� Select Financial Report Submission

20

Quick TutorialQuick Tutorial

� Run a Report� Select the new template, add parameters, Submit.

Select your template

21

Quick TutorialQuick Tutorial

� Run a Report� Monitor Requests, View Output

View Output

22

Quick TutorialQuick Tutorial

� Run a Report� Publish Report in Excel

23

Using Report Manager Using Report Manager –– TipsTips

� Create and Test your financial report on standard FSG, and then publish the report using Report Manager� Do not waste time over column heading formatting in Apps

� Use the Generic FSG template wherever possible. If you use custom templates, each template is associated with a single report

� Report Set considerations� Report Sets can only be submitted through standard FSG submission

� Responsibility submitting Report Set must be the same that is publishing it

� Use Repository Maintenance to avoid tablespace issues� Apply Patch first (Metalink 803014.1)

24

Using Report Manager Using Report Manager –– TricksTricks

� Use Report Manager templates directly from FSG submission� Users are familiar with the Request submission functionality

� Schedule your month-end reports to publish automatically� Use standard scheduling functionality

� Store Excel settings on custom Excel templates� Cell background color

� Cell / Text font

� Cell alignment

� Cell Border

� Cell Merge

� Underline

� Page Settings:� a. Orientation (Portrait / Landscape)

� b. Paper Size

� c. Width of Page

25

Using Report Manager Using Report Manager –– Tricks (contd.)Tricks (contd.)

� Display the Description column in the middle of the report

Description column in the middle of a wide report

26

Using Report Manager Using Report Manager –– Tricks (contd.)Tricks (contd.)

� Display the Description column in the middle of the report� Create a Custom Template� Use the Menu (Oracle > FSG Template > Move Line Items Right)

27

Using Report Manager Using Report Manager –– Tricks (contd.)Tricks (contd.)

� Use Report Manager templates directly from FSG submission

Before submission, select a template, and set Format = EXCEL. View Output will open the output in Excel, using the template

28

Using Report Manager Using Report Manager –– Tricks (contd.)Tricks (contd.)

� Use Report Manager templates directly from FSG submission� Use XML Publisher Responsibility

� Copy the “Generic FSG Template”, update the Data Definition to “FSGXML program”. Attach custom template file if required.

29

TrapsTraps

� Repository Maintenance could lead to disappearing menus� Patch 6884665 if on R 11i� Patch 6884280 if on R 12 (Metalink 803014.1)

� Can submit Report Set only from Java form� Need to update Output Option to XML for each report

� Mixup of Request Set submission vs Report Set submission menu items� “Report Set” on Java form, but “Request Set” in Report Manager

� Printing XML file if Profile Option Number of Copies is set to 1� Metalink 748037.1 workaround is to attach dummy printer to RGRARG

� Significant impact on Database size due to XML outputs

� Does not use Definition Access Sets functionality

� Report Manager output ignores Display Sets

30

TrapsTraps

� User to Value Security mandates use of Content Sets� Alternately, place specific reports on end-user menus

� Images (such as logos) can be used on templates from 12.0.6

� Profile Option for AutoArchive does not archive. Need to archive manually.

31

Traps (contd.)Traps (contd.)

� Repository Maintenance could lead to disappearing menus� Patch 6884665 if on R 11i

� Patch 6884280 if on R 12

32

Traps (contd.)Traps (contd.)

� Can submit Report Set only from Java form� You cannot submit a Report Set directly from Report Manager

� Also, need to update Output Option to XML for each report

� You can, however, publish a Report Set from Report Manager

33

Traps (contd.)Traps (contd.)

� Mixup of Request Set submission vs Report Set submission menu items� The menu labeled “Request Set Publish” publishes Report Sets

� The menu labeled “Report Set Publish” publishes Request Sets

34

Traps (contd.)Traps (contd.)

� Printing XML file if Profile Option Number of Copies is set to 1� Metalink 748037.1 workaround is to attach dummy printer to RGRARG

� However, workaround will lead to using Reprint for regular reports

35

Lessons learnedLessons learned

� Need to be on at least 12.0.4 or later for minimal issues

� Web interface can have a learning curve

� Use Report Manager selectively for upper management reports. Operational reports are efficiently done through standard FSG submission with text output

� Use Repository Maintenance frequently – move critical reports to network drives

� Longer than usual turnaround time on SRs

36

Presenter InformationPresenter Information

Prithis Das

Rolta TUSC

dasp@tusc.com