Enhancing Financial Reporting with FOCUS and WebFOCUS Best Practices

1. Getting Your Core FOCUS Onto Financial Reporting Language Steve Simon State Street Corporation ssimon@statestreet.com

2. During this hour • Discuss a few ‘best practices’ in relationship to Financial Modeling Language or what was called Financial Reporting Language. • Emphasis is on creating DYNAMIC query solutions.

3. During this hour • Which means getting down to the source code behind the query. • Preparation of temporary or permanent files to hold data extracts. • Generating DEFINES on the fly.

4. During this hour • Developing and utilizing ‘hierarchies’ created with FOCUS databases & JOIN with relational database tables. • How to populate ‘the hierarchy’ within a FOCUS database from sequential data sources utilizing MODIFY.

5. During this hour..we shall • Develop General Ledger and other financial reports with the assistance of the hierarchy. • Modify hierarchies to create reports that require ‘time based’ hierarchies.

6. During this hour..we shall • Learn how WebFOCUS may be used with SQL Server OLAP cubes. • Learn how easily reports can be created when their source data comes from an OLAP cube.

11. -SET &TEMPPATH = TEMPPATH(100,'A100'); -TYPE &TEMPPATH FILEDEF HOLDMAST DIR &TEMPPATH FILEDEF HOLDMAST DIR C:\IBI\APPS\COREFOCUSFRL APP HOLD COREFOCUSFRL FILEDEF SSCGL DISK C:/FUSE2008/SSCGL.foc -RUN

12. Best Practices

16. TABLE FILE CHARTDATA PRINT GL_ACCOUNT GL_ACCOUNT_PARENT GL_ACCOUNT_TYPE GL_ROLLUP_OP GL_ACCOUNT_LEVEL GL_ACCOUNT_CAPTION SYS_ACCOUNT ON TABLE SAVEAS CHARTDATAHOLD1 END

20. CREATE FILE SSCGL -RUN MODIFY FILE SSCGL COMPUTE TEMP1/A1 =' '; FIXFORM GL_ACCOUNT/A4 GL_ACCOUNT_PARENT/A4 FIXFORMGL_ACCOUNT_TYPE/A1 FIXFORM GL_ROLLUP_OP/A1 TEMP1 FIXFORMGL_ACCOUNT_CAPTION/A30 FIXFORM SYS_ACCOUNT/A25 COMPUTE GL_ACCOUNT_LEVEL = EDIT(TEMP1); MATCH GL_ACCOUNT ON MATCH REJECT ON NOMATCH INCLUDE DATA ON CHARTDATAHOLD1 END

25. Join SYS_ACCOUNTon the Hierarchy table with SYS_ACCOUNT on the fact table

26. TABLE FILE FUSEFASB PRINT MARKETVALB TOTALCOSTL TOTALCOSTB BY SYS_ACCOUNT WHERE SYS_ACCOUNT NE ' ' ON TABLE HOLD AS FUSEFASB1 FORMAT FOCUS INDEX SYS_ACCOUNT END

28. Demo 1

29. Creatinga • calendar

34. Demo 2 • Code for calendar

35. Creating an availability report

38. Demo 3 • ‘Availability Report’

39. FML & dynamic code generation

40. I want 6 days No changed my mind I want 21 days!!

41. Best Practices

42. So with Dialog Manager

43. -REPEAT LOOPER4 FOR &I FROM 1 TO &RECS STEP 1 -SET &J= &I +1; -SET &DIFFVAL = IF (&I GE 1) AND (&I LE 9) THEN '0' || EDIT(&I) ELSE EDIT(&I); -SET &DIFFVALJ = IF (&J GE 1) AND (&J LE 9) THEN '0' || EDIT(&J) ELSE EDIT(&J); -IF &I NE 1 THEN GOTO BYPASSME; -SET &STATEMENT = 'WHAT_AM_I/A3 = IF VAL' || &DIFFVAL | ' EQ VAL' || EDIT(&RECS) | ' THEN '; -SET &STATEMENT = &STATEMENT | &APOST || '-' || &APOST | ' ELSE ' ; -WRITE BUYORSELL &STATEMENT -GOTO LOOPER4

44. We generate the following • DEFINE

45. WHAT_AM_I/A3 = IF VAL01 EQ VAL20 THEN '-' ELSE IF VAL03 GT VAL02 THEN 'B' ELSE IF VAL04 GT VAL03 THEN 'B' ELSE … IF VAL10 GT VAL09 THEN 'B' ELSE IF VAL11 GT VAL10 THEN 'B' ELSE WHAT_AM_I; WHAT_AM_I = IF VAL12 GT VAL11 THEN 'B' ELSE IF VAL13 GT VAL12 THEN 'B' ELSE IF VAL14 GT VAL13 THEN 'B' ELSE …… IF VAL21 GT VAL20 THEN 'B' ELSE WHAT_AM_I;

46. For 6 days

48. For 21 days

50. Demo 4 • The ‘Roll Forward’ • report