Display distinct field values with comma-separator 1

[tekniks]

Hi,

I have the following records from the database:

MatID Country
2 US
2 PR
2 PR
2
2
2 US
2 CA
2 US
2 US

Display Requirements:
1) The values in field Country should be comma-separated
2) Duplicates should be suppressed.
3) The values have to be displayed first across and then down (can be acheived by formatting the section with Multiple columns)
4) There should not be any space separating the two distinct values when the Suppress duplicate is checked.

I did the part 2) and 3) but still the results shows duplicate values.

Any suggestions on how to accomplish all...

TKS

Tek

 

[synapsevampire]

It's a shame you didn't just demonstrate what the ouput would be rather than describing it, you managed to show the data, why not the intended output? You might also mention technical information, such as Crystal and database version, those are also important factors.

I'll assume that you want:

MTID Country
2 CA, PR, US

Select Database->Select Distinct Values

Group by MTID

Use the 3 formula method to create the data:

Group Header formula:
whileprintingrecords;
stringvar TheCountries:="";

Details level formula:
whileprintingrecords;
stringvar TheCountries:=TheCountries+{table.field}+", "

Group Footer formula:
whileprintingrecords;
stringvar left(TheCountries,len(TheCountries)-1)

-k

 

[lyanch]

HEre is one I do this with. This is the "details level formula" shown above for when "supress duplicates" doesn't work because of other fields etc.

Details Level:

shared stringVar Operators;

If InStr (Operators, {users.operator} ) = 0 then

Operators := Operators + iif(Operators <> "", ", ", "") + {users.operator}
else

Operators := Operators;


Lisa

 

[tekniks]

Sorry, if I was not clear in explaining things.
This is the data:
Lot_ID Request_ID Country
2 10
2 11 PR
2 12 PR
2 13
2 14
2 15 PR
2 16
2 17 US
2 18 US
2 19
2 20 PR

I tried to group by Lot_Id and Request_id separately with "format with multiple columns" checked but the results are same ---

This is how the data looks with Syn's formula:
PR, PR, PR, US,US, PR

This is how the data looks with Lisa's formula:
PR PR PR PR,US PR,US PR,US

The data should look like this:
PR,US


What are your suggestions for changes.

Thanks

Tek

 

[lyanch]

If you only want one PR,US you are including too many groups and resetting the formula too often. It looks like you would want to group only by Lot ID and not request ID.

Lisa

 

[lbass]

lyanch's solution should work. Group only on LotID, not on RequestID. Add her formula to the details section:

whileprintingrecords;
stringVar Operators;

If InStr (Operators, {users.operator} ) = 0 then
Operators := Operators + iif(Operators <> "", ", ", "") + {users.operator} else
Operators := Operators;

If you want the formula to reset on change of LotID, then create another formula {@reset} and add it to the LotID group header:

whileprintingrecords;
stringvar Operators := "";

Add the following formula {@display} to the group footer (LotID) section:

whileprintingrecords;
stringvar Operators;

Drag the groupname from the group header to the group footer section. Then suppress the group header and details section.

Do not format anything with multiple columns. You can format {@display} with "can grow" if you want it to wrap to additional lines.

-LB

 

[tekniks]

I am grouping on Lot ID and the information on Country has to be displayed in the Group Header for the LotID and not in the Details section as there are 2 other Group Headers before the Detail section can be printed.

Although the info has to be in the header yet the desired results are not being displayed inspite of putting the 3 formulas in GH, Detail and GF sections as suggested by Lisa and LB.(CR 8.5, Oracle 7.3.4)

Any more suggestions...?

Thanks

Tek

 

[lyanch]

There is no way to display this in the header. As you can see by tracking the output, it can't "get the data" until after it is displayed.

Lisa

 

[synapsevampire]

If you need to display this in the group header you'll require a subreport or perhaps a subquery.

A subquery would be dependent upon your Crystal and database version, which you still prefer to be top secret

So go with a subreport and use either approach to the string concatenation.

-k

 

[tekniks]

I have placed these formulas:

Group Header for LotID:
whileprintingrecords;
stringvar TheCountries:="";

Details Section(This comes after 3 Different Group Headers):
whileprintingrecords;
shared stringVar TheCountries;

If InStr (TheCountries, {CL_REQUEST_ST.COUNTRY_CODE} ) = 0 then
TheCountries := TheCountries + iif(TheCountries <> "", ", ", "") + {CL_REQUEST_ST.COUNTRY_CODE}
else
TheCountries := TheCountries;

Group Footer Section(LotID):
whileprintingrecords;
stringvar TheCountries;

and the result I'm getting is only -- US

Any suggestions why this is not working.

Thanks

Tek

 

[tekniks]

I also placed a sub-report in the Group Header for Lot ID.
The sub-report has Group Header(lot_id) and Detail Section in which only Country field is there. I place 3 formulas as suggested.

I get this result for the Lot_id=2 given earlier in the post:
PR PR PR,US PR,US

And for another Lot_id I get:
PR,US
although the actual database doesn't have a value US for that Lot_id.

And Syn, although I've already divulged the top-secret but let me do it once again -- CR 8.5 and Oracle 7.34

Thanks

Tek

 

[lbass]

You could display this in the group header by recreating the group and details in a subreport and then using the formulas and linking the subreport by the lotID.

You also might try inserting a crosstab in the lotID group header, using {table.country} as the column field and using maximum of {table.country} for the summary. You could then suppress the column labels, the row label and totals, and eliminate the grid. This would give you a unique display of countries per lotID, although they would not be comma separated.

If you have a limited number of possible countries, another approach would be to create a formula like the following:

if distinctcount({table.country},{table.lotID}) = 1 then
nthmostfrequent(1,{table.country},{table.lotID}) else

if distinctcount({table.country},{table.lotID}) = 2 then
nthmostfrequent(1,{table.country},{table.lotID}) + ", "+
nthmostfrequent(2,{table.country},{table.lotID}) else

if distinctcount({table.country},{table.lotID}) = 3 then
nthmostfrequent(1,{table.country},{table.lotID}) + ", "+
nthmostfrequent(2,{table.country},{table.lotID}) +", "+
nthmostfrequent(3,{table.country},{table.lotID}) else //etc.

You would have to account for the maximum number of countries. While you can place this in the group header, this will not return the results in alphabetical order.

-LB

 

[synapsevampire]

tek: Sorry, missed that version post

Your request is pretty basic, so I suspect that you're either doing something incorrectly, such as not resetting the variables, or we misunderstand the requirements.

If those are the only fields being displayed, and you've selected Database->Select Distinct records, you couldn't get repeated values.

-k