We help IT Professionals succeed at work.

crystal reports

mkramer777
mkramer777 asked
on
81 Views
Last Modified: 2017-07-08
I have used Crystal Reports only a couple times but need some assistance with dividing two fields.  I have a Crystal report created and there are 2 fields:  1 is Miles and the other is Gallons.  What I want to do is divide these 2 fields and have it give the result in another field which I would label MPG.  WHat is the best way to do this?  I will probably need step by step instructions.
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Create a formula

In the FIELD EXPLORER right click FORMULAS
Click NEW
Name - Calc_MPG
Formula - note select fields from the fields in report tree
{MilesField} / {Gallons Field}

Open in new window

Click SAVE AND CLOSE
Drag the formula onto the report just like you do a database field
Format as desired

mlmcc

Author

Commented:
Now that I look at the report that will not work.  The problem is with the report summing  3 fields.    We enter this info on a form in our accounting system.  We always enter all miles into only one of these 3 fields but the report is using a sum of all three fields to give a result.  I will need to know how to divide the sum by the no. of gallons.
Screen-Shot-2017-03-20-at-8.43.06-AM.png
Screen-Shot-2017-03-20-at-8.45.23-AM.png
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
It will be something like this

(Sum(MilesField1) + Sum(MilesField2) + Sum(MilesField3)) / Sum(FuelField)

Open in new window


mlmcc

Author

Commented:
OK  I got that part, but now it gives me a division by zero number.

Author

Commented:
I'm sorry, it gives me a "division by zero" ERROR
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you that worked.  I have 1 more problem I just realized.  On the screenshot attached, look at the the field of "On Road" and go down the page until you find the number of 471.  That is the on road miles for this unit number.  The problem is the no. of gallons.  It shows "0"  That is not actually correct.  The only reason the report is showing "0" is because that was the 1st entry entered for that unit number (there were no gallons for the first entry).  In the accounting system their are 5 entries.  The report will only show the first.  Can you help me figure out how I can get the "no. of gallons" field to be a sum of all the gallons for each unit number?
Screen-Shot-2017-03-20-at-11.40.41-A.png
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Do you have a group on the equipment number or field?
If so show it as a sum over the group rather than the field

Sum({FuelField}, {Equipment Field})

Open in new window


If you don't have a group then add a group on the equipment field then do the sum

mlmcc

Author

Commented:
How do I check if I have a group?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Look at the report in design mode
If there are groups you will see GH1, GH2, etc
Hover over the group in the left margin and you will see the field it is based on

Another way is through the REPORT menu

REPORT --> GROUP EXPERT
Groups will be listed in order

mlmcc

Author

Commented:
Here is what it shows in group expert.   How would I write the sum now?
Screen-Shot-2017-03-21-at-7.48.05-AM.png
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are the 5 fuel entries in the same record or are they separate records?

You have a group on equipment.

mlmcc

Author

Commented:
Hope I can explain this.  On the form in the accounting system you enter in a sequence number and than the info for a piece of equipment.  After you are done you click on an arrow button and it drops you to the bottom of the page (maybe it's a subform) and you enter in the miles, and the number of gallons.  After that you click another arrow key it puts you at the top of the page and you enter another sequence number but if you have more entries for the piece of equipment you typed in previously you will enter those as well and keep doing this procedure until you are finished with that piece of equipment.   So you could have 5, 10, 15, etc... miles and gallons info to be entered for 1 piece of equipment.  Each time you enter in some information on that piece you will have to enter in a sequence number.  Sorry if this is confusing.   See screenshot.
Screen-Shot-2017-03-21-at-8.35.16-AM.png
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Without more information on the data structure I am working blind.

Try this
If Sum({FuelField},{Equipment}) = 0 then
    0
Else
    (Sum({MilesField1},{Equipment}) + Sum({MilesField2},{Equipment}) + Sum({MilesField3},{Equipment})) / Sum(FuelField,{Equipment})

Open in new window


mlmcc

Author

Commented:
I'm getting a bit confused now.  Do you know if experts exchange would have someone that could freelance for a fee to help me with this?  Or are you available for that sort of thing?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
There are several new features for that.
   GIGS - Full projects experts can provide estimates
   LIVE - Live help at a agreed on rate per hour - done through some terminal sharing app on EE

If you hover on the 3 dots next to ASK A QUESTION you will see the options

I won't be available until the weekend, fully engaged with a client right now.

mlmcc
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I don't know if any of the other Crystal experts are open to Gigs or Live help.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are you still looking for help with this?

I can be available later this week

mlmcc
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.