There is a lot going on in these examples. I wouldn’t expect it to be clear without walking through them(which I hope to do a local user group meeting). Meanwhile, if you have questions or suggestions please drop me a line.
I create smaller parts of a report piecemeal -- then combine them into "_page_1.fex", "_page_2.fex", etc. using “EX” vs. “-INCLUDE”. I chose this method - because there appears to be inconsistency in how the amper-variables resolve via "-INCLUDE". However, “EX” requires that you specify each of the variables to be used.
EXAMPLE:
-*-
-* Ethnicity, gender, disability ~
-* &DGRP - 'C'(=Child Count)
-SET &DGRP = 'C';
-RUN
EX ddp_district_demog_page_1 AAA='AAA',
DGRP=&DGRP,
DN=&DN,
DT=&DT,
FIRSTTIME='Y',
GENFILES='Y',
PCHOLD=&PCHOLD,
PDFSTATE=&PDFSTATE,
NOBREAK=&NOBREAK,
NOCLOSE='Y',
PAGENO=&PAGENO,
REPORTNAME=&REPORTNAME,
SCHYR=&SCHYR,
TOTPAGES=&TOTPAGES
-RUN
-SET &NOBREAK = 'NOBREAK';
-SET &PAGENO = &PAGENO + 1;
-SET &FIRSTTIME = 'N';
-RUN
-*-
-* Ethnicity, gender, disability ~
-* &DGRP - (serving district='S')
-SET &DGRP='S';
-RUN
EX ddp_district_demog_page_2 AAA='AAA',
DGRP=&DGRP,
DN=&DN,
DT=&DT,
FIRSTTIME='N',
GENFILES='Y',
PCHOLD=&PCHOLD,
PDFSTATE=&PDFSTATE,
NOBREAK=&NOBREAK,
NOCLOSE='Y',
PAGENO=&PAGENO,
REPORTNAME=&REPORTNAME,
SCHYR=&SCHYR,
TOTPAGES=&TOTPAGES
-RUN
Compounded PDF Screen or disk:
Key to creating multipart reports (think “subreport” from Crystal) is a varying “ON TABLE…” statement using amper variables: ‘ON TABLE &PCHOLD FORMAT PDF &PDFSTATE &NOBREAK’.
Supply these with various values to display the report to the screen or save to disk, and to open the first, middle, or last part of the report. The example uses a separate .fex calls for each part of the page and a final call to the "page-footer" as "closer" of compounded report(see below). By default this "footer" fex closes the report – but, for multi-page reports -- for all but the last pages supply an amper-variable “NOCLOSE=’Y’”.
-*-
-* …1st part of the report:
-SET &PDFSTATE='OPEN';
-SET &NOBREAK='NOBREAK';
-*-
-* …2nd part of the report:
-SET &PDFSTATE='';
-SET &NOBREAK='NOBREAK';
-*-
-* …final part of the report:
-SET &PDFSTATE='CLOSE';
-SET &NOBREAK=' ';
-*-
-* For screen
-SET &PCHOLD='PCHOLD';
-*-
-* For disk
-SET &PCHOLD='HOLD'
-*…in which case you would also _name_ the report and use FILEDEF
If going to the screen
-*-
-* "Footer" example
-IF &NOCLOSE EQ 'Y' GOTO PDFNOCLOSE;
-PDFCLOSE
-SET &PDFSTATE='CLOSE';
-SET &NOBREAK='';
-RUN
-PDFNOCLOSE
When using this method– the WebFocus code has to be “tweaked” to still function in report painter. IN my code I supply two sets of ‘ON TABLE’ comments just prior to the beginning of the report:
-* For compounded report use:
-*ON TABLE &PCHOLD FORMAT PDF &PDFSTATE &NOBREAK
-*-
-* For painter use:
-*ON TABLE PCHOLD FORMAT PDF
I swap one or the other of these 'ON TABLE' statements in dependent on whether I am preparing the report for production -- or "tweaking" it in the painter.
SQLOUT
Using the default “SQLReport” appears to be problematic. It appears to work the first time – but, not on subsequent executions. This has something to do with how configured on our servers– but, to get around the problems we supply a SQL statement to webfocus and use the prepared SQLOUT using this method.
ENGINE SQLMSS SET DEFAULT_CONNECTION MDEDATAB
SQL SQLMSS PREPARE SQLOUT FOR
SELECT…
END
TABLE FILE SQLOUT
PRINT …
Note:
WebFocus appears to not like T-SQL memory variables. That is you have to supply very simple amper-variabled SQL statements vs. “DECLARE …”.
Footing Variables and Empty Reports
If you want a ‘dd-mmm-yyyy’ type date – here’s an example. ‘REPORT_DATE’ is a define created from concatenating the &DATE variable with three different format suffixes.
If you want ‘-Nothing to report’ to appear use the &LINES (system variable) – in combination with your own &NODATA variable. By default I set to ‘-Nothing to Report’ – but, if I want it to be custum for an individual page I can pass this value into the report for that page. When &LINES=0 – on the “empty data” condition –BLANKITEM will be set to the &NODATA verbiage and you’ll see that at the top of the report.
-DEFAULT &REPORTLINES = 0;
-SET &REPORTLINES = &LINES;
…
DEFINE FILE DDP_I_PAGE_FOOTING
NOTHING_TO_REPORT /A100 = IF &REPORTLINES EQ 0 THEN '&NODATA.EVAL';
REPORT_DATE /A11 = '&DATED.EVAL' || '-' || '&DATEMt.EVAL' || '-' || '&DATEYY.EVAL';
REPORT_TIME /A8 = STRREP(8,'&TOD.EVAL',1,'.',1,':',8,REPORT_TIME);
BLANKITEM /A100 = IF NOTHING_TO_REPORT NE '' THEN NOTHING_TO_REPORT ELSE '';
DAT_YER_1 /A5 = DAT_YER;
END
-RUN
-*ON TABLE &PCHOLD FORMAT PDF &PDFSTATE &NOBREAK
-*ON TABLE PCHOLD FORMAT PDF
TABLE FILE DDP_I_PAGE_FOOTING
SUM
BLANKITEM AS ''
BY BLANKITEM NOPRINT
BY DAT_YER NOPRINT
FOOTING BOTTOM
" <+0>&REPORTNAME.EVAL/P"
" <+0>Page &PAGENO.EVAL of &TOTPAGES.EVAL"
WHERE READLIMIT EQ 1;
WHERE RECORDLIMIT EQ 1;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE &PCHOLD FORMAT PDF &PDFSTATE &NOBREAK
