I'm working on a report with mailing addresses. Some of the rows in the database are longer than others and I want to know how to control the spacing between each entry in the report.
In the report I want to format like this: Address Line 1 Address Line 2 (if not null) City, State Zip Country (only if not US) -- blank line -- [next entry]
My table file is this: TABLE FILE FINAL PRINT SUP_NUM AS '' AGENCY_NAME AS '' IN 7 ADDRESS_LINE1 AS '' IN 30 OVER ADDRESS_LINE2 AS '' IN 30 OVER LOCATION AS '' IN 30 OVER COUNTRY_LOC AS '' IN 30 BY SMO_NUMBER NOPRINT
However, for rows without a address line 2 or that are in the US, I get something like Addy Ln 1 . City, State Zip -- blank line -- -- blank line -- [next]
To bump city and state up, I added a define entry to see if addy line 2 is null, and if so replace with city state and zip. But then that gives me three blank lines at the end of the address and I only want one. How can I get around this? Using PDF formatThis message has been edited. Last edited by: BDAVIS,
WebFOCUS 7.6.3 | Solaris 10 | Excel, PDF
June 12, 2009, 12:21 PM
Darin Lee
I don't think you're going to find a way around that one without some trickery. If all of the fields COULD have data, you've got to show them all. Bubble sorting (more or less what you've done) moves all the non-blank lines to the top and blanks to the bottom, but they still show up. The only way I can think of is to create a hold file appending all of the address lines together and then creating an alternate multi-segment master file using an OCCURS=. In essence, this would allow you to only show as many lines as necessary to display non-blank values. I've seen the technique described on other posts - maybe search on OCCURS.This message has been edited. Last edited by: Darin Lee,
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
June 14, 2009, 02:23 AM
Danny-SRL
Darin has a good approach, using an alternate master. I also seem to recall that McGuyver can be used.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
June 15, 2009, 10:36 AM
Francis Mariani
One quick method is to change the font size to 1 for lines that are blank:
TABLE FILE CAR
PRINT
COMPUTE SALES_IND/D1 = IF SALES EQ 0 OR SALES IS MISSING THEN 0 ELSE 1; NOPRINT
COUNTRY AS '' OVER
CAR AS '' OVER
MODEL AS '' OVER
SALES/D5S AS '' OVER
BODYTYPE AS ''
BY COUNTRY NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
ON MODEL SUBFOOT
" "
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=8, $
TYPE=REPORT, COLUMN=P4, SIZE=1, WHEN= SALES_IND EQ 0, $
ENDSTYLE
END
There still is a gap, but it's very small, almost undetectable.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 15, 2009, 11:57 AM
Glenda
Here's one way.
-SET &&INCLUDE = '-INCLUDE';
-*
&&INCLUDE.EVAL GRPPFILE
-*
JOIN
EENMBR IN ELGEMP TO ALL ERNMBR IN ELGADR AS J1
END
-*
DEFINE FILE ELGEMP
GROUP/A55 = EEGPNO||('-'|EELOCA);
NAME/A55 = TRIM('B',(EEFNAM||(' '|EELNAM)),61,' ', 1, 'A55');
CITY_LINE/A55 = ERCITY||(', '|ERSTAT)||(' '|EDIT(ERZPCD, '99999'));
ADDR1/A55 = IF ERADR1 EQ '' OR ' ' THEN ERADR2 ELSE ERADR1;
ADDR2/A55 = IF ERADR1 EQ '' OR ' ' THEN CITY_LINE ELSE
IF ERADR2 EQ '' OR ' ' THEN CITY_LINE ELSE ERADR2;
ADDR3/A55 = IF ERADR1 EQ '' OR ' ' THEN '' ELSE
IF ERADR2 EQ '' OR ' ' THEN '' ELSE CITY_LINE;
ADDR4/A55 = '';
SPLINE/A55 = '';
END
-*
TABLE FILE ELGEMP
PRINT
NAME
ADDR1
ADDR2
ADDR3
ADDR4
SPLINE
BY EENMBR
BY GROUP
WHERE ERDPCD EQ 'e'
WHERE ERTODT EQ '99999999'
WHERE EEGPNO EQ '00136'
ON TABLE HOLD AS WORKDATA
END
-*
DEFINE FILE WORKDATA
LINEDATA/A55 = NAME;
LINENO/I9 = 2;
END
-*
TABLE FILE WORKDATA
PRINT LINEDATA
LINENO
BY EENMBR
BY GROUP
ON TABLE HOLD AS NAMDATA
END
-*
DEFINE FILE WORKDATA
LINEDATA/A55 = ADDR1;
LINENO/I9 = 3;
END
-*
TABLE FILE WORKDATA
PRINT LINEDATA
LINENO
BY EENMBR
BY GROUP
WHERE ADDR1 NE ''
ON TABLE HOLD AS A1DATA
END
-*
DEFINE FILE WORKDATA
LINEDATA/A55 = ADDR2;
LINENO/I9 = 4;
END
-*
TABLE FILE WORKDATA
PRINT LINEDATA
LINENO
BY EENMBR
BY GROUP
WHERE ADDR2 NE ''
ON TABLE HOLD AS A2DATA
END
-*
DEFINE FILE WORKDATA
LINEDATA/A55 = ADDR3;
LINENO/I9 = 5;
END
-*
TABLE FILE WORKDATA
PRINT LINEDATA
LINENO
BY EENMBR
BY GROUP
WHERE ADDR3 NE ''
ON TABLE HOLD AS A3DATA
END
-*
DEFINE FILE WORKDATA
LINEDATA/A55 = ADDR4;
LINENO/I9 = 6;
END
-*
TABLE FILE WORKDATA
PRINT LINEDATA
LINENO
BY EENMBR
BY GROUP
WHERE ADDR4 NE ''
ON TABLE HOLD AS A4DATA
END
-*
DEFINE FILE WORKDATA
LINEDATA/A55 = SPLINE;
LINENO/I9 = 7;
END
-*
TABLE FILE WORKDATA
PRINT LINEDATA
LINENO
BY EENMBR
BY GROUP
ON TABLE HOLD AS SPLINE
END
-*
TABLE FILE NAMDATA
PRINT LINEDATA
BY EENMBR
BY GROUP
BY LINENO
ON TABLE HOLD AS FDATA
MORE
FILE A1DATA
MORE
FILE A2DATA
MORE
FILE A3DATA
MORE
FILE A4DATA
MORE
FILE SPLINE
END
-*
DEFINE FILE FDATA
SPLINE/A55 = '';
END
-*
TABLE FILE FDATA
PRINT
LINEDATA AS ''
BY EENMBR AS ''
BY GROUP AS ''
BY LINENO NOPRINT
END
-*
-EXIT
Hope it helps.
Glenda
In FOCUS Since 1990 Production 8.2 Windows
June 17, 2009, 11:55 AM
BDAVIS
Francis' method was simple and ended up working for my uses... thank you very much. And thanks to everyone else for their help.