Is there a way to include the account detail in FML output that is then sorted/subtotaled at the summary levels in the hierarchy?

Thanks,
Bethany

Bethany

I'm not quiet sure about your question. would you be so kind to try to explain it with an example?

Beth

Think you want something like this.

FOR
SPR_SUB_PROJECT_ID
'&SUB_PROJECT_ID' WITH CHILDREN ADD AS CAPTION LABEL R1

The ADD does the trick...

Here'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

Bethany,

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.

Bethany

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.

I 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

I 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'

quote:

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.