April 03, 2008, 09:30 AM
BethanyHere's an example of what I'm trying to do.
If you have a hierarchy like this:
child descriptin parent
3000 expenses 1000
3100 salary 3000
3200 supplies 3000
3300 travel 3000
4000 revenue 1000
4100 internet 4000
4200 mail order 4000
And then you have account codes that are tied to the various children like this.
account description child
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
Then the output would have:
Expenses
Salary
30001 Executive $1,400,000
30002 Administrative 1,000,000
30003 Support 2,000,000
Total Salary $4,400,000
Supplies
30004 Computer $ 50,000
30005 Paper 5,000
Total Supplies $ 55,000
Travel
30006 Local Travel $ 7,500
30007 Out of State 20,000
Total Travel $ 27,500
Total Expenses $4,482,500
Revenue
Internet
40000 Company site $5,000,000
40001 Other site 2,000,000
Total Internet $7,000,000
Mail Order
40002 Mail Order $1,500,000
Total Mail Order $1,500,000
Total Revenue $8,500,000
I hope this helps explain my question.
Thanks,
Bethany
April 03, 2008, 11:42 AM
Darin LeeBethany,
What you probably want to do is handle all of this with the FML syntax like:
'3100' GET CHILDREN ALL AS CAPTION LABEL R1 OVER
'3100' ADD ALL AS CAPTION LABEL R2 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
You can use BY and SUBTOTAL in combination with FML, but it gets comfusing sometimes because your accounts are all at the same level, but the "rollup" accounts only exist within the hierarchy. You could do some magic with defines and account ranges, but that's basically what the FML hierarchy functionality is giving you.
April 03, 2008, 02:32 PM
FrankDutchBethany
Darin posted a code that does a lot of what you want.
If you do it that way, you will get your report but without the account codes, and without the labels.
I used Darin's code but added some extra.
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
There is more but I do not all know it by hart, I will take a look tomorrow and see what I can bring up more.
April 03, 2008, 05:47 PM
BethanyI haven't had a chance to look try your solutions yet, but I was wondering if I could merge the account detail into the account hierarchy by making "account" the child and "child" the parent in the hierarchy table.
Like this:
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
Does this make sense?
Bethany
April 04, 2008, 08:16 AM
GuyI have just been working on a request that puts accounts within customized groups that have very little to do with how our account hiearchy is set up.
You can do define magic as Darin mentioned. FML may have worked but I did it with defines. I haven't worked with the FML much and I just went "define crazy".
Attached is the code I used to customize the groupings and sub totals for the report I am working on.
DEFINE FILE UNC_ORACLE_ID_FUND_ORG
ACCT_REV_EXP/A20=IF ACCOUNT_TYPE_LEVEL_1 LT '60' THEN '5' ELSE '7';
ACCT_REV_EXP_DESC/A30=IF ACCOUNT_TYPE_LEVEL_1 LT '60' THEN 'REVENUES' ELSE 'EXPENDITURES/TRANSFERS ';
EXPGRP/A2=IF ACCOUNT_LEVEL_2 EQ '51000' THEN '5A'
ELSE IF ACCOUNT_LEVEL_2 EQ '51100' THEN '5B'
ELSE IF ACCOUNT_LEVEL_2 FROM '51200' TO '51599' THEN '5C'
ELSE IF ACCOUNT_LEVEL_2 EQ '53010' THEN '5D'
ELSE IF ACCOUNT_LEVEL_2 EQ '51900' THEN '5E'
ELSE IF ACCOUNT_LEVEL_2 EQ '51600' THEN '5F'
ELSE IF ACCOUNT_LEVEL_2 EQ '51800' THEN '5G'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '52' OR '53' OR '54' AND ACCOUNT_LEVEL_2 NE '53010' THEN '5H'
ELSE IF ACCOUNT_LEVEL_4 EQ '56110' THEN '5I'
ELSE IF ACCOUNT_LEVEL_2 EQ '56000' AND ACCOUNT_LEVEL_4 NE '56110' THEN '5J'
ELSE IF ACCOUNT_LEVEL_1 EQ '581' AND ACCOUNT_LEVEL_2 NE '58530' THEN '5K'
ELSE IF ACCOUNT_LEVEL_1 EQ '57900' THEN '5L'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '55' OR '57' OR '58' AND ACCOUNT_LEVEL_1 NE '57900' OR ACCOUNT_LEVEL_1 EQ '57900' OR ACCOUNT_LEVEL_2 EQ '56600' OR '56700' OR '56800' THEN '5M'
ELSE IF ACCOUNT_LEVEL_2 EQ '61000' THEN '61'
ELSE IF ACCOUNT_LEVEL_2 EQ '62000' THEN '62'
ELSE IF ACCOUNT_LEVEL_2 EQ '63000' THEN '63'
ELSE IF ACCOUNT_LEVEL_2 EQ '64000' OR '65000' OR '66000' THEN '64'
ELSE IF ACCOUNT_LEVEL_1 EQ '680' THEN '68'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '71' THEN '71'
ELSE IF ACCOUNT_TYPE_LEVEL_1 EQ '70' AND ACCOUNT_TYPE_LEVEL_2 NE '77' OR '78' OR '79' AND ACCOUNT_LEVEL_1 NE '726' AND ACCOUNT_LEVEL_2 NE '72750' THEN '72'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '78' THEN '73'
ELSE IF ACCOUNT_LEVEL_2 EQ '72750' THEN '74'
ELSE IF ACCOUNT_LEVEL_1 EQ '726' THEN '75'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '77' THEN '77'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '79' THEN '79'
ELSE IF ACCOUNT_TYPE_LEVEL_2 EQ '91' THEN '91'
ELSE '80';
EXPGRP_LD/A39=IF EXPGRP EQ '5A' THEN 'Resident Tuition'
ELSE IF EXPGRP EQ '5B' THEN 'Non-Resident Tuition'
ELSE IF EXPGRP EQ '5C' THEN 'Other Tuition'
ELSE IF EXPGRP EQ '5D' THEN 'Fee For Service (COF)'
ELSE IF EXPGRP EQ '5E' THEN 'Long Bill Appropriated Fees'
ELSE IF EXPGRP EQ '5F' THEN 'Scholarship Allowance'
ELSE IF EXPGRP EQ '5G' THEN 'Student Fees'
ELSE IF EXPGRP EQ '5H' THEN 'Grants and Contracts'
ELSE IF EXPGRP EQ '5I' THEN 'Room and Board'
ELSE IF EXPGRP EQ '5J' THEN 'Other Auxiliary Services Revenue'
ELSE IF EXPGRP EQ '5K' THEN 'Investment Income'
ELSE IF EXPGRP EQ '5L' THEN 'Other Internal Income'
ELSE IF EXPGRP EQ '5M' THEN 'Other Revenue'
ELSE IF EXPGRP EQ '61' THEN 'Faculty Salaries'
ELSE IF EXPGRP EQ '62' THEN 'Exempt Salaries'
ELSE IF EXPGRP EQ '63' THEN 'Classified Salaries'
ELSE IF EXPGRP EQ '64' THEN 'Other Salaries'
ELSE IF EXPGRP EQ '68' THEN 'Fringe Benefits'
ELSE IF EXPGRP EQ '71' THEN 'Cost of Sales'
ELSE IF EXPGRP EQ '72' THEN 'Other Current Expense'
ELSE IF EXPGRP EQ '73' THEN 'Scholarships'
ELSE IF EXPGRP EQ '74' THEN 'Utilities'
ELSE IF EXPGRP EQ '75' THEN 'Risk Management'
ELSE IF EXPGRP EQ '77' THEN 'Travel'
ELSE IF EXPGRP EQ '79' THEN 'Capital'
ELSE IF EXPGRP EQ '80' THEN 'Other'
ELSE IF EXPGRP EQ '91' THEN 'Transfers';
END
TABLE FILE UNC_ORACLE_ID_FUND_ORG
SUM
BASE_BDGT
BY ACCT_REV_EXP NOPRINT
BY ACCT_REV_EXP_DESC NOPRINT
BY ACCOUNT_TYPE_LEVEL_1 NOPRINT
BY ACCOUNT_TYPE_DESC_1
BY EXPGRP
BY EXPGRP_LD
ACROSS FUND1 NOPRINT
ACROSS-TOTAL
ACROSS FUND1_LD AS ''
ACROSS FUND2 NOPRINT
ACROSS FUND2_LD AS ''
ON ACCT_REV_EXP_DESC SUBTOTAL AS '*TOTAL'
ON ACCOUNT_TYPE_DESC_1 SUBTOTAL AS '*TOTAL'
April 04, 2008, 09:09 AM
FrankDutchquote:
Does this make sense?
I think it does Bethany.
See if you can find something on the formulas FMLLIST('Ann') or FMLCAP('Ann') It might help you in some drill through cases.
If you build the report as mentioned above you will not see the accountcodes.
and for Guy....
I would do it the way you did.....if there was not FML. FML is invented for this.