Concatinating fields then breaking them out into separate objects. 1

[kimbred]

I have written a report that list the employees and their dependents grouped by benefit. This report needs to be rewritten to display the employee and the employee's dependents all on one line. I have got the report to concatinate the fields, but am having trouble breaking them back out into individual objects. I have attached the original report as well as the new report and the spreadsheet requested. Any help out there?

 

[lbass]

No, they are not attached. Why not just show some mock data that reflects the current layout? Please show the content of your accumulation formula and explain in what report section you are displaying the results. Then show how you want it to display.

-LB

 

[kimbred]

Original Report:
123456 Jeff Jones 5100002323 Address Data
Jenny Jones 511111111 Spouse
Jack Jones 111111111 Child
Judy Jones 222222222 Child
234567 Marlin Marvel 511884444 Address Data
Mary Marvel 555555555 Child
Merlin Marvel 777777777 Child
Desired Report:
123456 Jeff Jones 510002323 Jenny Jones 511111111 Jack Jones
234567 Marlin Marvel 511884444 " " " " Mary Marvel 555555555

Note that the second entry has no spouse listed so we have to leave a blank field for the name and SSN of the spouse.

 

[lbass]

You said in your original post that you had got the values to concatenate, but that you didn't know how to then break them apart. You haven't shown the formula you used to do the concatenation. Also, it is unclear why you want to break the resulting string apart again. Are you aiming for a certain spacing?

-LB

 

[kimbred]

ConcatHdr:

WhilePrintingRecords;
Shared stringVar ConCat := "";
ConCat := ToText ({F08336.BJAN8},0,"") + "|" + {F060116.YAALPH} + "|" + {F060116.YASSN} + "|"

ConcatDtl:
WhilePrintingRecords;
Shared stringVar ConCat;
Shared numberVar S;
IF ({@Relationship} <> "Spouse") Then
(
If (S = 0) Then
(
ConCat := ConCat + " " + "|" + " " + "|" + {F08901.HUALPH} + "|" + {F08901.HUSSN} + "|"
S := 1;
)
ELSE
(
ConCat := ConCat + {F08901.HUALPH} + "|" + {F08901.HUSSN} + "|"
S := 1;
)
)
ELSE
(
S := 1;
ConCat := ConCat + {F08901.HUALPH} + "|" + {F08901.HUSSN} + "|"
)

The reason I'm trying to do this is because our HR department wants an excel spreadsheet they can use for a mail merge. Rather than the dependents listed vertically they want them listed horizonally.

 

[lbass]

If this is working for you so far, but you want each element in its own cell in Excel, then create formulas like this for each possible element (this example is for the second element in the string):

evaluateafter ({@yourGFdisplayformula});
whileprintingrecords;
shared Stringvar Concat;
stringvar array x := split(Concat,"|");
numbervar j := ubound(x);
if j >= 2 then
x[2]

...where {@yourGFdisplayformula} looks something like this:
whileprintingrecords;
shared Stringvar Concat;

Repeat the element formula up to the maximum number of elements and add each to the group footer.

-LB