Retreiving multiple rows of data in a single field

[tekniks]

Hi,

I have the following data:

Section Countries
1 USA
1 IND
1 CHN
2 KRA
2 BRZ


I want to retreive the name of countries for a particular section in a single field on the report with comma separator like this:
Section 1
Countries: USA,IND,CHN

Section 2
Countries: KRA,BRZ

Any ideas how to implement this in a formula field.

Thanks

Tek

 

[pgtek]

hi
have you tried a cross tab
in the row have the section and in the collumn the countries

cheers


pgtek

 

[tekniks]

thanks pgtek, but I don't want to add another sub-report to the main report for this due to efficiency reasons.

~Tek

 

[lbass]

Crosstabs don't require subreports and are processed quickly, but you wouldn't get a comma-separated list. You could use a variable to do this. First group on {table.section}. Then create three formulas:

//{@reset} to be placed in the group header and suppressed:
whileprintingrecords;
stringvar x;
if not inrepeatedgroupheader then
x := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x := x + {table.country} + ", ":

//{@display} to be placed in the group footer section:
whileprintingrecords;
stringvar x;
left(x,len(x)-2);

-LB