Aggregating Strings

erjablow

I have a J2EE/Struts project, and there is a database structure like:

Company

ID Name

1 Acme Corporation
2 Yoyodyne, Inc.
3 National Association of Federations

and

Locations

Company_ID Country
1 USA
1 Canada
2 USA
2 France
3 USA
3 Brazil

I would like a report that will print in a list entries like

Acme Corporation USA, Canada
Yoyodyne, Inc. USA, France
National... USA, Brazil

I have tried creating two linked data sets, one providing the first table, and one providing sections of the second table but dependant on the id in the first. However, I see no way to construct the string "USA, Canada" from the two rows in the LOCATION table with id 1 above. Were these numerical data, I'd be using Total.sum(rows["someValue"]). I don't see any way to use the join function across rows.

Do I need to do scripting here, or is there a cunning way to write something like column["Country"].join(", ")?

mwilliams

Hi erjablow,

All you have to do is make a table with the company name and country. You then group the table on either company id or company name. Delete the data elements from the display row. Add an aggregation element to the group header under Location(s). Use concatenate as the function, string for the type, row["Location"] for the expression, "," for the separator, a length that will cover the length of all the countries in a given group, enter true into the show all values, and aggregate on your company group. This should give you what you're looking for. Make sure you put a value in everywhere I said, or you'll end up with an error even though it lets you click ok.

Hope this helps.