Transcription
Go Beyond the Basics withPeopleSoft nVision/QueryStephen Kelly SpearMC Consultingwww.spearmc.com1
Agenda SpearMC Solutions Overview PeopleSoft nVision‒ 9.1 nVision Highlights‒ nVision - Beyond the Basics PeopleSoft Query‒ 9.1 Query Highlights‒ Query - Beyond the Basics2
SPEARMC SOLUTIONS OVERVIEW3
About SpearMC SpearMC is a full-service consulting andtechnology services firm with specific focus onPeopleSoft Financials Our consultants and network of PeopleSoftAnalysts, Technical Leads and ProjectManagers average fifteen years of PeopleSoftexperience We are North America’s leading provider ofcustom-tailored PeopleSoft Financial trainingsolutions and educational contentdevelopment. It is our mission to provide the highest levelsof professional service at competitive rates4
Slide 5About SpearMCIn 2012, SpearMC wasrecognized by Inc.Magazine as one of theInc. 500 5000 FastestGrowing PrivateCompanies in Americaand by the San FranciscoBusiness Times one of the100 Fastest-GrowingCompanies in the BayArea5
About SpearMCSpearMC delivers Oracle/PeopleSoft services across numerous industry domainsand marketsFinancial ServicesPeopleSoft Financials v8.9 UpgradePeopleSoft Project Costing v8.9 ImplementationTechnologyUbisoft: PeopleSoft Financials v9.0 UpgradeNetApp: Oracle Data Warehouse & ReportingConsumer ProductsFinancials v8.8 Global ImplementationPeopleSoft Financials Long-Term SupportRetail & Logistics24 Hr Fitness: PeopleSoft Reporting StrategyBekins: PeopleSoft Billing v8.9 ImplementationHigher Ed & Government6Art Institute: PeopleSoft Financials TrainingCity of Seattle: Chart of Accounts Redesign
Our Expertise Project ultsImprovementBusiness IntelligenceEducationIT Strategy/ArchitectureApplicationDevelopmentERP IntegrationRecruiting/Support7
PEOPLESOFT – NVISION8
nVisionnVision is a good tool for reporting data from PeopleSoft in an end user 'usable' format Allows end users to retrieve information from the PeopleSoft databaseusing ledgers, trees, and queries and to place it into an Excel spreadsheetHas been the reporting product of choice for financial reporting, but alsoused frequently with other modules, including HRnVision Bursting‒‒Delivered Security template and scopes in nVisionCustom programmatic generation of rulesooScopes and report requestsLayouts and report requests9
nVision Components Ledger Structure Ledger Data CFs & Trees nVision Layouts Report Requests Report Books End-User Reports nVision Scopes10
Slide19nVision ComponentsComponentDefinitionChartfields Accounts, Project ID, Cost Centers (DeptID)Trees Graphical representation of chartfields Trees are used to present chartfield hierarchy via nodes, roll-ups and detail valuesLedger Structure &Data Store GL summary balances by accounting period at the chartfield level Structure dictates how the ledger will store USD and Local currenciesLayouts Excel-based templates that contain the “rules” or “instructions” that tell PeopleSoft how toretrieve and present ledger data All report formatting is done within the layoutsScopes Used to narrow or filter the amount of data that a report request retrieves Scopes allow for a limited set of layouts to be used repeatedly without having to go into the layoutand hard-code criteriaReport Requests Contain the report generation criteria such as Report Name, Report Layout, Scope, ReportingDate, Business Unit, Variables and DistributionReport Books Allow for several report requests to be grouped together and run at the same time. Report Books are run via the PeopleSoft Process Scheduler either on an ad-hoc or scheduled basisEnd-User Reports Excel worksheets that are distributed to Financial end-users11
9.1 NVISION HIGHLIGHTS12
Enhancements Accepting tree effective-date at thetime of criteria definition Supporting not-in criteria Ability to define the types of reportrequests Restoring query prompts to webversion of nVision Drilling down PS/nVision reports usingweb services (nVisionDrill VSTO add-in)13
Accepting Tree Effective-DateThese changes will affect only matrix layouts, as only in matrix layouts we have anoption of selecting trees — Tabular layouts are not affectedIf this checkbox is selected, the As of Datefor Trees specified in layout definition isoverridden at the time of Report RequestThe PeopleSoft nVision Filter Criteriadialog in nVision client is enhanced toinclude an As of Date field along with thetree name14
nVisionSupporting “Not in Criteria”Beside adding, modifying, anddeleting the nodes from thecriteria, the PS/nVision Layoutdesigner now includes a newfeature called “Not in Criteria”,which allows Layout designers toinclude fields for buildingnegative criteria; for example,selecting nodes for not-in clausepart of the Query15
Restoring Query Prompts toWeb Version of nVision The Define Report Requestpage of PS/nVision will includea new sub page called QueryPrompts Prompts, where you are able toadd all the required parametersfor queries in that given report These parameters are stored asrecords in a new table calledQuery Parameters; and theserecords are identified by theQuery name, report ID, andBusiness Unit as a primary key16
Ability to Define the Typesof Report RequestsSelect nVision, Share Report Request from the nVision menu . nVision Designer recognizes theconcept of public and privatereport requests Currently, only the web-basedversion honors the public orprivate report requests security This enhancement enables theusers of the Windows client ofPS/nVision to:– Have the flexibility of making reportrequest either public or private– Define whether to share the reportwith other usersSelect Users / Roles toshare your report17
NVISION - BEYOND THE BASICS18
nVision Layout ComparisonFeature / FunctionData SourcesDataData deliveryKey differencesbetweentabular layouts,matrix layouts,and Query:LayoutsSelection criteriaPS nVision variablesScopenPlosionDrilldown from instanceTimeSpansMatrix LayoutMultiple queries andledgers, labelsNumbers in matrix, textin labels and variablesMatrix intersections offield criteria andqueries (amounts) oflabel and field criteria(text)One per workbook, butcan have multipletabular layouts in sameworkbook.Scope, Business Unit,Effective Dates, QueryLedger, TimeSpan,Field, Label, StringYesMultiple instances fromthe layout workbookRows and columnsYesYes, with data keyed byyear, period19Tabular LayoutSingle queryQuerySingle queryText and numbersText and numbersSelected query resultcolumns, one data rowper spreadsheet rowAll query resultcolumns, one data rowper spreadsheet rowMultiple worksheetsper workbookNone: produces onesheet in templateworkbookScope, QueryQueryNo (but can put inmatrix layout)Multiple instances fromthe layout workbookNoneNoNoNoNoneNoneNoNo
nVision CodesnVision places code in Column A and Row 1. For more experienced users, that code can beadded or modified without using the layout definition box.20CodeMeaning%,The start of an nVision commandSTimeSpanLLedgerFChartfieldTSelect Tree NodesMSelected Summary Chartfield NodeVSelect Detail ValuesAll Detail ValuesRReverse SignNTree NodeXnPlodeCCopy Formula for nPlosionQQueryCColumn (Aggregate field from Query)
Tabular Layout Benefits Extend nVision Reporting to Non-Ledger TablesMultiple Queries in 1 LayoutFilter Query Data By Using ScopesInclude Excel Features in Layout‒‒‒ Formatting and FormulasPivot TablesMacrosSchedule by Using Report BooksUse Selected Fields From QueriesThe nVision GUI was designed toprompt against Financial Ledgerbased tables, but can be used topull data from any PeopleSoft tablevia a predefined query.You must know your field namesand type them in instead of pickingthem from a prompt list.21
Queries Used in Matrix Layout The query must include at least oneaggregate (i.e. sum or count)column.You must include the aggregate aspart of the query.The aggregate column much have aheading for identification (i.e. SumTotal Amount).Generally speaking, queries used inmatrix reporting should not containmuch criteria, as the matrix layoutwill limit the information returnedin the results. This allows you toreuse a shell query for manydifferent layouts.22
Queries Used in Matrix LayoutCombination of query data and time spans populate thereport .23The FISCAL YEAR andACCOUNTING PERIODrequirement can be a problemfor non-ledger PeopleSoft tables.However, this requirement canbe fulfilled by joining anaccounting date field to thecalendar table and/or by creatinga table view.
How to LimitReportInstances whenusing MultipleScope Fields?If you specify a field combinationtable, PS/nVision generates aninstance of the report for onlythose field values that are listedas valid combinations on thetable.PS/nVision normallyproduces a reportinstance for eachcombination of theselected nodes or detailvalues for all specifiedfields.Note: You can also create a dynamic record that is populated by a query and includesonly the combinations of field values that actually have data for that reporting period.24
Using Variables Create in out-of-view areaPut in all the nVision Variables you might want and label themName the cell for each Variable using Formulas Create from Selection25
Working with Functions In out of view area, set up 4 QTD columnsUse OFFSET formula in visible part of report to pick up correct QuarterExample: OFFSET(U10,0,QTR)26
PS/nVision - Defined NamesSample of names that can be inserted into cells on report icates the elapsed time to produce this instance. To see this elapsed timein the instance, enter the formula NvsElapsedTime in a cell and format thecell with a time format.Indicates the time this instance was saved. To see this time in the instance,enter the formula NvsEndTime in a cell and format the cell witha date or date/time format.Used on hidden layout sheets only to enable data retrieval on the sheet.By default, nVision will not process hidden layout sheets.Indicates the name or reference of the macro to be run on completion of theinstance.Stores the tree as of date from the report request.Stores the name of the query that provides the data for this tabular layout orinstance.27
nVision Design Tips Different ledgers cannot be combined in one field.Different TimeSpans cannot be combined in one field.nPloded rows or columns cannot be combined with non-nPloded rows orcolumns.If any field criteria are specified in an individual cell, all field criteria shouldbe specified in the cell, no field criteria will be inherited from the row,column, or worksheet.Take advantage of report variables and relative timespans to minimizelayout maintenance.The XLS output is generated in the format of the Excel version on theReport Server. As a rule, you need to make sure your end-users havecompatible versions of Excel to open the XLS output generated from theReport Server.28
PEOPLESOFT QUERY29
Continues to have benefits QueryAd-Hoc & Simple Tabular ReportingCapabilityQuickly puts operational informationin the hands of decision makersSchedulable30 Power of SQL Database Platform Independence Introduced Web Service for Query inPeopleTools 8.48 Output to Excel, HTML
9.1 QUERY HIGHLIGHTS31
PeopleSoft QueryEnhancements New connected query Ability to develop in-tree criteriaprompts Ability to email application engineresults Drilling URL in PSQUERY32
Connected QueryConnected Query provides the ability to create a single XML file based on a set ofqueries with parent-child relationshipsA new folder for Connected Query hasbeen added under Reporting Tools A Connected Query is ahierarchical object built withexisting PeopleSoft Queries A parent query can nest “n”levels of child queries and anychild query can have “m”sibling queries within ahierarchy The output for connectedquery is always an XML file Connected Query can be usedas a data source for XMLPublisher reports33
Ability to Add an In-Tree Criteria PromptYou can schedule queries that have in-tree prompts using the Schedule Querycomponent or using the Schedule links in the Query Manager and Query ViewerYou are able to add in-treecriteria prompts to yourqueries, and define treeoption in Expression 2Type34
Querying Results with Drilling URLsWhen you access the Run page of a query that has a Drilling URL defined, its queryresults are shown as linksClicking these result linkseither: Runs the destination querywith prompt key values if thesource query has promptcriteria defined and promptkeys are mapped to sourcequery result columns Launches the componentwith the search key values ifthe source query has searchkeys defined and search keysare mapped to source queryresult columns Redirects the external URL35
Drilling URL ExampleQuery URLCreate a prompt forBusiness Unit andProject and map thevalues from your onequery to incorporateinto your new query36
Email Query ResultsAbility to Email Application Engine ResultsScheduled Query You are able to add emailas an output type forscheduled query (anyApplication Engineprogram) results With this option, you canschedule queries to runand have the resultsemailed directly torecipients (not just a linkto the Report Manager)37
Why is RSS so Important?Users just want to be told when something that's important to them changed With PeopleTools 8.5, users now have the ability to subscribe to application data andget told when something they care about changed.38
QUERY – BEYOND THE BASICS39
Advanced FeaturesUnion Use to join separate queries.Left Outer Joins Retrieves all the data in the firstrecord and only matching data fromthe second record.SQL: D.VOUCHER ID( )Subquery Subset of data used by a main queryfor data comparisonsExpressions SQL Expressions Used in Query TwoWays:‒ As comparison values inselection criteria‒ As columns in the query output40
Query ExpressionsExpressions are calculations that PeopleSoft Query can perform as part of a querywhen PS Query doesn’t provide a way by default to calculate a value. Expressions will vary based on databaseplatform being utilizedYou can create thousands of differentexpressions using a combination of thefollowing:‒‒‒‒Database Hints (must begin with /* )SQL Functions (ABD, TRIM, CURRENT DATE)Operators ( , -, *, /, , , AND, OR, etc.)Values (4, A, False, True, INV AMT, DISC AMT)41
Query Expressions Examples LTRIM(E.INV ITEM ID,'0') – trims leading zeros off item idTO NUMBER(LTRIM(A.PO ID,'0')) – once the leading zeros have beentrimmed off the PO ID, the value is change to a number.TO NUMBER(LTRIM(F.VENDOR ID,'0')) – once the leading zeros havebeen trimmed off the VENDOR ID, the value is changed to a number.SUBSTR(A.ACCOUNTING DT,1,4) – takes accounting date, (2007-01-01)goes to position 1 and picks up positions 1 thru 4, which ends up being the4 digit year 2007.DECODE(A.ERNCD,'120',A.OTH EARNS,0) – if the earning code is equal to120 then return OTH EARNS, otherwise return a value 0.A.QTY ONHAND-B.QTY – simple equation that subtracts the 2 quantityfields to make sure they always return a zero.42
Using ExpressionsCASE statementsWrite an expression that looksbetween two dates: First, if the Hire Date is betweenthe two prompt dates, it will return"New Hire" Second, if the Termination Date isbetween the two dates, it willreturn "New Term" Third, if those two conditions arenot met, it will simply need toreturn "Existing"Create aprompt basedon HIRE DT43
Optional Prompts Using PromptsUser can either provide the prompt value to return results for a specificvalue or leave it blank to return rows for all values.44
How to Filter on a Field from Outer JoinedRecord For example: In a query that pulls project attributes, you may want to pull specificproject team member roles into the query as columns:Note:Different databaseplatforms usedifferent syntax45
How to Default Current Date in a Query PromptAutomatically Type the following in Edit Expression:TypeDefault DateCriteriacurrent dateExpressiondecode(:1,TO current date - 1decode(:1,TO 1)current date 1decode(:1,TO DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate) 1,:1) Note:Different databaseplatforms usedifferent functions(e.g. MSSQL uses“getdate” insteadof “sysdate”)Replace :1 with the actual prompt value. You must first create this prompt.Now you need to pass 01/01/1900 as an input parameter if you want to run thequery for current date. Passing any other values will make the query run for thatdate.This way you can achieve both (run the query for a user selected date or run aquery for current date which can be used to schedule).46
Questions?Comments?47
SpearMC Tools & Resources www.SpearMC.com48
SpearMC Education Sessions The Eight Essentials for Planning & BudgetingSession ID: 100480Date: Wed, April 10thTime: 3:00 p.m. – 4:00 p.m.Track: C-Level Development How to make PeopleSoft Multi-Currency Work for YouSession ID: 100460Date: Wed, April 10thTime: 4:15 p.m. – 5:15 p.m.Track: Financial Modules49
Contact Information Stephen Kelly, Enterprise Systems Consultantsteve.kelly@spearmc.comMillie Babicz, Financials Solution Architectmbabicz@spearmc.comMarcus Bode, Principalmbode@spearmc.comDavid Pigman, Technical Architectdpigman@spearmc.comKeith Harper, SCM Solution Architectkeith.harper@spearmc.com50
A PeopleSoft Modular Deep-Dive EventJuly 23-25, 2013Rosemont, IllinoisMark Your Calendar!A JD Edwards EnterpriseOne Deep-Dive EventDecember 2-4, 2013Denver, Colorado51
Online Series Offerings for 2013Take advantage of these improved series offerings for 2013,featuring Oracle sessions, vendor sessions and some of thebest customer sessions from the Quest community. Train fromthe comfort of your desk!SeriesProfessional Development SeriesMonthJuneExecutive SeriesHCM SeriesUpgrades SeriesMobility SeriesFinancials SeriesJuneJulyAugustSeptemberOctoberJD Edwards World SeriesTools & Technology SeriesNovemberNovember52
Layouts One per workbook, but can have multiple tabular layouts in same workbook. Multiple worksheets per workbook None: produces one sheet in template workbook Selection criteria Scope, Business Unit, Effective Dates, Query Ledger, TimeSpan, Field, Label, String Scope, Query Query PS n
