May 15, 2008, 10:17 AM
HuaWebFOCUS 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.
May 16, 2008, 10:16 AM
HuaFrank,
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.
May 17, 2008, 02:00 AM
FrankDutchHua
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, $
May 17, 2008, 02:53 PM
FrankDutchOn 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