Crystal Reports: How to concatenate a string to display 'detail' records in a header section?

[Former Member]

Within the details section I have a field "Country of origin" so this varies per item. I am trying to acheive a fiel ideally in the header (but could be a footer) which displays this information together in a line.

Eg -

Details A - field = UK

Details B - field = US

Details C - field = China

Details D - field = UK

The header or footer field would show - UK, US, China

Please note UK only appears once even though it is in both detail A and D.

Really struggling to get this to work so any help would be greatly appreciated?

Thanks.

[abhilash_kumar · Accepted solution]

Hi Alan,

Here's what you need to do:

1) Create this formula and place it on the Details Section:

WhilePrintingRecords;
stringvar array arr;
numbervar i;
if not({Customer.Country} in arr) then
(
    i := i + 1;
    redim preserve arr[i];
    arr[i] := {Customer.Country};
);
arr[i]

You may choose to suppress this formula

2) Create another formula to display the concatenated string. Place this formula on the footer:

WhilePrintingRecords;
stringvar array arr;
stringvar fin;
numbervar j;
for j := 1 to ubound(arr) do
fin := fin + arr[j] + ", ";
left(fin,len(fin)-2);

Let me know how this goes!

-Abhilash

[Former Member · Accepted solution]

Thanks Abhilash. The solution works well and I can use it as a group or report footer. In an ideal world I would like to be able to display the field as a page header.

It doesn't seem to let me do this...is this possible?

Alan

[abhilash_kumar · Accepted solution]

Hi Alan,

This will NOT work in the Page Header because the values are accumulated in the Details Section.

You can, however, show these values in the Page Header by using a Subreport. But that would mean running the same query twice (hitting the database twice).

All you need to do is add the same report as a subreport and place it in the Report Header.

Change the variable in the 2nd formula to a shared variable:

WhilePrintingRecords; 

stringvar array arr; 

shared stringvar fin;

.

.

.

...and the rest of the code is the same;

Then create a new formula in the Main Report to access the shared variable:

shared stringvar fin;

Place this formula in the Page Header and you should see that the variable returns all the values.

-Abhilash

[Former Member · Accepted solution]

Thanks for your help with this last week. I've tested the solution and it works well.

Alan

[Former Member · Accepted solution]

Hi Abhilash: I tried this method and it builds the array fine I presume. But the array accumulates across each group when I need it to clear out with each group. If I put a redim statement in a group header section. I get an array error. If I do not use redim in the group header the array builds and builds across all groups. If not the array building across all groups the stringvar "fin" is not clearing out with each group so I get a footer with

abcd, defg, ikjl, for first group (great it works)

for group2 I get abcd, defg, ijkl, abcd, pqrst,

then group 3

abcd, defg, ijkl, abcd, pqrst, trsu, xyz

WhilePrintingRecords;

stringvar array arr;

numbervar i;

if not({Customer.Country} in arr) then

(

    i := i + 1;

    redim preserve arr[i];

    arr[i] := {Customer.Country};

);

arr[i]

You may choose to suppress this formula

2) Create another formula to display the concatenated string. Place this formula on the footer:

WhilePrintingRecords;
stringvar array arr;
stringvar fin;
numbervar j;
for j := 1 to ubound(arr) do
fin := fin + arr[j] + ", ";
left(fin,len(fin)-2);

[abhilash_kumar · Accepted solution]

You need to create a reset formula in the Group Header that sets all the variables to defaults.

WhilePrintingRecords;

stringvar array arr := "";

stringvar fin := "";

numbervar j := 0;

numbervar i := 0;

Keep adding all the variables if I missed out on any.

-Abhilash

[Former Member · Accepted solution]

Hi may I know how to display play it per group? I am try to create a comma delimited Docnum. and group it per customer.

[Former Member · Accepted solution]

What if we need to have the same concatenated string in the Group header and not in Group footer?

[abhilash_kumar · Accepted solution]

Use a Subreport.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Your answer is very helpful. Nevertheless I am struggling with the following example:

Field Num1:

- Detalis A: UK

- Details B: USA

- Details C: China

Field Num 2:

- Details A: Spain

- Details B: UK

The result should be:

China, Spain, UK, USA

Please someone help me!!

[abhilash_kumar · Accepted solution]

Hi Isak,

Please post your question as a new discussion and post as much details as possible.

Screenshots would be great too.

-Abhilash

[Former Member]

Can this be told to reset on the change of a field.  I need the concatenation to be grouped on a particular field or two.

Thank you,
Bobby

[Former Member]

Hi Bobby,

If you use a string variable to concatenate the records directly (in Details section) then this variable can be reseted in group header.

Thanks,

Prathamesh

[Former Member]

Should I create another formula to reset the stringvar fin? I'm not sure how to accomplish this.

Thank you,
Bobby

[Former Member]

I think, reseting stringvar fin won't help. This is a mystery why a variable displaying the array value does not get reseted. Even an attempt to reset the array itself fails. I worked on this but couldn't get it right.A forum post by my respected senior colleague Jamie Wiseman on "reseting dynamic array" also does not seem to work in this case(may be I am wrong).

If someone can share their thoughts that would be great.

A working alternative is to create a manual running total  instead of an array.

Here's how:

1.Create 'Collect Values' formula and place it in 'Details' section:

Whileprintingrecords;
stringvar getCountryOforigin:= getCountryOfOrigin + {table.CountryOfOrigin} + ','

2. Create 'Show Values' formula and place it in 'Group Footer' section:

Whileprintingrecords;
stringvar getCountryOforigin;

3.Create 'Reset Value'  formula and place it in 'Group Header' section:

Whileprintingrecords;
stringvar getCountryOforigin:=''

Note:

1.It is possible to show the comma-separated values in Group Header also.

   Just place a sub report in Group Header(of main report) and link on

   CountryOforigin field and 'Hide' all sections of sub report except Group Footer.

2. Using long strings in Crystal Reports is always an issue because of the limit on length of string that a variable can hold.

   But this can always be checked and multiple string variables can be assigned.

   For that matter, same is the case with array size that has max limit of 1000 elements.

Thanks,

Prathamesh

[Former Member]

Bobby, would you like to repost your querry as a separate discussion along with the solution provided by me..

We might get suggestions from other experts !!

Thanks,

Prathamesh

[SebastianFuchs]

1. Generate a formula to define a StringVar Array
   StringVar Array arrCountries;
2. Generate a formula in your details section which checks if the value already exists in the array, otherwise add value to array
   Redim Preserve arrCountries [Count(arrCountries) + 1];
   arrCountries[Count(arrCountries)] := country;
3. Generate a formula to output your value list
   For i:= 1 to Length(arrCountries) Do {
     arrCounries[i];
   }

This is just example code, but I hope it helps you.