Multiple rows to 1 row - Please help new to CR. Thx. 1

[baycolor]

Hi, I'm using CR V10. Here is the issue...

Output wanted on my report:
ID Solution CustContacts OurContacts
1 CRM, EDW, SCM A, B C, D, E, F, G, H
2 ZBC, XYZ, DEF, KLM I J

Source Data:
Table 1
ID
1
2

Table 2
ID Solution
1 CRM
1 EDW
1 SCM
2 ABC
2 XYZ
2 DEF
2 KLM

Table 3
ID CustomerContacts OurContacts
1 A
1 B
1 C
1 D
1 E
1 F
1 G
1 H
2 I
2 J

As you can see I can join the tables by ID (NOTE: I must use Table 1 as first table) which is going to create a bunch of rows. Table 1 ID 123 joins to 3 rows in Table 2 which then will join to Table 3 create 24 rows.

My output can only have one row per ID. But for the last three output columns I want the data row values for Solution, CustomerContacts and OurContacts (for one ID) to be concatenated together into one field.

It is never known how many Solutions will be associated with one ID.
Neither is it known how many CustomerContacts or OurContacts will be associated with one ID.
Neither is it known how many unique IDs will from Table 1 will be waiting to be processed into the report.
Neither is there a max number of Solutions per ID.
Neither is there a max number of CustomerContacts or OurContacts per ID.

Somekind of looping with concatenation needs to be done probably using WhilePrinting, Previous or Next etc... but I'm not having any luck. And I'm not sure if I'm totally going off in the wrong direction with what I'm doing.

Does anyone have any code that can handle this (please note that I'm totally new to CR and everything seems to be a struggle so please be as specific as possible with any explanations and syntax).

Thanks in advance.

 

[kskid]

You will need basically 3 formulas each for Solutions, CustContact, and OurContacts

Group by ID

In the group header, place the following formula and suppress it.

WhilePrintingRecords;
global stringvar Sol := "";

In the detail section, place the following formula and suppress it.

WhilePrintingRecords;
global stringvar Sol;
sol := Sol + {table.solution};

Place the following formula in the group footer to display the results

WhilePrintingRecords;
global stringvar sol;

sol

-LW

-LW