We have a need to display addresses in a report. The address...

[Peter Vazny]

We have a need to display addresses in a report. The address length varies from 3 to 6 lines and is stored in fields AddressLine1 through AddressLine6 in a single row per record. If the address has less than 6 lines, the blank lines are at the end (starting from AddressLine6).

On top of that, we need the report to work both as a PDF and HTML (and if at all possible as a spreadsheet). The report needs to be able to be open in InfoAssist, even if there are some requirements to initially edit via text editor.

I tried:

 

MARKUP=ON in conjunction with <BR> tags, but it gets ignored in HTML report and garbles up PDF report.

HTMLENCODE=OFF but it messes up column width and either makes the columns unnecessarily wide (with WRAP=OFF), or it will arbitrarily wrap columns to fit the whole table to the screen.

placing the address in the subfoot, but I am not able to get rid of blank address lines, which makes the subfoot too long.

[Martin Yergeau]

Have you tried something such as

-* Assuming that each line is defined as A100V

DEFINE FILE xyz

fullAdr /A610V = TRIM_(BOTH, ' ', TRIM_(BOTH, ' ', AddressLine1) || ' ' | TRIM_(BOTH, ' ', AddressLine2) || ' ' | TRIM_(BOTH, ' ', AddressLine3) || ' ' | TRIM_(BOTH, ' ', AddressLine4) || ' ' | TRIM_(BOTH, ' ', AddressLine5) || ' ' | TRIM_(BOTH, ' ', AddressLine6));

END

[David Briars]

I call this an address directory/book type of requirement.

You might want to consider UNPIVOTing your data, so

Name AddressLine1 AddressLine2 AddressLine3

becomes

Name AddressLine1

Name AddressLine2

Name AddressLine3

 

There are many ways to do the UNPIVOT. Which way would be best would depend on the file type and size of your input file, e.g., are you dealing with data already extracted.

The IBI sample file EMPLOYEE, has PIVOTed AddressLine1, AddressLine2, and AddressLine3.

Let me know of an example against EMPLOYEE would be of assistance.

[Peter Vazny]

This seems like it will only concatenate the address lines together into one line. What I am looking for is something like:

Customer Name Address line 1 Date Entered Date Last Contact

Address line 2

Address line 3

[Peter Vazny]

David, I can unpivot address lines if necessary. Can you provide an example on how that would help One thing that I did not mention is that there is need for other data to be displayed in the same row as well as totaled/subtotaled per section.

[David Briars]

Oh, OK, if youve already UNPIVOTed your data, then it is a matter of grouping the data (for each customer in your case) and then deciding to show/not show the data based on the position of the report row in the group.

In the example below I UNPIVOT the EMPLOYEE Address Line data separately from the final report. Sounds like you might only need to take a look at the final report step:

 

image.png679815 21.5 KB

 

-* Environmental settings.

SET PAGE = OFF

-* Create a temporary/dummy file containing three records

TABLE FILE GGSALES

PRINT SEQ_NO

COMPUTE BLANK/A3 = ' ';

IF SEQ_NO LE 3

ON TABLE HOLD AS HLD3RECS FORMAT FOCUS INDEX BLANK

END

-RUN

-* UNPIVOT Address Line data.

JOIN BLANK WITH ADDRESS_LN1 IN EMPLOYEE

TO ALL

BLANK IN HLD3RECS

END

-*

DEFINE FILE EMPLOYEE

BLANK/A3 WITH ADDRESS_LN1 = ' ';

NAME/A26 = FIRST_NAME | ' ' | LAST_NAME ;

ADDRESS_LN/A20 = IF SEQ_NO EQ 1 THEN ADDRESS_LN1 ELSE

IF SEQ_NO EQ 2 THEN ADDRESS_LN2 ELSE

ADDRESS_LN3;

END

-*

TABLE FILE EMPLOYEE

PRINT ADDRESS_LN

ED_HRS

BY EMP_ID

BY NAME

BY TYPE

IF ADDRESS_LN NE ' '

IF EMP_ID EQ 071382660 OR 112847612 OR 326179357 OR 818692173 OR 119329144

ON TABLE HOLD AS HLDEXTR

END

-RUN

-* Create Education Hours Report

DEFINE FILE HLDEXTR

-*

NEW_GROUP_YN/A1 = IF ((EMP_ID EQ LAST EMP_ID) AND

(NAME EQ LAST NAME) AND

(TYPE EQ LAST TYPE)) THEN 'N' ELSE 'Y';

-*

LINECOUNTER/I1 = IF NEW_GROUP EQ 'Y' THEN 1 ELSE

LINECOUNTER + 1;

-*

EDHRS/F6.2S = IF LINECOUNTER EQ 1 THEN ED_HRS ELSE 0;

NAMEOUT/A26 = IF LINECOUNTER EQ 1 THEN NAME ELSE ' ';

EMPIDOUT/A9 = IF LINECOUNTER EQ 1 THEN EMP_ID ELSE ' ';

-*

END

-*

TABLE FILE HLDEXTR

"Education Hours by Address Type/Employee. "

PRINT

EMPIDOUT AS 'Employee ID'

NAMEOUT AS 'Name'

ADDRESS_LN AS 'Address'

EDHRS AS 'Education,Hours'

BY TYPE AS 'Address,Type'

ON TYPE SUBTOTAL AS 'Total for Address Type: '

ON TABLE SET STYLE *

INCLUDE=IBFS:/WFC/Repository/FOLDER1/code/stylesheets/warm.sty, $

ENDSTYLE

END

[Warren Hinchliffe]

I think " David Briars" technique, effectively the MacGyver technique, will work. As long as you build the steps in Info Assist.

[Peter Vazny]

I see. I am guessing there is no way to apply different background shading per employee, as this is essentially using multiple rows per record.

[Peter Vazny]

Waz, I cannot wrap my head around how I could approach this in InfoAssist. Maybe I am overthinking this, but I am not sure how I would go about this JOIN for example:

JOIN BLANK WITH ADDRESS_LN1 IN EMPLOYEE

TO ALL

BLANK IN HLD3RECS

END

 

What throws me off is the use of a defined field, which I dont think is possible to use in a JOIN in InfoAssist. Also what does the WITH do or why is it necessary in both the JOIN and the DEFINE. I found the definition in documentation, but I still dont understand it.

 

 

WITH can be used to move the logical home for the virtual field to a segment lower than that to which it would otherwise be assigned (for example, to count instances in a lower segment).

[David Briars]

I am guessing there is no way to apply different background shading per employee

 

Yes, you could apply styling to the groups as/if needed.

 

image.png669698 17.8 KB

 

-*

-* Environmental settings.

-*

SET PAGE = OFF

SET DROPBLNKLINE = ALL

-*

-* Create a temporary/dummy HOLD file containing three records

-* HOLD file will be used in the next step to UNPIVOT the three ADDRESS_LNn EMPLOYEE fields.

-*

TABLE FILE GGSALES

PRINT SEQ_NO

COMPUTE BLANK/A3 = ' ';

IF SEQ_NO LE 3

ON TABLE HOLD AS HLD3RECS FORMAT FOCUS INDEX BLANK

END

-RUN

-*

-* UNPIVOT EMPLOYEE Address Line data.

-*

JOIN BLANK WITH ADDRESS_LN1 IN EMPLOYEE

TO ALL

BLANK IN HLD3RECS

END

-*

DEFINE FILE EMPLOYEE

BLANK/A3 WITH ADDRESS_LN1 = ' ';

NAME/A26 = FIRST_NAME | ' ' | LAST_NAME ;

ADDRESS_LN/A20 = IF SEQ_NO EQ 1 THEN ADDRESS_LN1 ELSE

IF SEQ_NO EQ 2 THEN ADDRESS_LN2 ELSE

ADDRESS_LN3;

END

-*

TABLE FILE EMPLOYEE

PRINT ADDRESS_LN

ED_HRS

BY EMP_ID

BY NAME

BY TYPE

IF ADDRESS_LN NE ' '

IF EMP_ID EQ 071382660 OR 112847612 OR 326179357 OR 818692173 OR 119329144

ON TABLE HOLD AS HLDEXTR

END

-RUN

-*

-* Create Education Hours Report

-*

DEFINE FILE HLDEXTR

-*

NEW_GROUP_YN/A1 = IF ((EMP_ID EQ LAST EMP_ID) AND

(NAME EQ LAST NAME) AND

(TYPE EQ LAST TYPE)) THEN 'N' ELSE 'Y';

-*

LINECOUNTER/I1 = IF NEW_GROUP EQ 'Y' THEN 1 ELSE

LINECOUNTER + 1;

-*

EDHRS/F6.2S = IF LINECOUNTER EQ 1 THEN ED_HRS ELSE 0;

NAMEOUT/A26 = IF LINECOUNTER EQ 1 THEN NAME ELSE ' ';

EMPIDOUT/A9 = IF LINECOUNTER EQ 1 THEN EMP_ID ELSE ' ';

-*

END

-*

TABLE FILE HLDEXTR

"Education Hours by Address Type/Employee. "

PRINT

EMPIDOUT AS 'Employee ID'

NAMEOUT AS 'Name'

ADDRESS_LN AS 'Address'

EDHRS AS 'Education,Hours'

NEW_GROUP NOPRINT

-* Create alternate shading for groups.

COMPUTE BACKGROUNDFLAG/A16 = IF NEW_GROUP EQ 'Y' AND LAST BACKGROUNDFLAG EQ 'GREEN' THEN 'YELLOW'

ELSE IF NEW_GROUP EQ 'Y' THEN 'GREEN' ELSE LAST BACKGROUNDFLAG; NOPRINT

-*

BY TYPE AS 'Address,Type'

ON TYPE SUBTOTAL AS 'Total for Address Type: '

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = warm.sty, $

TYPE = DATA, BORDER=OFF, $

TYPE = DATA, BACKCOLOR=RGB(#dff0d8), WHEN = BACKGROUNDFLAG EQ 'GREEN',$

TYPE = DATA, BACKCOLOR=RGB(#fcf8e3), WHEN = BACKGROUNDFLAG EQ 'YELLOW',$

ENDSTYLE

END

[Pawan Vuppala]

Im wondering if you can COMPUTE a new field with carriage return (ASCI character) or tags within each field, HTML would be easy to tackle for sure, PDF is a different animal.

[David Beagan]

Yes, using carriage return characters, try running this code:

-DEFAULT &WFFMT = 'PDF';

 

TABLE FILE ibisamp/employee

PRINT EMP_ID

COMPUTE LB/A4 = IF '&WFFMT.(PDF).' EQ 'PDF' OR 'XLSX' THEN CHAR(13) | CHAR(10) ELSE '<BR>'; NOPRINT

COMPUTE ADDR2/A24 = IF ADDRESS_LN1 EQ ' ' THEN ADDRESS_LN2 ELSE LB || ADDRESS_LN2; NOPRINT

COMPUTE ADDR3/A24 = IF ADDRESS_LN2 EQ ' ' THEN ADDRESS_LN3 ELSE LB || ADDRESS_LN3; NOPRINT

COMPUTE ADDRS/A70 = ADDRESS_LN1 || ADDR2 || ADDR3;

LAST_NAME

FIRST_NAME

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE PCHOLD FORMAT &WFFMT.(PDF,XLSX,HTML).

ON TABLE SET STYLE *

TYPE=REPORT, ORIENTATION=LANDSCAPE, SIZE=10, SQUEEZE=ON, $

TYPE=REPORT, COLUMN=ADDRS, WRAP=1.6, LINEBREAK='CRLF', $

ENDSTYLE

END

 

Unfortunately, the LINEBREAK=CRLF is not compatible with InfoAssist. I thought maybe if I put it in a .sty then it could work with InfoAssist. Nope, didnt work and it wasnt pretty.

[Peter Vazny]

David,

Very interesting. Both the ability to use different line breaks based on report type and the LINEBREAK= parameter. I have found documentation for that: Displaying Multi-Line An and AnV Fields. It is specifically related to PDF and PostScript only.

Unfortunately, the inability to be used in InfoAssist is currently a no go for me. Found a reference here: https://techsupport.informationbuilders.com/public/wfn/11-1/05_denst.html

 

This is an advanced feature that can only be accomplished with a text editor.

 

A couple more notes for people with a similar problem:

 

HTMLENCODE has to be set to OFF for the <BR> to not get escaped/ignored, this however changes how column width is handled.

for users running on an EBCDIC based system, such as IBM i, the linefeed character has to be changed to 21: CHAR(13) | CHAR(21)