How to combine multiple database rows into one line on the report

[borozu]

Crystal reports 2013

As per above screenshot - all rows have same LINE_TYPE = "D". For now the report uses DETAILS section SUPPRESS <> "D" but it prints everything on single lines as in the DB. How to combine everything into one single line?

[JWiseman]

Hi borozu, one way of doing this is via a string running total / string aggregation. Chances are your database has a string agg function in it, but you can also do this in Crystal Reports using a set of formulas.

e.g. For this example I'm assuming that you are grouping on that product name field.

1. Create 3 formulas using something like the following code below.
2. You can suppress the details level formula.
3. Note there is also a separator in the 2nd details formula which you can set to what you want...just adjust the 3rd formula if the separator is not 2 characters in length.

// group header formula...reset the stringvar at the group level
whileprintingrecords;
stringvar allDescriptions:= "";

// details section formula...stringvar does the aggregation
whileprintingrecords;
stringvar allDescriptions;
if instr(allDescriptions, {your description field}) = 0
and {your LINE_TYPE field} = "D"
then allDescriptions:= allDescriptions + {your description field} + ", ";

// group footer formula...display formula
whileprintingrecords;
stringvar allDescriptions;
if length(allDescriptions) > 2
then allDescriptions:= allDescriptions[1 to length(allDescriptions) -2];