Summing the results of a formula from one group to another 1

[Zemlya]

I have a report written where there are details, group 2 and group 1. I have used a formula which gets me an average result for the details but eliminates zeros. This result is in Group 2 and is derived from details. I accomplished this using three different formulas.
I now need to sum these 'averages' and put the result into group 2 (and then also for toal company). Crystal will not allow me to use the summation tool and an overall average here is no good.
Can someone please help me figure out a way to get the result I need. You can post here, or email me kmurray@fizzbizz.com
Thanks

 

[kenhamady]

You will have to use a running total.

See the FAQ on running totals, and use the 3-formula technique. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Zemlya]

I have looked at the frequently asked questions on this forum but haven't seen anything that can help me. Let me give more of an example of what I am trying to do.

I have 5 delivery routes as follows.
1101
1102
1103
5836
5837
These routes are made of two combined fields. Depot (first two digits) and Route Code (last two digits). The Depot is Group 1 and the Route Code group 2.
Within the delivery route there are item ids which I need for another aspect of my report (in details section of report). There can be different item quantities on each route and may or may not have an original quantity.
I have a field called carrier id which is R for relay and B for Bulk.
I want to assign an average milage to the delivery routes based on if they are relay or bulk. I used the formula

FORMULA 1
if {ROUTE_BULK_PROFITABILITY.QTY_ORDERED_ORIG}=0 then 0 else
if {ROUTE_BULK_PROFITABILITY.CARRIER_ID} = 'B' then (362*.6) else (207*.6)

It applies this formula to each detail line within the delivery route. I want it to return a zero if there was no original quantity because

I then use another formula FORMULAS 2 to assign a value of 1 if there is an original quantity and zero if there was none.

I then sum these two formula's and divide the answer to FORMULA 2 into the answer for FORMULA 1. This gives me the correct milage for the route, Group 2.....there may be an easier way to do this but I don't know.

I now need to sum the values for all those routes in one Depot. This is where I am stuck because I cannot sum the formulas.....

When I get this problem solved I know that I am going to run into a similar issue with the total company calculation.

Any help would be greatly appreciated.

Zemlya

 

[MalcolmW]

You will have to use variables to do this. The variable DepotTotal will have to be reset to zero in another formula that you will have to place in the group header for depot.

//FORMULA 1
Global NumberVar DepotTotal ;
Global NumberVar GrandTotal ;
Local NumberVar FormulaResult
if {ROUTE_BULK_PROFITABILITY.QTY_ORDERED_ORIG}=0 then
  FormulaResult := 0
else
  if {ROUTE_BULK_PROFITABILITY.CARRIER_ID} = 'B' then
    FormulaResult := (362*.6) 
  else 
    FormulaResult := (207*.6) ;
DepotTotal := DepotTotal + FormulaResult ;
GrandTotal := GrandTotal + FormulaResult ;
FormulaResult 
//End of Formula 1

Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[Zemlya]

ok,
I know that I am needing alot of hand holding here but...

I made a new formula and typed in exactly what is above from Malcolm. Crystal will not accept the first line, it says that the remaining text does not appear to be part of the formula. Zemlya

 

[Zemlya]

Alright, I've been trying to figure this out.

I found what was wrong with the formula Malcolm wrote, so now I have it working. To be honest however I am still not sure what this is doing to help me out !? But I am getting a result of some kind... Zemlya

 

[MalcolmW]

Let us know what the bugs are - just looking at what I wrote, I can now see I forgot a semicolon after
Local NumberVar FormulaResult
Was there something else?
Do you have a formula in the report footer to dispay the result?
//Show GrandTotal
WhilePrintingRecords ;
Global NumberVar GrandTotal
//End
You will need something similar for the group total, but placed in the group footer. Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[Zemlya]

And here I tought I knew Crystal.
I am not in work today but this killed me Friday. I still think that this adds up every value for all detail lines which is not the answer I need. I need it to forget about the detail lines and sum the group average I get for these lines. IE sum the calculated values in group 2 to give me a result in group 1 and also a report grand total.

Thanks for all the help though Zemlya

 

[Zemlya]

Malcolm,

It does not like the Global or Local statement in from of the Variable declaration. Zemlya

 

[Zemlya]

I got it to work.

I used the resettotal function in the group1 header, the running total function in the group2 footer, and the print subtotal function in the group1 footer. This worked for the total report also...It's been a long week but thanks to everyones help I got it done Zemlya

 

[MalcolmW]

Sorry Zemlya - been really busy...
Regarding Global or Local - these key words are used in declaring variables in V8 of SCR. For more details, see the FAQ on variables.

Variables are useful tools - it is worth getting familiar with them, as they can give you a lot of control. And perhaps make your week shorter Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?