Array formulas 2

[O2BNSV]

If I have a report that has one group call Registrant Name and the detail includes the registrant's guests, is there a way to create an array or concatenation formula for the detail. For Example:

Group #1 = John Doe
Detail = Jane Doe
Tom Smith

is there a way to combine the detail in a group footer formula so that it would read "Jane Doe, Tom Smith"?

 

[PatriciaObreja]

You have to create one formula that will be placed in group header and will contain:

stringvar x := "";

And then another formula placed in Details section and also in Group Footer. In Details section it will be Supressed.

stringvar x := x & ", " & {Orders.Order ID}

 

[lbass]

Tweaking those a bit:

//{@reset} for GH:
whileprintingrecords;
stringvar x := "";

//{@accum} for details:
whileprintingrecords;
stringvar x := x + {table.name} + ", ";

//{@displ} for GF:
whileprintingrecords;
stringvar x;
if len(x) > 2 then
left(x,len(x)-2)

-LB

 

[O2BNSV]

This is a bit picky. Lbass, your suggestion works great. Is their a way to insert "and" before the last guest in the list?

 

[lbass]

Change the display formula to this:

//{@displ} for GF:
whileprintingrecords;
stringvar x;
stringvar array y := split(x,", ");
numbervar j := ubound-1;

left(x,instr(x,y[j])-1)+
(
if j > 1 then "and "+ y[j]
else y[j]
)

-LB

 

[briesen]

I have a follow-up to this question. I used this same logic although for a different type of summary. Mine is taking a six-digit order number and kicking back a bunch of three digit numbers (another field) in the same way this thread is. The only issue is that the three digit numbers are used multiple times per group.

How can I make it so it doesn't duplicate these numbers?

For example:

Order Number 100352 comes up with 150,070,050,150,150,150.

How do I make it so 150 is only listed once?

 

[lbass]

Use this kind of syntax:

//{@accum} for details:
whileprintingrecords;
stringvar x;
if instr(x,{table.name}) = 0 then
x := x + {table.name} + ", ";

The initial clause says that if the field is not already in the string, then add it.

-LB

 

[Scroller52]

If I have a report that has one group call Registrant Name and the detail includes the registrant's guests, is there a way to create an array or concatenation formula for the detail. For Example:

Group #1 = John Doe
Detail = Jane Doe
Tom Smith

is there a way to combine the detail in a group footer formula so that it would read "Jane Doe, Tom Smith"?"

lbass, i've got a very similar issue. instead of displaying the formula in the group footer, i need it in the group header. how is this achieved?

 

[lbass]

The display formula only works in the group footer, since it is based on a previous sequence of data. You could instead insert a crosstab in the group header that uses your detail field as the column field and a maximum of that field as the summary. You would have to eliminate the grid and suppress some unwanted labels though.

-LB

 

[Scroller52]

that somewhat works, but new issues come up. the crosstab columns cant grow so it might not be able to fit all of the letters of the names. there is not comma to separate the names and can it doesnt go to the next line when it hits the end of the page.

the only reason i need the contacts to be displayed across is because when there are a lot of contacts for an investor, it will push text onto the next page.

 

[lbass]

You could insert a subreport that uses the earlier formulas to concatenate the values, and then suppress all subreport sections except the report footer and then link the sub to the main report on the group field and place the sub in the group header.

Another method that might work if you know the maximum number of values (in this example, 6) that any group will have, is to use a formula like this (also requires that the values be unique within the group:

nthsmallest(1,{table.guest},{table.registrant}) +
(
if distinctcount({table.guest},{table.registrant}) >= 2 then
", "+nthsmallest(2,{table.guest},{table.registrant})
) +
(
if distinctcount({table.guest},{table.registrant}) >= 3 then
", "+nthsmallest(3,{table.guest},{table.registrant})
)+
(
if distinctcount({table.guest},{table.registrant}) >= 4 then
", "+nthsmallest(4,{table.guest},{table.registrant})
) +
(
if distinctcount({table.guest},{table.registrant}) >= 5 then
", "+nthsmallest(5,{table.guest},{table.registrant})
) +
(
if distinctcount({table.guest},{table.registrant}) >= 6 then
", "+nthsmallest(6,{table.guest},{table.registrant})
)

-LB