Transcript JD Edwards / PeopleSoft Enterprise One – Lessons Learned

WebFOCUS Financial Reporting with
JDEdwards / PeopleSoft EnterpriseOne
Paul McGuire, McGuire Business Solution, LLC
Brenda DeRusha, Scherer Brothers Lumber Co.
Copyright 2007, Information Builders. Slide 1
Introductions
Speakers
Paul McGuire – WebFOCUS Consultant / IBI Partner
McGuire Business Solutions, LLC
Brenda DeRusha – Controller, Manufacturing Operations
Scherer Brothers Lumber Company (SBL)
Copyright 2007, Information Builders. Slide 2
Goal of Presentation
To discuss real life lessons learned using WebFOCUS coding
techniques against a JD Edwards / PeopleSoft EnterpriseOne
(PE1) data structure for financial reporting, including:
 Data architecture
 Direct impacts to all financial reporting calculations
 Formatting
 Response times realized by users
 How challenges were overcome
Copyright 2007, Information Builders. Slide 3
Background
October 2004 – SBL went live on PE1
 Reports were developed using the PE1 Financial Report Writer
 Limitations realized
 Layout
 Font size
 Formatting options
 Use of white space
 Limited report delivery output options
 Performance became an issue
 October 2004
one hour to produce reports
 February 2006 11-12 hours to produce reports
 Monthly financial reports distributed via hard copy
Copyright 2007, Information Builders. Slide 4
Early 2006 - SBL Purchases WebFOCUS
Original High Level Goals
 Gain efficiencies in reporting
 Improve report presentation
 Utilize dynamic styling
 Provide options for report distribution
 Restructure presentation of reports
 Standardize row specs
 Replace hardcopy printing and distribution
Copyright 2007, Information Builders. Slide 5
Project Phases
 Phase I
Convert all financial reports to WebFOCUS
Create Dashboard for report generation
 Phase II
Improve performance
Maintain consistency for all financial reports
 Phase III
Utilize scheduling and distribution of financial reports
Electronic version control
Add drill down options
Evaluate consolidated data structures
Copyright 2007, Information Builders. Slide 6
Phase 1 Goals
 Convert (rewrite) all financial reports to WebFOCUS
 Restructure presentation of reports
 Standardize row specs
 Improve efficiencies in reporting
 Improve report presentation and readability
 Create dashboard to allow user selection for business unit
reporting on a consolidated or individual basis
Copyright 2007, Information Builders. Slide 7
Phase 1
 Converted all financial reports to WebFOCUS
 Approximately 30 unique financial reports





(consolidated vs. individual business unit reporting)
100+ possible report parameter combinations
Limited time and budget
Utilized multi-verb requests and hold techniques to resolve
rounding issues
Given existing data structure (F0902), define statements
heavily utilized
Provided foundation on which to build additional viewing
and report delivery options
Copyright 2007, Information Builders. Slide 8
Code Examples
To set the fiscal year header display
P1FISCAL_YEAR/I2=(&FISCAL_YEAR-1); -SET &NYEARDISP = IF &LEDGER_PER EQ 10 OR &LEDGER_PER EQ 11 OR &LEDGER_PER EQ 12 THEN (&FISCAL_YEAR + 2001)
ELSE (&FISCAL_YEAR+2000);
To set the ledger period
PDNUM/I2=&LEDGER_PER;
To set the sign on the amount field for financial report display (i.e., show revenues as a positive amounts)
NET_POSTING_01/D12.2= IF (EDIT(OBJECT_ACCOUNT)) LE 39999 THEN (NET_POSTING_01 * ( - 1 )/1000) ELSE
IF (EDIT(OBJECT_ACCOUNT)) GE 80000 AND (EDIT(OBJECT_ACCOUNT)) LE 84999 THEN (NET_POSTING_01 * ( - 1 )/1000 ) ELSE
IF (EDIT(OBJECT_ACCOUNT)) GE 85000 AND (EDIT(OBJECT_ACCOUNT)) LE 85120 THEN (NET_POSTING_01 * ( - 1 )/1000 ) ELSE
IF (EDIT(OBJECT_ACCOUNT)) EQ 85510 THEN (NET_POSTING_01 * ( - 1 )/1000) ELSE (NET_POSTING_01/1000);
To obtain current month actual amount
CPER_AMT/D12.2 = IF (&LEDGER_PER EQ 1) AND (LEDGER_TYPE EQ 'AA') THEN NET_POSTING_01 ELSE
IF (&LEDGER_PER EQ 2) AND (LEDGER_TYPE EQ 'AA') THEN NET_POSTING_02 ELSE, etc.
To obtain current month budget amount
CPLYPER_AMT/D12CB = IF (&LEDGER_PER EQ 1) AND LEDGER_TYPE EQ 'BA' THEN NET_POSTING_01 ELSE
IF (&LEDGER_PER EQ 2) AND LEDGER_TYPE EQ 'BA' THEN NET_POSTING_02 ELSE, etc.
To obtain current year-to-date actual amount
YTD_AMT/D12CB=
IF &LEDGER_PER EQ 1 AND LEDGER_TYPE EQ 'AA' THEN NET_POSTING_01 ELSE (Period 2 = Net Posting 01 + Net Posting 02 etc).
Same logic for Budgets (BA instead of AA)
To obtain current and previous year’s balance forward amounts
CYBAL_FWD/D12CN=IF ((EDIT(OBJECT_ACCOUNT)) LE 29999) AND FISCAL_YEAR = '&FISCAL_YEAR' THEN (BAL_FWD/1000)
ELSE 0;
PYBAL_FWD/D12.2=IF ((EDIT(OBJECT_ACCOUNT)) LE 29999) AND P1FISCAL_YEAR EQ FISCAL_YEAR THEN (BAL_FWD/1000)
ELSE 0;
Copyright 2007, Information Builders. Slide 9
Phase 1

Utilized WebFOCUS dynamic styling and visualization based on data and parameters
selected:


Discovered “competing” FML and Core FOCUS format processing with this approach
Zero suppression issue
Copyright 2007, Information Builders. Slide 10
Phase 1
 Created Dashboard and launch pages for report generation
 Allowed user to select consolidated or individual business
unit reporting at run time through Dashboard
 PE1 hierarchy structures were not being used and project
didn't allow for implementing
 Compromised – created reporting group structure
Copyright 2007, Information Builders. Slide 11
Results
What went well
 Nine new launch pages in Dashboard for all financial reporting
 Significantly improved reporting efficiencies – gained 11 hours
per month
 Dynamic styling and presentation viewing
 Leveraged strengths of WebFOCUS to overcome challenges
What we learned
 Highly dynamic styling with FML and WebFOCUS stylesheets
for complex reports was challenging
 Consolidated data structures and hierarchies can have a
significant impact on long term change management
Copyright 2007, Information Builders. Slide 12
Phase 2 Goals
 Address performance issues with minimal impact
Report performance had slowed due to growth of F0902
June ’06
one hour to produce reports
Sept ’07
seven hours to produce reports
 Change Management
 Maintain consistency
 Review data structures and approaches
 Consolidated vs. unconsolidated
 Minimize development for row spec changes/updates
Copyright 2007, Information Builders. Slide 13
Phase 2 Approach and Findings
Considered and explored:
 Consolidated data structures
 Existing formulas, row specs, and calculations
 Utilizing hold files
 Segregating retail from manufacturing
 Copying and restructuring F0902 data
 Analyzed data and structures (significant impact to all
formulas in all financial reports)
 Key question:
What is the lowest level of data required for all
financial reports?
Copyright 2007, Information Builders. Slide 14
ANSWER
 Group and summarize by Company, Fiscal Year, Ledger Type, Business
Unit, Object Account, Subsidiary, and Short Account ID
 Once lowest level was determined, we could aggregate at the database
level with little to no impact to all financial reports
Copyright 2007, Information Builders. Slide 15
Phase 2 Solutions
 Created a summarized reporting table to load nightly or at
user request
 Created back end scripts to aggregate data
SQL Code:
INSERT INTO RF0902SUM
(GBFY, GBCO, GBMCU, GBOBJ, GBSUB, GBLT, GBAID, GBAPYC, GBAN01,
GBAN02, GBAN03, GBAN04, GBAN05, GBAN06, GBAN07, GBAN08,
GBAN09, GBAN10, GBAN11, GBAN12, GBAN13, GBAN14, GBAPYN)
SELECT GBFY, GBCO, GBMCU, GBOBJ, GBSUB, GBLT, GBAID,
SUM(GBAPYC), SUM(GBAN01), SUM(GBAN02), SUM(GBAN03),
SUM(GBAN04), SUM(GBAN05),
SUM(GBAN06), SUM(GBAN07), SUM(GBAN08), SUM(GBAN09),
SUM(GBAN10), SUM(GBAN11), SUM(GBAN12), SUM(GBAN13),
SUM(GBAN14), SUM(GBAPYN)
FROM CF0902
GROUP BY GBFY, GBCO, GBMCU, GBOBJ, GBSUB, GBLT, GBAID
Copyright 2007, Information Builders. Slide 16
Phase 2 Solutions
 Changed access file to point to new data structure
SEGNAME=RF0902SUM,
TABLENAME=Reporting.bderusha.RF0902SUM
 Changed source of rollup structure to a new DB table
 Easily maintained due to a limited number of
users and infrequent changes
 Utilized ‘–include’ statement in applicable reports
Copyright 2007, Information Builders. Slide 17
Results
What went well
 Addressed performance for all financial reports
 Gained six hours per month with minimal to no impact on coding
 Eliminated > 99% of data to be processed by WebFOCUS
 No impacts to all define statements and internal processing
 Reporting table not expected to grow exponentially
What we learned
 Performance improvements with this approach outweighed need to move
to consolidated master file data structures
 If faced with similar performance situations, particularly with F0902
 Consider splitting reports to point to summary and details
 Utilize parameters as drill downs to key (real) fields
Copyright 2007, Information Builders. Slide 18
Phase 1 and 2 - Overall - What We Learned
When beginning a large scale reporting initiative, take additional time to:
 Evaluate pros and cons of consolidated master file data structures in
terms of:
 Report development (with children and hierarchical levels)
 Ongoing change management
 Maintaining hierarchies in one place - PE1 and refresh hierarchies
in WebFOCUS
 Evaluate and consider:
 Segregating between summary and detail financial report
 To help maximize report processing and drill down options
 Overall look and feel of all reports and impacts to output viewing
options (i.e. pdf vs. online, etc).
 Cascading style sheets
All of the above factor into overall enterprise reporting standards
Copyright 2007, Information Builders. Slide 19
Phase 3 Goals
 Evaluate consolidated vs. unconsolidated in conjunction with
PE1 account category codes
 Automatic scheduling and distribution of financial reports
 ReportCaster
 Electronic versions of financial reports (report library)
 Further enhance end user's personalized viewing
 Dashboard
 Exception/variance alerts
 Add drill downs from financial reports (to details)
Copyright 2007, Information Builders. Slide 20
Questions???
Copyright 2007, Information Builders. Slide 21