I have a focexec (working just fine) MainReport.fex which gets its output from a stored procedure. I want to actually call this FOCEXEC multiple times, potentially, from another FOCEXEC as part of a compound report. Basically, users call the Driver.fex passing a director and optional manager. If they include manager, it directly calls MainReport.fex (and this is working fine too). If you don't include manager, Driver.fex determines the managers for that director and SHOULD run the MainReport.fex multiple times, once for each manager.

So... MainReport.fex works fine called directly. It works fine if called for a one-time run by Driver.fex. through print statements I confirmed that Driver.fex is successfully getting the multiple rows for managers under a director. It fails with a "NO OUTPUT" message when I attempt to run it as a compound report.

Here's the code for Driver.fex (I have tried just hardcoding the variables for calling the MainReport.fex but that didn't change the results):

SET ALL=ON, NODATA=''
-DEFAULT &ddlDirector = 0;
-DEFAULT &ddlManager = 0;
-DEFAULT &txtManager = '';
-DEFAULT &rptfmt='PDF';

-IF &ddlManager NE '0' GOTO DIRECT_RUN;

TABLE FILE SP_MGR

PRINT DIRECTORID MANAGERID LOGINID USERNAME 
WHERE DIRECTORID = '&ddlDirector'
ON TABLE SAVE AS MYOUT FORMAT ALPHA
END

-IF &RECORDS EQ 0 GOTO NORPT;

-RUN
-SET &RECS = &LINES;
-SET &TEL = 1;
SET COMPOUND = OPEN NOBREAK

-START
-READ MYOUT &MYSTRING.60
-SET &MYDIRECTOR = SUBSTR(60,&MYSTRING,2,4,3,MYDIRECTOR);
-SET &MYMANAGER = SUBSTR(60,&MYSTRING,6,8,3,MYMANAGER);
-SET &MYLOGINID = SUBSTR(60,&MYSTRING,15,29,15,MYLOGINID);
-SET &MYUSERNAME = SUBSTR(60,&MYSTRING,36,60,25,MYUSERNAME);

SET COMPOUND = NOBREAK 
EX MainReport.fex ddlDirector='&ddlDirector', ddlManager='&MYMANAGER', txtManager='&MYUSERNAME', rptfmt=&rptfmt

-IF &TEL EQ &RECS GOTO FINISH;

-SET &TEL = &TEL + 1;
-IF &TEL LE &RECS GOTO START;

-FINISH
SET COMPOUND = CLOSE

-GOTO EXIT;



-DIRECT_RUN
EX MainReport.fex ddlDirector=&ddlDirector, ddlManager=&ddlManager, txtManager=&txtManager, rptfmt=&rptfmt
-GOTO EXIT

-NORPT
-HTMLFORM NOREC2

MainReport.fex

-DEFAULT &ddlDirector = 0;
-DEFAULT &ddlManager = 150;
-DEFAULT &ddlYear = 2006;
-DEFAULT &txtManager = '';
-DEFAULT &rptfmt = 'PDF';
SQL SQLMSS SET SERVER SERVERNAME
SQL SQLMSS                                                                      
EX DBB.dbo.SP_DLL &ddlManager, &ddlYear;

TABLE FILE SQLOUT
PRINT *          
ON TABLE HOLD AS REPDATA
END


TABLE FILE REPDATA
PRINT *
	
HEADING CENTER
"Report Title"

BY RName NOPRINT 
ON RName SUBTOTAL AS ''

ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLEMODE FIXED
ON TABLE PCHOLD FORMAT &rptfmt
ON TABLE SET STYLE *
	UNITS=IN,
	PAGESIZE='LETTER',
	LEFTMARGIN=0.50,
	RIGHTMARGIN=0.50,
	TOPMARGIN=0.10,
	BOTTOMMARGIN=0.0,
	ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
	GRID=OFF,
	FONT='ARIAL',
	SIZE=9,
	COLOR='BLACK',
	BACKCOLOR='NONE',
	STYLE=NORMAL,
	LINEBREAK='CRLF',
$
TYPE=TITLE,STYLE=BOLD,$
TYPE=HEADING, STYLE=BOLD, JUSTIFY=CENTER, BACKCOLOR=RGB(170 213 255),$
TYPE=SUBFOOT,STYLE=BOLD,$
TYPE=SUBTOTAL,STYLE=BOLD,$
TYPE=REPORT, COLUMN=P1, SQUEEZE=0.75, $
TYPE=REPORT, COLUMN=N1, $
TYPE=REPORT, COLUMN=N2, SQUEEZE=0.75,$
TYPE=REPORT, COLUMN=N3, SQUEEZE=0.75,$
TYPE=REPORT, COLUMN=N4, SQUEEZE=0.75,$
TYPE=REPORT, COLUMN=N5, SQUEEZE=0.75,$
TYPE=REPORT, COLUMN=N6, SQUEEZE=0.75,$
TYPE=REPORT, COLUMN=N7, SQUEEZE=0.5,$
TYPE=REPORT, COLUMN=N8, SQUEEZE=0.5,$
TYPE=REPORT, COLUMN=N9, SQUEEZE=0.5,$
TYPE=REPORT, COLUMN=N10, SQUEEZE=0.5,$
TYPE=TITLE, COLUMN=N2, JUSTIFY=CENTER,$
TYPE=TITLE, COLUMN=N3, JUSTIFY=CENTER,$
TYPE=TITLE, COLUMN=N4, JUSTIFY=CENTER,$
TYPE=TITLE, COLUMN=N5, JUSTIFY=CENTER,$
TYPE=TITLE, COLUMN=N6, JUSTIFY=CENTER,$
TYPE=TITLE, COLUMN=N7, JUSTIFY=CENTER,$
TYPE=TITLE, COLUMN=N8, JUSTIFY=CENTER,$
TYPE=TITLE, COLUMN=N9, JUSTIFY=CENTER,$
TYPE=TITLE, COLUMN=N10, JUSTIFY=CENTER,$
ENDSTYLE

END

Duplicate labels strike again! I have hit this a few timesas well. (You would think I would learn after once or twice), so what is probably happening here is that because you are -INCLUDing the same code (with the same labels) multiple times, it is confusing the syntax parser as to which -DIRECT_RUN/FINISH/NORPT label is should go to. Try adding an & variable to the end of your labels (DIRECT_RUN&CTR) in DRIVER.FEX and incrementing the value for each pass through the driver procedure.

Try changing the SUBSTR code from

-SET &MYDIRECTOR = SUBSTR(60,&MYSTRING,2,4,3,MYDIRECTOR);
-SET &MYMANAGER = SUBSTR(60,&MYSTRING,6,8,3,MYMANAGER);
-SET &MYLOGINID = SUBSTR(60,&MYSTRING,15,29,15,MYLOGINID);
-SET &MYUSERNAME = SUBSTR(60,&MYSTRING,36,60,25,MYUSERNAME);

to

-SET &MYDIRECTOR = SUBSTR(60,&MYSTRING,2,4,3,'A3');
-SET &MYMANAGER = SUBSTR(60,&MYSTRING,6,8,3,'A3');
-SET &MYLOGINID = SUBSTR(60,&MYSTRING,15,29,15,'A15');
-SET &MYUSERNAME = SUBSTR(60,&MYSTRING,36,60,25,'A25');

I don't actually see a duplicate label problem here, Darin. Are you sure?

If you're -INCLUDEing the same fex with labels multiple times, you've got duplicate labels.

Alan's suggestions may help, however. With DM, you've got to specify the field format (in quotes) instead of an output field, so the syntax used isn't quite correct. That doesn't explain, though, why it works once, but not multiple times.

Darin, I agree when you have -INCLUDEs, but I didn't see a -INCLUDE here, which is why I asked.

With the incorrect format spec in the SUBSTR, the values for ddlManager will be incorrect, which may cause 0 records to be returned and a NO HTML OUTPUT message.

I appreciate both your responses. I did the modification Alan B suggested re the SUBSTR syntax. However, please note as I stated, that if I modify that EX call to the report to actually hard-code the parameters passed and comment out the SUBSTR entirely, it still results in the NO OUTPUT error. And if I put a print statement into that loop to print out one of the fields, for example, I'm getting a message indicating that it's successfully reading and outputting the fields in the database rows. So I don't think it's an error in the SUBSTR arena.

Does the duplicate parameter name issue apply for EX? I didn't think so -- when I did a composite report before (on another project) I specified the same FOCEXEC multiple times for testing and never encountered an error.

Duplicate label is not an issue with EX.

Can you run it with -SET &ECHO=ALL; and check what is happening, in MainReport as well as the driver, also you may want to remove the quotes round the &vars on the EX line in the driver.

Your both right - no label problem with EX, no -INLCUDEs sorry to misdirect.
Back at square one here's what I think the problem is:
Something has to run with (ON TABLE SET COMPOUND=CLOSE) or after (SET COMPOUND=CLOSE) the close statement. You're not running anything after you do the SET COMPOUND=CLOSE, so it cannot close the document.
This would probably work:

-START
-READ MYOUT &MYDIRECTOR.A3. &MYMANAGER.A3. &MYLOGINID.A15. &MYUSERNAME.A25.

SET COMPOUND=NOBREAK
-IF &TEL NE &RECS THEN GOTO SKIPCLOSE;
SET COMPOUND=CLOSE NOBREAK
-SKIPCLOSE
EX MainReport.fex ddlDirector='&ddlDirector', ddlManager='&MYMANAGER', txtManager='&MYUSERNAME', rptfmt=&rptfmt
.
.
.
(Note that you can read all variables in separately with the -READ instead of having to do extra -SETs with SUBSTR to extract them)
This will set the compound to close with the report as you execute it for the last time.

Thanks for the set echo=all suggestion. I have incorporated Darin's suggestion to put all the reads into one statement. My stored procedure output is INTEGER, INTEGER, VARCHAR(15), VARCHAR(25). The reason why I was formatting through substr was there seems to be a lot of garbage in the read statement, padding between fields, etc.

If I do:

-READ MYOUT &MYDIRECTOR.A3. &MYMANAGER.A3. &MYLOGINID.A15. &MYUSERNAME.A25.

SET COMPOUND = NOBREAK 
-IF &TEL NE &RECS THEN GOTO SKIPCLOSE;

SET COMPOUND=CLOSE NOBREAK
-SKIPCLOSE
EX MainReport.fex ddlDirector='&ddlDirector', ddlManager='&MYMANAGER', txtManager='&MYUSERNAME', rptfmt='&rptfmt'
-IF &TEL EQ &RECS GOTO FINISH;

-SET &TEL = &TEL + 1;
-IF &TEL LE &RECS GOTO START;

-FINISH
SET COMPOUND = CLOSE

-GOTO EXIT;

the execution steps outputted look like:

EX MainReport.fex ddlDirector='137', ddlManager='7 1', txtManager=' 000013MGRNAMEHERE', rptfmt='PDF'

ddlManager should have been the number starting with that 1, txtManager has some numbers prepended to it and cuts it off.

I have looked through the online documentation and can't find any reference to the -READ command. What am I doing wrong to get that padding situation? How can I read those values appropriately?

However, the basic read operation and looping appears to work correctly, as it attempts to call the MainReport.fex 5 times (as it should in this case). If I leave in the badly formatted variables, it will execute the MainReport.fex but doesn't get any results with the bogus parameters and fails. If I plug in hardcoded values for the parameters that I know will work, it just heads off into processing and finally times out with a "NO DATA" message and no debug statements.

Have a look at the ECHO output and check the record layout for your SAVE file.

From your code it should read something like:

DIRECTORID the alias A3 3
MANAGERID the alias A3 3
LOGINID the alias A15 15
USERNAME the alias A25 25
TOTAl 46

Maybe the sizes are not like this and you may have to put some filler field in the -READ, use the figure on the right to get the actual position of the data.

Are you sure that you are reading the correct file, MYOUT, that there isn't one in the path somewhere?

No, I've checked my output file.

It looks like this:

 DIR MGR000007xxxxxxx        000013xxxxxxxxxxxxx            

The 000007 and 000013 represent the lengths of the fields following it.

The debug output states:

    ALPHANUMERIC RECORD NAMED  MYOUT   
  0 FIELDNAME                         ALIAS         FORMAT        LENGTH
    
    DirectorId                        DIRECTORID    I4              4
    ManagerId                         MANAGERID     I4              4
    LoginId                           LOGINID       A15V           21
    UserName                          USERNAME      A25V           31
    
    TOTAL                                                          60

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

Okay, this is interesting. I switched back to reading all 60 characters and using SUBSTR to parse it into my four fields. Still having issue with PDF generation running forever and returning "EDA no/data" only.

On a whim I passed EXL2K as format instead of PDF...and it works perfectly. I am at a loss.

Please look at the recent thread "Multiple compound reports executed in one fex" (by tryFOCUSing) that explains a couple issues with compound reporting we have run into. You may have to populate &variables (for OPEN/CLOSE or NOBREAK) and pass those into the actual fex to get this to work right.

The format of LOGINID and USERNAME is a A15V length (or A25V) I would suggest to change that to a fixed Alpha length, these kind of settings have caused many problems here.