quote:

Originally posted by Danny-SRL:
Snoop,
My solution rests on the MORE command which allows creating a report (or a HOLD file) using data from different files, as long as the data assembled from each file has the same names. Hence the use, in my example of the names ALINE and LEVEL. For your problem, it would demand a deeper knowledge of the quantity of data to out on the PDF page and the desired layout. It has the advantage of not having to use many JOINs which, in your case, seem to complicate.
If you need more info about the method, please ask.

Argh. This isn't going to work for me then. The PARENT portion of the PDF layout is 27 fields structured over 14 lines. The HISTORY section is 4 fields laid out over 2 lines. the MATERIALS section is 8 fields laid out over 4 lines.

I guess I need to scrap this and go back to the "three separate focexecs".

Snoop,

Don't despair. You can use an alternate master for the hold files. You create your HOLD file with all your data in 1 very long line - no problem nowadays with alpha fields up to 4K - and then cut it up with the alternate master.
I wouldn't say it's a piece of cake but not too complicated.
You can look at something I wrote in the subject "INPUT 1 RECORD OUTPUT 2 RECORDS"
Continue trying!

More and more questions.

Okay... so I'm back working on JJI's solution.

The key field that I'm reading is format A11V.

When I execute the -READ TEST &KEYFIELD.11. command, it appears to work, but what I get back is:

0 NUMBER OF RECORDS IN TABLE= 32 LINES= 32


ALPHANUMERIC RECORD NAMED MYOUT
0 FIELDNAME ALIAS FORMAT LENGTH

KEYFIELD KEYFIELD A11V 17

TOTAL 17

My key field actually has 000011 prepended to it.... so I need to get the last 11 characters of it to find my keyfield. This is actually presenting problems getting it to work right. I'm trying to reformat, etc. But from what I've read concerning the -READ statement, this should be okay. I'm doing a query against my view that returns ONLY the key field, and I have confirmed running that view that you do get returned an 11-character field. If I cause it to loop around and print out the remaining rows, they all have that 000011 prepended. What's that from?

I experimented with different formatting, A11, A11V (which is apparently not accepted), 11, etc. and it DOES truncate the value... so I get 000011 plus the first 5 characters of my key field, which is not what I want at all!

quote:

Don't despair. You can use an alternate master for the hold files. You create your HOLD file with all your data in 1 very long line - no problem nowadays with alpha fields up to 4K - and then cut it up with the alternate master.
I wouldn't say it's a piece of cake but not too complicated.
You can look at something I wrote in the subject "INPUT 1 RECORD OUTPUT 2 RECORDS"
Continue trying!

Like I said, I am learning a lot and continuing to try just about every technique posted.

I've read the solution you referenced. Let me get this straight -- you actually are physically creating a special MASTER file to the system that's used for this? I don't understand what you're doing at all unfortunately... for my particular situation, would I have three physical external master files, one for each report section, and then dump the results for the three queries into the specific master files? How would this provide me the ability to concatenate the three report sections together then? I'm not understanding how this resolves the problem of allowing me to format the separate report sections.

The other way will still work. The layout has to be the same for each file, but that doesn't mean the values have to be real or even exist. Take the code below which is Danny's code with some modification:
TABLE FILE CAR
SUM SALES RETAIL_COST DEALER_COST
BY CAR
ON TABLE HOLD
END
TABLE FILE CAR
PRINT STANDARD BY CAR
ON TABLE HOLD AS HOLD1
END
TABLE FILE CAR
PRINT WARRANTY BY CAR
ON TABLE HOLD AS HOLD2
END
DEFINE FILE HOLD
LEVEL/I1=1;
WARRANTY/A40=;
STANDARD/A40=;
END
DEFINE FILE HOLD1
SALES/I6=;
RETAIL_COST/D7=;
DEALER_COST/D7=;
WARRANTY/A40=;
LEVEL/I1=2;
END
DEFINE FILE HOLD2
SALES/I6=;
RETAIL_COST/D7=;
DEALER_COST/D7=;
STANDARD/A40=;
LEVEL/I1=3;
END
TABLE FILE HOLD
PRINT CAR NOPRINT
BY CAR NOPRINT NOSPLIT
BY LEVEL NOPRINT
ON CAR SUBHEAD
"ON CAR SUBFOOT
" "
ON LEVEL SUBHEAD
"PARENT"
"SALES= RCOST="
"DCOST="
WHEN LEVEL EQ 1;
SUBHEAD
"STANDARD"
"STANDARD="
"STANDARD LINE3"
WHEN LEVEL EQ 2;
SUBHEAD
"WARRANTY"
"WARRANTY="
"WARRANTY LINE3"
WHEN LEVEL EQ 3;
ON TABLE PCHOLD FORMAT PDF
MORE
FILE HOLD1
MORE
FILE HOLD2
END

I'm not worrying about any layout in fields any longer. That can all be done in subheadings and sytlesheets. The only thing I need is for each hold file to include all of the same fields so I define dummy fields for the ones that don't exist in each file. You're only working with about 40 fields so it's do-able. Need to make sure that the defined format matches the format of the field where it actually exists.

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

quote:

Originally posted by Darin Lee:
The other way will still work. The layout has to be the same for each file, but that doesn't mean the values have to be real or even exist. Take the code below which is Danny's code with some modification:
..
ON CAR SUBHEAD
"ON CAR SUBFOOT
" "
ON LEVEL SUBHEAD
"PARENT"
"SALES= RCOST="
"DCOST="
WHEN LEVEL EQ 1;
SUBHEAD
"STANDARD"
"STANDARD="
"STANDARD LINE3"
WHEN LEVEL EQ 2;
SUBHEAD
"WARRANTY"
"WARRANTY="
"WARRANTY LINE3"
WHEN LEVEL EQ 3;
ON TABLE PCHOLD FORMAT PDF
MORE
FILE HOLD1
MORE
FILE HOLD2
END

I'm not worrying about any layout in fields any longer. That can all be done in subheadings and sytlesheets. The only thing I need is for each hold file to include all of the same fields so I define dummy fields for the ones that don't exist in each file. You're only working with about 40 fields so it's do-able. Need to make sure that the defined format matches the format of the field where it actually exists.

Okay.... but this doesn't seem to provide what I need. I tried it out, and while I can get the formatted output to print out for what's basically the "heading" of each level, it only prints once. It leaves blank lines for all the "details" within the level.

Back to JJI's suggestion -- so I'm almost done with this one.

I have the three separate FOCEXEC's running perfectly with my output formatted just the way I want it.

I have the "main" FOCEXEC working just fine rolling through the data and calling the three FOCEXEC's in turn and concatenating the output.... well, almost.

I have finally narrowed down the problem to this:

If I run the execs like this:

EX FIRST KEY=&KEY
SET COMPOUND=NOBREAK
EX THIRD KEY=&KEY
SET COMPOUND=NOBREAK
-IF &TEL EQ &RECS GOTO FINISH;
EX THIRD KEY=&KEY

-SET &TEL = &TEL + 1;
IF &TEL LE &RECS GOTO START;
-FINISH
SET COMPOUND=CLOSE
EX THIRD KEY=&KEY

This works great. Notice that I'm running the THIRD report twice. That's how I can confirm that the "structure" of this is working fine.

The ONLY difference really between SECOND and THIRD is that SECOND is pulling its output from a stored procedure rather than a view or table.
It runs perfectly "stand-alone", but when I attempt to call it through this main program, it abends with an EDA/no output (which I know isn't correct, because I can run the SECOND FOCEXEC directly and it returns data just fine).

Here's what I have in the SECOND that calls the stored proc:

SQL SQLMSS SET SERVER servername
SET SQLENGINE=SQLMSS
SQL SQLMSS EX databasename.dbo.storedprocname '&KEY' ;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TCOM FORMAT ALPHA
ON TABLE SET ASNAMES ON
END

TABLE FILE TCOM
HEADING
"History"
""
BY ROWNUM NOPRINT
ON ROWNUM SUBHEAD
......

the rest is all the same.

If I rewrite the focexec to run directly against SQLOUT, then it prompts me to download or save a .fex file.

Snoop,

I am going to throw a wrench into the works. I have added more detail to my example. Tell me if it looks like what you might need.

DEFINE FILE CAR
TSALES/A6='SALES';
TRETAIL/A6='RETAIL';
TDEALER/A6='DEALER';
RETAIL/I6=RETAIL_COST;
DEALER/I6=DEALER_COST;
END
-*
-* This is the data from the first file, three fields and their titles.
TABLE FILE CAR
SUM TSALES SALES TRETAIL RETAIL TDEALER DEALER
BY CAR
ON TABLE SAVE AS SNOOPY
END
-* Because of the alternate MASTER for the SNOOPY file 
-* instead of having 3 fields per line, we now have 3 lines of 1 field per line
-RUN
-* Data from the second file
TABLE FILE CAR
PRINT STANDARD BY CAR
ON TABLE HOLD AS HOLD1
END
-* Data from the third file
TABLE FILE CAR
PRINT WARRANTY BY CAR
ON TABLE HOLD AS HOLD2
END
-* Because we want to concatenate the files, each file (SNOOPY, HOLD1, HOLD2) must have the same fields
-* both in name and format
-*
-* For SNOOPY, the line is made up of the title with the value
-* Set LEVEL to 1 so that the lines arrive first
DEFINE FILE SNOOPY
ALINE/A40=TITL | '=' | FTOA(VALUE,'(D6)', 'A7') ;
LEVEL/I1=1;
END
-* For HOLD1 there is only one field. If there were more than one we would do as for SNOOPY
-* Set LEVEL to 2 so that these lines arrive second
DEFINE FILE HOLD1
ALINE/A40=STANDARD;
LEVEL/I1=2;
END
-* For HOLD2 there is only one field. If there were more than one we would do as for SNOOPY
-* Set LEVEL to 3 so that these lines arrive third
DEFINE FILE HOLD2
ALINE/A40=WARRANTY;
LEVEL/I1=3;
END
-* Now produce the report. All lines will fall in place. Use SUBHEAD and SUBFOOT for clarity
TABLE FILE SNOOPY
PRINT ALINE AS ''
BY CAR NOPRINT NOSPLIT
BY LEVEL NOPRINT
ON CAR SUBHEAD
"Manufacturer: <CAR "
ON CAR SUBFOOT
"</1 "
ON LEVEL SUBHEAD
" "
"STANDARD details for <CAR"
WHEN LEVEL EQ 2;
SUBHEAD
" "
"WARRANTY details for <CAR"
WHEN LEVEL EQ 3;
ON TABLE PCHOLD FORMAT PDF
MORE
FILE HOLD1
MORE
FILE HOLD2
END

-* The SNOOPY master file:
FILENAME=SNOOPY  , SUFFIX=FIX
  SEGMENT=SNOOPY, SEGTYPE=S0
    FIELDNAME=CAR, ALIAS=CAR, USAGE=A16, ACTUAL=A16, $
  SEGMENT=MFIELDS, PARENT=SNOOPY, OCCURS=VARIABLE
    FIELDNAME=TITL, ALIAS=***, USAGE=A6, ACTUAL=A06, $
    FIELDNAME=VALUE, ALIAS=VAL, USAGE=I6, ACTUAL=A06, $

Good luck.

quote:

Originally posted by Danny-SRL:
I am going to throw a wrench into the works. I have added more detail to my example. Tell me if it looks like what you might need.
Good luck.

Yes -- that looks like it EXCEPT of course that i need to actually layout the fields in PDF. I am now stuck with some sort of type inconsistency that I have not yet been able to track down in my version with all my fields in it. Thanks, continuing to work on this.

Perhaps I'm the only one still listening or half-way excited about this, but I'm thrilled to say that I'm 99% of the way completed with this.

The ONLY issue I have at this point is FORCING the compound report break for every parent record. This works with them all concatenated together but i can't cause a page break to happen on the parent key field. I have tried putting the page break in the FOC_MAIN.FEX program but that apparently gets overridden by the NOBREAK directives in this FOCEXEC that compounds them all together. It seems like I should be able to indicate that I want to turn pagebreak back ON within this program, but I haven't found a reference to that yet. Suggestions?

I also had to convert one of the "detail" focexecs from a stored procedure to a view because I just couldn't get the stored procedure version to work -- the sp runs just fine, that particular focexec worked standalone just fine, but calling as EX PROC.FEX from this program caused a failure every time.

Here's the barebones of what I have at this point. I have actually stripped out even more since this same FOCEXEC can also be used to produce one single excel spreadsheet, but I'm not doing that with the compound technique at this point:

-* lots of lines collecting sort order and criteria from the calling program
-* primary data joins for the PARENT file reading
-* format the strings for heading information
-SET &SELKEY1 = IF &txtKEY1 EQ '' THEN '' ELSE ' Key 1: ' | &txtKEY1 ;
-SET &SELKEY2 = IF &txtKEY2 EQ '' THEN '' ELSE ' Key 2: ' | &txtKEY2 ;
..
-SET &SELKEYN = IF &txtKEYN EQ '' THEN '' ELSE ' Key N: ' | &txtKEYN ;
-SET &HEADSELECT = &SELKEY1 || &SELKEY2 || .. || &SELKEYN ;
-* ALSO BUILD A STRING HEADSORT FOR THE HEADER
-SET &HEADSORT = 'sorted by ..... ';

TABLE FILE PARENTVIEW
PRINT KEYFIELD

-COLLECT_SORTS
-* here establish the sort order as passed in by calling program


-BUILD_CRITERIA
-* here I build the various WHERE statements based on parameters
-IF &txtKEY1 EQ '' GOTO CK_KEY2;
WHERE KEY1 EQ '&txtKEY1'

-CK-KEY2
-IF &txtKEY2 EQ '' GOTO CK_KEYN;
WHERE KEY2 EQ '&txtKEY2'

-CK-KEYN
-IF &txtKEYN EQ '' GOTO DO_HOLD:
WHERE KEYN EQ '&txtKEYN'

-DO_HOLD
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
-GOTO VOLGEND
-START
-VOLGEND
-READ MYOUT &KEYFIELD.17.

-SET &MYKEYFIELD = SUBSTR(17,&KEYFIELD,7,17,11,MYKEYFIELD);

EX FOC_MAIN.FEX KEYFIELD=&MYKEYFIELD, HEADSORT='&HEADSORT', HEADSELECT='&HEADSELECT'

SET COMPOUND = NOBREAK

EX FOC_COMMENTS.FEX KEYFIELD=&MYKEYFIELD

-IF &TEL EQ &RECS GOTO FINISH;

SET COMPOUND = NOBREAK
EX FOC_MATERIALS.FEX KEYFIELD=&MYKEYFIELD

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

-FINISH
SET COMPOUND = CLOSE
EX FOC_MATERIALS.FEX KEYFIELD=&MYKEYFIELD

-GOTO EXIT
-NORPT
-HTMLFORM NOREC2
-EXIT

I didn't see what version you are on, but there are 2 ways to page-break a compound PDF report on the common key to all three. (ie each page has all 3 report pieces on it).

If you are on 7.1x or above, you can do a "coordinated compound report" which uses the new "compound report syntax" (not the syntax you are using).

If you have an earlier version, or want to use the syntax you have, you need to have the entire compound report in a loop, where the value of &MYKEYFIELD changes each time. Then you need to have the OPEN and CLOSE pieces of the compound report SET commands replaced by variables that evaluate depending on if you are the first, middle or last iterations of your loop.

That is the first time through the loop you get OPEN, all the rest you get blank. The last time through your loop you get CLOSE, the rest you get blank.

Thanks. I ended up doing the second option and it's working for me now. Appreciate it!