Displaying Distinct Values from a column in crystal report.

[Former Member]

Hi,

i have a requirement, i have to display list of plant names field where plant number is the prompt in BEx query , i wrote the below code to get the plant name list from the field, but plant name field contain repetitive values and those values are not unique because  of that i am getting first plant name multiple time instead of all plant names. please help me in finding the solution.

Formula Code:

Stringvar s:="";

Numbervar i:=3;

For i:=1 to distinctcount({0PUR_C01_YQRY_0PUR_C01_003.[0PLANT]})

do s:=s +" "+totext(join(makearray(GroupName ({0PUR_C01_YQRY_0PUR_C01_003.[0PLANT]})),','));

s [1 to length(s)];

Thanks,

Ambreen

[Former Member]

Hi Ambreen,

Why is i:=3? Where have you placed the formula? If you place it in Report Footer, it should work.

Alternatively, Abhilash's formula will be helpful since the 'array' size is directly based on GroupNumber.

-Prathamesh

[Former Member]

Hi Prathamesh,

Formula is correct, its not working because the plant field contains repetitive records, and the i:=1 i have used in formula. i am unable to find how to filter the duplicate records in the formula. so that it will display the complete list .

thanks,

ambreen

[Former Member]

I'm wondering how there will be any duplicate records since the array list is based on the GroupName().

-Prathamesh

[abhilash_kumar]

Hi Prathamesh,

Yes, you're right!

The array list does not and will never have duplicates since its based on the Group.

I believe Ambreen sees only 1 value as the width of the field is not large enough to fit all the records. She needs to check the 'Can Grow' option.

-Abhilash

[Former Member]

hi abhilash,

i am getting the list of repetitive values of plant even after using groupname().

thanks,

ambreen.

[abhilash_kumar]

Where is the 1st formula that contains the array placed? Place that on the Group Header/Group Footer.

-Abhilash

[abhilash_kumar]

Also, if you have any more groups after the {0PUR_C01_YQRY_0PUR_C01_003.[0PLANT]} group, make sure the 1st formula is in Group Header 1 and not on the other Group's Headers.

-Abhilash

[Former Member]

hi abhilash,

i have only group in my report that is plant group. and the formula which i am using is

Stringvar s:="";

Numbervar i:=1;

For i:=1 to distinctcount({0PUR_C01_YQRY_0PUR_C01_003.[0PLANT]})

do s:=s +" "+totext(join(makearray(GroupName ({0PUR_C01_YQRY_0PUR_C01_003.[0PLANT]}))));

s [1 to length(s)];

thanks,

ambreen

[abhilash_kumar]

Let's try this again. Can you try replacing the 1st formula with this code:

WhilePrintingRecords;
stringvar array plant;

numbervar x;

if NOT({0PUR_C01_YQRY_0PUR_C01_003.[0PLANT]} IN plant) then

(

     x := x + 1;

     redim preserve plant[x];

     plant[x] := {0PUR_C01_YQRY_0PUR_C01_003.[0PLANT]};

);

"" //Don't miss these quotes at the end of the formula - we need it.

Then use the 2nd formula in the Report Footer. This in no way can contain duplicates. The other formula should also not contain duplicates. Anyway, please try this and let us know.

-Abhilash

[GSG]

@Abhilash Kumar, the provided formula has helped me. I was struggling with the report for a long time. Finally, I got my solution. Thank you

Gurpreet