Improving Syntax on this formula.

Crewdawg · Posted: Fri Oct 28, 2005 2:38 pm Post subject: Improving Syntax on this formula.

First off I want to say bear with me on this. I may leave out some key piece of information here so just post what other information you need to understand what I'm showing you.

This report shows our "Planned" and "Actual" times on individual jobs on a Work Order. It has a line for each job and then there is a Running Total on the bottom of the page. The problem is the database saves this "Time Field" (expressed as hh.mm) as a "Number Field". For example if we planned to put 3 hours and 30 minutes on a job the database stores this as 3.3. If we actuallu put 3 hours 37 minutes on the job the database stores this as 3.37. The formula I have included below works for a good estimate and tends to be fairly accurate when dealing with small workorders or individual numbers. The majority of work orders in which I need to use this report include a 1000 individual jobs. So after round several times on each line, and then doing a running talley on these 1000 lines, you can see why the reports can be hundreds of hours off when compared to the hard numbers.

The WOMNT_CARD.STANDARD_HOURS_DURATION field is where the database stores the "Planned Hours" for each particular card. The formula below is used on a report field named @PlannedMinutes (Number Field).

Round(Round ({WOMNT_CARD.STANDARD_HOURS_DURATION}, 0) + ((Remainder ({WOMNT_CARD.STANDARD_HOURS_DURATION}, 1) *100) / 60), 2)

I hope this makes sense to those reading. Your sugestions are greatly appreciated. As you can see by the images below the Planned total is only 18 hrs off, but the Actual total is 170hrs different.

This image is an example of what my Crystal Report generates.

This is an example of what my internal system generates.

_________________
----------------------------------------------------
Multithreading is just one thing after, before, or simultaneous with another.

kgroff · Site Admin

Try something like:

truncate({WOMNT_CARD.STANDARD_HOURS_DURATION})+(({WOMNT_CARD.STANDARD_HOURS_DURATION}-truncate({WOMNT_CARD.STANDARD_HOURS_DURATION}))*100/60)

Crewdawg · Posted: Thu Nov 17, 2005 4:41 pm Post subject: Solution

Between your post and another post on tek-tips.com I was able to quickly solve my problem and improve my report. Thanks all to contributed. here is a quote from user LBASS at Tek-Tips.