Concatenate First and Last Value in a Group in Crystal Reports

Monterey
Monterey used Ask the Experts™
on
Hello,
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can we assume that there are no trips which have only one leg?

Author

Commented:
No we cannot...there ARE trips with only one leg...
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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.
mlmcc
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Author

Commented:
All the legs are numbered...every trip starts with leg 1 and continues from there.  How do you get it into the footer easily?
Is there 1 record per leg, with both the Origin and Destination in the same record (as opposed to separate "Origin" and "Destination" records with a single "location" field that contains the Origin or the Destination)?

 If so, I think you can get the Origin for the last Leg in the group header, using a bit of a trick.

 Create a formula like the following.  Let's call it leg_and_origin.  I'm assuming that the "Leg" field is numeric.

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


 That creates a string that combines the Leg and Origin.  If the Leg could have more than 5 digits, add more "#"s.  I'm assuming that it won't, but if it does, you need to have a "#" for each potential digit in the Leg number.  If the Leg number will never be more than 2 digits, you could change the "#####" to "##".  That would just remove some extra spaces from in front of the number, but since you're probably never going to be looking at that, extra spaces probably don't matter.  Anyway ...

 Maximum on that formula for a group will give you the last Leg in the group, with its Origin.  You could put that maximum in the group header to get the Origin for the last Leg in the group, but that would include the Leg number.  You can use a formula like the following to extract just the Origin.

Split (Maximum ({@leg_and_origin}, {Trip field}) + " --  -- ", " -- ") [ 2 ]


 Put that formula in the group header, and you should get the Origin for the last Leg in the group.


 For the Origin for the first leg, just put the Origin field in the group header, and make sure the records are sorted by Leg.

 James

Commented:
Two potential solutions from me: The first is that it might be easier to use Min(Origin) & Max(Origin) in the Group Header...

Commented:
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.
243johnm,

 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.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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...

mlmcc

Commented:
James: you're right...

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

John
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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.

mlmcc

Author

Commented:
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.
mlmcc,

 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.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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'

mlmcc

Author

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


if maximum({COSTING_DATA.LEG_SEQ},{COSTING_DATA.TRIP_NUMBER})=1
then {LEGSUM.LEGO_ZONE_DESC} & " to " & {LEGSUM.LEGD_ZONE_DESC}
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.  :-)

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial