Get distinct comma separated list of values in Crystal Reports Footer

[MAW74656]

I have a report with many detail lines. For example:

Horse 123
Cow 453
Pig 123
Sheep 546

I need to put a summary at the bottom of the report, which in this case would read:

123, 453, 546

A comma separated list of values, WITHOUT repeats.

I don't care if I need to use sql expressions, Crystal Reports code, or vb code to do it, but I have to be able to print a comma separated list of distinct values.

I have tried doing this with an sql expression, and it will not work in the report but will from SQL Query Analyzer directly on the DB. Can a Crystal Reports sql expression field use the full range of SQL query commands? Or is it just a subset? What are the limits? Help on any of these questions would be much appreciated. I'm using Crystal Reports 10, btw.

[jggtz]

To do that, you have to Group your data and create 2 formulas:

The first one, let's say @comma_field:

whileprintingrecords;
stringvar names;
names := names & ", " & {table.field};

Place it in your GroupHeader section which you can suppress; the formula will return you next:
, Jules, Christina, Paul


and the secondformula, let's call it @final (or whatever you like):

whileprintingrecords;
stringvar names;
Mid(names, 3);

Place it in your Report Footer section.