CODEWITHSUNDEEP
crystal-reportsconcatenation
user2858200
user2858200

Reputation: 133

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

Upvotes: 1

Views: 6457

Answers (2)

Dave Deschere
Dave Deschere

Reputation: 1

Just a minor syntax error in Chris' second formula. The semicolon should be moved down a line, after the closing parenthesis of the FOR loop, so the last three lines would be:

fin := fin + a[j] + ", "
);
fin

Upvotes: 0

campagnolo_1
campagnolo_1

Reputation: 2750

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

Upvotes: 1

Related Questions