Crystal Reports 2011 - compare two different values having hours and minutes

Hi!

I'm currently creating a report that will list and show flighs for Aircrafts. For all of the Aircrafts you collect flighthours and flight cycles (landings) for each flight.
What I'm looking for now is a way to summarize each weeks generated flighthours and show that value in the format HH:MM.

The database table is called "flightunit_fh" and the field is called "total". I've used the formula
if {ACREG.MSN} = {FLIGHTLEG.MSN} then
maximum({FLIGHTUNIT_FH.TOTAL}) - minimum({FLIGHTUNIT_FH.TOTAL})
which obviously cannot calculate correctly since it's comparing the two values by calculating with hundreds, not seconds.

Can anyone help out and explain how I can be able to compare two different values for this field? I've set a restriction for the report to only calculate and show records for the lastfullmonth, hence the use of maximum and minimum as start/end for the summary.
Thanks in advance!
pitamanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pitamanAuthor Commented:
Example
Here is a quick look at the current list of flights..
0
mlmccCommented:
Try this

Create a formula to calculate the difference in minutes

Name - FlightLength
DateDIff('n',{TakeoffField},{TouchDownField})

CHange your formula to

if {ACREG.MSN} = {FLIGHTLEG.MSN} then
maximum({@FlightLength}) - minimum({@FlightLength})

The other way would be to convert the FH field to minutes then do the subtraction.
If you want to do it that way, what data type is the FH field?

mlmcc
0
pitamanAuthor Commented:
Hi!
Thanks for quick response. I've added the new formula and it works fine, calculating the exact number of minutes for each flight. My previous calculation will not work with this new data though, comparing the max number of minutes with the min number of minutes will not help me to get the total number of minutes / total diff between the first and the last record as the intention was before.

However, with this new way of gathering data, I can see that I now only need to sum up all those displayed minutes and then divide them into HH:MM. How can I do that in the simplest way?
I have create a summary, but do not succeed to show the value as valid HH:MM, it's just a summary of the minutes (of course). I've also made a new formula in which I use the sum for the FlightLength formula and then divides it with 60.

This also opens up a new question. The report has the aircraft registration as the group, then the flights are listed per date. Is there a way to bring along a summary for each aircraft and display this data on the last page?
2013-10-02-15-49-14.jpg
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

mlmccCommented:
WHat formula are you using to convert to HH:MM

It should be something like

CStr(Sum({@minutesFormula}) \ 60,0) & ":" & CStr(Sum(({@minutesFormula}) MOD 60,0)

mlmcc
0
pitamanAuthor Commented:
Super! Now it divides the minutes into perfect HH:MM - great!
The only trick I have left to solve is to reset this value for each new member of the group "MSN".

It seems like the report is now using all the aircrafts flights and shows the group total as the total for each individual. How can I reset the value for each new member of the group?

I also need to transport the values for msn (group value), FH and CY over to the last page (report footer) for a easy-to-see summary for the user. Do I have to create a lot of formulas that needs to be populated by each and every msn once the report goes them through?

Thanks in advance!
0
mlmccCommented:
Are you grouping on the member?

If so you can put the summary in the group footer

CStr(Sum({@minutesFormula},{memberfield)) \ 60,0) & ":" & CStr(Sum(({@minutesFormula},{memberfield}) MOD 60,0)

mlmcc
0
pitamanAuthor Commented:
Works like a charm - thanks!

The only little issue now is that one of the Aircrafts have produced 56 hours and 1 minute during the report selection period. The minute is shown as :1 instead of :01.
How can this be adjusted?
The formula is now looking like this:
CStr(Sum({@FlightLength},{flightleg.msn}) \ 60,0) & ":" & CStr(Sum({@flightLength},{flightleg.msn}) MOD 60,0)
0
mlmccCommented:
Try

CStr(Sum({@FlightLength},{flightleg.msn}) \ 60,0) & ":" & CStr(Sum({@flightLength},{flightleg.msn}) MOD 60,"00")

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pitamanAuthor Commented:
Super - many thanks! Case closed and report ready for use!
0
pitamanAuthor Commented:
Super fast response and accurate guidance!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.