Transcript During this hour..we - Information Builders

Getting Your Core FOCUS Onto
Financial Reporting Language
Steve Simon
State Street Corporation
[email protected]
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.
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.
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.
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.
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.
So where
do we start
Hierarchies
&
FOCUS reporting
File
persistence
-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
Best Practices
From
data source
Extract
hierarchy data
from source
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 SAVE AS CHARTDATAHOLD1
END
Creating the
flat file
Loading the
FOCUS file
CREATE FILE SSCGL
-RUN
MODIFY FILE SSCGL
COMPUTE TEMP1/A1 =' ';
FIXFORM GL_ACCOUNT/A4 GL_ACCOUNT_PARENT/A4
FIXFORM GL_ACCOUNT_TYPE/A1
FIXFORM GL_ROLLUP_OP/A1 TEMP1
FIXFORM GL_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
Data from the
fact table
Join SYS_ACCOUNT
on the Hierarchy table
with SYS_ACCOUNT
on the fact table
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
Demo 1
Creating a
calendar
Demo 2
Code for calendar
Creating an
availability
report
Demo 3
‘Availability
Report’
FML & dynamic
code generation
I want 6 days
No changed my mind
I want 21 days!!
Best Practices
So with Dialog
Manager
-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
We generate the
following
DEFINE
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;
For 6 days
For 21 days
Demo 4
The ‘Roll Forward’
report
Working with
OLAP cubes
SQL Server
Analysis Services
&
Visual Studio 2005
Demo 5
OLAP cubes
& WebFOCUS
During this hour..we
• Discussed a few Financial Modeling
Language ‘best practices’.
• Emphasis was placed on creating
DYNAMIC query solutions.
During this hour..we
• Delved into the source code behind
the queries.
• More fine tuning can be done at code
level.
• Saw how to retain our ‘holdmasts’
and our data extracts.
• Generated DEFINES on the fly.
(Code is now more extensible)
During this hour..we
• Developed and utilized ‘hierarchies’
created with FOCUS databases and
JOINED to relational database tables.
• Learnt how to populate ‘the hierarchy’
within a FOCUS database from
sequential data sources utilizing
MODIFY.
During this hour..we
• Developed a General Ledger and
other financial reports with the
assistance of the hierarchy.
• Modified hierarchies to create reports
requiring ‘time based’ hierarchies.
During this hour..we have
• Learnt how WebFOCUS may be used
with SQL Server OLAP cubes.
• Learnt how easily reports can be
created when their source data
comes from an OLAP cube.
Thank you!
Please remember to
complete your evaluation
form and submit it to the
proctor 
Thank you for attending this session and the
2008 Information Builders Summit
in Nashville
Questions
PowerPoint Presentation & Source Code may be found at…
http://cid4c765fc825912e4d.skydrive.live.com/browse.aspx/Public