Search code examples
crystal-reportsconcatenation

Concatenate distinct values within one column in crystal reports

i have a recordset in details section

Example:

Column1----Column2
value_1----5678976
value_2----7263535
value_1----1938773
value_2----3333445
value_3----2848465

So at the report header, i need to specify all distinctive values from Column1, comma separated. From mentioned example correct string will be:

value_1,value_2,value_3

I tried to use formula

WhilePrintingRecords;
shared stringvar final_result;
shared stringvar current_value;

if len(final_result) = 0 then final_result = "";

current_value:= {Column1};
final_result := final_result+ "," + current_value;

This is doesn't work

Tried different one

WhilePrintingRecords;
stringVar final_result := "";
final_result = final_result + (
if InStr(final_result, {Column1}) > 0 then ""
else final_result := "," + {Column1};
);

Also no result. Where did i go wrong?

Thanks

Solution

  • In order to make the formula show anything, you will need to call the values after you declare them. So your formula should look more like this:

    WhilePrintingRecords; 
shared stringvar final_result; 
shared stringvar current_value := {Column1};
if len(final_result) < 1 then final_result := {Column2}; 
final_result + ", " + current_value

    The problem is that you will only get one record since the formula is in the Report Header. The same would happen if you created an array and tried to display it in the report header. I think your best bet would be to create a subreport that you then display in the report header.

    Cheers,

    Chris

    EDIT: This took me a while but I think I finally got it. The key is the difference between WhileReadingRecords and WhilePrintingRecords. You need to fill the first formula while reading the records, that will ensure that when you actually go to display the data (WhilePrintingRecords), the formula in the Header has something in it to display. So here it goes: Formula 1 (place in Detail section):

    WhileReadingRecords;
stringvar array a;
numbervar i;
if not({Column1} in a) then
(
i := i + 1;
redim preserve a[i];
a[i] := {Column1};
);
a[i]

    Formula 2 (place in Report Header):

    WhilePrintingRecords;
stringvar array a;
stringvar fin;
numbervar j;
for j := 1 to ubound(a) do
(
fin := fin + a[j] + ", ";
)
fin

    Hope this works now. -Chris