DIVIDING BY TWO DIFFERENT SUMS

building reports crystal reports
1

Please forgive me if my terminology is not up to Crystal Report standards.

Given a group footer, when you have multiple sum boxes. One box gives you the sum of X and another box gives you Y. Can you create a third box and put in the formula box X / box Y ?

Please see attachement
Box 1
@Planned Total Service Time (String)
whileprintingrecords;
NumberVar SPlannedService;
upsltRunningTime (SPlannedService,false )

Box 2
@Total Size1 Planned Pickup (Number)
whileprintingrecords;

NumberVar PlannedStopPickup1;
NumberVar PlannedOrderPickup1;
NumberVar Size1P;
NumberVar SummarySize1P;

if {?UPSLT_CMN_MC_LEVEL_DETAIL}=“0” then
Size1P := PlannedStopPickup1
else if {?UPSLT_CMN_MC_LEVEL_DETAIL}=“1” then
Size1P := PlannedOrderPickup1
else if {?UPSLT_CMN_MC_LEVEL_DETAIL}=“2” then
Size1P := {#Lineitem Total Pickup Planned Size1};

SummarySize1P := SummarySize1P + Size1P;
Size1P

Custom Box 3:
@Total Size1 Planned Pickup / @Total Size1 Planned Pickup

There would be conversion problem, but would this logic work?
Crystal_Report.png

Martin.Lakin (BOB member since 2016-07-22)

2

The formula you were using for Box 3 would have given you the result for just the last row in the group. You will need to use:

Sum( @Total Size1 Planned Pickup,{insert your group column here} ) / Sum( @Total Size1 Planned Pickup,{insert your group column here} )

This will sum Box 1 (for the current group) then divide it by the sum of Box 2.

For best results you should be converting Box 1 to a number first (you can do this either in your original Box 1 formula or in the Box 3 formula - use CDbl or ToNumber to do the conversion).

Hope that helps!

Cheers
Daniel

what’stheplandan :australia: (BOB member since 2016-03-07)

3

Looking at what you typed, your division would always return a 1: @Total Size1 Planned Pickup / @Total Size1 Planned Pickup. But I assume that’s not what you want. :slight_smile:

Just a note about doing division of sums or counts - you always need to check the divisor to make sure it’s not zero before you do the division, otherwise Crystal will error out if that happens. So, I would rewrite Dan’s response as this:

If Sum(Box2,{insert your group column here} ) > 0 then
Sum(Box1,{insert your group column here} ) / Sum(Box2,{insert your group column here} )
else
0

-Dell

hilfy :us: (BOB member since 2007-04-16)

4

Box 1
@Planned Total Service Time (String)

-CODE-
whileprintingrecords;
NumberVar SPlannedService;
upsltRunningTime (SPlannedService,false )

//will this work for converting?
TONUMBER({@Plannted Total Service})

does the logic in sequence from top down ?

Martin.Lakin (BOB member since 2016-07-22)

5

Yes, it goes from the top down and the last thing in the formula is the value that’s provided as the formula’s result.

-Dell

hilfy :us: (BOB member since 2007-04-16)

6

is there a Crystal Report for Dummies book you’d recommend?

Martin.Lakin (BOB member since 2016-07-22)

7

Honestly, I’ve been using Crystal for @20 years (since version 4.5), so I’m not familiar with what’s currently available for Crystal books.

-Dell

hilfy :us: (BOB member since 2007-04-16)

8

if you purchase book please read only notes and tips in italic which will give you high level idea and limitations which may cause problems

surya.g :india: (BOB member since 2009-11-24)