Link to home
Start Free TrialLog in
Avatar of Monterey

asked on

Concatenate First and Last Value in a Group in Crystal Reports

I am grouping a report by Trip # which have Legs (not all trips have the same number of legs).  I would like to show the Origin of Leg 1 of a trip and the Origin of the last leg of a trip in the Trip # header.  Any ideas?   Here's what the data looks like (roughly):

Trip #3583
                          Origin              Destination
     Leg 1             CA                   OR
     Leg 2             OR                   WA
     Leg 3             WA                  CA

I would like the Trip #3583 to read CA to WA (origin of first leg and origin of last leg).

Any help would be greatly appreciated.
Avatar of peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Can we assume that there are no trips which have only one leg?
Avatar of Monterey


No we cannot...there ARE trips with only one leg...
That will require you to use

1.  A subreport to build it
2.  Some fancy SQL as a command or stored procedure to build it

Crystal works on 1 record at a time so trying to use information from a record yet to come won't work.  It can look 1 record ahead (next) and 1 record back (previous) but that doesnt help except in special circumstances

Are the legs numbered in the database?

I can get it into the group footer easily.
All the legs are numbered...every trip starts with leg 1 and continues from there.  How do you get it into the footer easily?
Avatar of James0628

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Two potential solutions from me: The first is that it might be easier to use Min(Origin) & Max(Origin) in the Group Header...
The other is a bit more complex. If the Min(Origin) & Max(Origin) doesn't work, then a pair of running totals should work. The first would store the value in the first Origin field it encountered while the second would change according to the value in the Origin field. You could then print them out in the GH.

 Minimum (Origin) and Maximum (Origin) won't work.  They do in that example, because the origins happen to be in alphabetical order (CA is first and WA is last).  But if, for example, those legs were reversed -- CA - WA, WA - OR, OR - CA -- min would get you CA, which is correct, but max would get you WA, which is wrong.  The last origin is OR.

 And running totals won't work, because they are produced as the records are read.  A running total that involves anything more than the first record in a group won't have its final value in the group header.  You might be able to use them in the group footer (I've never tried to use one to do something like this).  FWIW, you wouldn't need a running total for the final Origin.  Just put the Origin field in the group footer and you'll get the last Origin in the group.  But you might be able to use one to display the first Origin in the group footer.

A running total also won't work because the calculation won't be available in the group header.

James solution will work so long as all numbers are the same length.  Use

CStr ({Leg field}, "00000") + " -- " + {Origin}

That will give all numbers leading 0's so legs 10, 11 will sort after 2,3,4...

James: you're right...

mlmcc: check CR docs on processing times: anything available in the group footer is also available in the header

Not a running total.  They are calculated in the printing pass and done as each record is printed.  Thus the final value for the group isn't known until the last record for the group is printed.  At that point you can't go back to the group header and print it.

All very interesting comments...I appreciate the time spent on this matter.  I did try the running total and the min/max option and they did not work.  I will try the other option today posted by James0628 and let you know how it goes...Again many thanks for the time.

 Just being picky, but I did mention that a running total wouldn't work in the group header in my earlier post.  :-)

 And the OP could use a "00000" format in the CStr, but the "#####" that I used should work just as well.  You get leading spaces, instead of 0's.  Works the same either way (unless I'm missing something basic).  FWIW, I actually started with 0's when I was testing the idea, but changed it to #'s because I preferred the numbers without the leading 0's, and the Maximum still worked fine.  But if you're not actually going to look at the combined string, it doesn't matter if you've got leading 0's or spaces.

Ok.  I only looked at the result and spaces are "invisible".  If it does add spaces then that should work since a space is "less than" a  '0'

With help from all comments, I found my answer and it works!

else split({@Leg+Origin},"--")[2] & " to " & split(maximum({@Leg+Dest},{COSTING_DATA.TRIP_NUMBER}),"--") [2]

Thank you all so much and especially James0628!
You're welcome.  Glad I could help.

 FWIW, the " --  -- " that I appended to the maximum in my Split formula was just a precaution.  If you somehow ended up with a maximum that did not include the "--" delimiters (maybe if the group only included one record, which contained a null in one of those fields), Split would produce 1 element and you'd get an error on the [ 2 ] index.  I added the "--"s to hopefully avoid that.  One set should be enough, but I figured if I was adding 1, I might as well add 2, just to be extra sure.  :-)