Nvision Peoplesoft by fkx75474

VIEWS: 597 PAGES: 45

More Info
									<Insert Picture Here>

Advanced Reporting Techniques for PeopleSoft Enterprise
                                           Larry Grey
                               Grey Sparling Solutions

 • 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
   More about the Presenter

• Larry Grey
  • PeopleSoft Employee from 1994 - 2005
  • Product Manager for Reporting Tools from 1996 – 2005.
      •PS/nVision                  •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 PeopleSoft

• Our Products
  • Address common PeopleSoft needs.
  • Leverage your existing PeopleSoft 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
• 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
   <Insert Picture Here>

Review of PeopleTools 8.48
 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
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


                                 Parameters              Page

• Portal/Menu Choices
    •   PSC  No Navigation (Content)

    •   PSP  Include portal navigation
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
   • Run to HTML
   • Run to Excel

 • Paste in Notepad in preparation for modification
    •   Run to Query Viewer

    •   Run directly to Excel
                           HTML           Query Name

                        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 nVision 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

 • 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 nVision reports for all
  managers of divisions and offices
• Running nVision 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?
  • PeopleSoft-delivered table
  • Custom Table
• Problem 2: How to apply security?
  • Self service – Filter data based on user running
  • 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
• nVision Bursting
  • Delivered Security template and scopes in nVision
  • Custom programmatic generation of rules
     • Scopes and report requests
     • Layouts and report requests
Delivered methods for Identifying data for a

• 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
   Delivered Financials Security Views

• Financials Security applies appropriate view to prompt tables and
   • 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)
   • TableSet Security by User ID (SEC_SETID_OPR)
   • Ledger Security by Perm List (ledger security by permission list)
   • 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
  Row-level Security Hooks

• Report against table keyed by

• Automatically adds criteria for
  current user to SQL
                                    PS_SP_PROJ_OPRVW A
                                    WHERE A.OPRID = 'VP1'
  nVision Row Level Security for Self Service

• Same construct as standard row level security, but
  applied in ledger template.
Bursting in nVision for Production

• 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
Moving your tree to DEPT_NODE page

• Insert data from your existing tree into
  A.EFF_STATUS, A.DESCR, ' ', ' '

• 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
  Use Scope and Security Template to use
  tree-based rule

• Use scope that
  accesses nodes

• Use
  to route results
   Similar for bursting at department detail

• Use scope that
  generates reports based

  field with U: to designate
  routing to user
Managing Production Reporting
  Managing Production Reporting

• Setting up nVision Jobstreams to run many reports
  • 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
• Step 2: Create Job Definitions that refer to
  process definitions
• Step 3: Schedule Jobset to identify the
 Process for Crystal and SQR

• Each process definition identifies a
  unique report to run
  • APY2020
  • FIN0011
  • FIN5005
• Job items are tied to specific reports
Process for nVision and Query

• Each process definition does not identify a
  unique report to run
• 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 nVision Jobstreams

• Two Approaches
 • nVision Report Books
 • nVision Report Requests
• Differences are the paramters to supply
  and granular control over processing
 • Parallel
 • Serial
  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
 Scheduling Specific Report Requests using
 Schedule Jobset

• Click on Jobset
• Fill in Parameters
  needed for
  Process Definition
Getting Context from nVision
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
 GS nVision Context Helper

• Puts user interface on
  • Snap-on to Excel
  • Helps users and
    auditors understand
    numbers better
  • Complimentary to
    Session Attendees
• Display nVision Criteria for Cell
  •   Inherited Criteria (Drill, Scopes)
  •   Sheet Criteria
  •   Row Criteria
  •   nPlosion Inheritance
  •   Excel Formula (for copy cell)
• Drill To PIA
   <Insert Picture Here>

How to get nVision Context Helper
Email us at

To top