Avatar of Monterey
Monterey
 asked on

Concatenate First and Last Value in a Group in Crystal Reports

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.
Crystal Reports

Avatar of undefined
Last Comment
James0628

8/22/2022 - Mon
peter57r

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

ASKER
No we cannot...there ARE trips with only one leg...
Mike McCracken

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Monterey

ASKER
All the legs are numbered...every trip starts with leg 1 and continues from there.  How do you get it into the footer easily?
ASKER CERTIFIED SOLUTION
James0628

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
243johnm

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
James0628

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 McCracken

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
243johnm

James: you're right...

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

John
Your help has saved me hundreds of hours of internet surfing.
fblack61
Mike McCracken

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
Monterey

ASKER
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.
James0628

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mike McCracken

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
Monterey

ASKER
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!
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy