Multiple Numbers to one line of text

[GLmill]

I have a report that contains records with a "Number A" and a "Number B". The Number A is what is used for Grouping. What I need to be able to do is list all the Number B's that are contained in the records of that group. Here is an example of what the records look like.

Number A Number B
1 1
1 1
1 1
1 2
1 2
1 3
2 1
2 1
2 2
and so on.

I have the Number A's grouped and totaled and would like to have the report look like this.

Number A Number B
1 1, 2, 3
2 1, 2

Please Help!!!!!!!!
I have tried everything that I can think of and know that this is a very easy formula to write. But, I am way off somehow.

 

[synapsevampire]

It's not all that easy...

I'm going to assume that you don't want the list of all B's, you want the list of all distinct B's as in the example, not the description.

Group by A, and in the group header of A create a formula like:

whileprintingrecords;
stringvar MyNums := "";

In the details place a formula like:
whileprintingrecords;
stringvar MyNums := MyNums+totext({table.Bfield},0,"&quot+",";

In the group footer use something like:
whileprintingrecords;
stringvar MyNums;
left(MyNums, len(MyNums)-1)

This has a 254 char limitation in CR 8.5 or below.

-k

 

[lbass]

To only show distinct values for "Number B," you could amend SV's formula to the following:

whileprintingrecords;
stringvar MyNums;

if instr(MyNums,{table.Bfield}) = 0 then
MyNums := MyNums+totext({table.Bfield},0,"&quot+"," else
MyNums := MyNums;

-LB

 

[synapsevampire]

Thanks, LB, forgot the direction there...

The details formula should have been in an additional group footer on based on Number B.

-k

 

[GLmill]

The formula is working except I am not getting a distinct count. The result looks like this for the B field: 1, 1, 1

 

[GLmill]

I was able to get the distinct count of Number B's that I needed by using the following variation of the formula above in the details section:

whileprintingrecords;
stringvar MyNums;


if instr(MyNums,totext({PRODUCTINFO.SequenceNo},0,"&quot) = 0 then
MyNums := MyNums+totext({PRODUCTINFO.SequenceNo},0,"&quot+"," else
MyNums := MyNums;