Download
getting your c ore focus onto financial reporting language n.
Skip this Video
Loading SlideShow in 5 Seconds..
Getting Your C ore FOCUS Onto Financial Reporting Language PowerPoint Presentation
Download Presentation
Getting Your C ore FOCUS Onto Financial Reporting Language

Getting Your C ore FOCUS Onto Financial Reporting Language

103 Views Download Presentation
Download Presentation

Getting Your C ore FOCUS Onto Financial Reporting Language

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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.

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

  8. Best Practices

  9. 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

  10. 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

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

  12. 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

  13. Demo 1

  14. Creatinga • calendar

  15. Demo 2 • Code for calendar

  16. Creating an availability report

  17. Demo 3 • ‘Availability Report’

  18. FML & dynamic code generation

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

  20. Best Practices

  21. So with Dialog Manager

  22. -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

  23. We generate the following • DEFINE

  24. 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;

  25. For 6 days

  26. For 21 days

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