Report Manager in Oracle EBS R12

A deep dive into the Report Manager functionality of Oracle EBS R12

1. A Hitchhikers Guide to Oracle Report Manager in R12 Prepared by: Prithis Das Dynamique Solutions LLC Session ID#: 13976

2. Presenter’s background Over 18 years in the Oracle EBS sphere Over 22 years of business process improvement experience Consulting experience with Big 5 and other major organizations Oracle Certified Specialist in General Ledger Chartered Accountant and Management Accountant from India Authored and presented papers on SLA, Web ADI, Reports Manager at several OAUG and local events Currently on a major R12 upgrade Contact: Cell: +1.630.3649457 Email: [email protected] Web: www.dynamique.us 2

3. Agenda Objectives Case Study Company Background Reporting background Report Manager features What Report Manager does not do Setup Considerations Quick Tutorial Security in Oracle Report Manager Using Report Manager – Tips Using Report Manager – Tricks Traps Lessons learned 3

4. Provide useful information for R12 implementation teams for implementation of Oracle General Ledger, Financial Statement Generator and Report Manager Inform users about new R12 Report Manager features Help avoid potential pitfalls using actual experience from implementing Report Manager Objectives 4

5. 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 Case Study Company Background 5

6. 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 Reporting Background 6

7. 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. Users can be given access to Report Manager only without the need to open up Java forms. Incorporates security rules to restrict access to published content Report Manager features 7

8. What 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

9. Setup Considerations Patchset level – Metalink 726958.1 (15 Jan 2014) Check for latest before implementation Release 12.2 (R12.FRM.C patches) Release 12.1.3 (R12.FRM.B patches) Release 12.0 (R12.FRM.A) Release 11i (FRM.H) Note 1077728.1 : Report Manager users must use Internet Explorer for full functionality 9

10. Setup Considerations Report Manager uses Self Service Workflow Applications Report Manager requires the BNE platform used by Web ADI Functional Dependencies FSGs should have been defined in GL Standard reports 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 10

11. Custom menu and responsibility 11 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 (or the GL_SUPERUSER” menu: Financial Report Template Editor Financial Report Submission Standard Report Submission Variable Report Submission Repository Management Report Set Publish Request Set Publish Request View Security Workbench

12. Custom menu and responsibility 12 To enable Drilldown you must also assign the following functions to the custom menu Account Analysis and Drilldown (Search Page) Account Analysis and Drilldown (Product Branding) FSG Drilldown: Launch Page FSG Drilldown: Select Content Set Rows FSG Drilldown: Effective Range Selection Page FSG Drilldown: Balance Inquiry Page For each of them choose the Function Name value without "_G"

13. Custom menu and responsibility Create custom menu for Report Manager System Administrator > Application > Menu 13

14. Custom menu and responsibility Add this menu as a sub-menu to the GL_SUPERUSER responsibility or a custom responsibility 14

15. Browser and Excel Settings Works with Internet Explorer, Firefox, Netscape See Metalink 389422.1 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 15

16. Quick Tutorial Create a Template Submit a single FSG report Monitor Request and View Output Submit a Request Set 16

17. Quick Tutorial Create a Custom Template Select Financial Report Template Editor Select a Report, Select Excel Template Editor, Click on Create 17

18. Quick Tutorial Create a Template Confirm ActiveX control interaction by clicking on Yes, if prompted 18 Excel template gets created with FSG components

19. Quick Tutorial Create a Template Personalize template with fonts, graphics etc , then upload to Oracle 19 Use Oracle menu to upload modified template to Oracle

20. Quick Tutorial Create a Template Name the template, select Drill option, Upload. 20

21. Quick Tutorial Create a Template Name the template, select Drill option, Upload. 21

22. Quick Tutorial Run a Report Select Financial Report Submission 22

23. Quick Tutorial Run a Report Select the new template, add parameters, Submit. 23 Select your template

24. Quick Tutorial Run a Report Monitor Requests, View Output 24 View Output

25. Quick Tutorial Run a Report Publish Report in Excel 25

26. Quick Tutorial Financial Report drilldown Double-click on an amount in the report to view the details of transactions totaling the amount displayed Can update some of the drilldown parameters, if desired 26

27. Quick Tutorial Reports Repository All reports submitted through Report Manager are stored in the reports repository Allows an organization to: Organize and view completed reports Update publishing and security options for completed reports Archive and delete reports 27

28. Quick Tutorial Reports Repository menu opens the Repository 28

29. Security in Report Manager Report Manager offers 4 security options: No Security Individual user access to reports is determined by assigned responsibility Flexfield Value Security Standard flexfield value security (GL Security Rules or Data Access Sets) is attached to a responsibility and applied to publishing and viewing financial reports User to Value Security Security can be defined in Oracle Report Manager to control access to secured financial reports PL/SQL Based Security Create and apply custom security suited to an organization’s specific needs 29

30. Security in Report Manager Flexfield Value Security The report is a financial report (FSG) The report is submitted with a content set Exactly one segment value in the content set has a display type of PE (Page/Expand) Choose the security in the report submission parameter User to Value Security Applied to FSGs that use a content set or standard reports that contain a valid expansion value Based on segment values for reports published in the repository View reports based on a segment value and framed by a security model set for the specific user(s) 30

31. Using Report Manager – Tips 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) 31

32. Using Report Manager – Tricks 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 32

33. Using Report Manager – Tricks (contd.) Display the Description column in the middle of the report 33 Description column in the middle of a wide report

34. Using Report Manager – 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) 34

35. Using Report Manager – Tricks (contd.) Use Report Manager templates directly from FSG submission 35 Before submission, select a template, and set Format = EXCEL. View Output will open the output in Excel, using the template

36. Using Report Manager – 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. 36

37. Traps 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 Mix-up 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 37

38. Traps 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 PDF output on wide reports breaks into several pages without proper column headings 38

39. Traps (contd.) Repository Maintenance could lead to disappearing menus Patch 6884665 if on R 11i Patch 6884280 if on R 12 39

40. 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 40

41. Traps (contd.) Mix-up of Request Set submission vs Report Set submission menu items in earlier releases of R12 The menu labeled “Request Set Publish” publishes Report Sets The menu labeled “Report Set Publish” publishes Request Sets 41

42. 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 42

43. Lessons 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 43