Advanced Reporting Techniques for PeopleSoft Enterprise

1. Advanced Reporting Techniques for PeopleSoft Enterprise Larry Grey Grey Sparling Solutions Larry.Grey@GreySparling.com

2. Agenda • Presenter Overview • Review of PeopleTools 8.48 • Business Objects Enterprise • XML Publisher • Tips and Techniques • Drilling from your PeopleSoft Reports • Securing your PeopleSoft Reports • Managing your PeopleSoft Production Reporting • Understanding PeopleSoft Report Context • Questions

3. More about the Presenter • Larry Grey • PeopleSoft Employee from 1994 - 2005 • Product Manager for Reporting Tools from 1996 – 2005. • Co-founder of Grey Sparling Solutions

4. Who is Grey Sparling Solutions? We Enhance the PeopleSoft Experience • Our Products • Address common PeopleSoft needs. • Leverage your existing PeopleSoft infrastructure. • Enable functionality with minimal cost and effort

5. Products that Enhance Auditing Experience • Compliance and Accountability • Security Administrative Experience • Operations Productivity • Application Support Productivity • Developer Producitivity End-User Experience • End-user Productivity • Enhanced Reporting

6. More about the Founders • PeopleSoft Tools and Technology Experts • PeopleTools Developers, Product Strategists since 1994 • Recognized by Oracle as the leading PeopleSoft technology experts • Recognized by customers as leading Peoplesoft technology experts - More than 200 PeopleSoft conference presentations • Industry Leaders in Reporting Best Practices • Defined reporting solutions for PeopleSoft and NetSuite • Extensive contacts among all key Business Intelligence Partners • Architects of PeopleSoft EPM / Data Warehousing leading practices • Visit our BLOG to read more PeopleSoft tips and techniques • http://blog.greysparling.com • The only PeopleSoft-related blog listed on Oracle’s blogroll (http://blogs.oracle.com/)

7. Review of PeopleTools 8.48

8. Review of PeopleTools 8.48 • XML Publisher (Oracle Functionality for PeopleSoft Customers) • Embedded into PS/Query • Embedded into Application Engine • No need to migrate from existing middleware • Business Objects Enterprise Integration • Integration with Process Scheduler for running reports • Continuing support of existing Crystal Functionality • NT-specific process scheduler integration • Crystal Report Explorer integration • Windows Crystal Reports • All other existing reporting tools still supported • PS/nVision • PS/Query • SQR

9. Drilling From your Reports

10. Drilling from your Reports • Capturing URLs to use • Embedding URLs into your reports

11. Paste in Notepad in preparation of modification Menu.Component Page Parameters • Portal/Menu Choices • PSC  No Navigation (Content) • PSP  Include portal navigation Capturing Pages • Open Page • Copy URL

12. Capturing Pages • Note: you may need to append &Action=U if the default action of a page is Add.

13. Paste in Notepad in preparation for modification • Run to Query Viewer • Run directly to Excel HTML Query Name Excel Query Name Capturing Queries • Go to Query Viewer • Search for Query • Right-Click to Copy from link for query • Run to HTML • Run to Excel

14. Bus Unit Account Adding Parameters to Query • Find the different parameters on the Query • Add the a BIND reference for each parameter

15. Embedding URLs into nVision Reports • Open Report with Data in it • Use Hyperlink Function • Replace Parameters with “&{cellref}&”

16. Embedding URLs into Queries • Open Query with Data in it • Create SQL Expression in Query • Use %CONCAT %TRIMSUBSTR to embed fields from query into parameters • Add Expression as Field in Query

17. Embedding URLs into Crystal • Use Query with Hyperlinks Embedded in it. • Set Hyperlink in Crystal (Field Property)

18. Securing Your Reports

19. Securing your Reports • Ad-hoc reporting • Production reporting • Other potential solutions

20. Examples: Securing your reports • Running and distributing nVision reports for all managers of divisions and offices • Running nVision and distributing reports for all department managers • Securing PS/Query and Crystal

21. Problems to solve • Problem 1: How to identify what data a user has access to? • PeopleSoft-delivered table • Custom Table • Problem 2: How to apply security? • Self service – Filter data based on user running report • Production Reporting – Filter and distribute reports based on security rule for a set of users

22. Applying Security Approaches • Hooks to apply self-service security • Reporting View in GL • OPRID key in reporting tables • Query Security • nVision Bursting • Delivered Security template and scopes in nVision • Custom programmatic generation of rules • Scopes and report requests • Layouts and report requests

23. Delivered methods for Identifying data for a user • Security views in Financials • Tables to capture Business_Unit, SETID, and LEDGER mappings to user • Pages to maintain mappings • Process to invoke security type • DEPT_TBL for department-based bursting in nVision • MANAGER_ID captures user who owns department • DEPT_NODE_TBL for tree-based bursting in nVision • DISTLIST captures set of roles and users for a node in a tree • FASTVIEWS in HR translate department tree to data ownership

24. Delivered Financials Security Views • Financials Security applies appropriate view to prompt tables and reports: • Unit Security by Perm List (unit security by permission list) (SEC_BU_CLS) • Unit Security by User ID (SEC_BU_OPR) • TableSet Security by Perm List (tableset security by permission list) (SEC_SETID_CLS) • TableSet Security by User ID (SEC_SETID_OPR) • Ledger Security by Perm List (ledger security by permission list) (SEC_LEDGER_CLS) • Ledger Security by User ID (SEC_LEDGER_OPR) • nVision Ledger Security (LEDGER_SECURITY) • ChartField Pagelet Security (GL_PE_CF_SEC_COMP) • Pay Cycle by user ID (SEC_PYCYCL_OPR) • Project Security (SEC_PROJECT) • Uses delivered tables and pages to maintain security

25. Row-level Security Hooks • Report against table keyed by OPRID, OPRCLASS, or ROWSECCLASS • Automatically adds criteria for current user to SQL SELECTFROM PS_SP_PROJ_OPRVW AWHERE A.OPRID = 'VP1'

26. nVision Row Level Security for Self Service reporting • Same construct as standard row level security, but applied in ledger template.

27. Bursting in nVision for Production Reporting • Tree-based bursting • Configure existing tree to use page/table that allows assignment of users to nodes • Use of Scope and Security Template in nVision Report Request • DEPT_TBL-based bursting • Use of Scope and Security Template in nVision Report Request

28. Moving your tree to DEPT_NODE page • Insert data from your existing tree into DEPT_NODE_TBL • Modify your tree to use the pages INSERT INTO PS_DEPT_NODE_TBL SELECT DISTINCT A.SETID, A.TREE_NODE, A.EFFDT, A.EFF_STATUS, A.DESCR, ' ', ' ' FROM PS_TREE_NODE_TBL A, PSTREENODE B WHERE A.TREE_NODE = B.TREE_NODE AND B.TREE_NAME = 'DEPARTMENTS'

29. Modifying your tree to use the pages • Open up tree structure for your tree and modify it to use the DEPT_NODE_TBL

30. Maintain your tree to add users/roles to nodes

31. Use Scope and Security Template to use tree-based rule • Use scope that accesses nodes • Use %DES…DISTLIST to route results

32. Similar for bursting at department detail • Use scope that generates reports based on DEPT_TBL • Prefix MANAGER_ID field with U: to designate routing to user

33. Managing Production Reporting

34. Managing Production Reporting • Setting up nVision Jobstreams to run many reports together • After data staging processes • Stand-alone • Setting up Query and Crystal Jobstreams • After data staging processes • Stand-alone • ** XML Publisher

35. Overall Approach for this • Step 1: Create Run Control or Report Requests • Step 2: Create Job Definitions that refer to process definitions • Step 3: Schedule Jobset to identify the parameters

36. Process for Crystal and SQR • Each process definition identifies a unique report to run • APY2020 • FIN0011 • FIN5005 • Job items are tied to specific reports

37. Process for nVision and Query • Each process definition does not identify a unique report to run • NVSRUN • RPTBOOK • PSQUERY • Jobs are not tied to specific reports (they are placeholders for parameters) • Schedule definition provides the parameter that identifies the report to run

38. Setting up nVision Jobstreams • Two Approaches • nVision Report Books • nVision Report Requests • Differences are the paramters to supply and granular control over processing • Parallel • Serial

39. Setting up nVision Job (example) • All steps of job are same process name • Jobs are not tied to specific reports • (parameters to be specified in Schedule Jobset)

40. Scheduling Specific Report Requests using Schedule Jobset • Click on Jobset Parameters • Fill in Parameters needed for Process Definition

41. Getting Context from nVision

42. Where Context Information is Stored • Defined Names: • NVsInstSpec • ReqBU • Cells in Row 1 or Column 1 • Row 1 contains column criteria • Column 1 contains row criteria • nPlosion • Timespans / Years and Periods • Tree nPlosion / Levels and details

43. GS nVision Context Helper • Puts user interface on Context • Snap-on to Excel • Helps users and auditors understand numbers better • Complimentary to Session Attendees

44. Features • Display nVision Criteria for Cell • Inherited Criteria (Drill, Scopes) • Sheet Criteria • Row Criteria • nPlosion Inheritance • Excel Formula (for copy cell) • Drill To PIA

45. How to get nVision Context Helper Email us at OOW-281460@GreySparling.com