Skip to Content

Concatenate a field into a single cell seperated by comma

I have an output like,

Name ID
Pete 1
Pete 2
Mark 3
Mark 4

The expected output is,

Name ID
Pete 1,2
Mark 3,4

To do so,

1     Group the report on the Name

Create a formula Group_header  and place it in the Group Header section

  WhilePrintingRecords;

    StringVar chain := ”;

    NumberVar ChCnt := 1

2     Create a formula Details and place it in the Details section

WhilePrintingRecords;
    StringVar Item:= {ASPENTDMProd_query.Customer Name};
    StringVar Chain;
    NumberVar ChCnt;

if  ChCnt = 1
    then (ChCnt:= 2; chain := Item)
    else
if instr(Chain, Item) = 0 then
    if Length(Chain) + Length(Item) > 254
    then Chain := Chain else
    chain := chain + ‘, ‘ + Item

3     Create a formula Group_Footer and place it in the Group Footer Section

WhilePrintingRecords;

    StringVar Chain

4     Suppress the Details/Footer Section based on how the output is required

Be the first to leave a comment
You must be Logged on to comment or reply to a post.