Advanced Reporting Techniques for PeopleSoft Enterprise

1
Advanced Reporting Techniques for PeopleSoft
Enterprise

• Larry Grey
• Grey Sparling Solutions
• Larry.Grey_at_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

PS/nVision PS/Query Tree Manager Crystal Cube Manager Process Scheduler Report Manager Cognos Essbase Charting
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
  Oracles 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
Capturing Pages

• Open Page
• Copy URL

12
Capturing Pages

• Note you may need to append ActionU if the
  default action of a page is Add.

13
Capturing Queries

• Go to Query Viewer
• Search for Query
• Right-Click to Copy from link for query
• Run to HTML
• Run to Excel

14
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 DESDISTLIST 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_at_GreySparling.com