CODEWITHSUNDEEP
crystal-reports
playrox
playrox

Reputation: 11

Consolidate duplicate entries into one row

My report looks like:

Number   Route    Transportnumber
1         A            1A
2         B            2B
3         C            3C
3         C            3CE

How can I get the information from number 3 into a single row? It should look like:

Number   Route    Transportnumber
1         A            1A
2         B            2B
3         C            3C, 3CE

I tried the following formula in a stored procedure: 

whileprintingrecords;
stringvar s;
if s="" then
     s:={sp.Transportnumber}
else
     s:=s+", "+{sp.Transportnumber};

This instead results in: 

Number   Route    Transportnumber   Formula
1         A            1A              1A
2         B            2B              2B
3         C            3C              3C
3         C            3CE             3C, 3CE

Upvotes: 1

Views: 135

Answers (1)

4444
4444

Reputation: 3680

Inside your report, create a Group based on Route. Then hide the Group Header and Details section. Instead, put all the fields you want to see in the Group Footer. This step ensures that even if Routes repeat, you only ever see one of each letter.

Then create a formula with a shared variable, called RouteTransports. Place it in your Details section. Whenever the group changes, reset it with RouteTransports = "". Whenever the group doesn't change, add the value of Transportnumber to your variable. Something like:

RouteTransports = RouteTransports & ", " & {yourtable.Transportnumber}

Finally, create a final formula ShowRouteTransports in the Group Footer to display the results with RIGHT(RouteTransports, LEN(RouteTransports)-2) The end result will look like this:

Number   Route    Transport Number
1        A        1A
2        B        2B
3        C        3C, 3CE

Upvotes: 1

Related Questions