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'

WebFOCUS 7.6.4

Windows XP against an Oracle Database mainly SCT Banner Higher Education ODS and EDW

Various output formats

Guy Brenckle

Budget Analyst

University of Northern Colorado