totaling/summing problem 1

[ajdesigns]

I have a sales invoice report that calculates the no of boxes sent per item some items have different size boxes than others so i have to divide the ammount by ordered ammount then round up. ie ordered 160, box capacity 12 so 160/12=13.33 rounded up = 14 boxes.I then want to calculate total no of boxes sent for the complete order.This I cannot do, I have tried insert total/subtotal for boxesrounded but this is not available as a field although it is in the report.I have tried a formula ie: sum({boxesrounded}) but I get the message "the sum or running total could not be created".I have tried running total with no luck.Whhy is this formula not showing as an available field in order to insert a subtotal.

 

[lbass]

You need to share the formula you are using.

-LB

 

[ajdesigns]

sorry

First I have to split the field because it is a string formatted as 12PK, 36PK etc
split({stockm.despatch_units},"PK")[1]

then change it to a number
if isnumeric({@split units}) then
tonumber({@split units})
Else 0


if {@to_number}<>0
Then Sum ({@NEGATE QTY}, {stkhstm.lot_number}) / {@to_number}
Else
0


-{stkhstm.movement_quantity}

Local NumberVar RoundUp := {@boxes};
If Int(RoundUp)/2 = RoundUp/2 then RoundUp else Truncate(Roundup) + 1;
the report is contained as 3 groups ( no detail lines)
I cannot insert detail lines everything needs to be grouped.


grp 1 references
grp 2 item nos line qty item price line price
grp 3 lot info lot no, expiry, qty, boxes,commodity code country of origin.
I hope this helps

 

[lbass]

So is the first formula {@tonumber} and the second formula {@boxes}? And what is the content of {@Negate Qty}? The reason the formula is not available, is that you are using a summary in one {@boxes}.

Regardless of your answers, you could do the following. Let's say your round up formula is called {@roundup}. Create three formulas:

//{@reset} to be placed in the group header that corresponds to the order:
whileprintingrecords;
numbervar totalboxes := 0;

//{@accum} to be placed in the section where you have your roundup
//formula:
whileprintingrecords;
numbervar totalboxes := totalboxes + {@roundup};

//{@display} to be placed in the group footer corresponding to the order:
whileprintingrecords;
numbervar totalboxes;

-LB

 

[ajdesigns]

the @negateQty is there because I have to pull the qty from a stock history table which shows the amount as a movement against a sale therefore it is a negative so I have to use negate to make it a positive .

 

[ajdesigns]

Hi
This gives me a subtotal of the total boxes by group/product.But how do i get a grand total of all products sent, ie: the sum of @display

 

[lbass]

Add another variable that is not reset. Change {@accum} to:

whileprintingrecords;
numbervar totalboxes := totalboxes + {@roundup};
numbervar grtotboxes := grtotboxes + {@roundup};

Then add another display formula to be placed in the report footer:

whileprintingrecords;
numbervar grtotboxes;

-LB

 

[ajdesigns]

that works great thanks for all your help
AJD