Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Trouble with compound report calling stored proc

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Trouble with compound report calling stored proc
 Login/Join
 
Silver Member
posted
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




WebFOCUS 7.1.5 on Win2K using SQL2000/SQL2005/ORACLE10.4
 
Posts: 47 | Registered: March 02, 2007Report This Post
Virtuoso
posted Hide Post
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.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
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?


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
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.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Silver Member
posted Hide Post
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.


WebFOCUS 7.1.5 on Win2K using SQL2000/SQL2005/ORACLE10.4
 
Posts: 47 | Registered: March 02, 2007Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
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.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Silver Member
posted Hide Post
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.


WebFOCUS 7.1.5 on Win2K using SQL2000/SQL2005/ORACLE10.4
 
Posts: 47 | Registered: March 02, 2007Report This Post
Virtuoso
posted Hide Post
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?


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Silver Member
posted Hide Post
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,


WebFOCUS 7.1.5 on Win2K using SQL2000/SQL2005/ORACLE10.4
 
Posts: 47 | Registered: March 02, 2007Report This Post
Silver Member
posted Hide Post
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.


WebFOCUS 7.1.5 on Win2K using SQL2000/SQL2005/ORACLE10.4
 
Posts: 47 | Registered: March 02, 2007Report This Post
Virtuoso
posted Hide Post
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.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
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.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Trouble with compound report calling stored proc

Copyright © 1996-2020 Information Builders