I am trying to create the below hierarchy report (not sure it show up very well) from the Banner FTVORGN table. Seems like FML should handle it but I'm not getting anywhere.
I've created a test synonym and added the PARENT OF and REFERENCE= parameters, and added the FOR/WITH parameter in the focexec, but am not getting close.
1. If I put a '1' or a 'C' for the FTVORGN_ORGN_CODE_PRED I only get one level
Does that mean I need to specify ALL the Account codes in FOR/OVER statements?
2. If this can be done can it be done dynamically by only specifying the highest level needed or null and get them all?

 TABLE FILE RJM_FTVORGN
PRINT
FTVORGN_ORGN_CODE FTVORGN_ORGN_CODE_PRED
FTVORGN_TITLE FTVORGN_COAS_CODE
FOR FTVORGN_ORGN_CODE_PRED
'1' WITH CHILDREN ALL
END 

 FILENAME=RJM_FTVORGN, SUFFIX=SQLORA  ,
 REMARKS='Organization Validation Table', $
  SEGMENT=FTVORGN, SEGTYPE=S0, $
    FIELDNAME=FTVORGN_COAS_CODE, ALIAS=FTVORGN_COAS_CODE, USAGE=A1, ACTUAL=A1,
      DESCRIPTION='CHART OF ACCOUNTS CODE:  The primary identification code for any chart of       accounts which is used to uniquely identify that chart from any other in a      multi-chart environment.', 
      PROPERTY=PARENT_OF, REFERENCE=FTVORGN_ORGN_CODE, $
$
    FIELDNAME=FTVORGN_ORGN_CODE, ALIAS=FTVORGN_ORGN_CODE, USAGE=A6, ACTUAL=A6,
      DESCRIPTION='ORGAINZATION CODE:  Indentifies the individual organization code which appears  on a transaction.',
          PROPERTY=PARENT_OF, REFERENCE=FTVORGN_ORGN_CODE_PRED, $
$
    FIELDNAME=FTVORGN_EFF_DATE, ALIAS=FTVORGN_EFF_DATE, USAGE=YYMD, ACTUAL=DATE,
      DESCRIPTION='EFFECTIVE DATE:  The effective date of this particular record.', $
    FIELDNAME=FTVORGN_NCHG_DATE, ALIAS=FTVORGN_NCHG_DATE, USAGE=YYMD, ACTUAL=DATE,
      DESCRIPTION='NEXT CHANGE DATE:  The change date  for this particular record.  I.E.. if the   record included a termination date, the next change date would reflect the      date that the termination date was entered.  Requires a future change record.', $ 

ORGANIZATION DESCRIPTION ENTRY STATUS ORGN CMB EFF TERM NEXT CHANGE
1 FSU Chart N A 30-JUN-1950 31-DEC-2099
A Gen Division N A 30-JUN-1950 31-DEC-2099
10 Gen Division N A 30-JUN-1950 31-DEC-2099
100 Revenue N A 30-JUN-1950 31-DEC-2099
1000 Tuition N A 30-JUN-1950 31-DEC-2099
10000 On-Campus Tuition Y A 30-JUN-1950 31-DEC-2099
1001 State Appropriation N A 20-JAN-2006 20-JAN-2006 31-DEC-2099
10008 State Appropriation Y A 20-JAN-2006 20-JAN-2006 31-DEC-2099
1002 Other GF Revenue N A 30-JUN-1950 31-DEC-2099
10020 Other GF Revenue Y A 30-JUN-1950 31-DEC-2099
110 General Division Expenditures N A 30-JUN-1950 31-DEC-2099
1100 General Division Expenditures N A 30-JUN-1950 31-DEC-2099
11000 Utilities Y A 19-OCT-2005 31-DEC-2099
1110 General Expenses N A 16-JUN-2005 31-DEC-2099
11001 Insurance Y A 19-OCT-2005 31-DEC-2099
11002 Credit Card Charges Y A 16-JUN-2005 31-DEC-2099
11003 Financial Aid Admin Y A 19-OCT-2005 31-DEC-2099
11004 Debts-Rentals-Transfers Y A 16-JUN-2005 31-DEC-2099
11005 Sick Leave Payout Y A 16-JUN-2005 31-DEC-2099
11006 Resignation Incentive/Buyout Y A 16-JUN-2005 31-DEC-2099
11007 Pooled Comp Y A 16-JUN-2005 31-DEC-2099
11008 Unall Gen Fund Salary Y A 16-JUN-2005 31-DEC-2099
11009 Insurance Work Comp Y A 01-MAY-2009 31-DEC-2099
11010 Computer/Technology Replacement Y A 01-FEB-2006 31-DEC-2099
11011 Scholarship Phase- In Y A 01-FEB-2006 31-DEC-2099
140 Plant Projects N A 30-JUN-1950 31-DEC-2099
1400 General Funded Plant Projects N A 30-JUN-1950 31-DEC-2099
14000 Minor Caps Y A 19-OCT-2005 31-DEC-2099
14001 Deferred Maint Y A 19-OCT-2005 31-DEC-2099
14002 Deferred Maint Y A 19-OCT-2005 31-DEC-2099
14003 Other Funded Projects Y A 19-OCT-2005 31-DEC-2099
14004 Fixed Assets Y A 30-JUN-1950 31-DEC-2099
14005 Walks and Roads Y A 15-AUG-2005 31-DEC-2099
14006 Plant - Renovations Y A 01-FEB-2006 31-DEC-2099
1410 Aux Funded Projects N A 30-JUN-1950 31-DEC-2099
14100 Aux Funded Projects Y A 19-OCT-2005 31-DEC-2099
150 Plant Capitalized N A 30-JUN-1950 31-DEC-2099
1500 Plant Capitalized N A 30-JUN-1950 31-DEC-2099
15000 Plant Capitalized Y A 30-JUN-1950 31-DEC-2099
B Executive N A 30-JUN-1950 31-DEC-2099
21 Presidents Office N A 30-JUN-1950 31-DEC-2099
210 Presidents Office N A 30-JUN-1950 31-DEC-2099
2100 Presidents Office N A 30-JUN-1950 31-DEC-2099
21000 Presidents Office Y A 30-JUN-1950 31-DEC-2099
21000C Presidents Office Carryover Y A 01-APR-2010 31-DEC-2099
21001 Board of Trustees Y A 30-JUN-1950 31-DEC-2099
21001C Board of Trustees Carryover Y A 01-APR-2010 31-DEC-2099
21002 Summer Univer/Leadership Y A 28-JUL-2005 31-DEC-2099  

This message has been edited. Last edited by: Kerry,

quote:

FOR FTVORGN_ORGN_CODE_PRED

You are using FTVORGN_ORGN_CODE_PRED, but I cannot find it in the Master File you posted.

For this to work you must have a Child field, and a Parent Field.

Oops

FILENAME=RJM_FTVORGN, SUFFIX=SQLORA  ,
 REMARKS='Organization Validation Table', $
  SEGMENT=FTVORGN, SEGTYPE=S0, $
    FIELDNAME=FTVORGN_COAS_CODE, ALIAS=FTVORGN_COAS_CODE, USAGE=A1, ACTUAL=A1,
      DESCRIPTION='CHART OF ACCOUNTS CODE:  The primary identification code for any chart of       accounts which is used to uniquely identify that chart from any other in a      multi-chart environment.',
      PROPERTY=PARENT_OF, REFERENCE=FTVORGN_ORGN_CODE, $
$
    FIELDNAME=FTVORGN_ORGN_CODE, ALIAS=FTVORGN_ORGN_CODE, USAGE=A6, ACTUAL=A6,
      DESCRIPTION='ORGAINZATION CODE:  Indentifies the individual organization code which appears  on a transaction.',
          PROPERTY=PARENT_OF, REFERENCE=FTVORGN_ORGN_CODE_PRED, $
$
    FIELDNAME=FTVORGN_EFF_DATE, ALIAS=FTVORGN_EFF_DATE, USAGE=YYMD, ACTUAL=DATE,
      DESCRIPTION='EFFECTIVE DATE:  The effective date of this particular record.', $
    FIELDNAME=FTVORGN_NCHG_DATE, ALIAS=FTVORGN_NCHG_DATE, USAGE=YYMD, ACTUAL=DATE,
      DESCRIPTION='NEXT CHANGE DATE:  The change date  for this particular record.  I.E.. if the   record included a termination date, the next change date would reflect the      date that the termination date was entered.  Requires a future change record.', $
    FIELDNAME=FTVORGN_ACTIVITY_DATE, ALIAS=FTVORGN_ACTIVITY_DATE, USAGE=YYMD, ACTUAL=DATE,
      DESCRIPTION='ACTIVITY DATE:  The date when the information for a record on a table was       entered or last updated.', $
    FIELDNAME=FTVORGN_USER_ID, ALIAS=FTVORGN_USER_ID, USAGE=A30, ACTUAL=A30,
      DESCRIPTION='USER IDENTIFICATION:  The unique identification code of the user.', $
    FIELDNAME=FTVORGN_TERM_DATE, ALIAS=FTVORGN_TERM_DATE, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON,
      DESCRIPTION='TERMINATION DATE:  The  date when this particular record is no longer in        effect.', $
    FIELDNAME=FTVORGN_TITLE, ALIAS=FTVORGN_TITLE, USAGE=A35, ACTUAL=A35,
      DESCRIPTION='TITLE:  THe hierarchy description or title appropriate for this level.', $
    FIELDNAME=FTVORGN_STATUS_IND, ALIAS=FTVORGN_STATUS_IND, USAGE=A1, ACTUAL=A1,
      DESCRIPTION='STATUS INDICATOR:  The current status of the associated validation table        record.', $
$
    FIELDNAME=FTVORGN_ORGN_CODE_PRED, ALIAS=FTVORGN_ORGN_CODE_PRED, USAGE=A6, ACTUAL=A6,
      MISSING=ON,
      DESCRIPTION='PREDECESSOR ORGANIZATION CODE:  The value of the next higher organizational     level unless this is level 1.',$
$     PROPERTY=PARENT_OF, REFERENCE=FTVORGN_ORGN_CODE, $
$  

If you are using ODS the following produces a nice report.

"PROD_FTVORGN" is not a delivered table in ODS. It is an extract from production. This a a report that I worked on with another employee.

  

SET ASNAMES = ON
DEFINE FILE PROD_FTVORGN
ORG_CD/A6=FTVORGN_ORGN_CODE;
END
TABLE FILE PROD_FTVORGN
PRINT
     FTVORGN_TITLE
     FTVORGN_DATA_ENTRY_IND
BY ORG_CD
BY HIGHEST 1 FTVORGN_NCHG_DATE NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_FTVORG FORMAT FOCUS INDEX 'ORG_CD'
ON TABLE SET HTMLCSS ON
END
JOIN
 ORGANIZATION_HIERARCHY.ORGANIZATION_HIERARCHY.ORGANIZATION_CODE IN
ORGANIZATION_HIERARCHY TO MULTIPLE HOLD_FTVORG.SEG01.ORG_CD IN HOLD_FTVORG AS J0
 END
TABLE FILE ORGANIZATION_HIERARCHY
PRINT
     ORGANIZATION_DESC AS 'Name of Org'
     FTVORGN_DATA_ENTRY_IND AS 'Data ,Entry'
     ORGANIZATION_STATUS AS 'Active/,Inactive'
     FMGR_FIRST_NAME AS 'FMGR,First Name'
     FMGR_LAST_NAME AS 'FMGR,Last Name'
BY ORGANIZATION_LEVEL_1 AS 'Level,    1'
BY ORGANIZATION_LEVEL_2 AS 'Level,    2'
BY ORGANIZATION_LEVEL_3 AS 'Level,    3'
BY ORGANIZATION_LEVEL_4 AS 'Level ,    4'
BY ORGANIZATION_LEVEL_5 AS 'Level,    5'
BY ORGANIZATION_LEVEL_6 AS 'Level,    6'
BY ORGANIZATION_LEVEL_7 AS 'Level ,    7'
BY ORGANIZATION_LEVEL_8 AS 'Level ,    8'
HEADING
"University of "
"Org Hierarchy"
"as of  <+0>&DATEMDYY <+0> "
FOOTING
"<TABPAGENO"
ON TABLE SUBFOOT
"Summary of Org Levels "
"Level 1 - University Level Rollup"
"Level 2 - Executive Division Rollup"
"Level 3 - Executive Subgroup Rollup"
"Level 4 - Dean/AVP Rollup"
"Level 5 - Director Rollup"
"Level 6 - Director Subgroup Rollup"
"Level 7 - Data Entry"
"Level 8 - Timesheet Entry"
" "
WHERE ORGANIZATION_STATUS EQ &REPORT_STATUS;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<PDF,PDF>,<HTML,HTML>,<Excel 2000,EXL2K>).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Letter',
     LEFTMARGIN=0.250000,
     RIGHTMARGIN=0.250000,
     TOPMARGIN=0.500000,
     BOTTOMMARGIN=0.000000,
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
     PAGECOLOR='WHITE',
$
TYPE=REPORT,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
     FONT='ARIAL',
     SIZE=9,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
TYPE=DATA,
     SIZE=8,
     BACKCOLOR=( RGB(208 208 208) 'WHITE' ),
$
TYPE=DATA,
     COLUMN=N1,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N2,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N3,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N4,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N5,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N6,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N7,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N8,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N9,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N10,
     FONT='ARIAL',
     SIZE=7,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N11,
     FONT='ARIAL',
     SIZE=7,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N12,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N13,
     FONT='ARIAL',
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLOR='WHITE',
     BACKCOLOR=RGB(38 100 175),
     STYLE=BOLD,
$
TYPE=TITLE,
     COLUMN=N1,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N2,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N3,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N4,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N5,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N6,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N7,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N8,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N9,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N10,
     FONT='ARIAL',
     SIZE=7,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N11,
     FONT='ARIAL',
     SIZE=7,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N12,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TITLE,
     COLUMN=N13,
     FONT='ARIAL',
     SIZE=8,
     COLOR='BLACK',
     STYLE=NORMAL,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=BOLD+UNDERLINE,
$
TYPE=HEADING,
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=MEDIUM,
     BORDER-LEFT=OFF,
     BORDER-RIGHT=OFF,
     BORDER-TOP-STYLE=OUTSET,
     BORDER-BOTTOM-STYLE=OUTSET,
     BORDER-LEFT-STYLE=RIDGE,
     BORDER-RIGHT-STYLE=RIDGE,
     BORDER-TOP-COLOR='SILVER',
     BORDER-BOTTOM-COLOR='SILVER',
     BORDER-LEFT-COLOR='SILVER',
     BORDER-RIGHT-COLOR='SILVER',
     SIZE=12,
     BACKCOLOR=RGB(224 224 224),
     STYLE=BOLD,
     JUSTIFY=CENTER,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=2,
     SIZE=8,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=10,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=2,
     SIZE=10,
$
TYPE=FOOTING,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=OFF,
     BORDER-RIGHT=OFF,
     SIZE=8,
     STYLE=BOLD,
     JUSTIFY=LEFT,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=MEDIUM,
     BORDER-LEFT=OFF,
     BORDER-RIGHT=OFF,
     BACKCOLOR=RGB(250 208 60),
$
TYPE=ACROSSVALUE,
     SIZE=8,
$
TYPE=ACROSSTITLE,
     SIZE=9,
     COLOR='WHITE',
     BACKCOLOR=RGB(38 100 175),
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=MEDIUM,
     BORDER-LEFT=OFF,
     BORDER-RIGHT=OFF,
     BORDER-TOP-STYLE=GROOVE,
     BORDER-BOTTOM-STYLE=GROOVE,
     COLOR='BLACK',
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
ENDSTYLE
END

Thanks Guy.
I found this MFD AF_ORGANIZATION_HIERARCHY in my production Master Files folder. It is in the ODSMGR schema. However this is just step one, because I was hoping to use the PARENTOF parameter in a roll up type report. Or maybe it needs to be a recursive JOIN. That's my next test.
If anybody has any ideas on how to do this it would be appreciated. Short of doing a gargantuan DEFINE for every lowest level entity.

Have your tried to run with the field FTVORGN_ORGN_CODE, instead of FTVORGN_ORGN_CODE_PRED?

TABLE FILE RJM_FTVORGN
PRINT
FTVORGN_ORGN_CODE FTVORGN_ORGN_CODE_PRED
FTVORGN_TITLE FTVORGN_COAS_CODE
FOR FTVORGN_ORGN_CODE
'1' WITH CHILDREN ALL
END

Yea I tried that but I'm still not getting what I want.
Maybe this a FOCUS database only feature, maybe the Banner table isn't set up to handle this.

No offense WAZ I know you are good. Maybe there is an IBIer out there that can get this to work, short of opening a case with them. That's next becasue it seems like it should be able to do this.

Becasue the advantage is the next step to JOIN to the tables that have the data in them and do the roll up reports.

I have to admit that almost all of my FML requests are from FOCUS files. Could you try TABLEing the file and HOLDing FORMAT FOCUS and Indexing the parent/child columns.

Format FOCUS only supports 12 character names for indexed fields, Format XFOCUS supports more.

I have posted on this previously. Hierarchy: your parent and child format must be identical. Your child must be indexed. I am not aware of being able to index a field outside of FOCUS. I will look for one I have done in the past and post it if I can or work on yours. But change your formats and index and do not put your number within quotes on your hierarchy statement.

Hi, the child field needs to be indexed and the field formats for the parent and child must be identical and each child can only have one parent. Your formats do not match nor did you index the child field. I personally have always used a FOCUS file to hold the data for a hierarchy.
[quote]FIELDNAME=GL_ACCOUNT, ALIAS=GLACCT, FORMAT=A7,
TITLE='Ledger,Account', FIELDTYPE=I, $
FIELDNAME=GL_ACCOUNT_PARENT, ALIAS=GLPAR, FORMAT=A7,
TITLE=Parent,
PROPERTY=PARENT_OF, REFERENCE=GL_ACCOUNT, $
quote]

Thanks Kathy.
I opened a case with IBI. They gave me an anlaysis fex. So now I have the data right, it has to follow all those rules, but am still getting only one row. I'll let you know all the details when we get it figured out.