Hi,
I looked through the subject lines and did not see what I am looking for. I have a compound report, not using the composer. The first report has an open and the last report has the close. The first report is in the fex and reports 2-5 are -includes. It is only the last one that will not come up. So I have commented out reports 2-4 and the fex for the first one runs and the second one still shows no data. However, when I run the last focexec stand alone, it runs and has data, 544 lines. So, I made everything on table hold instead of on table pchold so I could see what is going on. That is when I saw in the compound report, my second report is not returning any records. It almost sounds like it is out of memory. I tried clearing cache, etc and reopen webFOCUS and try it again to no avail.

Here is what follows my first report. ON TABLE PCHOLD FORMAT PDF CLOSE is also in the buildkey11.fex

-**************
-TYPE REPORT 2
-*INCLUDE APP/LEAD_SHEET1.FEX

-**************
-TYPE REPORT 3
-*INCLUDE APP/ODC_ODM_INCLUDE.FEX

-**************
-TYPE REPORT 4 Tonys labor report
-*INCLUDE app/hierarchy_template_tony1.fex

-**************
-TYPE REPORT 5 MATERIAL REPORT
-**** my problem child report
-INCLUDE app/buildkey11.fex

Any ideas? I am stumped.

Regards,
Kathy

It seems that Report one effects Report 5, could there be joins left behind from report 1.

This may cause 0 records to be returned.

You could add in report 5 include a JOIN CLEAR * and perhaps a DEFINE FILE ? CLEAR, in case a define is left behind.

Are the FEX's large, could you post them ?

Hi, Thanks for the quick response. I did issue a JOIN CLEAR * in the beginning of the last fex and it did not make a difference.

Regards,

Kathy

Not seeing the code makes it difficult.

Perhaps you have &variables used in a WHERE clause that may have been set in the first fex that could also cause this ?

Have you tried running Fex 5 first, then Fex 1 in the compound report ?

try SET EMPTYREPORT = ON?

To (possibly) verify that it's not an "out of memory" or "cache" problem, add a RECORDLIMIT of 5 (or so) and ensure that there something (like a time stamp) in your report heading that should force a new report instead of trying to use a cashed report... Just some food for thought...

Other problems could be duplicate DM labels.

Another thing to try is taking the COMPOUND statements out of PCHOLD statement of the included fexes and add them to the code stream -

SET COMPOUND = OPEN
TABLE FILE CAR
SUM RCOST
    DCOST
 BY COUNTRY
 BY CAR
 BY MODEL
ON TABLE PCHOLD FORMAT PDF
END
SET COMPOUND = NOBREAK
-RUN
-INCLUDE app/somereport.fex
SET COMPOUND = CLOSE
-RUN
-INCLUDE app/buildkey11.fex

One final question, are you running the main fex from MRE also?

T

Does this happen with COMPOUND only or does this also happen when running 'normal'?
If it also fails in normal mode, SET ECHO ALL and then try to find out what's wrong reading the resulting code that is actually being executed.
If it runs ok normally, then switch to HOLD iso PCHOLD, turn on echo and try to find out what's wrong ...

Thank you everyone for your responses. In order of posting:
1. The focexec is large.
2. running the last fex FIRST has given me output and I am bringing back the others one at a time.
3. set emptyreport=on gave me an empty report which told me I was not getting data for some odd reason in the compound
4. Memory: I commented out the where statements and put in a record limit in the fex and I did get my record limit of 10. When I brought back one where statement in addition to the record limit of 10, no data again.
5. I have no DM labels.
6. set compound=open before the fex and set compound=close after the last fex didn't do anything different for me.
7. I had changed my pchold to hold so I could see what was going on and that is when I saw, 0 records were being selected in the compound versus being run alone.

Do you want the trace or the code? I need to tell you my where statements are using the 'in file' which are all hold files I created from the hierarchy.

quote:

6. set compound=open before the fex and set compound=close after the last fex

Kathy,

SET COMPOUND = CLOSE needs to go before the final fex, and the final fex needs to produce an output. If the final fex does not produce anything then the compound is likely to fail.

T

I like to do the "ON TABLE PCHOLD FORMAT PDF OPEN" in my first fex and "ON TABLE PCHOLD FORMAT PDF CLOSE" in my last fex, like this (NOBREAK is optional):[/code]SET PAGE = OFF
TABLE FILE CAR
SUM DCOST RCOST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY LT 'ITALY'
ON TABLE PCHOLD FORMAT PDF OPEN NOBREAK
END
-RUN
TABLE FILE CAR
SUM DCOST RCOST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY EQ 'ITALY'
ON TABLE PCHOLD FORMAT PDF NOBREAK
END
-RUN
TABLE FILE CAR
SUM DCOST RCOST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY GT 'ITALY'
ON TABLE PCHOLD FORMAT PDF CLOSE
END
-RUN[/code]Keep in mind, as Tony said, "the final fex needs to produce an output"...

quote:

If the final fex does not produce anything then the compound is likely to fail.

That hasn't held true in my experience. In fact I have a procedure which intentionally has no data to close a report.

Scenario: user can select up to 12 components of a report packet so I don't know which one will be the last one. So I have a fex with an impossible WHERE statement (like WHERE COUNTRY EQ 'ENGLAND' AND COUNTRY EQ 'JAPAN') that has the ON TABLE PCHOLD FORMAT PDF CLOSE and EMPTYREPORT=OFF. That runs last and closes my compound doc.

This has worked for us at least since 7.1.x and I believe we used in with 5.3.x.

I agree Darin...

Just Food For Thought, regarding:

quote:

WHERE COUNTRY EQ 'ENGLAND' AND COUNTRY EQ 'JAPAN'

Use:

quote:

WHERE COUNTRY NE COUNTRY

instead. Keeping it as simple as possible...

quote:

I had changed my pchold to hold so I could see what was going on and that is when I saw, 0 records were being selected in the compound versus being run alone

Do you mean to say that in non compound mode you do get output from the fex but not where you're in compound mode?

Correct. It runs stand alone, but it didn't in the compound. Now I have moved it to be the first fex and all is well. However, it cannot be the first focexec and when I moved it to be number two in the line up of fexes, it goes back to 0 records. It seems I'm hitting some kind of limit or memory issue. I cannot use the composer because I have portrait and landscape reports.

quote:

I cannot use the composer because I have portrait and landscape reports.

Actually, I don't think that is true. In 7.6.5, you were allowed to set orientation on different page layouts in the same compound document. From the "Creating Reports with Graphical Tools" manual:

quote:

How to Set Different Page Orientations for Individual Page Layouts
You may set portrait and landscape page orientations for different pages in one document.
Page orientations set at the Page Layout level override the page orientation set at the
Compound Document level. If no page orientation is set for the individual page layout, then
the Compound Document page orientation property is applied to all pages in the document.

Kathy,

By now, it might be a good ideal to post your actual fexes, the first & the problem one. With all the eyes here, it will be easily spotted what could be a simple oversight of yours.

Hua

When I was using the document composer, it ignored my individual report settings and always went with the 'report' setting. So I opted fort the easy way out... hmm. Here is my code for the problem fex that runs standalone but not in the compound when it is not the first fex.

-TYPE MATERIAL TAB REPORT
-**************************************************************************
-SET &ECHO=ALL;
-*uncomment the defaults to use the gui painter, THEN COMMENT BACK out
-**************************************************************************
-*-DEFAULT &JOBNAME='CA_4225';
-*-DEFAULT &DEPARTMENT='All Departments';
-*-DEFAULT &YEAR=;
-*-DEFAULT &SPACER=;
-*-DEFAULT &MONTH=;
SET BLANKINDENT=OFF
SET HOLDLIST=PRINTONLY
SET EMPTYREPORT=ON
SET ASNAMES = ON
JOIN CLEAR *
-RUN
**************************************************
-TYPE Reading in date from tm1_active_year_month
**************************************************
TABLE FILE TM1_ACTIVE_YEAR_MONTH
PRINT *
ON TABLE HOLD
END
-RUN
-READ HOLD &YEAR.A9. &SPACER.A3. &MONTH.A10.
-TYPE YEAR=&YEAR MONTH=&MONTH
**************************************************
-TYPE Load YEAR hierarchy
**************************************************
TABLE FILE YEARS
PRINT YEARS_PARENT
BY YEARS_CHILD
ON TABLE HOLD FORMAT ALPHA
END
-RUN
CREATE FILE TM1YEARS
MODIFY FILE TM1YEARS
FIXFORM FROM HOLD
MATCH YEARS_CHILD
ON NOMATCH INCLUDE
ON MATCH CONTINUE
GOTO TOP
DATA ON HOLD
END
-RUN
TABLE FILE TM1YEARS
PRINT YEARS_PARENT AS 'PARENT'
FOR YEARS_CHILD
'All Years'
WITH CHILDREN ALL
ON TABLE HOLD AS HOLDYRSX
END
-RUN
TABLE FILE HOLDYRSX
PRINT COMPUTE E01/A10=LJUST(9,E01,'A9');
ON TABLE HOLD AS HOLDYEAR FORMAT ALPHA
END
-RUN
**************************************************
-TYPE Load MONTHS hierarchy
**************************************************
TABLE FILE months
PRINT MONTH_PARENT
BY MONTH_CHILD
ON TABLE HOLD FORMAT ALPHA
END
-RUN
CREATE FILE TM1MONTH
MODIFY FILE TM1MONTH
FIXFORM FROM HOLD
MATCH MONTH_CHILD
ON NOMATCH INCLUDE
ON MATCH CONTINUE
GOTO TOP
DATA ON HOLD
END
-RUN
TABLE FILE TM1MONTH
PRINT MONTH_PARENT AS 'PARENT'
FOR MONTH_CHILD
'All Months'
WITH CHILDREN ALL
ON TABLE HOLD AS HOLDMTHX
END
-RUN
TABLE FILE HOLDMTHX
PRINT COMPUTE E01/A10=LJUST(10,E01,'A10');
ON TABLE HOLD AS HOLDMNTH FORMAT ALPHA
END
-RUN
**************************************************
-TYPE Load JOB hierarchy
-TYPE save file for data for if statement in report.
**************************************************
DEFINE FILE FCSTJOBD
JOBNAME/A50=JOB_CHILD;
JOBPARENT/A50=JOB_PARENT;
END
TABLE FILE FCSTJOBD
PRINT JOBPARENT
BY JOBNAME
WHERE JOBPARENT NE ' '
ON TABLE HOLD FORMAT ALPHA
END
-RUN
CREATE FILE TM1JOBS
MODIFY FILE TM1JOBS
FIXFORM FROM HOLD
MATCH JOBNAME
ON NOMATCH INCLUDE
ON MATCH CONTINUE
GOTO TOP
DATA ON HOLD
END
-RUN
TABLE FILE TM1JOBS
PRINT JOBPARENT
FOR JOBNAME
'&JOBNAME'
WITH CHILDREN ALL
ON TABLE HOLD AS HOLDJOBX FORMAT ALPHA
END
-RUN
TABLE FILE HOLDJOBX
PRINT
COMPUTE E01/A50=LJUST(50,E01,'A50');
ON TABLE HOLD AS HOLDJOBS FORMAT ALPHA
END
-RUN
-***************************************************
-TYPE load ACCOUNT hierarchy
-TYPE save file for data for if statement in report.
-***************************************************
TABLE FILE FCSTACCOUNT
PRINT ACCTPARE
BY ACCTCHLD
WHERE ACCTPARE NE ' '
ON TABLE HOLD FORMAT ALPHA
END
-RUN
CREATE FILE TM1ACCT
MODIFY FILE TM1ACCT
FIXFORM FROM HOLD
MATCH ACCTCHLD
ON NOMATCH INCLUDE
ON MATCH CONTINUE
GOTO TOP
DATA ON HOLD
END
-RUN
TABLE FILE TM1ACCT
SUM
ACCTPARE
FOR
ACCTCHLD
'TOTAL DIRECT MATL' WITH CHILDREN ALL
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDACT1 FORMAT ALPHA
END
-RUN
TABLE FILE HOLDACT1
PRINT
COMPUTE CHILD_ACCT/A25=LJUST(25,E01,'A25');
ON TABLE HOLD AS HOLDACCT FORMAT ALPHA
END
-RUN
-***************************************************
-* grabbing data and putting into a hold file for
-* summary and detail reports and file concatenation,
-* BE SURE TO INCLUDE WHERE STMTS HERE.
-***************************************************
TABLE FILE FCSTDETAIL
PRINT
BUSINESS_UNIT
SCENARIO
CURRENCY
SOURCE
DEPARTMENT
AMOUNT
YEAR
MONTH
ACCOUNT
QUANTITY
RATE
YEARMONTH
CURRENCY
BY JOB
BY PART_NUMBER
-*IF RECORDLIMIT EQ 10
WHERE ACCOUNT IN FILE HOLDACCT;
WHERE JOB IN FILE HOLDJOBS;
WHERE MONTH IN FILE HOLDMNTH;
WHERE YEAR IN FILE HOLDYEAR;
ON TABLE HOLD AS HOLDDATA
END
-RUN
-******************************************************
-* JOIN HOLDDATA TO HOLD FILE 'JOBPFILE' TO INCLUDE PARENT DATA IN FILE
-******************************************************
JOIN CLEAR *
JOIN INNER JOB IN HOLDDATA TO JOBNAME IN TM1JOBS AS J1
END
-******************************************************
-TYPE create key for summary records
-******************************************************
DEFINE FILE HOLDDATA
DATE1/A8=TODAY(DATE1);
SORTKEY/A50='0000000000000000000000000000000000000000000000000';
END
TABLE FILE HOLDDATA
PRINT
JOBPARENT
BUSINESS_UNIT
SCENARIO
CURRENCY
SOURCE
JOB
DEPARTMENT
PART_NUMBER
AMOUNT
YEAR
MONTH
ACCOUNT
QUANTITY
RATE
YEARMONTH
CURRENCY
BY SORTKEY
BY PART_NUMBER
ON TABLE HOLD AS HOLDFIL1
END
-RUN
-***********************************************************
-TYPE create key for children records
-***********************************************************
DEFINE FILE HOLDDATA
DATE1/A8=TODAY(DATE1);
SORTKEY/A50=JOBPARENT;
END
TABLE FILE HOLDDATA
PRINT
JOBPARENT
BUSINESS_UNIT
SCENARIO
CURRENCY
SOURCE
JOB
DEPARTMENT
PART_NUMBER
AMOUNT
YEAR
MONTH
ACCOUNT
QUANTITY
RATE
YEARMONTH
CURRENCY
BY SORTKEY
BY PART_NUMBER
ON TABLE HOLD AS HOLDFCST FORMAT FOCUS INDEX ACCOUNT
-***********************************************************************
-* below is appending the two data files together (HOLDFIL1 AND HOLDFCST)
-***********************************************************************
MORE
FILE HOLDFIL1
END
-RUN

SET FORMULTIPLE=ON
-**************************************************
-TYPE first report in material tab
-**************************************************
DEFINE FILE HOLDFCST
TITLENAME/A50=IF SORTKEY EQ '0000000000000000000000000000000000000000000000000' THEN '&JOBNAME' ELSE JOBPARENT;
-*read in &year read from TM1_ACTIVE_YEAR_MONTH
YEAR1/A4=EDIT('&YEAR','$$$$$9999');
YEAR2/I4=EDIT(YEAR1);
YEAR3/I4YY=YEAR2;
YEAR4/YY=YEAR3;
-* Reading in month from TM1_ACTIVE_YEAR_MONTH
INMONTH/I2=&MONTH;
INMODCD/A4=DECODE INMONTH( 01 'Jan_' 02 'Feb_' 03 'Mar_' 04 'Apr_' 05 'May_' 06 'Jun_' 07 'Jul_' 08 'Aug_' 09 'Sep_' 10 'Oct_' 11 'Nov_' 12 'Dec') ;
ACTUALS/A8=INMODCD|YEAR1;
EDITYR1/A4=EDIT(YEAR,'9999$$$$$');
EDITYR2/I4=EDIT(EDITYR1);
EDITYR3/I4YY=EDITYR2;
EDITYR4/YY=EDITYR3;
-***Prior year
PRYRACT/D17.2=IF EDITYR4 LT YEAR4 AND SCENARIO EQ 'ACT' THEN AMOUNT ELSE 0;
-*** year to date
YTDACT_QTY/D17B=IF EDITYR4 EQ YEAR4 AND SCENARIO EQ 'ACT' THEN QUANTITY ELSE 0;
YTDACT_DOL/D17.2B=IF EDITYR4 EQ YEAR4 AND SCENARIO EQ 'ACT' THEN AMOUNT ELSE 0;
-*** job to date
JTDACT_QTY/D17B=IF SCENARIO EQ 'ACT' THEN QUANTITY ELSE 0;
JTDACT_UNIT/D17.2B=IF SCENARIO EQ 'ACT' THEN RATE ELSE 0;
JTDACT_DOLL/D17.2B=IF SCENARIO EQ 'ACT' THEN AMOUNT ELSE 0;
-*** Estimate TO Complete
ETC_QTY/D17B=IF SCENARIO EQ 'ETC' THEN QUANTITY ELSE 0;
ETC_UNIT/D17.2B=IF SCENARIO EQ 'ETC' THEN RATE ELSE 0;
ETC_DOLL/D17.2B=IF SCENARIO EQ 'ETC' THEN AMOUNT ELSE 0;
-*** Estimate AT Complete
EAC_QTY/D17B=IF SCENARIO EQ 'ETC' OR 'ACT' THEN QUANTITY ELSE 0;
EAC_UNIT/D17.2B=IF SCENARIO EQ 'ETC' OR 'ACT' THEN RATE ELSE 0;
EAC_DOLL/D17.2B=IF SCENARIO EQ 'ETC' OR 'ACT' THEN AMOUNT ELSE 0;
-*** Baseline
BUD_QTY/D17B=IF SCENARIO EQ 'BUD' THEN QUANTITY ELSE 0;
BUD_UNIT/D17.2B=IF SCENARIO EQ 'BUD' THEN RATE ELSE 0;
BUD_DOLL/D17.2B=IF SCENARIO EQ 'BUD' THEN AMOUNT ELSE 0;
-*** Variance to Baseline
VAR_QTY/D17B=BUD_QTY - EAC_QTY;
VAR_UNIT/D17.2B=BUD_UNIT - EAC_UNIT;
VAR_DOLL/D17.2B=BUD_DOLL - EAC_DOLL;
END

TABLE FILE HOLDFCST
-*******************************************
-* first report must have OPEN after holding the data with PCHOLD FORMAT PDF
-*******************************************
SUM
JOB NOPRINT
YTDACT_QTY AS 'Quantity'
YTDACT_DOL AS 'YTD,Dollars'
JTDACT_QTY AS 'Quantity'
JTDACT_UNIT AS 'JTD,Unit Price $'
JTDACT_DOLL AS 'Dollars'
ETC_QTY AS 'Quantity'
ETC_UNIT AS 'ETC,Unit Price $'
ETC_DOLL AS 'Dollars'
EAC_QTY AS 'Quantity'
EAC_UNIT AS 'EAC,Unit Price $'
EAC_DOLL AS 'Dollars'
BUD_QTY AS 'Quantity'
BUD_UNIT AS 'Baseline,Unit Price $'
BUD_DOLL AS 'Dollars'
VAR_QTY AS 'Quantity'
VAR_UNIT AS 'VAR,Unit Price $'
VAR_DOLL AS 'Dollars'
COMPUTE CURRENT_TIME/HHISa = HGETC(8, 'HHISa'); NOPRINT
BY SORTKEY NOPRINT
BY PART_NUMBER AS 'Part Number'

ON SORTKEY PAGE-BREAK
HEADING
"MATERIAL UNITS / DOLLARS REPORT"
"Job Number: Report Run on <+0>&DATEtrMDYY<+0>at FOOTING
""
WHERE JOB IN FILE HOLDJOBS;
ON TABLE SET PAGE-NUM OFF
ON TABLE SET FORMULTIPLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF OPEN
ON TABLE SET HTMLCSS ON

Kathy Phillips
Web FOCUS 8.2.05.14, 8.1.05, 8.08, 8.0.7, 8.0.5,8.0.2m, 7.6.10,7.7.03
Windows

You need to include your code inside of the

[code]

[/code] tags or HTML messes it up.
A couple of common debugging techniques that may help. Use -SET &ECHO=ALL; which returns all of the code that is run and allows you to se if it is running and populating &vars as expected. Also, since you have many different table requests that the final report depends on, try putting some -EXITs in at different points to see if the individual pieces are working as expected.

Here is the driver fex:

-TYPE buildkey1
-SET &ECHO=ALL;

-******************************************************************************
-TYPE REPORT 3 MATERIAL REPORT
-INCLUDE APP/buildkey11.fex

-******************************************************************************
-*NEXT REPORT
-******************************************************************************
-DEFAULT &SOURCE=;
-*DEFAULT &JOBNAME='CA_4225';
-*DEFAULT &DEPARTMENT='All Departments';
-*-DEFAULT &YEAR='2009';
-*-DEFAULT &MONTH=;
SET BLANKINDENT=OFF
SET HOLDLIST=PRINTONLY
SET EMPTYREPORT=ON
JOIN CLEAR *
-***************************************************
-TYPE Reading in today's date from tm1_active_year_month
-***************************************************
TABLE FILE TM1_ACTIVE_YEAR_MONTH
PRINT *
ON TABLE HOLD
END
-RUN
-READ HOLD &YEAR.A9. &SPACER.A3. &MONTH.A10.
-TYPE YEAR=&YEAR MONTH=&MONTH
-***************************************************
-TYPE Load YEAR hierarchy
-***************************************************
TABLE FILE YEARS
PRINT YEARS_PARENT
BY YEARS_CHILD
ON TABLE HOLD FORMAT ALPHA
END
-RUN
CREATE FILE TM1YEARS
MODIFY FILE TM1YEARS
FIXFORM FROM HOLD
MATCH YEARS_CHILD
ON NOMATCH INCLUDE
ON MATCH CONTINUE
GOTO TOP
DATA ON HOLD
END
-RUN
TABLE FILE TM1YEARS
PRINT YEARS_PARENT AS 'PARENT' NOPRINT
FOR YEARS_CHILD
'All Years'
WITH CHILDREN ALL
ON TABLE HOLD AS HOLDYRSX
END
-RUN
TABLE FILE HOLDYRSX
PRINT COMPUTE E01/A10=LJUST(9,E01,'A9');
ON TABLE HOLD AS HOLDYEAR FORMAT ALPHA
END
-RUN
-***************************************************
-TYPE Load MONTHS hierarchy
-***************************************************
TABLE FILE months
PRINT MONTH_PARENT
BY MONTH_CHILD
ON TABLE HOLD FORMAT ALPHA
END
-RUN
CREATE FILE TM1MONTH
MODIFY FILE TM1MONTH
FIXFORM FROM HOLD
MATCH MONTH_CHILD
ON NOMATCH INCLUDE
ON MATCH CONTINUE
GOTO TOP
DATA ON HOLD
END
-RUN
TABLE FILE TM1MONTH
PRINT MONTH_PARENT AS 'PARENT' NOPRINT
FOR MONTH_CHILD
'All Months'
WITH CHILDREN ALL
ON TABLE HOLD AS HOLDMTHX
END
-RUN
TABLE FILE HOLDMTHX
PRINT COMPUTE E01/A10=LJUST(10,E01,'A10');
ON TABLE HOLD AS HOLDMNTH FORMAT ALPHA
END
-RUN
-***************************************************
-TYPE Load JOB hierarchy
-TYPE save file for data for if statement in report.
-***************************************************
DEFINE FILE FCSTJOB
JOBNAME/A50=JOB_CHILD;
JOBPARENT/A50=JOB_PARENT;
END
TABLE FILE FCSTJOB
PRINT JOBPARENT
BY JOBNAME
WHERE JOBPARENT NE ' '
ON TABLE HOLD FORMAT ALPHA
END
-RUN
CREATE FILE TM1JOBS
MODIFY FILE TM1JOBS
FIXFORM FROM HOLD
MATCH JOBNAME
ON NOMATCH INCLUDE
ON MATCH CONTINUE
GOTO TOP
DATA ON HOLD
END
-RUN
TABLE FILE TM1JOBS
PRINT JOBPARENT AS 'PARENT' NOPRINT
FOR JOBNAME
'&JOBNAME'
WITH CHILDREN ALL
ON TABLE HOLD AS HOLDJOBX FORMAT ALPHA
END
TABLE FILE HOLDJOBX
PRINT
COMPUTE E01/A50=LJUST(50,E01,'A50');
ON TABLE HOLD AS HOLDJOBS FORMAT ALPHA
END
-RUN
-***************************************************
-TYPE load ACCOUNT hierarchy
-TYPE save file for data for if statement in report.
-***************************************************
TABLE FILE FCSTACCOUNT
PRINT ACCTPARE
BY ACCTCHLD
WHERE ACCTPARE NE ' '
ON TABLE HOLD FORMAT ALPHA
END
-RUN
CREATE FILE TM1ACCT
MODIFY FILE TM1ACCT
FIXFORM FROM HOLD
MATCH ACCTCHLD
ON NOMATCH INCLUDE
ON MATCH CONTINUE
GOTO TOP
DATA ON HOLD
END
-RUN
TABLE FILE TM1ACCT
SUM
ACCTPARE
FOR
ACCTCHLD
'All Accounts' WITH CHILDREN ALL
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDACCT FORMAT ALPHA
END
-RUN
TABLE FILE HOLDACCT
PRINT
COMPUTE CHILD_ACCT/A25=LJUST(25,E01,'A25');
ON TABLE HOLD AS HOLDACT1 FORMAT ALPHA
END
-RUN
-******* N E W STUFF ********************************
-*////////////////////////////////////////////////////
-* grabbing data and putting into a hold file for
-* summary and detail reports and file concatenation,
-* BE SURE TO INCLUDE WHERE STMTS HERE.
-***************************************************
TABLE FILE FCST
PRINT
BUSINESS_UNIT
SCENARIO
CURRENCY
SOURCE
JOB
DEPARTMENT
AMOUNT
YEAR
MONTH
ACCOUNT
BY JOB
WHERE JOB IN FILE HOLDJOBS;
WHERE MONTH IN FILE HOLDMNTH;
WHERE YEAR IN FILE HOLDYEAR;
ON TABLE HOLD AS HOLDDATA
END
-RUN

-******************************************************
-TYPE create key for summary records
-******************************************************
DEFINE FILE HOLDDATA
DATE1/A8=TODAY(DATE1);
SORTKEY1/A3='000';
END
TABLE FILE HOLDDATA
SUM
BUSINESS_UNIT
SCENARIO
CURRENCY
SOURCE
JOB
DEPARTMENT
AMOUNT
YEAR
MONTH
ACCOUNT
BY ACCOUNT
BY JOB
BY SORTKEY1
BY SCENARIO
ON TABLE HOLD AS HOLDFIL1
END
-RUN
-***********************************************************
-TYPE create key for children records
-***********************************************************
DEFINE FILE HOLDDATA
DATE1/A8=TODAY(DATE1);
SORTKEY/I3=IF (JOB NE LAST JOB) THEN (SORTKEY + 1) ELSE SORTKEY;
SORTKEY1/A3=EDIT(SORTKEY);
END
TABLE FILE HOLDDATA
SUM
BUSINESS_UNIT
SCENARIO
CURRENCY
SOURCE
JOB
DEPARTMENT
AMOUNT
YEAR
MONTH
ACCOUNT
BY ACCOUNT
BY JOB
BY SORTKEY1
BY SCENARIO
ON TABLE HOLD AS HOLDFCST FORMAT FOCUS INDEX ACCOUNT
-***********************************************************************
-TYPE below is appending the two data files together (HOLDFIL1 & HOLDFCST)
-***********************************************************************
MORE
FILE HOLDFIL1
END
-RUN
-TYPE join hierarchy to HOLDFCST, save file for data for if statement in report.
-***************************************************
JOIN CLEAR *
JOIN
INNER ACCTCHLD IN TM1ACCT TO MULTIPLE ACCOUNT IN HOLDFCST
AS J0
END
-RUN

SET FORMULTIPLE = ON
-**************************************************
-TYPE first report in buildkey1
-**************************************************
DEFINE FILE TM1ACCT
TITLENAME/A50=IF SORTKEY1 EQ '000' THEN 'Summary' ELSE JOB;
YEAR1/A4=EDIT('&YEAR','$$$$$9999');
YEAR2/I4=EDIT(YEAR1);
YEAR3/I4YY=YEAR2;
YEAR4/YY=YEAR3;
EDITYR1/A4=EDIT(YEAR,'9999$$$$$');
EDITYR2/I4=EDIT(EDITYR1);
EDITYR3/I4YY=EDITYR2;
EDITYR4/YY=EDITYR3;
-***
PRYRACT/D17.2=IF EDITYR4 LT YEAR4 AND SCENARIO EQ 'ACT' THEN AMOUNT ELSE 0;
YTDACT/D17.2=IF EDITYR4 EQ YEAR4 AND SCENARIO EQ 'ACT' THEN AMOUNT ELSE 0;
JTDACT/D17.2=PRYRACT + YTDACT;
CRYR_ETC/D17.2=IF EDITYR4 EQ YEAR4 AND SCENARIO EQ 'ETC' THEN AMOUNT ELSE 0;
CURYR_TOT/D17.2=YTDACT + CRYR_ETC;

TOTETC/D17.2=IF SCENARIO EQ 'ETC' THEN AMOUNT ELSE 0;
OUTYRS/D17.2= CRYR_ETC + TOTETC;
EACAMT/D17.2=JTDACT + TOTETC;
BACAMT/D17.2=IF SCENARIO EQ 'BUD' THEN AMOUNT ELSE 0;
VACAMT/D17.2=EACAMT - BACAMT;
END
TABLE FILE TM1ACCT
SUM
PRYRACT AS 'Prior Years,Actual'
YTDACT AS 'Year ToDate,Actual'
JTDACT AS 'Job To Date,Actual'
CRYR_ETC AS 'Current Year,ETC'
CURYR_TOT AS 'Current Year,Total'
OUTYRS AS 'Out Years,ETC'
TOTETC AS 'Total ETC'
EACAMT AS 'EAC'
BACAMT AS 'BAC'
VACAMT AS 'VAC'
BY SORTKEY1 NOPRINT
FOR
ACCTCHLD
'SALES REVENUE' ADD ALL AS 'Sales Revenue' LABEL R1 OVER
" " LABEL R2 OVER
'DIRECT LABOR' ADD ALL AS 'Direct Labor' LABEL R3 OVER
'DIRECT FRINGE OVH' ADD ALL AS 'Direct Fringe' LABEL R4 OVER
'LABOR OVERHEAD' ADD ALL AS 'Labor Overhead' LABEL R5 OVER
BAR AS '-' OVER
'TOTAL DIRECT LABOR' ADD ALL AS 'Subtotal Labor' LABEL R7 OVER
" " LABEL R8 OVER
'DIRECT MATERIAL' ADD ALL AS 'Direct Materials' LABEL R9 OVER
'DM ACCRUED MATL' ADD ALL AS 'Accrued Materials' LABEL R10 OVER
'DM PB BILLINGS' ADD ALL AS 'PB Billings' LABEL R11 OVER
'DM PB LIQUIDATIONS' ADD ALL AS 'PB Liquidations' LABEL R12 OVER
'SCRAP RECOVERY' ADD ALL AS 'Scrap Recovery' LABEL R13 OVER
BAR AS '-' OVER
'TOTAL DIRECT MATL' ADD ALL AS 'Total Direct Materials' LABEL R15 OVER
" " LABEL R16 OVER
'ODM MISC-MATL EQUIP' ADD ALL AS 'Non Inventory Material' LABEL R17 OVER
'ODM SUBCONTRACT' ADD ALL AS 'Subcontract' LABEL R18 OVER
'ODM TOOLING' ADD ALL AS 'Tooling' LABEL R19 OVER
'ODM GAGES' ADD ALL AS 'Gages' LABEL R20 OVER
'ODM TESTING MATL' ADD ALL AS 'Testing Materials' LABEL R21 OVER
'501046' ADD ALL AS 'Freight - Inbound' LABEL R22 OVER
'501045' ADD ALL AS 'Freight - Outbound' LABEL R23 OVER
BAR AS '-' OVER
'TOT OTHR DIRECT MATL' ADD ALL AS 'Total Other Direct Material' LABEL R25 OVER
" " LABEL R26 OVER
'ODC TOOLING' ADD ALL AS 'Tooling' LABEL R27 OVER
'ODC CONSULT SERVICES' ADD ALL AS 'Commissions' LABEL R28 OVER
'ODC PACKAGING MATL' ADD ALL AS 'Packaging Material' LABEL R29 OVER
'ODC EQUIPMENT' ADD ALL AS 'Equipment' LABEL R30 OVER
'ODC GAGES' ADD ALL AS 'Gages' LABEL R31 OVER
'ODC MISC' ADD ALL AS 'ODC Misc' LABEL R32 OVER
'ODC TRAVEL' ADD ALL AS 'Meals/Travel' LABEL R33 OVER
BAR AS '-' OVER
'TOTAL OTHER DIR COST' ADD ALL AS 'Total Other Direct Cost' LABEL R35 OVER
" " LABEL R36 OVER
'TOTAL DIRECT COSTS' ADD ALL AS 'Total Direct Costs' LABEL R37 OVER
" " LABEL R38 OVER
'TOTAL COST OF SALES' ADD ALL AS 'Cost Held in WIP' LABEL R39 OVER
" " LABEL R40 OVER
'Management Reserve' ADD ALL AS 'Management Reserve' LABEL R41 OVER
" " LABEL R42 OVER
'PROGRAM MARGIN' ADD ALL AS 'Program Margin' LABEL R43 OVER
" " LABEL R44 OVER
'GENERAL & ADMIN' ADD ALL AS 'General & Administrative' LABEL R45 OVER
BAR AS '-' OVER
'TOTAL COST' ADD ALL AS 'Total Program Costs' LABEL R47 OVER
" " LABEL R48 OVER
'EBIT' ADD ALL AS 'EBIT' LABEL R49 OVER
'EBIT RATE' ADD ALL AS 'EBIT %' LABEL R50 OVER
BAR AS '-' OVER
'NORMAL PROFIT' ADD ALL AS 'Normal Profit' LABEL R52 OVER
" " LABEL R53 OVER
RECAP R54/D17.2=R49 + R52;
AS 'Revised EBIT' OVER
RECAP R55/D7.2%=R54 / R1;
AS 'Revised EBT %' OVER
BAR AS '-'

ON SORTKEY1 PAGE-BREAK
HEADING
"Hierarchy Report"
"TOTAL DIRECT COSTS"
"&JOBNAME " "
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE SET FORMULTIPLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET BLANKINDENT ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
.
.
ENDSTYLE
END
-RUN

-**************
-TYPE REPORT 2
-*INCLUDE APP/LEAD_SHEET1.FEX

-**************
-TYPE REPORT 4
-INCLUDE APP/ODC_ODM_INCLUDE.FEX

-**************
-TYPE REPORT 5 Tonys labor report
-INCLUDE APP/hierarchy_template_tony1.fex

This message has been edited. Last edited by: Kathy P,

Kathy Phillips
Web FOCUS 8.2.05.14, 8.1.05, 8.08, 8.0.7, 8.0.5,8.0.2m, 7.6.10,7.7.03
Windows

Darin, I did not include any html code, it looks fine to me. I'm not sure what you mean it is messing up. I have -set &echo=all is in all of my fexes. I believe it also shows here that I have it. I have used -exit to narrow down my problem along with commenting out the other fexes which would be the same as -exit. I have explained in detail the problem and how I narrowed it down. Am I missing something?

quote:

WHERE JOB IN FILE HOLDJOBS;
WHERE MONTH IN FILE HOLDMNTH;
WHERE YEAR IN FILE HOLDYEAR;

Change these to a hardcoded value and run the compound. If you get data, then the problem is in your IN FILE files.

Ginny, 2 quick questions.

These exact where statements work in the other reports that hold the exact same data so why would it work in all the others and not only the one within the compound? There is one other report that does not have all the same where data within the compound report.

Why would it work standalone and not in the compound when being number 2, 3 , 4 fex? It does work when it is the first fex being executed.

I'm going to start working on streamlining all the code and removing redundant code. I have been training others as we move along on this. Everyone got a report to write so of course, everything had to be there for them.

Maybe you've got hold files with the same names in the application paths and you are getting the wrong files to compare to. It is something to look into. That would explain why it is running when it is first.

quote:

I have used -exit to narrow down my problem along with commenting out the other fexes which would be the same as -exit. I have explained in detail the problem and how I narrowed it down. Am I missing something?

Although the fex ran perfectly by itself, it's quite possible the hold files are messed up when compounded. Darin probably sugguested you to insert -exit into the problem fex, immediately after each hold file created. I would also comment out the 'ON TABLE HOLD ...' to actually see data being created.

Hua

Ginny, that makes sense. I am not using any mapping. I think it might clear up when I stream line the entire report.

Hua, I already did those things. I have spent a considerable amount of time debugging this and comparing...

Thank you.

Just before you run that last report, do a ? FILEDEF followed by a -RUN and see what you have lying around that might have the same name. It might be as simple as adding a FILEDEF CLEAR * just before that last report, unless of course you have some guys on disk that it is finding.

It's always pathing!

When you "made everything on table hold instead of on table pchold", did you leave in the FORMAT PDF? If not, try it again with the FORMAT PDF... Look for a "PANELED REPORT" error message. I just saw that here at FMCC...

When I put the report first, all is well. Here is the results of the ? filedef. However, if I make it number 2, 3, etc, I 0 records.

quote:

? FILEDEF
-RUN
0 NUMBER OF RECORDS IN TABLE= 3616 LINES= 6
0 NUMBER OF RECORDS IN TABLE= 330 LINES= 18
Lname Device Lrecl Recfm Append Filename
============================================================
FHOLD DISK 32756 VB D:\ibi\srv76\dm\edatemp\ts0000
79\fhold.ftm
FOCPOST DISK 32756 VB D:\ibi\srv76\dm\edatemp\ts0000
79\focpost.ftm
FOCSML DISK 32756 VB D:\ibi\srv76\dm\edatemp\ts0000
79\focsml.ftm
HOLDMAST DISK 0 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdmast.mas
HOLDFIL1 DISK 320 F D:\ibi\srv76\dm\edatemp\ts0000
79\holdfil1.ftm
HOLDDATA DISK 240 F D:\ibi\srv76\dm\edatemp\ts0000
79\holddata.ftm
HOLDACT1 DISK 66 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdact1.ftm
HOLDACCT DISK 66 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdacct.ftm
HOLD DISK 50 V D:\ibi\srv76\dm\edatemp\ts0000
79\hold.ftm
HOLDSCEN DISK 15 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdscen.ftm
HOLDSCEX DISK 17 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdscex.ftm
HOLDJOBS DISK 50 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdjobs.ftm
HOLDJOBX DISK 54 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdjobx.ftm
HOLDMNTH DISK 10 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdmnth.ftm
HOLDMTHX DISK 16 F D:\ibi\srv76\dm\edatemp\ts0000
79\holdmthx.ftm
HOLDYEAR DISK 10 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdyear.ftm
HOLDYRSX DISK 12 F D:\ibi\srv76\dm\edatemp\ts0000
79\holdyrsx.ftm
CHRT999 DISK 0 V D:\ibi\srv76\dm\edatemp\ts0000
79\chrt999.ftm
IBICOMPD DISK 0 V D:\ibi\srv76\dm\edatemp\ts0000
79\ibicompd.pdf
-EXIT

When I move it, 0 records when I am trying to get the detail data.

Here is the output when I put it as the second report.

quote:

TABLE FILE FCSTDETAIL
PRINT
BUSINESS_UNIT
SCENARIO
CURRENCY
SOURCE
DEPARTMENT
AMOUNT
YEAR
MONTH
ACCOUNT
QUANTITY
RATE
YEARMONTH
CURRENCY
BY JOB
BY PART_NUMBER
-*IF RECORDLIMIT EQ 10
WHERE ACCOUNT IN FILE HOLDACCT;
WHERE JOB IN FILE HOLDJOBS;
WHERE MONTH IN FILE HOLDMNTH;
WHERE YEAR IN FILE HOLDYEAR;
ON TABLE HOLD AS HOLDDATA
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
? FILEDEF
-EXIT
Lname Device Lrecl Recfm Append Filename
============================================================
HOLDDATA DISK 308 F D:\ibi\srv76\dm\edatemp\ts0000
79\holddata.ftm
HOLDMAST DISK 0 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdmast.mas
HOLDACCT DISK 25 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdacct.ftm
HOLDACT1 DISK 54 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdact1.ftm
FHOLD DISK 32756 VB D:\ibi\srv76\dm\edatemp\ts0000
79\fhold.ftm
FOCPOST DISK 32756 VB D:\ibi\srv76\dm\edatemp\ts0000
79\focpost.ftm
FOCSML DISK 32756 VB D:\ibi\srv76\dm\edatemp\ts0000
79\focsml.ftm
HOLD DISK 50 V D:\ibi\srv76\dm\edatemp\ts0000
79\hold.ftm
HOLDJOBS DISK 50 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdjobs.ftm
HOLDJOBX DISK 102 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdjobx.ftm
HOLDMNTH DISK 10 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdmnth.ftm
HOLDMTHX DISK 28 F D:\ibi\srv76\dm\edatemp\ts0000
79\holdmthx.ftm
HOLDYEAR DISK 10 V D:\ibi\srv76\dm\edatemp\ts0000
79\holdyear.ftm
HOLDYRSX DISK 24 F D:\ibi\srv76\dm\edatemp\ts0000
79\holdyrsx.ftm
IBICOMPD DISK 0 V D:\ibi\srv76\dm\edatemp\ts0000
79\ibicompd.pdf
HOLDFIL1 DISK 280 F D:\ibi\srv76\dm\edatemp\ts0000
79\holdfil1.ftm
CHRT999 DISK 0 V D:\ibi\srv76\dm\edatemp\ts0000
79\chrt999.ftm