I am looking for suggestions to generate financial statements.
We have Mapics IFM in AS400 for our accounting system. There are 3 parent-child structural files(natures, units and periods) that will be used to retrieve all the raw g/l transactions.
The examples I've seen so far are single dimensional which only allow one FOR field.
Please help. Thanks.

I guess my question would be, how are you going to build a three-dimensional financial statement?

There are many good solutions when multiple hierarchies are used. I' working on one now that has a departmental hierarchy and an account hierarchy. The best way I've come up with is to make several passes at the data, adding the departmental hierarchy as an attribute (additional defined field) of the accounts, then using the account hierarchy in the final pass while sorting on the department values.

The FML heirarchies realyy has to do with your rows. Even though you have 3 dimensions, what do you want to show as your rows.
I typically show the Account dimension as the FOR field and Time or Business Unit as a BY.

WebFOCUS is very new to me. I am at the conceptual level on how to approach FML. Darin and Efrem, you both sound what I like to hear. Thanks for the quick response.

We have the nature parent-child relationships that defines the rows such as assets and liabilities(one structure per type,eg external/internal balance sheets or income statements). Then we have the unit parent-child relationships that defines the companies/departments/salespersons that defines how many columns on the financial report. Then we have the period structures that tells all the periods for different types of report ranges such as Annual, YTD, or This/Last year comparasions.
I hope to give the users the selection screen to select a nature structure(a report type), a unit structure(consolidated vs single company) and a period structure. I will be able to provide an appropriate FML report.
In the end, I will use Efrem's sugguestion to have FOR account BY company for most of the reports. I am having trouble visualizing how to retrieve all the last-children periods of any given tree into a temporary file, so I can join them with the g/l transaction file.

Hua

Without the FML functionality you can create many smart reports that come close to your demands.
FML is just a (great) extra to help you building smart summation and subtotals, that you normally only would get with a spreadsheet approach.

If I was in your place I would start with some basic reports and training and after a while introduce the FML functionality.

Good morning Frank,
Thank you for your advice.
Here is what I did with basic report. I know the data that has max. 4 levels, so I use the old school method to join 4 times, each time puts the result to the hold file before the next join to be able drill down to the last-child. If I am not very careful with the join, I would loose some of the nodes because not all branch have the same levels. I thought there got to be a better way to do this. Can I use GET/WITH CHILDREN in basic report painter?
I have not yet take any formal training with WebFOCUS, I learn as much as I needed to accomplish a task.

Hua

do you have the example databases that come with WebFocus?
If so there is an example available for the Hierarchy database.
What you need is the CENTGLL master
In the master you find some special field properties. The main are PARENT_OF and CAPTION.
If you have this added to the special fields, you can create a repetetive join, so it is not needed to do hat you did by joining level1 to level2 and level2 to level3 and so on.

If you search here on FP you will find several examples on this.

Frank,

Here is the period structure file:

FILENAME=yaclrep, SUFFIX=DB2 ,
REMARKS='Period Structure Member', $
SEGMENT=YACLREP, SEGTYPE=S0, $
FIELDNAME=CLASCD, ALIAS=CLASCD, USAGE=A10, ACTUAL=A10,
TITLE='Admin,division id', DESCRIPTION='Admin division id', $
FIELDNAME=CLEHCD, ALIAS=CLEHCD, USAGE=A10, ACTUAL=A10, FIELDTYPE=I,
TITLE='Period,structure id', DESCRIPTION='Period structure id', $
FIELDNAME=CLK1CD, ALIAS=CLK1CD, USAGE=A10, ACTUAL=A10,
TITLE='Child period,admin div', DESCRIPTION='Child period admin div', $
FIELDNAME=CLFJNB, ALIAS=CLFJNB, USAGE=A10, ACTUAL=A10,
TITLE='Child,period', DESCRIPTION='Child period', $
FIELDNAME=CLD0NA, ALIAS=CLD0NA, USAGE=A40, ACTUAL=A40,
TITLE='Child period,name overrde', DESCRIPTION='Child period name overrde',
REFERENCE=CLEHCD, PROPERTY=CAPTION, $
FIELDNAME=CLEENB, ALIAS=CLEENB, USAGE=P4, ACTUAL=P2,
TITLE='Period structure,level id', DESCRIPTION='Period structure level id',
PROPERTY=LEVEL_NUMBER, $
FIELDNAME=CLEVNB, ALIAS=CLEVNB, USAGE=P7.2, ACTUAL=P3,
TITLE='Period structure,sequence', DESCRIPTION='Period structure sequence', $
FIELDNAME=CLK2CD, ALIAS=CLK2CD, USAGE=A10, ACTUAL=A10,
TITLE='Parent period,admin div', DESCRIPTION='Parent period admin div', $
FIELDNAME=CLBUNB, ALIAS=CLBUNB, USAGE=A10, ACTUAL=A10,
TITLE='Parent,period', DESCRIPTION='Parent period',
REFERENCE=CLEHCD, PROPERTY=PARENT_OF, $
FIELDNAME=CLTENB, ALIAS=CLTENB, USAGE=P8, ACTUAL=P4,
TITLE='Prd str mbr,str fld hdg', DESCRIPTION='Prd str mbr str fld hdg', $
FIELDNAME=CLL4NB, ALIAS=CLL4NB, USAGE=P6, ACTUAL=P3,
TITLE='Period str,member no', DESCRIPTION='Period str member no', $
FIELDNAME=CLL5NB, ALIAS=CLL5NB, USAGE=P6, ACTUAL=P3,
TITLE='Last mbr in,period family', DESCRIPTION='Last mbr in period family', $
FIELDNAME=CLCEST, ALIAS=CLCEST, USAGE=A1, ACTUAL=A1,
TITLE='Record,status', DESCRIPTION='Record status', $
FIELDNAME=CLDDNB, ALIAS=CLDDNB, USAGE=P12, ACTUAL=P6,
TITLE='Audit stamp:,record sts', DESCRIPTION='Audit stamp: record sts', $
FIELDNAME=CLC4NB, ALIAS=CLC4NB, USAGE=P12, ACTUAL=P6,
TITLE='Audit,stamp: entry', DESCRIPTION='Audit stamp: entry', $
FIELDNAME=CLDCNB, ALIAS=CLDCNB, USAGE=P12, ACTUAL=P6,
TITLE='Audit stamp:,last change', DESCRIPTION='Audit stamp: last change', $


The actual values of the periods such as MAY08( not 200805 )are stored in the files, which I can not simply ask for a date range, but to rely on this structural file for all the periods to be reported. Idealy, I like to have a flat file consists of 2 fields: Period structure ID & Child Period, for joining the g/l transactions file.

Hua

I never used the FML on a period structure.
For a period structure I would expect an drill down like reporting.

The FML we use is on the GL structure like

mother        child
10000 sales   1100   photography
100   ...     1101   body
100   ...     1102   lenses
100   ...     1103   bags
10000 sales   1200  video cameras
.             1201
20000 cost of good sold
...           2100
30000 costs   600  personel
30000 ....    700  housings
...           800  travel expenses        

and the master would be something like

FILENAME=GLSYST, SUFFIX=DB2 ,
REMARKS='GL Hierarchy', $
SEGMENT=SEG1, SEGTYPE=S0, $
FIELDNAME=GLCODE, ALIAS=GLCODE, USAGE=A6, ACTUAL=A6, FIELDTYPE=I, $
FIELDNAME=GLREPNAME, ALIAS=GLREPNAME, USAGE=A40, ACTUAL=A40,
  TITLE='name account', REFERENCE=GLCODE, PROPERTY=CAPTION, $
FIELDNAME=GLGROUP, ALIAS=GLGROUP, USAGE=A10, ACTUAL=A10,
  TITLE='Parent,group', DESCRIPTION='Parent group',
  REFERENCE=GLCODE, PROPERTY=PARENT_OF, $

On the "date" hierarchy issue I also wanted to ad that if you have the transaction date in your records set up as a small date you can do this.
And suppose the field AMOUNT holds the value.
ACCOUNT is the account code
ACNAME is the ACCOUNT NAME

DEFINE FILE TRANSACTIONS
TRMONTH/tM=TRANSDATE;
TRQUARTER/YQ=TRANSDATE;
TRYEAR/YY/=TRANSDATE;
DEBETA/D12.2=IF AMOUNT GT 0 THEN AMOUNT ELSE 0;
CREDITA/D12.2=IF AMOUNT LT 0 THEN -1*AMOUNT ELSE 0;
END

TABLE FILE TRANSACTIONS
SUM DEBETA CREDITA
BY ACCOUNT
BY ACNAME
ACROSS HIGHEST TRYEAR
ACCROSS TRQUARTER
END

This would give you a very basic report

If you create the proper hierarchy you can add this to the report

TEXT "expenses" LABEL T1 OVER
TEXT "Salary" LABEL T2 OVER
'3100' GET CHILDREN ALL AS CAPTION LABEL R1 OVER
'3100' ADD ALL AS CAPTION LABEL R2 OVER
BAR "-" OVER
TEXT " " OVER
TEXT "Supplies" OVER
'3200' GET CHILDREN ALL AS CAPTION LABEL R1 OVER
'3200' ADD ALL AS CAPTION LABEL R2 OVER
'3300' GET CHILDREN ALL AS CAPTION LABEL R1 OVER
'3300' ADD ALL AS CAPTION LABEL R2 OVER
'3000' ADD ALL AS CAPTION LABEL R5

if this is your legend hierarchy.....

  
  child     description     parent
   3000      expenses       1000
   3100      salary         3000
   3200      supplies       3000
   3300      travel         3000
   4000      revenue        1000
   4100      internet       4000
   4200      mail order     4000  
   30000     executive      3100
   30001     administrative 3100
   30002     support        3100
   30003     computer       3200
   30004     paper          3200
   30005     local travel   3300
   30006     out of state   3300
   40000     company site   4100
   40001     other site     4100  
   40002     mail order     4200

you have to play with it a bit and search for the proper commands like "GET CHILDREN" and "WHEN EXIST"

good luck