MINUS BALANCE

You do this sort of thing using Groups in the report.
I assume you have ID numbers that identify each PO, DO and SJ.
You would then insert a group using the PO ID, and insert  a second group using the DO ID

You then use Insert >Summary to add a Sum of qtySj to each group level.
You can also Insert>Summary to add the Sum of QtyDO to the PO group.

You can then create formula fields ...in the PO Group Header or Footer you can create a formula which is
QTYPO  - <the sum of QtyDo you added above>

and in the DO group header/footer you can create a formula field to calculate the other balance..
QtyDO - <Sum of SJ you added above>

When you are creating the Balance formula fields, you will see that each of the summary values appears in the field list and you can select them just as any other field to create the expression you need.

You might need to reposition some of the fields between group headers and footers and you can suppress any sections that you dot want to see.

Please see the screen shot.

Should also group for DO ?

Thank you.
DO-Balance-Minus.PNG

You need to group if the DO can have several related records.  In this case I think the answer is yes, group on DO also.

mlmcc

How if I don't want to make it as group, since it will show at tree view at the left side of the report ?

What should I do ?

Thank you.

You can sort by it but you won't have the group header/footer to use for the totals

mlmcc

Hi mlmcc,

Yes, I have sorted it.
But the problem is the running total has minus result, since the DO Qty has 2 details.

Thank you.

How are you doing the subtraction?

mlmcc

-How are you doing the subtraction?
{@POQty}-{#TotalQtyDO}

Where #TotalQtyDO is running total.

Thank you.

What is @POQty?

mlmcc

In your screenshot, there is a -600 in the SISA/DO column.  There is a 0 in that column on the line above that.  Is that 0 correct?  I think maybe it should be 400 (the same as the SISA/SJ column).

 If the line above should be 400, instead of 0, it looks like maybe the field used by your #TotalQtyDO running total is 600 in both of those last two lines (Maybe because they were part of the same shipment or something?), so the total of 600 for those two lines gets added to the running total twice (once for each line).

 If that's correct, I'm not sure how to fix it, unless there's another field that you can use for your running total.  The SJ/Qty column seems like an obvious choice, but it's probably not that simple.

 James

Hi mlmcc,

-What is @POQty?
{QtyPo}
At ItemCode Group Header.

Thank you.

Hi James,

-In your screenshot, there is a -600 in the SISA/DO column.  There is a 0 in that column on the line above that.  Is that 0 correct?  I think maybe it should be 400 (the same as the SISA/SJ column).
Yes.

- If the line above should be 400, instead of 0, it looks like maybe the field used by your #TotalQtyDO running total is 600 in both of those last two lines (Maybe because they were part of the same shipment or something?), so the total of 600 for those two lines gets added to the running total twice (once for each line).
Yes, because it is POQty - PODo

How to solve this problem ?

Thank you.

Hi All,

May be we could create a formula to set the QtyDO to 0, when it is the same with the above data. Like suppressed if duplicated.

How about that ?

Thank you.

Should that line above be 0 or 400?  I'm not sure which one you were saying "Yes" to.  :-)

 If the 0 on the line above is correct and the only problem is that the last line has -600, should that column ever be negative?  If not, maybe you could simply change the formula from

{@POQty}-{#TotalQtyDO}

   to

if {@POQty}-{#TotalQtyDO} > 0 then
  {@POQty}-{#TotalQtyDO}
else
  0


 James

Yes, the new formula should work.
But I think this is not a good way to do it.
How if the QtyDO might be around  10%  < QtyPO > 10 %.

Thank you.

Sorry, but I'm not sure what you mean by that.

 James

Some company has policy that vendor my send item with 10% tolerance.
Thus if Qty PO is 100, vendor is allowed to send 90 or 110.

Thank you.

I don't fully understand your data or report structure, which is why my last suggestion was really just a brute force approach to try to avoid seeing negative results.  :-)

 So, I am not at all sure if this is what you're looking for, but you can give it a try.

if {@POQty}-{#TotalQtyDO} > 0 or
 Abs ({@POQty}-{#TotalQtyDO}) <= ({@POQty} / 10) then
  {@POQty}-{#TotalQtyDO}
else
  0


 That says if the difference is positive, or if the difference (positive or negative) is <= 10% of @POQty, then show the difference.  Otherwise, show 0.

 James

Hi James,

Could we create a formula to hold DONo, say its name is @LastDONo ?

Then If {DoNo} <> @LastDoNo
@LastDONo := {DoNo }
{@POQty}-{#TotalQtyDO}
ELSE
@POQtyDOBalance

Something like that.

Thank you.

You can't assign a value to a formula, so you can't use something like
{@LastDONo} := {DoNo}

 You could save the value from the field in a variable, but you may not need one.  You can use the Previous function to get the value for a field from the previous record.  If you're going to use Previous, I would also use OnFirstRecord, because I'm not sure what Previous gives you when you're on the first record and there is no previous record.  So, it might be something like this:

If OnFirstRecord or {DoNo} <> Previous ({DoNo}) then
{@POQty}-{#TotalQtyDO}
ELSE
{@POQtyDOBalance}


 James

Hi James,

I've tried it.

The formula can not refer to itself, either directly or indirectly.

Thank you.

I was kind of wondering about that.  You had

ELSE
@POQtyDOBalance


 I just accepted that as what you wanted, but if what you're working on is the @POQtyDOBalance formula, then obviously that won't work (and wouldn't make any sense).

 Assuming that what you're looking at is the @POQtyDOBalance formula, then what do you really want after the Else?

 James

-  Assuming that what you're looking at is the @POQtyDOBalance formula, then what do you really want after the Else?
I mean to show the last balance of @POQtyDOBalance , suppose it is 0 then just show zero.

Now, since it has error then I omit the else :

If OnFirstRecord or {DoNo} <> Previous ({DoNo}) then
{@POQty}-{#TotalQtyDO}

Ant the result = 2.145, same as QtyPO.

The other problem is the sum of QtyDO at group footer. The value is exceed 600 as we know it.

Thank you.
DO-Balance-Minus-01.PNG

I'm sorry, but I really don't know what you're looking for at this point.

 You said "show the last balance of @POQtyDOBalance", but does "last" mean the final value for that group, or the value from the previous record, or something else?

 And you said that with the new version of the formula, the result is 2,145, but is that right or wrong?

 It might help if you could post another screenshot, but highlight the numbers that are wrong, and say what they should be, and why.

 It would probably also help if you could post the report (.RPT file), so that I could see the report structure and formulas for myself.

 James

- You said "show the last balance of @POQtyDOBalance", but does "last" mean the final value for that group, or the value from the previous record, or something else?
I mean the final value for that group which is @POQtyDOBalance.

- And you said that with the new version of the formula, the result is 2,145, but is that right or wrong?
Wrong, should be 0, same as above record.

-It might help if you could post another screenshot, but highlight the numbers that are wrong, and say what they should be, and why.
I do now. Please have a look at it.

- It would probably also help if you could post the report (.RPT file), so that I could see the report structure and formulas for myself.
Please have a look.

Thank you.
DO-Balance-Minus-01.PNG
rptLaporanRekapPODanPengirimanv2.rpt

- Edit the #TotalQtyDO running total.  In the Evaluate section, select the "On change of field" option, and select the NoDO field.

I can not select NoDO field, since it is not a group.
We can just type in textbox beside on change field.

Thank you.

It doesn't have to be a group.  Make sure that you're selecting "On change of _field_", not "On change of group".  I had absolutely no problem making that change in the report that you posted.

 If you can have more than one NoDO for a single ProdName, then the report should also be sorted by NoDO.  I didn't think of that before.  Just add a sort (not a group) on NoDO.  The records will be sorted by NoDO within the ProdName group.

 There is an assumption here that you won't have the same NoDO for different ProdName values.  If the same NoDo could appear in more than one ProdName group, the running total may need to be changed, so that it resets the total at the correct times.

 James

I am sorry, that textbox is uneditable, should use arrow to feed it from dataset.
It works now for the balance, but not for the sum total.

Thank you.
DO-Balance-Minus-02.PNG

I'm not sure what you're trying to edit.  Just delete the sum from group footer 4 and put #TotalQtyDO there instead.

 James

-I'm not sure what you're trying to edit.  Just delete the sum from group footer 4 and put #TotalQtyDO there instead.
I  mean "On change of _field"

- Just delete the sum from group footer 4 and put #TotalQtyDO there instead.
Great, it works now.

Thank you very much for your help.

Ah.  I see.  I've never tried to edit that field in a running total, since selecting something from the list is easier.  So I didn't realize that you couldn't edit it.  I thought you were trying to edit a field on the report.

 I'm glad you were finally able to get it working.  You're welcome.

 James