Summarizing a fomula that includes a multiplication

[mbutch]

I'm trying to find our stocking value by multiplying the stocking level for a product times it's cost, which is easy enough, but I can't get it to summarize the results to get a grand total. I'm using SCR 8 and a VFP database.

 

[dcm]

mbutch: This should be straightforward. Can you detail your formula so that we might see why it won't summarise. David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[mbutch]

Group 1 (Vendor)
Group 2 (Product number) (Low Level) (In Stock) (Cost) (Low Level * Cost)
Details(Suppressed)
Group 2(Suppressed)
Group 1 Sum (Low Level * Cost)
Report Footer Sum (Low Level * Cost)

Like I said, I can't get the Sum function to work for low level * cost

 

[kenhamady]

If you create a formula called {@TotCost} which has the formula Level*Cost

You can put this field on the detail band, highlight it (again on the detail band) with your mouse and select Insert-Grand Total or Insert-Subtotal.
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[mbutch]

No such luck. The Subtotal and Grand Total functions are still greyed out, and if I right click on the TotCost field the choice is not there.

 

[MalcolmW]

Just copy the text of the formula TotCost into your next post on this thread. Malcolm

 

[IdoMillet]

I suspect either "low level" or "cost" (or both)
are already aggregate functions.

Crystal can't do totals of totals, but there
are several ways to overcome this limitation.

Please confirm that this is indeed the situation
and explain the nature of the totals (minimum,
sum, ?). We can then advise you on the best approach.

hth,
- Ido

 

[mbutch]

<b>12weeksales</b>
if ({items.item_state} = &quot;CM&quot; or {items.item_state} = &quot;PI&quot; or {items.item_state} = &quot;PS&quot; or
{items.item_state} = &quot;SH&quot; or {items.item_state} = &quot;SV&quot; or {items.item_state} = &quot;BO&quot and {cms.odr_date} >= CurrentDate - 84 then {items.quanto} else 0

<b>12weekavg</b>
Sum ({@12weeksales}, {stock.number}) / 12

<b>leadtime</b>
if NumericText({supplier.note3}[1 to 2]) then ToNumber({supplier.note3}[1 to 2]) else 2

<b>new low level</b>
{@12weekavg} * {@lead time}

<b>reorder level</b>
Round ({@new low level} * 1.25)

<b>final low level</b>
if {@reorder level} >= .5 then {@reorder level} else
if Sum ({@1yearsales}, {stock.number}) >= 4 then 1 else
if Sum ({@12weeksales}, {stock.number}) >= 2 then 1 else 0

<b>low level stocking value</b>
{@final low level} * {buyprice3.unit_cost}

There are a number of formulas used to get the final low level, but I also just tried making a simple multiplication (1*2)and then summing it, and I still couldn't do it.

 

[dcm]

mbutch: As Ido has stated before you cannot use a formula within a summary if it relies upon a summary itself and it appears that deep down your low_level does indeed rely upon a summary in either final_low_level or 12weekavg. You may have to consider re-engineering this so that the initial calculations are written to disk (i.e. report is exported) and then read into a subsequent report as data e.g. look at exporting first report to ODBC then incorporating CREXPORT table into the second report - you'll be able to summarise and summary then! David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[kenhamady]

This is too deep to get specific, but if you need to sum values that rely on summaries, you can do it using a variable running total. See the FAQ on running totals and use the 3-formula technique. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)