Has anyone ever successfully burst a compound Excel report?
We have formatted an Excel spreadsheet to a customers specifications. The data comes from 2 different tables. The customer would like the data delivered in separate workbooks according to sub-departments and the sub-department is not the primary by field nor is it named the same in the 2 tables.

We have this formatted exactly to the customers specs but are stumped on how to deliver

If you feel like a challenge see below and feel free to be creative
-**
-*SET ECHO = ON
SET LINES = 9999
SET COMPOUND = OPEN
DEFINE FILE DW_SL_MON_BAL
SUB_DEPT_NAME/A50=
IF FS054 EQ '01' THEN 'DEANS OFFICE' ELSE
IF FS054 EQ '05' THEN 'Business-Finance' ELSE
IF FS054 EQ '10' THEN 'Business-Human Resources' ELSE
IF FS054 EQ '15' THEN 'Business-IR-Data Reporting' ELSE
IF FS054 EQ '20' THEN 'Business-Facilities' ELSE
IF FS054 EQ '21' THEN 'Instructional and Informational Systems' ELSE
IF FS054 EQ '22' THEN 'Other Resources' ELSE
IF FS054 EQ '25' THEN 'External Affairs' ELSE
IF FS054 EQ '30' THEN 'Communications/Public Relations' ELSE
IF FS054 EQ '35' THEN 'Global Health' ELSE
IF FS054 EQ '40' THEN 'Office of Student Affairs' ELSE
IF FS054 EQ '45' THEN 'Office of Research' ELSE
IF FS054 EQ '50' THEN 'Institutional Review Board' ELSE
IF FS054 EQ '55' THEN 'Academic Affairs' ELSE
IF FS054 EQ '60' THEN 'CEPH' ELSE
IF FS054 EQ '65' THEN 'Faculty Mentoring' ELSE
IF FS054 EQ '70' THEN 'Diversity' ELSE
IF FS054 EQ '75' THEN 'Deans Commitments' ELSE
IF FS054 EQ '80' THEN 'Student Aid' ELSE
IF FS054 EQ '85' THEN 'Public Health Solutions' ELSE
IF FS054 EQ '90' THEN 'Distance Education' ELSE
IF FS054 EQ '91' THEN 'Tuition Receipts' ELSE
IF FS054 EQ ' ' THEN 'Missing Sub Department';
END
TABLE FILE DW_SL_MON_BAL
SUM
FS948 AS 'Budget To Date'
FS956 AS 'Current YTD Expenditures'
COMPUTE SUB_TOT_BAL/D20.2 = FS948 - FS956; AS 'SubTotal Balance'
ENCUM_AMT AS 'Encumbrances'
COMPUTE TOT_BAL/D20.2 = SUB_TOT_BAL - ENCUM_AMT; AS 'Total Balance'
BY FS054 AS 'Sub Dept Number'
BY FS040 AS 'Account Name - Short'
BY FS008 AS 'Account Number'
ON FS054 SUBTOTAL AS 'TOTAL SUB DEPT'
ON FS054 PAGE-BREAK
HEADING
"&DATEtrMDYY <+0> "
"FOOTING
""
WHERE FS897 EQ '4601';
WHERE ( FS914 GE '1000' ) AND ( FS914 LE '6999' );
WHERE ACCT_BAL_TYP EQ 'M';
WHERE ( CALENDAR_MM EQ 8 );
WHERE ( FISCAL_YR EQ '2009' );
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL' FS948 SUB_TOT_BAL FS956 ENCUM_AMT TOT_BAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
TITLETEXT= 'Summary (Sub Dept)',
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=N1,
COLOR=RGB(0 51 0),
FOCEXEC=app/summary_report1_revenue1121(FS054=N1),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N1,
COLOR=RGB(0 51 0),
FOCEXEC=app/summary_report1_revenue1121(FS054=N1),
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
$
TYPE=ACROSSTITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
STYLE=BOLD,
$
ENDSTYLE
END
-RUN
DEFINE FILE DW_SL_MON_BAL
SUB_DEPT_NAME/A50=
IF FS054 EQ '01' THEN 'DEANS OFFICE' ELSE
IF FS054 EQ '05' THEN 'Business-Finance' ELSE
IF FS054 EQ '10' THEN 'Business-Human Resources' ELSE
IF FS054 EQ '15' THEN 'Business-IR-Data Reporting' ELSE
IF FS054 EQ '20' THEN 'Business-Facilities' ELSE
IF FS054 EQ '21' THEN 'Instructional and Informational Systems' ELSE
IF FS054 EQ '22' THEN 'Other Resources' ELSE
IF FS054 EQ '25' THEN 'External Affairs' ELSE
IF FS054 EQ '30' THEN 'Communications/Public Relations' ELSE
IF FS054 EQ '35' THEN 'Global Health' ELSE
IF FS054 EQ '40' THEN 'Office of Student Affairs' ELSE
IF FS054 EQ '45' THEN 'Office of Research' ELSE
IF FS054 EQ '50' THEN 'Institutional Review Board' ELSE
IF FS054 EQ '55' THEN 'Academic Affairs' ELSE
IF FS054 EQ '60' THEN 'CEPH' ELSE
IF FS054 EQ '65' THEN 'Faculty Mentoring' ELSE
IF FS054 EQ '70' THEN 'Diversity' ELSE
IF FS054 EQ '75' THEN 'Deans Commitments' ELSE
IF FS054 EQ '80' THEN 'Student Aid' ELSE
IF FS054 EQ '85' THEN 'Public Health Solutions' ELSE
IF FS054 EQ '90' THEN 'Distance Education' ELSE
IF FS054 EQ '91' THEN 'Tuition Receipts' ELSE
IF FS054 EQ ' ' THEN 'Missing Sub Department';
OBJCD_DEF1/A1=
IF FS914 GE '0100' AND FS914 LE '0999' THEN 'Y' ELSE 'N';
END
TABLE FILE DW_SL_MON_BAL
SUM
FS948 AS 'Budget To Date'
FS956 AS 'Current YTD Expenditures'
COMPUTE SUB_TOT_BAL/D20.2 = FS948 - FS956; AS 'SubTotal Balance'
ENCUM_AMT AS 'Encumbrances'
COMPUTE TOT_BAL/D20.2 = SUB_TOT_BAL - ENCUM_AMT; AS 'Total Balance'
BY OBJ_LVL3 SUBTOTAL MULTILINES AS 'Object Category' AS '*TOTAL'
HEADING
"&DATEtrMDYY <+0> "
"REVENUE 0100 TO 0999 Month FOOTING
""
WHERE FS897 EQ '4601';
WHERE ACCT_BAL_TYP EQ 'M';
WHERE ( CALENDAR_MM EQ 8 );
WHERE ( FISCAL_YR EQ '2009' );
WHERE OBJCD_DEF1 EQ 'Y';
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
TITLETEXT= 'Summary By Object',
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
$
TYPE=ACROSSTITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
ENDSTYLE
END
-RUN
DEFINE FILE DW_SL_MON_BAL
OBJCD_DEF2/A1=
IF FS914 GE '1000' AND FS914 LE '1999' THEN 'Y' ELSE 'N';
END
TABLE FILE DW_SL_MON_BAL
SUM
FS948 AS 'Budget To Date'
FS956 AS 'Current YTD Expenditures'
COMPUTE SUB_TOT_BAL/D20.2 = FS948 - FS956; AS 'SubTotal Balance'
ENCUM_AMT AS 'Encumbrances'
COMPUTE TOT_BAL/D20.2 = SUB_TOT_BAL - ENCUM_AMT; AS 'Total Balance'
BY OBJ_LVL3 SUBTOTAL MULTILINES AS 'Object Category' AS '*TOTAL'
HEADING
"&DATEtrMDYY <+0> "
"PERSONNEL 1000 TO 1999 Month FOOTING
""
WHERE FS897 EQ '4601';
WHERE ACCT_BAL_TYP EQ 'M';
WHERE ( CALENDAR_MM EQ 8 );
WHERE ( FISCAL_YR EQ '2009' );
WHERE OBJCD_DEF2 EQ 'Y';
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
TITLETEXT= 'Summary By Object',
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
$
TYPE=ACROSSTITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
ENDSTYLE
END
-RUN
DEFINE FILE DW_SL_MON_BAL
OBJCD_DEF3/A1=
IF FS914 GE '2000' AND FS914 LE '6999' THEN 'Y' ELSE 'N';
END
TABLE FILE DW_SL_MON_BAL
SUM
FS948 AS 'Budget To Date'
FS956 AS 'Current YTD Expenditures'
COMPUTE SUB_TOT_BAL/D20.2 = FS948 - FS956; AS 'SubTotal Balance'
ENCUM_AMT AS 'Encumbrances'
COMPUTE TOT_BAL/D20.2 = SUB_TOT_BAL - ENCUM_AMT; AS 'Total Balance'
BY OBJ_LVL3 SUBTOTAL MULTILINES AS 'Object Category' AS '*TOTAL'
HEADING
"&DATEtrMDYY <+0> "
"NON PERSONNEL 2000 TO 6999 Month FOOTING
""
WHERE FS897 EQ '4601';
WHERE ACCT_BAL_TYP EQ 'M';
WHERE ( CALENDAR_MM EQ 8 );
WHERE ( FISCAL_YR EQ '2009' );
WHERE OBJCD_DEF3 EQ 'Y';
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
TITLETEXT= 'Summary By Object',
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
$
TYPE=ACROSSTITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
ENDSTYLE
END
-RUN
DEFINE FILE DW_SL_MON_BAL
OBJCD_DEF3/A1=
IF FS914 GE '2000' AND FS914 LE '6999' THEN 'Y' ELSE 'N';
OBJCD_DEF2/A1=
IF FS914 GE '1000' AND FS914 LE '1999' THEN 'Y' ELSE 'N';
OBJCD_DEF4/A1= ' '
END
TABLE FILE DW_SL_MON_BAL
SUM
OBJCD_DEF4 AS ' '
FS948 AS 'Budget To Date'
FS956 AS 'Current YTD Expenditures'
COMPUTE SUB_TOT_BAL/D20.2 = FS948 - FS956; AS 'SubTotal Balance'
ENCUM_AMT AS 'Encumbrances'
COMPUTE TOT_BAL/D20.2 = SUB_TOT_BAL - ENCUM_AMT; AS 'Total Balance'
HEADING
"&DATEtrMDYY <+0> "
"TOTAL NON PERSONNEL AND PERSONNEL Month FOOTING
""
WHERE FS897 EQ '4601';
WHERE ACCT_BAL_TYP EQ 'M';
WHERE ( CALENDAR_MM EQ 8 );
WHERE ( FISCAL_YR EQ '2009' );
WHERE (OBJCD_DEF3 EQ 'Y') OR (OBJCD_DEF2 EQ 'Y');
ON TABLE SET PAGE-NUM OFF
ON TABLE SUMMARIZE
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
TITLETEXT= 'Summary By Object',
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
$
TYPE=ACROSSTITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
ENDSTYLE
END
-RUN
SET LINES = 99999
DEFINE FILE DWTRANS
OBJC_DEF5/A1=
IF OBJ_CD GE '0100' AND OBJ_CD LE '0999' THEN 'Y' ELSE 'N';
END
TABLE FILE DWTRANS
PRINT
TTRANS_TYP_DESC AS 'Transaction,Type,Desc'
TRANS_DESC AS 'Transaction Description'
TRANS_AMT AS 'Current YTD Expenditures'
TRANS_REF1 NOPRINT AS 'Reference 1'
TRANS_REF2 NOPRINT AS 'Reference 2'
BY OBJ_LVL3 SUB-TOTAL AS 'Object Category' AS '*TOTAL'
BY TRANS_DATE AS 'Transaction,Date'
HEADING
"&DATEtrMDYY <+0> "
"TOTAL REVENUE"
FOOTING
""
WHERE ( ACCT_DEPT_NBR EQ '4601' );
WHERE TF_FISCAL_YR EQ 2009;
WHERE OBJC_DEF5 EQ 'Y';
WHERE (TTRANS_TYP IS-FROM '030' TO '049') OR (TTRANS_TYP IS-FROM '060' TO '069');
WHERE GL_SL_IND EQ 'SL';
ON TABLE SET PAGE-NUM OFF
ON TABLE SUMMARIZE
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
TITLETEXT= 'Summary By Transaction',
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
$
TYPE=ACROSSTITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
ENDSTYLE
END
-RUN
DEFINE FILE DWTRANS
OBJC_DEF6/A1=
IF OBJ_CD GE '1000' AND OBJ_CD LE '1999' THEN 'Y' ELSE 'N';
END
TABLE FILE DWTRANS
PRINT
TTRANS_TYP_DESC AS 'Transaction,Type,Desc'
TRANS_DESC AS 'Transaction Description'
TRANS_AMT AS 'Current YTD Expenditures'
TRANS_REF1 NOPRINT AS 'Reference 1'
TRANS_REF2 NOPRINT AS 'Reference 2'
BY OBJ_LVL3 SUB-TOTAL AS 'Object Category' AS '*TOTAL'
BY TRANS_DATE AS 'Transaction,Date'
HEADING
"TOTAL PERSONNEL EXPENSES"
FOOTING
""
WHERE ( ACCT_DEPT_NBR EQ '4601' );
WHERE TF_FISCAL_YR EQ 2009;
WHERE OBJC_DEF6 EQ 'Y';
WHERE (TTRANS_TYP IS-FROM '030' TO '049') OR (TTRANS_TYP IS-FROM '060' TO '069');
WHERE GL_SL_IND EQ 'SL';
ON TABLE SET PAGE-NUM OFF
ON TABLE SUMMARIZE
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
TITLETEXT= 'Summary By Transaction',
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
$
TYPE=ACROSSTITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
ENDSTYLE
END
-RUN
DEFINE FILE DWTRANS
OBJC_DEF7/A1=
IF OBJ_CD GE '2000' AND OBJ_CD LE '6999' THEN 'Y' ELSE 'N';
END
TABLE FILE DWTRANS
PRINT
TTRANS_TYP_DESC AS 'Transaction,Type,Desc'
TRANS_DESC AS 'Transaction Description''
TRANS_AMT AS 'Current YTD Expenditures'
TRANS_REF1 NOPRINT AS 'Reference 1'
TRANS_REF2 NOPRINT AS 'Reference 2'
BY OBJ_LVL3 SUB-TOTAL AS 'Object Category' AS '*TOTAL'
BY TRANS_DATE AS 'Transaction,Date'
HEADING
"TOTAL NON PERSONNEL EXPENSES"
FOOTING
""
WHERE ( ACCT_DEPT_NBR EQ '4601' );
WHERE TF_FISCAL_YR EQ 2009;
WHERE OBJC_DEF7 EQ 'Y';
WHERE (TTRANS_TYP IS-FROM '030' TO '049') OR (TTRANS_TYP IS-FROM '060' TO '069');
WHERE GL_SL_IND EQ 'SL';
ON TABLE SET PAGE-NUM OFF
ON TABLE SUMMARIZE
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
TITLETEXT= 'Summary By Transaction',
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
$
TYPE=ACROSSTITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
ENDSTYLE
END
-RUN
SET COMPOUND = CLOSE
DEFINE FILE DWTRANS
OBJC_DEF6/A1=
IF OBJ_CD GE '1000' AND OBJ_CD LE '1999' THEN 'Y' ELSE 'N';
OBJC_DEF7/A1=
IF OBJ_CD GE '2000' AND OBJ_CD LE '6999' THEN 'Y' ELSE 'N';
OBJC_DEF8/A1= ' ';
END
TABLE FILE DWTRANS
PRINT
TTRANS_TYP_DESC AS 'Transaction,Type,Desc'
TRANS_DESC AS 'Transaction Description'
TRANS_AMT AS 'Current YTD Expenditures'
TRANS_REF1 NOPRINT AS 'Reference 1'
TRANS_REF2 NOPRINT AS 'Reference 2'
BY OBJ_LVL3 SUB-TOTAL MULTILINES AS 'Object Category' AS '*TOTAL'
BY TRANS_DATE AS 'Transaction,Date'
HEADING
"&DATEtrMDYY <+0> "
"TOTAL NON PERSONNEL AND PERSONNEL"
FOOTING
""
WHERE ( ACCT_DEPT_NBR EQ '4601' );
WHERE (TF_FISCAL_YR EQ '2009');
WHERE (OBJC_DEF6 EQ 'Y') OR (OBJC_DEF7 EQ 'Y');
WHERE (TTRANS_TYP IS-FROM '030' TO '049') OR (TTRANS_TYP IS-FROM '060' TO '069');
WHERE GL_SL_IND EQ 'SL';
ON TABLE SET PAGE-NUM OFF
ON TABLE SUMMARIZE
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
TITLETEXT= 'Summary By Transaction',
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
$
TYPE=ACROSSTITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
ENDSTYLE
END

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

809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML

Geoff

You start this report with a long define SUB_DEPT_NAME. This field is not used in the report so why?
If you need that field I would suggest to use the formula DECODE, it is shorter and therefor better to handle.
And you do the same define command again, while you use the same table.

Put all the defines in the first part, unless you need to use the same defined FIELDNAME with other formulas, it makes the code better readable.

Frank:
The FSO54 is a numeric value that the customer wanted displayed as an alpha value. We chose to use a define to decode the 2 digit number as a more readable description because the sub- department name was not described in the table as as an alpha. there was probably a better way to do this with dialogue manager or calling some subroutine but we were rushed and did not want to give upon it. The report displays perfectly and reconciles across all 3 tabs. we just can't figure out how to deliver it to the responsible parties. Thanks for the response. I knew this would be a real long shot.

Geoff

If there is no relation between the several sheets based upon one or other field you can use as burst field it will be difficult, but if there is a relation I'm sure it can be done.
We needed however to know a bit more about the structure.
Is there somewhere a hidden link?
Can you convert one or other field in the second part that comes from the DWTRANS tables that meets to the department or sub_departments code?
Is the information that comes from the DWTRANS table needed in the same worksheet or can it be send as a separate worksheet?
(So you send each department two sheets, one as a burst report bases on their special FS054 code and the other as a non burst report)

Frank we found that if we set
WHERE FS054 EQ '&FS054.Enter Sub Department.'; on the first table and then
WHERE ACCT_DEPT_SUB_DEPT EQ '&FS054'; on the second table it seems to work.
Even though the fields are named differently in the 2 tables they contain the same values that is they are both subdepts. We are looking but the initial 2 subdepts we looked at balance across all 3 tabs.

To answer your question about delivery we can run one report for entire dept and deliver to one person to redistribute for now