Insert Picture Here Advanced Reporting Techniques for People

<Insert Picture Here> Advanced Reporting Techniques for People. Soft Enterprise Larry Grey Sparling Solutions Larry. Grey@Grey. Sparling. com

Agenda • Presenter Overview • Review of People. Tools 8. 48 • Business Objects Enterprise • XML Publisher • Tips and Techniques • Drilling from your People. Soft Reports • Securing your People. Soft Reports • Managing your People. Soft Production Reporting • Understanding People. Soft Report Context • Questions

More about the Presenter • Larry Grey • People. Soft Employee from 1994 - 2005 • Product Manager for Reporting Tools from 1996 – 2005. • PS/n. Vision • Process Scheduler • PS/Query • Report Manager • Tree Manager • Cognos • Crystal • Essbase • Cube Manager • Charting • Co-founder of Grey Sparling Solutions

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

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

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

<Insert Picture Here> Review of People. Tools 8. 48

Review of People. Tools 8. 48 • XML Publisher (Oracle Functionality for People. Soft 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/n. Vision • PS/Query • SQR

Drilling From your Reports

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

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

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

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

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

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

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

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

Securing Your Reports

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

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

Problems to solve • Problem 1: How to identify what data a user has access to? • People. Soft-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

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

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 n. Vision • MANAGER_ID captures user who owns department • DEPT_NODE_TBL for tree-based bursting in n. Vision • DISTLIST captures set of roles and users for a node in a tree • FASTVIEWS in HR translate department tree to data ownership

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) • Table. Set Security by Perm List (tableset security by permission list) (SEC_SETID_CLS) • Table. Set 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) • n. Vision Ledger Security (LEDGER_SECURITY) • Chart. Field 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

Row-level Security Hooks • Report against table keyed by OPRID, OPRCLASS, or ROWSECCLASS • Automatically adds criteria for current user to SQL SELECT FROM PS_SP_PROJ_OPRVW A WHERE A. OPRID = 'VP 1'

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

Bursting in n. Vision 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 n. Vision Report Request • DEPT_TBL-based bursting • Use of Scope and Security Template in n. Vision Report Request

Moving your tree to DEPT_NODE page • Insert data from your existing tree into DEPT_NODE_TBL 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' • Modify your tree to use the pages

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

Maintain your tree to add users/roles to nodes

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

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

Managing Production Reporting

Managing Production Reporting • Setting up n. Vision 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

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

Process for Crystal and SQR • Each process definition identifies a unique report to run • APY 2020 • FIN 0011 • FIN 5005 • Job items are tied to specific reports

Process for n. Vision 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

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

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

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

Getting Context from n. Vision

Where Context Information is Stored • Defined Names: • NVs. Inst. Spec • Req. BU • Cells in Row 1 or Column 1 • Row 1 contains column criteria • Column 1 contains row criteria • n. Plosion • Timespans / Years and Periods • Tree n. Plosion / Levels and details

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

Features • Display n. Vision Criteria for Cell • Inherited Criteria (Drill, Scopes) • Sheet Criteria • Row Criteria • n. Plosion Inheritance • Excel Formula (for copy cell) • Drill To PIA

<Insert Picture Here> How to get n. Vision Context Helper Email us at OOW-281460@Grey. Sparling. com