Create a Comma Separated List in Crystal

When a field in the system allows for multiple values (such as Products and Services) you may want to display these items as a comma separated list instead of in one column.  

This is easiest to do on a sub-report that one manipulates the single value.  You need three Crystal formulas to accomplish this.  One will be placed in the header, one in the details and one in the footer.  For this example, we will use the products and services table (EV626_EVENT_PRODS) and display a comma separated list of all product/service codes for an event.

Formula 1

We will name this formula Create List and place it in the header of the subreport.  Inside the formula, we only need one line:

StringVar Products := "";

Formula 2

We will name this formula Build List and place it in the details section of the subreport.  It will be a repeat for every row of data.  Inside the formula, we only need a few lines:

StringVar Products;

Products:=Products &{EV626_EVENT_PRODS}.{EV626_PROD_CODE} &", ";

You'll notice that we had to re-declare the Products variable.  Even though we re-declare it, the previous value is still retained.  This is because the default variable type is a Global variable (see Using Variables in Crystal Reports for more information).

Formula 3

We will name this formula Display List and put it in the footer section of the sub-report.  

WhilePrintingRecords;

StringVar Products;

The line WhilePrintingRecords is necessary any time you create a formula that is not directly using a database field.  If we leave the formula like this, it will display our text, but with a trailing comma after the last entry.  To clean that up, you can use:

Left(Products,Length(Products)-2)

This trims off the last two characters from the end of the text.  In our case that would be a trailing comma and a space.

Once you've placed the formulas on the sub-report, you can suppress the header and details.  Only the list of values will display at the end.